Repository files navigation

Note: Have you updated from an older version of OpenResty? You must update topgmoon 1.12 or above, due to a change in Lua pattern compatibility to avoid incorrectresults from queries that return affected rows.

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

pgmoon was originally designed for use inOpenResty to take advantageof thecosocketapi to provideasynchronous queries but it also works in the regular any Lua environment whereLuaSocket orcqueues is available.

It's a perfect candidate for running your queries both inside OpenResty'senvironment and on the command line (eg. tests) in web frameworks likeLapis.

$ luarocks install pgmoon

$ opm get leafo/pgmoon

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

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

A socket implementationis required to use pgmoon, depending on theenvironment you can chose one:

• OpenResty — The built in socket is used, no additional dependencies necessary
• LuaSocket —luarocks install luasocket
• cqueues —luarocks install cqueues

If you're on PUC Lua 5.1 or 5.2 then you will need a bit libray (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 withinOpenResty will prioritize built in functions if possible

Parsing complex types like Arrays and HStore requireslpeg to be installed.

localpgmoon=require("pgmoon")localpg=pgmoon.new({host="127.0.0.1",port="5432",database="mydb",user="postgres"})assert(pg:connect())localres=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 poolafter 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 beoverwritten 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 needthen open anissue.

Functions in table returned byrequire("pgmoon"):

Creates a newPostgres object from a configuration object. All fields areoptional unless otherwise stated. The newly created object will notautomatically connect, you must callconect after creating the object.

Available options:

• "database": the database name to connect torequired
• "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)
• "application_name": set the name of the connection as displayed inpg_stat_activity. (default:"pgmoon")
• "pool": (OpenResty only) name of pool to use when using OpenResty cosocket (default:"#{host}:#{port}:#{database}")
• "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 createdopensssl.ssl.context to use when created cqueues SSL connections
• "luasec_opts": Manually created options object to use when using LuaSec SSL connections

Methods on thePostgres object returned bynew:

localsuccess,err=postgres:connect()

Connects to the Postgres server using the credentials specified in the call tonew. On success returnstrue, on failure returnsnil and the errormessage.

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 butgenerally corresponds to no timeout.

localsuccess,err=postgres:disconnect()

Closes the socket. Returnsnil if the socket couldn't be closed. On mostsocket types,connect can be called again to reestaablish a connection withthe same postgres object instance.

postgres:keepalive()

Relinquishes socket to OpenResty socket pool via thesetkeepalive method. Anyarguments passed here are also passed tosetkeepalive. After calling thismethod, the socket is no longer available for queries and should be considereddisconnected.

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

-- return values for successful querylocalresult,err,num_queries=postgres:query("select name from users limit 2")-- return value for failure (status is nil)localstatus,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 returnvalue isnil, followed by a string describing the error. Since a single calltopostgres:query can contain multiple queries, the results of any queries thatsucceeded before the error occurred are returned after the error message.(Note: queries are atomic, they either succeed or fail. The partial result willonly contain succeed queries, not partially data from the failed query)

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

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

• 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

SeeExtended and simple query protocolfor more information about the differences and trade-offs.

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

SELECT queries,INSERT withreturning, or anything else that returns aresult set will return an array table of results. Each result is a hash tablewhere the key is the name of the column and the value is the result for thatrow of the result.

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

Might return:

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

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

Might return:

{affected_rows=2}

Any queries with no result set or updated rows will returntrue.

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

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

Might return:

num_queries=2res= {  {    {id=123,name="Leafo"    },    {id=234,name="Lee"    }  },  {    {id=546,title="My first post"    }  }}

Similarly for queries that return affected rows or justtrue, they will bewrapped up in an addition array table when there are multiple of them. You canalso mix the different query types as you see fit.

localsql_fragment=postgres:escape_literal(val)localres=postgres:query("select created_at from users where id ="..sql_fragment)

Escapes a Lua value int a valid SQL fragment that can be safely concatenatedinto a query string.Never concatenate a variable into query withoutescaping it in some way, or you may open yourself up toSQL injectionattacks.

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 harderror, to ensure that you provide a valuethat is safe for escaping.

