SQLite Lua: A Complete Integration Guide with lsqlite3

Overview

SQLite is a self-contained, zero-configuration, embedded SQL database engine that pairs well with Lua. For SQLite Lua development, the two main options are lsqlite3 (a direct binding to the C library) and luasql (a database-independent driver that supports multiple backends including SQLite).

lsqlite3 is the more common choice. It gives you direct access to the SQLite C API while keeping a Lua-friendly interface. luasql is useful if you need to swap between different databases without changing your code.

Installing lsqlite3

With LuaRocks:

luarocks install lsqlite3

For OpenResty (nginx with Lua), the recommended way to install lsqlite3 is via the OpenResty Package Manager (OPM). This pulls a pre-built module that is compatible with OpenResty’s LuaJIT environment and the nginx event loop, so you do not need to compile against the bundled Lua headers yourself:

opm install Starrhoro/lsqlite3

Opening a Database

Once lsqlite3 is installed, the next step is opening a connection to a database file. SQLite databases are stored as single files on disk, making them trivial to back up and move between systems. The sqlite3.open() function accepts a path and an optional flags argument that controls whether the file is created, opened read-only, or accessed with a shared cache:

local sqlite3 = require("lsqlite3")

-- File-based database
local db = sqlite3.open("app.db")

-- In-memory database (useful for testing)
local mem_db = sqlite3.open_memory()

-- Read-only mode
local ro_db = sqlite3.open("app.db", sqlite3.OPEN_READONLY)

-- Shared cache (multiple connections share the same in-memory cache)
local cached_db = sqlite3.open("file::memory:?cache=shared", sqlite3.OPEN_READWRITE + sqlite3.OPEN_CREATE)
db:close()

SQLite uses WAL (Write-Ahead Logging) mode by default in recent versions, which allows concurrent reads while writing. This is important for multi-process environments like OpenResty where several nginx workers may access the same database. You can enable WAL mode explicitly with a PRAGMA statement after opening the connection:

db:execute("PRAGMA journal_mode = WAL")

Creating Tables

After opening a connection, you define the schema by executing CREATE TABLE statements. The example below sets up a typical blog schema with a users table and a posts table that references it through a foreign key; a pattern that exercises auto-increment, unique constraints, default values, and referential integrity all in one pass:

local db = sqlite3.open("app.db")

db:execute([[
  CREATE TABLE IF NOT EXISTS users (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT    NOT NULL UNIQUE,
    email    TEXT,
    created_at TEXT  NOT NULL DEFAULT (datetime('now'))
  )
]])

db:execute([[
  CREATE TABLE IF NOT EXISTS posts (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id    INTEGER NOT NULL,
    title      TEXT    NOT NULL,
    body       TEXT,
    published  INTEGER DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id)
  )
]])

Prepared Statements

Prepared statements protect against SQL injection by separating SQL logic from data. lsqlite3 uses db:prepare() to create a statement object, then :step() to execute it and :reset() to clear it for reuse. Once prepared, a statement can be run many times with different values, which avoids the overhead of re-parsing the SQL on each execution, a meaningful win when inserting hundreds of rows in a loop.

Positional Bindings (?)

local stmt = db:prepare("INSERT INTO users (username, email) VALUES (?, ?)")
stmt:bind_values("alice", "alice@example.com")
stmt:step()
stmt:reset()

stmt:bind_values("bob", "bob@example.com")
stmt:step()
stmt:reset()

stmt:finalize()

Positional bindings use ? placeholders where each question mark is matched in order to the values you supply via :bind_values(). This is the simplest approach when your parameter count is small and the intended order is clear. After binding, :step() runs the statement once and :reset() clears the bound values so you can bind new ones for the next execution.

Named Bindings (:name)

local stmt = db:prepare(
  "INSERT INTO posts (user_id, title, body) VALUES (:uid, :title, :body)"
)

stmt:bind_names({ uid = 1, title = "Hello", body = "World" })
stmt:step()
stmt:reset()

