santoku-mysql
View on LuaRocksMySQL and MariaDB client with santoku-sqlite's API: server-side prepared statements, verified TLS, and native and OpenResty cosocket transports
santoku-mysql is a MySQL and MariaDB client with santoku-sqlite's API. The core takes a transport backend: require("santoku.mysql")(backend). santoku-socket's santoku.socket.tcp is the native transport, and santoku-resty's santoku.resty.tcp runs over nginx cosockets, so a query never blocks a worker. Lua drives the socket, and a C codec frames packets, decodes binary rows and encodes parameters. Every bound query is a server-side prepared statement, cached per connection. TLS is required and verified by default; plain TCP is an explicit opt-out. santoku-mysql doesn't depend on either transport, so list santoku-socket or santoku-resty 2.8.0 or newer beside it in your dependencies. The examples write password = "secret" inline. A server reads it from the environment once, in init_by_lua, as the start-server tab's Secrets cards show.
Open a handle and query
open(opts) logs in and returns a handle; a failed login raises the server's message, code and sqlstate. getter(sql, prop) returns a function that runs the statement and returns the first row: prop true gives a table keyed by column name, false gives nothing, and anything else gives the columns as values. When no row matches, the function returns nothing, so the result tests as nil. all(sql, prop) returns every row, each as a named table with prop true or as its first column otherwise. runner returns the affected row count, inserter the insert id, and exec runs SQL with no parameters through the text protocol. Each getter, all, runner and inserter prepares its statement once, when you create it. A connection keeps at most max_statements prepared statements (64 by default) and closes the least recently used one with COM_STMT_CLOSE when it needs room, so SQL built at runtime can't exhaust the server's max_prepared_stmt_count. A statement the server no longer knows (error 1243, as after a proxy switches backends) is prepared again once. USE drops the cached statements, because a statement keeps the database it was prepared under. A call that binds a different Lua type (string, integer, float or boolean) in a position than the last call prepares the statement again, because MySQL 8 casts a string to the type of the statement's first execution, so SELECT ? run with 0.1 and then 'a' would return 0. nil matches any type and prepares nothing new. close() sends COM_QUIT and closes the connection. A call on a closed handle raises mysql: handle closed, and a second close does nothing.
local mysql = require("santoku.mysql")(require("santoku.socket.tcp"))
local db = mysql.open({
host = "db.internal", port = 3306,
user = "app", password = "secret", database = "app",
tls = { cafile = "/etc/ssl/db-ca.pem" },
})
db.exec("CREATE TABLE IF NOT EXISTS notes (id INT PRIMARY KEY AUTO_INCREMENT, body TEXT)")
local add = db.inserter("INSERT INTO notes (body) VALUES (?)")
local one = db.getter("SELECT id, body FROM notes WHERE id = ?", true)
local bodies = db.all("SELECT body FROM notes ORDER BY id")
local id = add("first")
print(one(id).body, #bodies())
print(one(id + 1000))
db.close()
Binding parameters
Values travel in COM_STMT_EXECUTE and never enter the SQL text. ?NNN names a parameter by position, and repeats reuse it. :name takes the next position and reuses it on repeats, so positional arguments fill names in order of first use. A table as the only argument binds :name parameters by key. Question marks and colons inside quotes, backticks and comments are left alone, and := and :: aren't parameters. Integral numbers below 2^63 bind as BIGINT and other numbers as DOUBLE; booleans bind as 1 and 0, strings as binary-safe strings, and nil as NULL. The scanner reads the body of /*! and /*M! comments as SQL, as the server does, and treats a backslash as a plain character when the session runs NO_BACKSLASH_ESCAPES. A value it can't bind, such as a function, raises before anything is sent, and the connection and any open transaction stay as they were.
local get = db.getter("SELECT ?2, ?1, ?2")
print(get("a", "b"))
local find = db.getter("SELECT id FROM users WHERE email = :email OR alias = :email")
print(find({ email = "[email protected]" }))
print(find("[email protected]"))
Types
Rows decode from the binary protocol straight into Lua values. Integers of every width, signed or unsigned, become numbers; past 2^53 they lose precision, so read large ids as strings with CAST(x AS CHAR) when every digit matters. FLOAT and DOUBLE become numbers. DECIMAL stays a string, so no digits are lost. DATE becomes YYYY-MM-DD, DATETIME and TIMESTAMP add hh:mm:ss and the column's fractional digits, and TIME becomes [-]hhh:mm:ss, hours past 24 included. Zero and invalid dates that the server allows come back as written, such as 0000-00-00. Strings, BLOBs, BIT, ENUM, SET and JSON become Lua strings with every byte kept. NULL is nil everywhere, so a named row omits the key.
local id, total, at, body = db.getter("SELECT CAST(18446744073709551615 AS UNSIGNED), "
.. "CAST(1.5 AS DECIMAL(10,2)), TIMESTAMP('2024-01-02 03:04:05.25'), NULL")()
print(id, total, at, body)
Transactions
transaction(fn, ...) runs fn between START TRANSACTION and COMMIT and returns what fn returns. If fn raises, it rolls back and raises the same error. Inside a transaction, transaction(fn) just calls fn. A string first argument passes options, as in transaction("READ ONLY", fn). begin, commit and rollback are there for manual control. begin raises mysql: a transaction is already open when one is, and commit and rollback raise mysql: no transaction is open when none is. begin(tag) and the options string go into the SQL text as written, so pass only fixed text such as READ ONLY or WITH CONSISTENT SNAPSHOT, never user input.
local move = db.runner("UPDATE accounts SET balance = balance + ? WHERE id = ?")
db.transaction(function ()
move(-10, 1)
move(10, 2)
end)
TLS and unix sockets
Over TCP the client asks for TLS on every connection and verifies the server's certificate and host name. tls = true uses the system trust store; a table sets cafile, capath, sslname and verify. A DNS host matches the certificate's DNS names, and an IP host such as 127.0.0.1 matches only its IP addresses (IP:127.0.0.1 in subjectAltName). A server that offers no TLS is refused unless you pass tls = false, which sends queries in the clear. path connects through a unix socket on the same machine, where TLS is off by default. caching_sha2_password works over TLS or a unix socket. Over plain TCP its full login needs opts.rsa, a function that encrypts with a public key, and opts.rsa_key, the server's public key pinned by you; the client never fetches the key from the server, because anyone on the path could swap it and read the password. mysql_native_password works everywhere.
local local_db = mysql.open({ path = "/run/mysqld/mysqld.sock", user = "app", password = "secret" })
local by_ip = mysql.open({ host = "10.0.0.5", user = "app", password = "secret",
tls = { cafile = "/etc/ssl/db-ca.pem" } })
local legacy = mysql.open({ host = "10.0.0.6", user = "app", password = "secret", tls = false })
Under OpenResty
With santoku.resty.tcp every query yields to nginx while it waits on the network. open is lazy there: it checks nothing until the first call. Each call borrows a connection from nginx's keepalive pool and returns it afterwards. A reused connection runs COM_RESET_CONNECTION, SET NAMES and init_sql first, so no call sees another call's user variables, temporary tables, locks or prepared statements; that costs about 0.13 ms more per call than skipping it on loopback, and statements prepare again on each call. A connection whose call ran USE closes instead of going back to the pool, since the reset keeps the database. Leave autocommit on: a call that leaves a transaction open outside begin is rolled back and raises. Up to pool_size connections (16 by default) open per worker for each set of options, and a call past that waits for one to come free, raising "no pooled connection came free" once timeout_ms (30000) passes. pool_idle_ms (60000) is the keepalive timeout. A transaction keeps one connection for its light thread until it ends. A network error, or a Lua error raised while a result is still arriving, closes the connection it hit. iter reads its whole result before returning, because a pooled connection can't stay mid-result. Certificates verify against lua_ssl_trusted_certificate in nginx.conf, and a cafile option raises.
http {
lua_ssl_trusted_certificate /etc/ssl/db-ca.pem;
lua_ssl_verify_depth 2;
server {
location = /user {
content_by_lua_block {
local mysql = require("santoku.mysql")(require("santoku.resty.tcp"))
local db = mysql.open({ host = "db.internal", user = "app", password = "secret",
database = "app", tls = { sslname = "db.internal" } })
local name = db.getter("SELECT name FROM users WHERE id = ?")(tonumber(ngx.var.arg_id))
ngx.say(name or "")
}
}
}
}
Errors and lost connections
A server error raises the message, the MySQL error code and the sqlstate, and the connection stays usable. A network error, a packet out of sequence or a reply past 1 GiB raises a message that starts with mysql: and drops the connection. Natively the next call reconnects, and it also reconnects when the server closed an idle connection, as after KILL or wait_timeout. Inside a transaction the client never reconnects: the failing call raises, and so do commit and rollback, with mysql: connection lost inside a transaction.
local err = require("santoku.error")
local ok, msg, code, state = err.pcall(db.exec, "SELEC 1")
print(ok, code, state)
Differences from santoku-sqlite
Connection settings replace the file path in open. Every connection runs SET NAMES utf8mb4 and then init_sql, so text arrives as UTF-8 whatever the server or column charset; latin1 columns convert on the server. The client asks for session tracking (CLIENT_SESSION_TRACK), so it sees USE and sql_mode changes as they happen. Natively, getter, all, iter, runner and inserter prepare when you create them, as in santoku-sqlite; under OpenResty they prepare on their first call. query(sql, ...) prepares, runs and closes a statement once and returns the rows as arrays plus the column names, as santoku-sqlite's does. changes() returns the row count of the last write and keeps it across reads, as in santoku-sqlite. info() and ping() are extra: info() returns the connection id, server version, current database and whether TLS is on, and ping() checks the connection. santoku-sqlite's db, authorizer and progress have no MySQL counterpart and don't exist here.
local rows, names = db.query("SHOW SESSION STATUS LIKE 'Com_stmt_prepare'")
print(names[1], rows[1][2], db.info().version, db.ping())