pgmoon
Note: Have you updated from an older version of OpenResty? You must update to pgmoon 1.12 or above, due to a change in Lua pattern compatibility to avoid incorrect results from queries that return affected rows.
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 the regular any Lua environment where [LuaSocket][] or [cqueues][] 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
Using OpenResty's OPM
```bash $ opm get leafo/pgmoon ```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")
:
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 underslying 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 reestaablish 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 partially data from the failed query)
Additional return values: notifications and notices
--- In addition to the return values above, pgmoon will also return two additional values if the query generates them, notifications an notices. ```lua 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 a 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.
postgres:escape_literal(val)
local sql_fragment = postgres:escape_literal(val)
local res = postgres:query("select created_at from users where id = " .. sql_fragment)
Escapes a Lua value int a valid SQL fragment that can be safely concatenated into a query string. Never concatenate a variable into 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.
postgres:escape_identifier(val)
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.
tostring(postgres)
print(tostring(postgres)) --> "<Postgres socket: 0xffffff>"
Returns string representation of current state of Postgres
object.
Extended and simple query protocols
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:
Type conversion
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.
Extended protocol
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.
Empty Arrays
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 erorr 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[]")
Handling JSON
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"
Handling hstore
Because hstore
is an extension type, a query is reuired 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)
Converting NULL
s
By default NULL
s in Postgres are converted to nil
, meaning they aren’t
visible in the resulting tables. If you want to convert NULL
s to some visible
value set convert_null
to true
on the Postgres
object and the
postgres.NULL
object will be used to represent NULL.
local pgmoon = require("pgmoon")
local config = {
database = "my_database",
convert_null = true
}
local postgres = pgmoon.new(config)
assert(postgres:connect())
local res = postgres:query("select NULL the_null")
assert(postgres.NULL == res[1].the_null)
As shown above, the NULL
value is set to postgres.NULL
. It’s possible to change
this value to make pgmoon use something else as NULL
. For example if you’re
using OpenResty you might want to reuse ngx.null
.
Also note that you can use postgres.NULL
as an extended query parameter or
inside escape_literal
to generate the value for NULL
.