stmt:bind_names({ uid = 1, title = "Second post", body = nil })
stmt:step()
stmt:reset()

stmt:finalize()

Calling :finalize() is mandatory in lsqlite3. It releases the internal SQLite statement handle and any associated memory. If you omit it, you may exhaust the available statement slots, particularly in long-running scripts or OpenResty worker processes that handle many requests. The :reset() method alone only clears bindings so the statement can be reused; it does not free the underlying C resource.

Querying Data

Single Row with step()

local stmt = db:prepare("SELECT username, email FROM users WHERE id = ?")
stmt:bind_values(1)

for row in stmt:rows() do
  print(row[1], row[2])  -- 1-indexed columns
end

stmt:finalize()

The :rows() iterator on a prepared statement fetches results one row at a time, returning each row as a positional table. For quick lookups where you just want all matching rows, lsqlite3 also provides the :urows() shortcut on the database handle itself; it prepares, executes, and iterates in a single step.

All rows at once

for row in db:urows("SELECT id, username, email FROM users") do
  print(row.id, row.username, row.email)  -- named columns when available
end

The :urows() method returns a row iterator where each row is a table. If column names are not available, it returns positional tables indexed by integer. This iterator-style interface is idiomatic Lua and works naturally in generic for loops, which makes it the most commonly used read pattern when you want to iterate over every result row.

When you need manual control over row-by-row iteration, for example when you want to stop early or apply conditional logic per row, the :nrows() method provides a lower-level cursor. Unlike :urows(), it does not use a Lua iterator; you call it in a loop and check for nil to detect the end of the result set.

Multiple Rows with nrows()

local stmt = db:prepare("SELECT id, title FROM posts WHERE user_id = ?")
stmt:bind_values(1)

local count = 0
while true do
  local row = stmt:nrows()
  if not row then break end
  print(row.id, row.title)
  count = count + 1
end

stmt:finalize()

For simple aggregate queries where you only need a single scalar value, lsqlite3 provides the :first_i64() convenience method. This returns the first column of the first row as a Lua number, which is perfect for COUNT, SUM, or last_insert_rowid() calls without the ceremony of preparing and stepping a full statement.

Quick row count

local n = db:first_i64("SELECT COUNT(*) FROM users WHERE published = 1")
print("Published posts: " .. n)

Transactions

SQLite wraps every individual write in an implicit transaction, but explicit transaction control gives you both performance and correctness benefits. By bracketing multiple writes between BEGIN and COMMIT, SQLite holds the journal lock once and applies all changes atomically, so either everything lands or nothing does. This is especially important for multi-table inserts where a partial write would leave the database in an inconsistent state:

db:execute("BEGIN")

db:execute("INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com')")
local user_id = db:first_i64("SELECT last_insert_rowid()")

db:execute("INSERT INTO posts (user_id, title, body) VALUES (?, ?, ?)",
            user_id, "My First Post", "Hello world")

db:execute("COMMIT")

The first example shows the simple case where everything succeeds. In real applications, you must also handle failures: if any statement inside a transaction errors, you should issue a ROLLBACK to discard all changes made so far. Lua’s pcall is the standard way to catch these errors and roll back safely:

db:execute("BEGIN")

local ok, err = pcall(function()
  db:execute("INSERT INTO users (username, email) VALUES (?, ?)", "alice", "alice@example.com")
  db:execute("INSERT INTO posts (user_id, title) VALUES (?, ?)", 999, "Nonexistent user")
end)

if not ok then
  db:execute("ROLLBACK")
  print("Transaction failed: " .. err)
else
  db:execute("COMMIT")
end

Error Handling

lsqlite3 returns error codes as numbers that map to the SQLite C API result codes. Instead of comparing against raw integers, use the named constants the module exposes; this makes your error checks self-documenting and guards against version differences where numeric codes might shift.

local sqlite3 = require("lsqlite3")

db:execute("INSERT INTO users (username) VALUES ('alice')")
-- If 'alice' already exists (UNIQUE constraint):
-- db:errno() == sqlite3.CONSTRAINT
-- db:errmsg() == "UNIQUE constraint failed: users.username"

