Your nginx worker just froze for 800 milliseconds. No CPU spike, no disk pressure — just one Lua handler doing a blocking SELECT inside a cosocket-driven gateway. That single line of code stalls every request that worker was multiplexing, and your p99 latency graph turns into a cliff. Choosing a Lua database driver is not a style preference; it decides whether your gateway stays concurrent under load or collapses one worker at a time.
This guide compares the three drivers that still matter in 2026, with live repository data, real API examples pulled from each project, and the operational traps that only show up in production.
TL;DR: Use lua-resty-mysql if you are on OpenResty and speak MySQL. Use pgmoon if you are on OpenResty and speak PostgreSQL — it is the only pure-Lua Postgres driver worth deploying. Use LuaSQL only when you need one API across SQLite, Oracle, ODBC or Firebird, and you are not inside an nginx event loop.
The Contenders at a Glance
| LuaSQL | pgmoon | lua-resty-mysql | |
|---|---|---|---|
| GitHub stars | 580 | 436 | 727 |
| Last push | 2026-08-03 | 2026-09-09 | 2026-06-20 |
| Implementation | C bindings per driver | Pure Lua (MoonScript source) | Pure Lua, cosocket-based |
| Databases | MySQL, PostgreSQL, SQLite, Oracle, ODBC, ADO, Firebird | PostgreSQL only | MySQL / MariaDB only |
| Execution model | Blocking | Non-blocking inside OpenResty, blocking via LuaSocket | Non-blocking (ngx_lua cosockets) |
| Parameterised queries | No (manual escaping) | Yes ($1, $2) | No (manual escaping) |
| Connection pooling | None built in | keepalive() into the cosocket pool | set_keepalive() into the cosocket pool |
| Install | luarocks install luasql-postgres (one rock per driver) | luarocks install pgmoon (+ luaossl for TLS) | Copy lib/resty/mysql.lua or use a bundle |
| License | Same as Lua 5.1 (MIT-style) | MIT | BSD-style (OpenResty) |
The star counts are close, which is misleading. They solve different problems: LuaSQL is a compatibility layer, pgmoon is a protocol implementation, and lua-resty-mysql is a cosocket integration.
Decision Matrix: Pick by Use Case
| Your situation | Pick | Why |
|---|---|---|
| OpenResty gateway, MySQL/MariaDB backend | lua-resty-mysql | Production-ready cosocket driver, zero C dependencies, pools per worker |
| OpenResty gateway, PostgreSQL backend | pgmoon | Only maintained pure-Lua Postgres driver; parameterised queries and keepalive() |
| Standalone Lua script (cron job, CLI, migration tool) | LuaSQL | Works outside nginx; single API over six database families |
| SQLite embedded in a desktop or edge tool | LuaSQL | luasql.sqlite3 is the shortest path to a cursor API |
| Oracle / ODBC / Firebird legacy integration | LuaSQL | Nothing else in the Lua ecosystem covers these |
Need LISTEN/NOTIFY with PostgreSQL | pgmoon | wait_for_notification() is built in |
| You want prepared statements as a hard requirement | pgmoon | $1 placeholders; the other two require manual quoting |
LuaSQL: The Polyglot That Speaks to Everything
LuaSQL is the oldest option and still the widest. Its own README states the scope plainly: connect to ODBC, ADO, Oracle, MySQL, SQLite, Firebird and PostgreSQL databases; execute arbitrary SQL; retrieve results row by row through a cursor. With 580 stars and a commit in August 2026, it is maintained but not modernised.
Install one rock per backend — the drivers are separate shared objects:
| |
The API is a three-object dance: environment, connection, cursor.
| |
Two things to notice. First, env:connect takes positional arguments — sourcename, username, password, hostname, port — so every configuration value ends up in argument order rather than a named table. Second, fetch({}, "a") allocates a fresh table per row unless you pass the same table back in, which matters on large result sets.
Where LuaSQL hurts in 2026: it blocks. If you require "luasql.postgres" inside an content_by_lua_block, that C call holds the nginx worker for the duration of the query, and every other request multiplexed onto that worker waits. It also has no pooling, no parameter binding, and no async story. Treat it as the right tool for offline scripts and legacy integrations, not for request-path code.
pgmoon: PostgreSQL for OpenResty Without a C Extension
pgmoon is a pure-Lua PostgreSQL driver that speaks the wire protocol directly. 436 stars, last push 2026-09-09, MIT licensed, and it works both inside OpenResty (cosockets) and in plain Lua via LuaSocket.
| |
The documented example is compact, and it is the API you will actually write:
| |
That $1 form is the reason to prefer pgmoon over manual escaping. Values are sent as protocol parameters, not concatenated into SQL text, so a name containing a quote cannot rewrite your statement.
Inside OpenResty, hand the socket back to the connection pool instead of closing it:
| |
pgmoon’s README documents the caveats honestly, and you should internalise them before shipping:
- Duplicate column names collapse.
select 1 as dog, 'hello' as dogyields{ { dog = "hello" } }because results land in a Lua table. Alias your columns. - Connections are not safe to share across concurrently running coroutines. The driver tracks a
busyflag acrossconnect,query,disconnect,keepaliveandwait_for_notification, and will refuse re-entry — so one connection per in-flight coroutine, managed through the pool. - Arrays and HStore need
lpeginstalled to parse complex types. - TLS needs an extra dependency;
luaosslis the maintained choice, while the README explicitly calls LuaCrypto abandoned.
lua-resty-mysql: The Production Workhorse for MySQL
lua-resty-mysql is the driver most OpenResty deployments end up using, and its README labels it production ready. 727 stars, last push 2026-06-20, maintained under the OpenResty organisation. It requires ngx_lua 0.9.11 or newer and the bit library (already present in LuaJIT 2 builds of ngx_lua).
Install by making the module visible to nginx:
| |
The documented synopsis, trimmed to the essential calls:
| |
Every call returns the OpenResty error tuple. Resist the temptation to assert() inside a request handler — a failed query should produce a 503, not an nginx 500 with an unreadable traceback.
The driver’s practical features are the ones you need at scale: set_timeout bounds a stalled query, set_keepalive(max_idle_timeout, pool_size) returns the connection to the pool, get_reused_times() tells you whether pooling is actually working, and set_compact_arrays(true) returns result rows as plain arrays instead of { field = ..., value = ... } pairs, which cuts allocation noticeably on wide result sets. Multi-resultset support and automatic error logging are documented as well.
The trade-off is real: lua-resty-mysql has no prepared statements. You must quote values yourself, and the README provides an SQL literal quoting section for exactly this reason. Inside nginx you can reach for ngx.quote_sql_str(), but a hand-rolled "'" .. value .. "'" is how injection bugs get shipped.
Running All Three Side by Side
For a realistic lab, start PostgreSQL and MySQL next to OpenResty using the official images. Nothing here is project-specific glue — it is the same compose file you would use to reproduce a latency problem on your laptop.
| |
Then wire a single endpoint that exercises the pool:
| |
Run wrk -t4 -c100 -d30s http://localhost:8080/pg and then the same against /mysql. Do not trust a blog post’s benchmark table (including this one) — measure against your schema, your payload size and your network. What you are looking for is not raw throughput but latency stability: p99 should stay flat as concurrency rises. If it climbs linearly with -c, pooling is broken or a query is blocking.
Performance and Concurrency: What Actually Decides
Driver choice affects throughput through three mechanisms, and only one of them is the driver’s own speed.
1. Blocking versus non-blocking. A cosocket driver yields the worker while the database works; a C-binding driver does not. Inside nginx this is not a micro-optimisation — with worker_connections 1024 and four workers, one blocked handler can strand hundreds of queued requests. If you must use LuaSQL in a request path, run it in a separate process, never inside content_by_lua*.
2. Pool reuse. Both OpenResty drivers rely on the cosocket connection pool, sized per worker and keyed by host/port. set_keepalive(10000, 100) in lua-resty-mysql and keepalive() in pgmoon both return a socket for reuse; forgetting the call means a fresh TCP handshake, TLS negotiation and database authentication on every request. On PostgreSQL that can cost several milliseconds per request before your query even starts.
3. Allocation per row. LuaSQL’s cursor with fetch({}, "a") builds a table per row. lua-resty-mysql’s default result shape is a table of single-key tables unless you enable compact arrays. pgmoon builds associative rows directly. For a 10,000-row export, the difference shows up as GC pauses, not query time.
An honest comparison also has to say what these drivers cannot do: none of them is an ORM, none of them manages migrations, and none of them will retry a failed transaction for you. If you want that layer, you are choosing a framework, not a driver — which is a different decision than the one this article covers.
Pitfalls and Migration Notes
These are the failures I would warn a team about before they deploy any of the three.
- String-concatenated SQL is the default failure mode of two of these drivers. pgmoon supports
$1placeholders; lua-resty-mysql and LuaSQL do not. In OpenResty, usengx.quote_sql_str(value)and never interpolate raw input. - A forgotten
keepalive()looks like a database problem. Connection counts climb,too many connectionsappears in the database log, and latency rises in steps. Checkget_reused_times()first — if it stays at 0, you are not pooling. - LuaSQL needs one rock per backend.
luasql-postgreswill not talk to MySQL. This bites when a container image is built with only one driver and a second integration is added later. - Charset and packet size are explicit in both OpenResty drivers. Set
charset = "utf8mb4"for MySQL if you store emoji, and raisemax_packet_sizefor large blobs; the default 1 MB silently limits what you can return in one query. - Transactions are manual.
BEGIN/COMMIT/ROLLBACKare just queries. Wrap them inpcalland roll back on error, or a failed statement leaves the connection in an aborted transaction inside the pool — and the next request inherits it. - Duplicate column names overwrite each other in pgmoon. Always alias:
select u.id as user_id, o.id as order_id. - Do not share one connection across coroutines. OpenResty may resume a different coroutine on the same connection; both drivers guard against re-entry, which means you get an error instead of corrupted protocol state. One connection per in-flight request, via the pool.
Which Should You Actually Use?
If you run OpenResty, the answer is decided by your database engine, not by benchmarks. MySQL or MariaDB: lua-resty-mysql, because it is maintained under the OpenResty org, cosocket-native, and has the pool and timeout knobs you need. PostgreSQL: pgmoon, because it is the only pure-Lua option that supports parameter binding and LISTEN/NOTIFY, and because it does not require compiling C extensions into a container.
If you are writing scripts outside nginx — a nightly export, a migration runner, a CLI tool — LuaSQL remains the pragmatic choice. Its cursor API is dated, its performance is unremarkable, and it will not win a concurrency contest, but it covers SQLite, Oracle, ODBC and Firebird in a single interface, and nothing else in the Lua ecosystem does.
The combination I would actually ship in 2026: pgmoon for request-path Postgres, lua-resty-mysql for MySQL, and LuaSQL for offline tooling only. Do not mix a blocking driver into a request path “just for one query” — that one query is exactly the one that will freeze a worker during your next traffic spike.
For related reading, see our Lua web frameworks comparison to pick the framework these drivers sit under, our Lua testing frameworks guide for wiring integration tests against a real database, and our C++ database client libraries comparison if you are evaluating drivers in a compiled stack instead.
FAQ
Is lua-resty-mysql still maintained in 2026?
Yes. The repository received commits in June 2026 and the README still labels the library production ready. It is maintained under the OpenResty organisation alongside lua-resty-redis and lua-resty-core, which is a reasonable proxy for long-term support.
Can I use pgmoon outside OpenResty?
Yes. pgmoon works with LuaSocket in plain Lua, but then it blocks like any socket client. Its real advantage — cosocket-based non-blocking I/O and keepalive() pooling — only applies inside OpenResty or another ngx_lua environment.
Why does lua-resty-mysql not support prepared statements?
The driver speaks the MySQL text protocol through ngx_lua cosockets and exposes query, send_query and read_result. Parameter binding would require the binary protocol and per-connection statement handles, which the project has not implemented. Use ngx.quote_sql_str() for escaping, or move the query into a service written in a language with a full driver.
Which driver is fastest?
There is no universal answer, and any table claiming otherwise is measuring someone else’s schema. What is measurable in your own environment is latency stability under concurrency: run wrk against each endpoint and watch p99 as you raise -c. A blocking driver will show a p99 that degrades sharply; pooled cosocket drivers stay flat until the database itself saturates.
How many connections should I keep in the pool?
Start with set_keepalive(10000, 100) for MySQL and pool sizes proportional to worker_connections for pgmoon, then watch the database’s connection count. The pool is per worker, so the effective maximum is roughly pool size × worker count — a number that surprises people who size the pool from a single-worker test.
💰 想测试你的市场判断力?我用 Polymarket 做预测市场交易——这是全球最大的预测市场平台,从大选结果到技术监管时间线,什么都可以押注。和赌博不同,这是真正的信息市场:你懂的信息越多,胜率越高。我靠预测技术相关事件的走向已经赚了不少。用我的邀请链接注册:Polymarket.com