Using SQLite from Lua

Overview

SQLite is a self-contained, zero-configuration, embedded SQL database engine. In Lua, the two main options for working with SQLite 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):

opm install Starrhoro/lsqlite3

Opening a Database

lsqlite3 opens a database with sqlite3.open():

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. You can enable it explicitly:

db:execute("PRAGMA journal_mode = WAL")

Creating Tables

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.

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()

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()

Always call :finalize() when done with a prepared statement to free its resources.

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()

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 aren’t available, it returns positional tables.

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()

Quick Row Count

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

Transactions

SQLite operations are atomic when wrapped in a transaction. Without a transaction, each execute() call is its own mini-transaction, which is slower and less safe for bulk operations.

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

On error, roll back:

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. The module defines constants for them:

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) — row available from :step()
• sqlite3.DONE (101) — query finished
• sqlite3.ERROR (1) — generic error
• sqlite3.CONSTRAINT (19) — constraint violation
• sqlite3.MISUSE (21) — API used incorrectly

Wrap database calls in pcall to catch errors:

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 worker process has its own SQLite connection. Opening a database in the init phase keeps it available for all requests:

-- 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

Always use prepared statements for user data. Never concatenate strings into SQL:

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

Built-in SQLite Functions

SQLite includes many built-in functions you can use in queries:

-- 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

• /guides/lua-config-files/ — reading configuration data (often stored in SQLite)
• /guides/lua-serialization/ — serializing Lua data structures for storage
• /tutorials/file-io/ — working with files alongside database operations