Common error codes:

• sqlite3.OK (0): success
• sqlite3.ROW (100): a row is available from :step()
• sqlite3.DONE (101): the query has finished executing
• sqlite3.ERROR (1): a generic error occurred
• sqlite3.CONSTRAINT (19): a constraint violation was detected
• sqlite3.MISUSE (21): the API was used incorrectly

When a database call fails, lsqlite3 does not raise a Lua error by default. Instead it sets an error code that you must check manually. Wrapping calls in pcall gives you a Lua-level exception that catches the failure and lets you handle it in a single error path, which is cleaner for scripts that perform multiple sequential database operations:

local ok, err = pcall(function()
  db:execute("INSERT INTO users (username) VALUES (?)", "duplicate-name")
end)

if not ok then
  print("Database error: " .. err)
end

OpenResty Integration

In OpenResty, each nginx worker process is a separate Lua VM, so every worker must open its own independent SQLite connection. The standard pattern is to open the database during the worker initialization phase and store the handle in a module-level variable that all request handlers within that worker share:

-- In init_by_lua (or init_worker_by_lua)
local db = sqlite3.open("/path/to/app.db")
db:execute("PRAGMA journal_mode = WAL")

-- Store in shared dict
ngx.shared.sqlite_db = db  -- won't work directly; use a per-worker pattern instead

-- Better: open in init_worker and store globally
local db  -- global per worker

init_worker_by_lua_block {
  db = sqlite3.open("/path/to/app.db")
  db:execute("PRAGMA journal_mode = WAL")
}

content_by_lua_block {
  local stmt = db:prepare("SELECT * FROM posts WHERE id = ?")
  stmt:bind_values(ngx.var.arg_id)
  for row in stmt:rows() do
    ngx.say(row.title)
  end
  stmt:finalize()
}

SQL injection prevention

SQLite Lua applications must guard against injection attacks by using prepared statements for all user-supplied data. String concatenation into SQL is the single biggest security risk when working with databases. In lsqlite3, prepared statements with positional (?) or named (:name) placeholders keep user-supplied values completely separate from the SQL text. The C-level SQLite library treats bound values as opaque data that can never be interpreted as SQL keywords or operators:

-- WRONG — vulnerable to SQL injection
local query = "SELECT * FROM users WHERE username = '" .. username .. "'"
db:execute(query)

-- RIGHT — user data is bound as a parameter
local stmt = db:prepare("SELECT * FROM users WHERE username = ?")
stmt:bind_values(username)

These two blocks show the wrong approach followed by the correct one. Notice that in the prepared-statement version, the username value is never interpolated into the SQL string. It travels through a completely separate channel inside the SQLite engine, making injection attacks structurally impossible.

Built-in SQLite functions

SQLite bundles a rich set of built-in scalar and aggregate functions that you can call directly in your SQL statements. These cover string manipulation, date and time formatting, and standard aggregates like COUNT, AVG, and GROUP_CONCAT. Since they execute inside the SQLite engine rather than in Lua, they are far more efficient than fetching raw rows and post-processing them in application code:

-- String functions
"SELECT LOWER(username), UPPER(email) FROM users"
"SELECT LENGTH(body) FROM posts"
"SELECT TRIM(title) FROM posts"

-- Date/time
"SELECT datetime('now')"
"SELECT date('now', '-7 days')"
"SELECT strftime('%Y-%m-%d', created_at) FROM users"

-- Aggregate
"SELECT COUNT(*), AVG(price) FROM products WHERE price > 10"
"SELECT user_id, COUNT(*) as cnt FROM posts GROUP BY user_id ORDER BY cnt DESC"

See Also

These resources cover complementary topics for SQLite Lua development workflows:

• Lua Config Files — reading configuration data (often stored in SQLite)
• Lua Serialization — serializing Lua data structures for storage
• File I/O Tutorial , working with files alongside database operations