Async queries, parameter binding, transactions, and SQLite.

Databases and drivers

demi.database provides asynchronous connections and parameterized queries. SQLite is the built-in driver. Other databases need a driver implementing the native DBDriver/DBConnection interfaces and registration with DatabaseService; selecting an arbitrary name does not install a driver.

Sequential worker queries

local Task = require("demi.task")
local job = Task.fork(function()
    local Database = require("demi.database")
    local db, err = Database.open({path = "scores.db"})
    assert(db, err)
    assert(db:execute("CREATE TABLE IF NOT EXISTS scores(name TEXT, points INTEGER)"):wait())
    assert(db:execute("INSERT INTO scores VALUES (?, ?)", {"Ada", 120}):wait())
    local result, query_error = db:query("SELECT name, points FROM scores WHERE points > ?", {100}):wait()
    assert(result, query_error)
    assert(db:close():wait())
    return result.rows
end)

Inside a Task.fork worker, Database.open(settings) connects and waits for setup, returning a ready connection or nil, error. Chain db:query(...):wait() to receive decoded rows directly. Query, execute, transaction, close and connect operations all support wait(timeout_ms), returning their existing result or nil, error. A successful connect wait returns {connected = true}.

Omit the timeout to wait until completion or task cancellation; zero checks immediately and negative values are rejected. Timeout returns nil, "timeout" and task cancellation returns nil, "cancelled". Both stop waiting without cancelling the database operation; a write may still commit. open and wait are worker-only and return nil, error on the main thread. Import the database module inside the worker and return plain result data through the task; native connection and operation handles are not copied task arguments or results.

Polling on the main thread

local Database = require("demi.database")
local pending, callback
local function when_done(operation, next_step)
    pending, callback = operation, next_step
end

function on_start()
    local db, err = Database.connect({ driver = "sqlite", path = "scores.db" })
    assert(db, err)
    local opening = db:connect_operation()
    when_done(opening, function()
        when_done(db:execute("CREATE TABLE IF NOT EXISTS scores(name TEXT, points INTEGER)"), function()
            when_done(db:execute("INSERT INTO scores(name, points) VALUES (?, ?)", {"Ada", 120}), function()
                when_done(db:query("SELECT name, points FROM scores WHERE points > ?", {100}), function(result)
                    print(result.rows[1][1], result.rows[1][2])
                    when_done(db:close(), function() end)
                end)
            end)
        end)
    end)
end

function on_update(dt)
    if not pending or not pending:done() then return end
    local operation, next_step = pending, callback
    pending, callback = nil, nil
    next_step(assert(operation:result(), operation:error()))
end

SQLite relative paths are below the application's writable databases/ directory, not the project or cooked files. :memory: creates a private in-memory database. Absolute paths and traversal outside the database directory are rejected by the Lua API. Driver options, such as SQLite busy_timeout_ms, go in the connection's options table.

Operations retain asynchronous done(), result(), error(), and cancel() methods. Poll from main-thread callbacks such as on_update; a ready operation can have failed, so check its result/error. Connections serialize operations in submission order over a shared native database pool. One connection does not allocate its own OS thread, and queued database requests do not occupy database workers. A Lua worker calling wait waits cooperatively on its own thread while the main loop continues.

Parameters and result rows

Plain Lua strings bind as SQL TEXT. Use Database.blob(bytes) to bind a BLOB, including empty values and embedded NUL bytes. Selected BLOBs return binary-safe Lua strings.

Use positional ? placeholders and a dense parameter array. Never build SQL by concatenating player-provided text. Use Data.null for SQL NULL values rather than holes in a Lua array. Plain Lua strings bind as SQL TEXT; wrap bytes with Database.blob(bytes) to bind SQL BLOB, including an empty BLOB. The same wrapper works in each transaction entry's parameters array.

local bytes = string.char(0, 255, 65)
-- With a connected db and the when_done/on_update helper above:
when_done(db:execute("INSERT INTO files(data) VALUES (?)", { Database.blob(bytes) }), function(result)
    print(result.changes)
end)

Results contain a columns array and a rows array of positional rows, preserving duplicate column names and nulls. SQL NULL becomes Data.null; BLOB values become binary safe Lua strings, preserving embedded NUL and non UTF-8 bytes. TEXT and BLOB results are both Lua strings, so SQL type information is not retained in the returned Lua value. Modification results also report changed rows and the last inserted row ID.

Transactions and cancellation

connection:transaction(statements) runs an ordered array of {sql = "...", parameters = {...}} entries as one transaction. A statement failure or observed cancellation rolls it back. Individual query/execute calls accept one statement; use the transaction API for a batch.

Cancellation settles the operation handle immediately and asks running work to stop. SQLite checks cancellation during query execution. Waiting on a database lock is bounded by its busy timeout. Cancellation cannot undo a write already committed, and third-party drivers must implement their own cancellation and timeout support. Shutdown joins workers rather than abandoning live connections; a non-cooperative driver may delay shutdown.

Adding another driver

Registry access and connect submission are synchronized. Driver factories run outside service locks on the submitting thread and must support concurrent calls. Service shutdown rejects new submissions and joins the database pool; owners must join Lua workers before shutting down their externally owned database service.

Calls for one connection are serialized, but may run on different worker threads. Drivers requiring thread affinity must provide an adapter for that requirement. Factory construction runs on the caller thread and should not perform I/O. Destructors must also tolerate final cleanup after worker shutdown.

Implement DBDriver::connect and a DBConnection with query, execute, transaction and close behavior. Register a factory under a name with DatabaseService::registerDriver before connecting. Driver calls run on workers and must not access Lua, game entities or renderer state. Keep each connection's mutable driver state inside that connection, observe the cancellation token, and return the common JSON result shape. The Lua call remains Database.connect({driver = "your_driver", path = "your_endpoint", options = {...}}); SQL dialect and driver-specific connection options are not translated automatically.