gem-hunter

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.

$ luarocks install pgmoon

$ opm get leafo/pgmoon

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.

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()

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.

A Postgres connection is not safe to share between coroutines that may run concurrently. pgmoon tracks an internal busy flag for the duration of connect, query, disconnect, keepalive, and wait_for_notification; if any of these is invoked while the connection is already mid-operation (for example, from another coroutine that yielded mid-query) it will raise a "pgmoon: connection is busy" error rather than silently corrupting the protocol stream. If you need to issue queries from multiple coroutines, use PostgresPool instead of sharing a single connection.

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

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). With the nginx socket type, the certificate is also checked against host unless host is an IP address, in which case only the certificate chain is verified
• "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:

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(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.

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()

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

-- 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)

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.

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"
    },
    {
      id = 234,
      name = "Lee"
    }
  },
  {
    {
      id = 546,
      title = "My first post"
    }
  }
}

Similarly for queries that return affected rows or just true, they will be wrapped up in an addition array table when there are multiple of them. You can also mix the different query types as you see fit.

local res = assert(pg:query_array("select 1 as a, 'two' as b"))

res.fields --> { "a", "b" }
res[1] --> { 1, "two" }

Identical to query, but each row in the result is an array of values ordered by column position instead of a table keyed by field name. The ordered list of field names is stored in the fields key of each result.

Unlike query, NULL values are always included in rows using the postgres.NULL sentinel value, regardless of the convert_null setting, so that values always align with their position in fields.

Because rows are not keyed by field name, this interface supports queries that return multiple columns with the same name. Rows are also cheaper to construct than the tables returned by query, making this interface suitable for queries that return a large number of rows.

local sql_fragment = postgres:escape_literal(val)

local res = postgres:query("select created_at from users where id = " .. sql_fragment)

Escapes a Lua value into a valid SQL fragment that can be safely concatenated into a query string. Never concatenate a variable into a query without escaping it in some way, or you may open yourself up to SQL injection attacks.

This function is aware of the following Lua value types:

• type(val) == "number" → escape_literal(5.5) --> 5.5
• type(val) == "string" → escape_literal("your's") --> 'your''s'
• type(val) == "boolean" → escape_literal(true) --> TRUE
• val == pgmoon.NULL → escape_literal(pgmoon.NULL) --> NULL

Any other type will throw a hard error, to ensure that you provide a value that is safe for escaping.

