coreweave/pgmoon

coreweave/pgmoon

Description: A pure Lua Postgres driver for use in OpenResty & more

Language: MoonScript

License: MIT

Stars: 0

Forks: 0

Open issues: 0

Created: 2021-10-04T18:50:07Z

Pushed: 2023-07-06T21:08:28Z

Default branch: master

Fork: yes

Parent repository: leafo/pgmoon

Archived: yes

README:

pgmoon

!test

> 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

$ opm get leafo/pgmoon

Dependencies

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 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 within [OpenResty][] will prioritize built in functions if possible

Parsing complex types like Arrays and HStore requires lpeg to be installed.

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

new(options={})

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 conect 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)
• "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 in pg_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 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:

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…