pgmoon

pgmoon is a PostgreSQL client library written in pure Lua (MoonScript).

pgmoon was originally designed for use in [OpenResty][] to take advantage of the cosocket api to provide asynchronous queries but it also works in any regular Lua environment where [LuaSocket][], [cqueues][], or [LuaPosix][] is available.

It's a perfect candidate for running your queries both inside OpenResty's environment and on the command line (eg. tests) in web frameworks like [Lapis][].

Install

$ luarocks install pgmoon

<details> <summary>Using <a href="https://opm.openresty.org/">OpenResty's OPM</a></summary>

$ opm get leafo/pgmoon

</details>

Dependencies

pgmoon supports a wide range of environments and libraries, so it may be necessary to install additional dependencies depending on how you intend to communicate with the database:

Tip: If you're using OpenResty then no additional dependencies are needed (generally, a crypto library may be necessary for some authentication methods)

A socket implementation is required to use pgmoon, depending on the environment you can choose one:

• [OpenResty][] — The built-in socket is used, no additional dependencies necessary
• [LuaSocket][] — luarocks install luasocket
• [cqueues][] — luarocks install cqueues
• [LuaPosix][] — luarocks install luaposix (Unix sockets only via socket_path)

If you're on PUC Lua 5.1 or 5.2 then you will need a bit library (not needed for LuaJIT):

$ luarocks install luabitop

If you want to use JSON types you will need lua-cjson

$ luarocks install lua-cjson

SSL connections may require an additional dependency:

• OpenResty — luarocks install lua-resty-openssl
• LuaSocket — luarocks install luasec
• cqueues — luarocks install luaossl

Password authentication may require a crypto library, [luaossl][].

$ luarocks install luaossl

Note: [LuaCrypto][] can be used as a fallback, but the library is abandoned and not recommended for use

Note: Use within [OpenResty][] will prioritize built-in functions if possible

Parsing complex types like Arrays and HStore requires lpeg to be installed.

Example

local pgmoon = require("pgmoon")
local pg = pgmoon.new({
  host = "127.0.0.1",
  port = "5432",
  database = "mydb",
  user = "postgres"
})

assert(pg:connect())

local res = assert(pg:query("select * from users where status = 'active' limit 20"))

assert(pg:query("update users set name = $1 where id = $2", "leafo", 99))

If you are using OpenResty you can relinquish the socket to the connection pool after you are done with it so it can be reused in future requests:

pg:keepalive()

Considerations

PostgreSQL allows for results to use the same field name multiple times. Because results are extracted into Lua tables, repeated fields will be overwritten and previous values will be unavailable:

pg:query("select 1 as dog, 'hello' as dog") --> { { dog = "hello" } }

There is currently no way around this limitation. If this is something you need then open an issue.

Reference

Functions in table returned by require("pgmoon"):

new(options={})

Creates a new Postgres object from a configuration object. All fields are optional unless otherwise stated. The newly created object will not automatically connect, you must call connect after creating the object.

Available options:

• "database": the database name to connect to required
• "host": the host to connect to (default: "127.0.0.1")
• "port": the port to connect to (default: "5432")
• "user": the database username to authenticate (default: "postgres")
• "password": password for authentication, may be required depending on server configuration
• "ssl": enable ssl (default: false)
• "ssl_verify": verify server certificate (default: nil)
• "ssl_required": abort the connection if the server does not support SSL connections (default: nil)
• "socket_type": the type of socket to use, one of: "nginx", "luasocket", "cqueues" (default: "nginx" if in nginx, "luasocket" otherwise)
• "socket_path": path to Unix socket (e.g. "/var/run/postgresql/.s.PGSQL.5432"); when set host and port are ignored. In nginx, connects using unix:/path syntax; otherwise uses luaposix
• "application_name": set the name of the connection as displayed in pg_stat_activity. (default: "pgmoon")
• "pool_name": (OpenResty only) name of pool to use when using OpenResty cosocket (default: "#{host}:#{port}:#{database}:#{user}")
• "pool_size": (OpenResty only) Passed directly to OpenResty cosocket connect function, see docs
• "backlog": (OpenResty only) Passed directly to OpenResty cosocket connect function, see docs
• "cqueues_openssl_context": Manually created opensssl.ssl.context to use when created cqueues SSL connections
• "luasec_opts": Manually created options object to use when using LuaSec SSL connections

Methods on the Postgres object returned by new:

postgres:connect()

local success, err = postgres:connect()

Connects to the Postgres server using the credentials specified in the call to new. On success returns true, on failure returns nil and the error message.

postgres:settimeout(time)

postgres:settimeout(5000) -- 5 second timeout

Sets the timeout value (in milliseconds) for all subsequent socket operations (connect, write, receive). This function does not have any return values.

The default timeout depends on the underlying socket implementation but generally corresponds to no timeout.

postgres:disconnect()

local success, err = postgres:disconnect()

Closes the socket. Returns nil if the socket couldn't be closed. On most socket types, connect can be called again to reestablish a connection with the same postgres object instance.

postgres:keepalive(...)

postgres:keepalive()

Relinquishes socket to OpenResty socket pool via the setkeepalive method. Any arguments passed here are also passed to setkeepalive. After calling this method, the socket is no longer available for queries and should be considered disconnected.

Note: This method only works within OpenResty using the nginx cosocket API

postgres:query(query_string, ...)

-- return values for successful query
local result, err, num_queries = postgres:query("select name from users limit 2")

-- return value for failure (status is nil)
local status, err, partial_result, num_queries = postgres:query("select created_at from tags; select throw_error() from users")

Sends a query (or multiple queries) to the server. On failure the first return value is nil, followed by a string describing the error. Since a single call to postgres:query can contain multiple queries, the results of any queries that succeeded before the error occurred are returned after the error message. (Note: queries are atomic, they either succeed or fail. The partial result will only contain succeed queries, not partial data from the failed query)

<details> <summary>Additional return values: notifications and notices</summary>

---

In addition to the return values above, pgmoon will also return two additional values if the query generates them, notifications and notices.

local result, err, num_queries, notifications, notices  = postgres:query("drop table if exists some_table")

In this example, if the table some_table does not exist, then notices will be an array containing a message that the table didn't exist.

---

</details>

The query function has two modes of operation which correspond to the two protocols the Postgres server provides for sending queries to the database server:

• Simple protocol: you only pass in a single argument, the query string
• Extended protocol: you pass in a query with parameter placeholders ($1, $2, etc.) and then pass in additional arguments which will be used as values for the placeholders

See Extended and simple query protocol for more information about the differences and trade-offs.

On success, the result returned depends on the kind of query sent:

SELECT queries, INSERT with returning, or anything else that returns a result set will return an array table of results. Each result is a hash table where the key is the name of the column and the value is the result for that row of the result.

local res = pg:query("select id, name from users")

Might return:

{
  {
    id = 123,
    name = "Leafo"
  },
  {
    id = 234,
    name = "Lee"
  }
}

Any queries that affect rows like UPDATE, DELETE, or INSERT return a table result with the affected_rows field set to the number of rows affected.

local res = pg:query("delete from users")

Might return:

{
  affected_rows = 2
}

Any queries with no result set or updated rows will return true.

When using the simple protocol (calling the function with a single string), you can send multiple queries at once by separating them with a ;. The number of queries executed is returned as a second return value after the result object. When more than one query is executed then the result object changes slightly. It becomes an array table holding all the individual results:

local res, num_queries = pg:query([[
  select id, name from users;
  select id, title from posts
]])

Might return:

num_queries = 2

res = {
  {
    {
      id = 123,
      name = "Leafo"
    },