local sql_fragment = postgres:escape_identifier(some_table_name)`

local res = postgres:query("select * from " .. sql_fragment .. " limit 20")

Escapes a Lua value for use as a Postgres identifier. This includes things like table or column names. This does not include regular values, you should use escape_literal for that. Identifier escaping is required when names collide with built in language keywords.

The argument, val, must be a string.

print(tostring(postgres)) --> "<Postgres socket: 0xffffff>"

Returns string representation of current state of Postgres object.

print(postgres.transaction_status) --> "I"

A string field containing the current transaction status of the connection as reported by the PostgreSQL server. This value is updated after every query and during connection. Possible values:

• "I" — Idle, not in a transaction block
• "T" — In a transaction block (after BEGIN, before COMMIT/ROLLBACK)
• "E" — In a failed transaction block (queries will be rejected until ROLLBACK)

This can be useful for detecting uncommitted transactions or handling error recovery:

pg:query("BEGIN")
print(pg.transaction_status) --> "T"

local res, err = pg:query("SELECT * FROM nonexistent_table")
if not res then
  print(pg.transaction_status) --> "E"
  pg:query("ROLLBACK") -- required to recover from error state
end

print(pg.transaction_status) --> "I"

Although Lua is single-threaded, in asynchronous environments that share state, multiple coroutines may attempt to use the same database connection while another coroutine is yielded. This can cause issues because PostgreSQL connections are stateful — if one coroutine starts a query and yields (e.g., waiting for network I/O), another coroutine might try to use the same connection while it's mid-operation, corrupting the protocol state.

pgmoon tracks when a connection is busy executing a query. PostgresPool uses this busy tracking to manage a pool of connections, automatically assigning queries to available connections and creating new ones as needed.

The PostgresPool connection is designed to match the interface of the Postgres connection, so it can be a drop in for when you need a connection pool.

local PostgresPool = require("pgmoon.pool").PostgresPool
local pool = PostgresPool({
  host = "127.0.0.1",
  port = "5432",
  database = "mydb",
  user = "postgres",
  max_pool_size = 10
})

assert(pool:connect())

-- Queries are automatically assigned to available connections
-- Safe to call from multiple coroutines simultaneously
local res = assert(pool:query("select * from users limit 10"))

The first connection must be established with connect before any queries are made (this is to validate that you connection settings are valid). If the pool is fully occupied when a query is issued, then a new connection will dynamically be established and used, and then returned to the pool.

Methods like disconnect, settimeout, setkeepalive operate on every connection currently in the pool.

PostgresPool accepts the same configuration options as Postgres, plus:

• "max_pool_size": Maximum number of connections in the pool (optional). When all connections are busy and the limit is reached, queries will return an error.

PostgresPool provides the same query interface as Postgres:

• pool:connect() — Creates the first connection in the pool
• pool:disconnect() — Disconnects all connections in the pool
• pool:keepalive(...) — Calls keepalive on all connections (OpenResty only)
• pool:settimeout(...) — Sets timeout on all existing and future connections
• pool:query(...) — Runs a query on an available connection
• pool:simple_query(q) — Runs a simple query on an available connection
• pool:extended_query(...) — Runs an extended query on an available connection
• pool:set_type_deserializer(...) — Sets type deserializer on all connections
• pool:escape_literal(val) — Same as Postgres:escape_literal
• pool:escape_identifier(val) — Same as Postgres:escape_identifier
• pool:setup_hstore() — Same as Postgres:setup_hstore

Additional pool-specific methods:

• pool:pool_size() — Returns the current number of connections in the pool
• pool:active_connections() — Returns the number of connections currently executing queries
• pool:reserve() — Returns a raw Postgres connection for exclusive use (see below)
• pool:release(pg) — Returns a reserved connection back to the pool (see below)

Note: wait_for_notification is not supported with PostgresPool because notifications are tied to the specific socket connection that issued the LISTEN command.

When you need to run a transaction across multiple queries, use reserve() to get exclusive access to a connection. This prevents other coroutines from using the same connection between your transaction queries.

local pg = pool:reserve()  -- Returns raw Postgres connection, marked as reserved
pg:query("BEGIN")
pg:query("INSERT INTO users (name) VALUES ('test')")
pg:query("COMMIT")
pool:release(pg)  -- Returns connection to pool

Returns a raw Postgres connection object from the pool, marked as reserved. While reserved, the connection will not be used by pool:query() or other pool methods.

• Returns: Postgres connection object on success
• Returns: nil, "not connected" if pool has no connections
• Returns: nil, "pool exhausted, max_pool_size reached" if at capacity

Returns a reserved connection back to the pool for reuse.

• Validates the connection belongs to this pool
• If the connection is still in a transaction (transaction_status is "T" or "E"), automatically issues ROLLBACK to reset it to idle state
• Returns: true on success
• Returns: nil, "connection not from this pool" if connection belongs to a different pool
• Returns: nil, "connection not reserved" if the connection was not reserved

pgmoon will issue your query to the database server using either the simple or extended protocol depending if you provide parameters and parameter placeholders in your query. The simple protocol is used for when your query is just a string, and the extended protocol is used when you provide addition parameters as arguments to the query method.

The protocols have some trade-offs and differences:

local res, err = postgres:query("select name from users where id = $1 and status = $2", 12, "ready")

• Advantage: Parameters can be included in query without risk of SQL injection attacks, no need to escape values and interpolate strings
• Advantage: Supports the pgmoon_serialize method to allow for custom types to be automatically serialized into parameters for the query
• Disadvantage: Only a single query can be sent a time
• Disadvantage: Substantially more overhead per query. A no-op query may be 50% to 100% slower. (note that this overhead may be negligible depending on the runtime of the query itself)
• Disadvantage: Some kinds of query syntax are not compatible with parameters (eg. where id in (...), dynamic expressions), so you may still need to use string interpolation and assume the associated risks

local res, err = postgres:query("select name from users where id = " .. postgres:escape_literal(12) .." and status = " .. postgres:escape_literal("ready"))

• Advantage: Higher performance. Low overhead per query means more queries can be sent per second, even when manually escaping and interpolating parameters
• Advantage: Multiple queries can be sent in a single request (separated by ;)
• Disadvantage: Any parameters to the query must be manually escaped and interpolated into the query string. This can be error prone and introduce SQL injection attacks if not done correctly

Note: The extended protocol also supports binary encoding of parameter values & results, but since Lua treats binary as strings, it's generally going to be faster to just consume the string values from Postgres rather than using the binary protocol which will require binary to string conversion within Lua.

pgmoon can establish an SSL connection to a Postgres server. It can also refuse to connect to it if the server does not support SSL. Just as pgmoon depends on LuaSocket for usage outside of OpenResty, it depends on luaossl/LuaSec for SSL connections in such contexts.

local pgmoon = require("pgmoon")
local pg = pgmoon.new({
  host = "127.0.0.1",
  ssl = true, -- enable SSL
  ssl_verify = true, -- verify server certificate
  ssl_required = true, -- abort if the server does not support SSL connections
  ssl_version = "tlsv1_2", -- e.g., defaults to highest available, no less than TLS v1.1
  cafile = "...", -- certificate authority (LuaSec only)
  cert = "...", -- client certificate
  key = "...", -- client key
})

assert(pg:connect())

Note: In Postgres 12 and above, the minium SSL version accepted by client connections is 1.2. When using LuaSocket + LuaSec to connect to an SSL server, if you don't specify an ssl_version then tlsv1_2 is used.

In OpenResty, make sure to configure the lua_ssl_trusted_certificate directive if you wish to verify the server certificate.

Postgres has a handful of authentication types. pgmoon currently supports trust, peer and password authentication with scram-sha-256-auth or md5.

Postgres has a very rich set of types built in. pgmoon will do its best to convert any Postgres types into the appropriate Lua type.

All integer, floating point, and numeric types are converted into Lua's number type. The boolean type is converted into a Lua boolean. The JSON type is decoded into a Lua table using Lua CJSON. Lua tables can be encoded to JSON as described below.

Any array types are automatically converted to Lua array tables. If you need to encode an array in Lua to Postgres' array syntax you can use the pgmoon.arrays module. See below.

Any other types are returned as Lua strings.

Arrays are automatically deserialized into a Lua object when they are returned from a query. Numeric, string, and boolean types are automatically loaded accordingly. Nested arrays are also supported.

Use encode_array to encode a Lua table to array syntax for a query:

local pgmoon = require("pgmoon")
local pg = pgmoon.new(auth)
pg:connect()

local encode_array = require("pgmoon.arrays").encode_array
local my_array = {1,2,3,4,5}
pg:query("insert into some_table (some_arr_col) values(" .. encode_array(my_array) .. ")")

Arrays that are returned from queries have their metatable configured for the PostgresArray type (defined in require("pgmoon.arrays")).

When using the extended query protocol (query with parameters), an array object created with PostgresArray will automatically be serialized when passed as a parameter.

local PostgresArray = require("pgmoon.arrays").PostgresArray

postgres:query("update user set tags = $1 where id = 44", PostgresArray({1,2,4}))

Keep in mind that calling PostgresArray mutate the argument by setting its metatable. Make a copy first if you don't want the original object to be mutated.

Additionally, array types must contain values of only the same type. No run-time checking is performed on the object you pass. The type OID is determined from the first entry of the array.

When trying to encode an empty array an error will be thrown. Postgres requires a type when using an array. When there are values in the array Postgres can infer the type, but with no values in the array no type can be inferred. This is illustrated in the error provided by Postgres:

postgres=# select ARRAY[];
ERROR:  cannot determine type of empty array
LINE 1: select ARRAY[];
               ^
HINT:  Explicitly cast to the desired type, for example ARRAY[]::integer[].

You can work around this error by always including a typecast with any value you use, to allow you to pass in an empty array and continue to work with an array of values assuming the types match.

local empty_tags = {}
pg:query("update posts set tags = " .. encode_array(empty_tags) .. "::text[]")

json and jsonb values are automatically decoded as Lua tables in a query result (using the cjson library if available).

To send JSON in a query you must first convert it into a string literal, then interpolate it into your query. Ensure that you treat it like any other paramter, and call escape_literal on the string to make it suitable to be safely parsed as a value to PostgreSQL.

local pgmoon = require("pgmoon")
local pg = pgmoon.new(auth)
assert(pg:connect())

local my_tbl = { hello = "world" }

local json = require "cjson"

pg:query("update my_table set data = " .. db.escape_literal(json.encode(my_tbl)) .. " where id = 124")

Because hstore is an extension type, a query is required to find out the type id before pgmoon can automatically decode it. Call the setup_hstore method on your connection object after connecting to set it up.

local pgmoon = require("pgmoon")
local pg = pgmoon.new(auth)
pg:connect()
pg:setup_hstore()

Use encode_hstore to encode a Lua table into hstore syntax suitable for interpolating into a query.

Note: The result of encode_hstore is a valid Postgres SQL fragment, it is not necessary to call escape_literal on it. It can safely be inserted directly into the query

local encode_hstore = require("pgmoon.hstore").encode_hstore
local tbl = {foo = "bar"}
pg:query("insert into some_table (hstore_col) values(" .. encode_hstore(tbl) .. ")")

You can manually decode a hstore value from string using the decode_hstore function. This is only required if you didn't call setup_hstore.

local decode_hstore = require("pgmoon.hstore").decode_hstore
local res = pg:query("select * from some_table")
local hstore_tbl = decode_hstore(res[1].hstore_col)

PostgreSQL has a rich set of types. When reading a query's results pgmoon must attempt to interpret the types from postgres and map them to something usable in Lua. By default implementations are included for primitives like numbers, booleans, strings, and JSON.

You can provide your own type deserializer if you want to add custom behavior for certain types of values returned by PostgreSQL.

You must have some knowledge of types and type OIDs. Every type in PostgreSQL is stored in the pg_type catalog table. Each type has an OID (stored as a 32 bit positive integer) to uniquely identify it. The core types provided by Postgres have fixed type OIDs (for example, boolean is always 16), but third-party types may be added without fixed OIDs.

Also note that any composite versions of existing types have their own OID, for example, while a single boolean value has type OID 16, an array of boolean values has type OID 1000. Arrays are homogeneous and must contain the same type for every value.

Adding support for a new type in pgmoon can be done using the set_type_deserializer(oid, type_name, [deserializer]) method:

local pgmoon = require("pgmoon")
local pg = pgmoon.new(config)

-- in this example we create a new deserializer called bignumber and provide
-- the function to deserialize (type OID 20 is an 8 byte integer)
pg:set_type_deserializer(20, "bignumber", function(val)
    return "HUGENUMBER:" .. val
end)

-- in this example we point another OID to the "bignumber" deserializer we
-- provided above (type OID 701 is a 8 byte floating point number)
pg:set_type_deserializer(701, "bignumber")

The arguments are as follows:

• oid The OID from pg_type that will be handled
• name The local name of the type. This is a name that points to an existing deserializer or will be used to register a new one if the deserializer argument is provided
• deserializer A function that takes the raw string value from Postgres and converts it into something more useful (optional). Any existing deserializer function with the same name will be overwritten

When using the query method with params, (aka the extended query protocol), and values passed into parameters must be serialized into a string version of that value and a type OID.

pgmoon provides implementations for Lua's basic types: string, boolean, numbers, and postgres.NULL for the NULL value.

If you want to support custom types, like JSON, then you will need to provide your own serializer.

Serializing vs escaping: pgmoon has two methods for preparing data to be sent in a query. Escaping is used when you want to turn some value into a SQL fragment that can be safely concatenated into a query. This is done with postgres:escape_literal() and is suitable for use with the simple query protocol. Serializing, on the other hand, is used to convert a value into a string representation that can be parsed by Postgres as a value when using the extended query protocol. As an example, an escaped string would be 'hello' (notice the quotes, this is a fragment of valid SQL syntax, whereas a serialized string would be just the string: hello (and typically paired with a type OID, typically 25 for text). Serializing is the opposite of deserializing, which is described above.

Note: Serializing is NOT the same as escaping. You can not take a serialized value and concatenate it directly into your query. You may, however, take a serialized value and escape it as a string, then attempt to cast it to the appropriate type within your query.

To provide your own serializer for an object, you can add a method on the metatable called pgmoon_serialize. This method takes two arguments, the value to be serialized and the current instance of Postgres that is doing the serialization. The method should return two values: the type OID as an integer, and the string representation of that value.

Note: The type OID 0 can be used for "unknown", and Postgres will try to infer the type of the value based on the context. If possible you should always try to provide a specific type OID.

-- this metatable will enable an object to be serialized as json for use as a
-- parameter in postgres:query()
local json_mt = {
  pgmoon_serialize = function(v)
    local cjson = require("cjson")
    return 114, cjson.encode(v) -- 114 is oid from pg_type catalog
  end
}

local data = {
  age = 200,
  color = "blue",
  tags = {"one", "two"}
}

postgres:query("update user set data = $1 where id = 233", setmetatable(data, json_mt))

The pgmoon_serialize method can also return nil and an error message to abort serialization. This will block the query from running at all, and the error will be returned fro