Using PostgreSQL with OpenResty

The modules

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

OpenResty comes with support for MySQL built in via thelua-resty-mysqlpackage, but there is no standard PostgreSQL module. I'm aware of the followingthree modules for working with PostgreSQL within OpenResty:

• ngx_postgres
• lua-resty-mysql
• pgmoon

pgmoon

This guide will be aboutpgmoon. I wrotepgmoon because of somedisadvantages from the other modules (seebelow).pgmoon is production ready,serving hundreds of thousands of queries per day among all of my projects,includingitch.io andstreak.club. It’s also the default driver forLapis.

Installation

The latest version ofpgmoon is onluarocks.org. Install with thefollowing command:

luarocks install pgmoon

Connecting

pgmoon uses an almost identical interface tolua-resty-mysql.

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

localpgmoon=require("pgmoon")localpg=pgmoon.new({database="mydb"})assert(pg:connect())

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

After creating thepgmoon instance you can callconnect to connect tothe database. You are now ready to make queries.

Queries

Use thequery method to send a query to the database:

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

Any thing that returns a result set, likeSELECT, orUPDATE/INSERT withRETURNING will return the rows as a array like table.

Any queries that affect rows likeUPDATE,DELETE, orINSERT return atable result with theaffected_rows field set to the number of rows affected.

localres=pg:query("delete from users where id = 23")ifres.affected_rowsandres.affected_rows>0thenprint"Row was deleted"end

Preventing SQL injection

If you're constructing queries from user provided input you must escape saidinputs 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 isthe name of something, like a column or table. It’s important to note thatPostgreSQL does not use strings for column & table names.

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

localtable_name="My Crazy Table"localtitle="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 amethod to relinquish control of a socket so another request can use it. It’simportant to use this feature for optimal performance, otherwise you'll beopening a new socket for each request.

Withpgmoon you just need to call thekeepalivemethod when you're done using it. After callingkeepalive you should nolonger issue any queries from it, so setting its reference tonil is a goodpractice.

pg:keepalive()pg=nil

Reference manual

This guide just briefly skims over how the module works. I definitely recommendreading over theREADME on GitHub formore detailed documentation and information about more advanced features liketype deserialization.

Issues I had with other modules

I mentioned above that I builtpgmoon to work around some of the limitationsof the other modules. Here’s why:

• ngx_postgres isn’t actually a Lua module, but an Nginx module. You'll have to create a speciallocation 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 liketrue would result as the string"true".

Another reason I built pgmoon was so I could have a database driver that worksboth inside and outside of OpenResty.pgmoon will automatically use LuaSocketif the Nginx context is not available. This is great for avoiding any surpriseswhen running scripts inside and outside of OpenResty.

A PostgreSQL client library written in pure Lua, designed for standalone use and use within OpenResty