Using PostgreSQL with OpenResty

The modules

OpenResty is a special distribution on Nginx designed for building web applications in Lua. In order to take advantage of the asyncrhonous event loop in Nginx you must use specially designed modules for interacting with a database, as opposed to any generic SQL bindings like LuaSQL.

OpenResty comes with support for MySQL built in via the lua-resty-mysql package, but there is no standard PostgreSQL module. I'm aware of the following three modules for working with PostgreSQL within OpenResty:

• ngx_postgres
• lua-resty-mysql
• pgmoon

pgmoon

This guide will be about pgmoon. I wrote pgmoon because of some disadvantages from the other modules (see below). pgmoon is production ready, serving hundreds of thousands of queries per day among all of my projects, including itch.io and streak.club. It’s also the default driver for Lapis.

Installation

The latest version of pgmoon is on luarocks.org. Install with the following command:

luarocks install pgmoon

Connecting

pgmoon uses an almost identical interface to lua-resty-mysql.

In order to connect to the database we'll need to require the module and call the new function:

local pgmoon = require("pgmoon")

local pg = pgmoon.new({ database = "mydb" })
assert(pg:connect())

For simplicity I've only provided the database in the connection options, refer to the documentation to see how you can configure other things like port, host, and password.

After creating the pgmoon instance you can call connect to connect to the database. You are now ready to make queries.

Queries

Use the query method to send a query to the database:

local res = pg:query("select id, name from users")
local res2 = pg:query([[
  update users set name = 'hello', updated_at = now() returning updated_at
]])

Any thing that returns a result set, like SELECT, or UPDATE/INSERT with RETURNING will return the rows as a array like table.

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 where id = 23")
if res.affected_rows and res.affected_rows > 0 then
  print "Row was deleted"
end

Preventing SQL injection

If you're constructing queries from user provided input you must escape said inputs otherwise your application will be vulnerable to SQL injection.

pgmoon provides two methods to escape literals and identifiers:

• pg:escape_literal(val)
• pg:escape_identifier(val)

A literal is value in PostgreSQL, like a number of a string. An identifier is the name of something, like a column or table. It’s important to note that PostgreSQL does not use strings for column & table names.

Here’s an example of properly escaping an identifier and literal:

local table_name = "My Crazy Table"
local title = "WhoaA!!"

pg:query("update " .. pg:escape_identifier(table_name) ..
  " set count = count + 1 where title = " .. pg:escape_literal(title))

Connection pooling

OpenResty’s cosocket API provides connection pooling for sockets by providing a method to relinquish control of a socket so another request can use it. It’s important to use this feature for optimal performance, otherwise you'll be opening a new socket for each request.

With pgmoon you just need to call the keepalive method when you're done using it. After calling keepalive you should no longer issue any queries from it, so setting its reference to nil is a good practice.

pg:keepalive()
pg = nil

Reference manual

This guide just briefly skims over how the module works. I definitely recommend reading over the README on GitHub for more detailed documentation and information about more advanced features like type deserialization.

Issues I had with other modules

I mentioned above that I built pgmoon to work around some of the limitations of the other modules. Here’s why:

• ngx_postgres isn’t actually a Lua module, but an Nginx module. You'll have to create a special location in your Nginx configuration to allow communication between the database and your Lua code. Additionally there doesn’t appear to be a way to get the error messages from the database if there is an error in your query.
• lua-resty-mysql doesn’t appear to be updated anymore. There was only one authentication method which made it difficult to connect. Additionally it did not deserialize types correctly, things like true would result as the string "true".

Another reason I built pgmoon was so I could have a database driver that works both inside and outside of OpenResty. pgmoon will automatically use LuaSocket if the Nginx context is not available. This is great for avoiding any surprises when running scripts inside and outside of OpenResty.