localsql_fragment=postgres:escape_identifier(some_table_name)`localres=postgres:query("select * from"..sql_fragment.." limit 20)

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

The argument,val, must be a string.

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

Returns string representation of current state ofPostgres object.

pgmoon will issue your query to the database server using either the simple orextended protocol depending if you provide parameters and parameterplaceholders in your query. The simple protocol is used for when your query isjust a string, and the extended protocol is used when you provide additionparameters as arguments to thequery method.

The protocols have some trade-offs and differences:

localres,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 thepgmoon_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

localres,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 befaster to just consume the string values from Postgres rather than using thebinary protocol which will require binary to string conversion within Lua.

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

localpgmoon=require("pgmoon")localpg=pgmoon.new({host="127.0.0.1",ssl=true,-- enable SSLssl_verify=true,-- verify server certificatessl_required=true,-- abort if the server does not support SSL connectionsssl_version="tlsv1_2",-- e.g., defaults to highest available, no less than TLS v1.1cafile="...",-- certificate authority (LuaSec only)cert="...",-- client certificatekey="...",-- client key})assert(pg:connect())

Note: In Postgres 12 and above, the minium SSL version accepted by clientconnections is 1.2. When using LuaSocket + LuaSec to connect to an SSLserver, if you don't specify anssl_version thentlsv1_2 is used.

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

Postgres has a handful of authentication types. pgmoon currently supportstrust, 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 toconvert any Postgres types into the appropriate Lua type.

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

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

Any other types are returned as Lua strings.

Arrays are automatically deserialized into a Lua object when they are returnedfrom a query. Numeric, string, and boolean types are automatically loadedaccordingly. Nested arrays are also supported.

Useencode_array to encode a Lua table to array syntax for a query:

localpgmoon=require("pgmoon")localpg=pgmoon.new(auth)pg:connect()localencode_array=require("pgmoon.arrays").encode_arraylocalmy_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 thePostgresArray type (defined inrequire("pgmoon.arrays")).

When using the extended query protocol (query with parameters), an array objectcreated withPostgresArray will automatically be serialized when passed as aparameter.

localPostgresArray=require("pgmoon.arrays").PostgresArraypostgres:query("update user set tags = $1 where id = 44",PostgresArray({1,2,4}))

Keep in mind that callingPostgresArray mutate the argument by setting itsmetatable. Make a copy first if you don't want the original object to bemutated.

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

When trying to encode an empty array an error will be thrown. Postgres requiresa type when using an array. When there are values in the array Postgres caninfer the type, but with no values in the array no type can be inferred. Thisis illustrated in the erorr provided by Postgres:

postgres=# select ARRAY[];ERROR:  cannot determine type of empty arrayLINE 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 valueyou use, to allow you to pass in an empty array and continue to work with anarray of values assuming the types match.

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

json andjsonb values are automatically decoded as Lua tables in a queryresult (using thecjson library if available).

To send JSON in a query you must first convert it into a string literal, theninterpolate it into your query. Ensure that you treat it like any otherparamter, and callescape_literal on the string to make it suitable to besafely parsed as a value to PostgreSQL.

localpgmoon=require("pgmoon")localpg=pgmoon.new(auth)assert(pg:connect())localmy_tbl= {hello="world"}localjson=require"cjson"pg:query("update my_table set data ="..db.escape_literal(json.encode(my_tbl)).." where id = 124"

Becausehstore is an extension type, a query is reuired to find out the typeid before pgmoon can automatically decode it. Call thesetup_hstore method onyour connection object after connecting to set it up.

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

Useencode_hstore to encode a Lua table into hstore syntax suitable forinterpolating into a query.

Note: The result ofencode_hstore is a valid Postgres SQL fragment, it isnot necessary to call escape_literal on it. It can safely be inserteddirectly into the query

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

You can manually decode a hstore value from string using thedecode_hstorefunction. This is only required if you didn't callsetup_hstore.

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

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

You can provie you own type deserializer if you want to add custom behavior forcertain types of values returned by PostgreSQL.

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

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

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

localpgmoon=require("pgmoon")localpg=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:"..valend)-- 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 frompg_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 thedeserializer argument is
• 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), andvalues passed into parameters must be serialized into a string version of thatvalue and a type OID.

pgmoon provides implementations for Lua's basic types: string, boolean,numbers, andpostgres.NULL for theNULL value.

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

Serializing vs escaping: pgmoon has two methods for preparing data to be sentin a query.Escaping is used when you want to turn some value into a SQLfragment that can be safely concatenated into a query. This is done withpostgres:escape_literal() and is suitable for use with the simple queryprotocol.Serializing, on the other hand, is used to convert a value into astring representation that can be parsed by Postgres as a value when usingthe extended query protocol. As an example, anescaped string would be'hello' (notice the quotes, this is a fragment of valid SQL syntax, whereasa serialized string would be just the string:hello (and typically pairedwith a type OID, typically25 for text). Serializing is the oposite ofdeserializing, which is described above.

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

To provide your own serializer for an object, you can add a method on themetatable calledpgmoon_serialize. This method takes two arguments, the valueto be serialized and the current instance ofPostgres that is doing theserialization. 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 toinfer the type of the value based on the context. If possible you shouldalways 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()localjson_mt= {pgmoon_serialize=function(v)localcjson=require("cjson")return114,cjson.encode(v)-- 114 is oid from pg_type catalogend}localdata= {age=200,color="blue",tags= {"one","two"}}postgres:query("update user set data = $1 where id = 233",setmetatable(data,json_mt))

Thepgmoon_serialize method can also returnnil and an error message toabort serialization. This will block the query from running at all, and theerror will be returned from thepostgres:query() method.

Note: Postgres supports a binary representation for values when using theextended query protocol, but at this time pgmoon does not support it.

By defaultNULLs in Postgres are converted tonil, meaning they aren'tvisible in the resulting tables. If you want to convertNULLs to some visiblevalue setconvert_null totrue on thePostgres object and thepostgres.NULL object will be used to represent NULL.

localpgmoon=require("pgmoon")localconfig= {database="my_database",convert_null=true}localpostgres=pgmoon.new(config)assert(postgres:connect())localres=postgres:query("select NULL the_null")assert(postgres.NULL==res[1].the_null)

As shown above, theNULL value is set topostgres.NULL. It's possible to changethis value to make pgmoon use something else asNULL. For example if you'reusing OpenResty you might want to reusengx.null.

Also note that you can usepostgres.NULL as an extended query parameter orinsideescape_literal to generate the value forNULL.

Author: Leaf Corcoran (leafo) (@moonscript)Email:leafot@gmail.comHomepage:http://leafo.net

Note: Future changenotes will be published on GitHub releases page:https://github.com/leafo/pgmoon/releases

• 1.15.0 — 2022-6-3 - Extended query protocol
• 1.14.0 — 2022-2-17 - OpenResty crypto functions used, better empty array support,
• 1.13.0 — 2021-10-13 - Add support for scram_sha_256_auth (@murillopaula), 'backlog' and 'pool_size' options while using ngx.socket (@xiaocang), update LuaSec ssl_protocol default options (@jeremymv2),application_name option (@mecampbellsoup)
• 1.12.0 — 2021-01-06 - Lua pattern compatibility fix, Support for Lua 5.1 through 5.4 (@jprjr). Fix bug where SSL vesrion was not being passed. Default to TLS v1.2 when using LuaSec. Luabitop is no longer automatically installed as a dependency. New test suite.
• 1.11.0 — 2020-03-26 - Allow for TLS v1.2 when using LuaSec (Miles Elam)
• 1.10.0 — 2019-04-15 - Support luaossl for crypto functions, added better error when missing crypto library
• 1.9.0 — 2018-04-02 - nginx pool name includes user, connection reports name aspgmoon
• 1.8.0 — 2016-11-07 — Add cqueues support, SSL calling fix for Nginx cosocket (@thibaultCha)
• 1.7.0 — 2016-09-21 — Add to opm, add support for openresty pool, better default pool, support for hstore (@edan)
• 1.6.0 — 2016-07-21 — Add support for json and jsonb array decoding
• 1.5.0 — 2016-07-12 — Add SSL support (@thibaultCha), Add UUID array type (@edan), Add support for notifications (@starius)
• 1.4.0 — 2016-02-18 — Add support for decoding jsonb, add a json serializer (@thibaultCha)
• 1.3.0 — 2016-02-11 — Fix bug parsing a string that looked like a number failed, add support for using in ngx when in init context (@thibaultCha), add cleartext password auth, fix warning with md5 auth
• 1.2.0 — 2015-07-10 — Add support for PostgreSQL Arrays
• 1.1.1 — 2014-08-12 — Fix a bug with md5 auth
• 1.1.0 — 2014-05-21 — Add support for multiple queries in one call
• 1.0.0 — 2014-05-19 — Initial release

Copyright (C) 2021 by Leaf Corcoran

Permission is hereby granted, free of charge, to any person obtaining a copyof this software and associated documentation files (the "Software"), to dealin the Software without restriction, including without limitation the rightsto use, copy, modify, merge, publish, distribute, sublicense, and/or sellcopies of the Software, and to permit persons to whom the Software isfurnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included inall copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS ORIMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THEAUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHERLIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS INTHE SOFTWARE.