# "SQL Database"
_Path: en/lua/storage/sql_
> "Execute SQL queries against PostgreSQL, MySQL, and SQLite databases. Features include parameterized queries, transactions, prepared statements, and a…"
## Table of Contents
- SQL Database
## Content
# SQL Database
The `sql` module runs queries against configured PostgreSQL, MySQL, and SQLite databases. It supports parameterized queries, transactions, prepared statements, and query builders.
This page is an API reference. Its snippets assume a configured database, permission to acquire it, and any tables named by the query. They illustrate individual calls rather than a standalone application. The combined recipe at the end states its additional schema and driver assumptions.
For database configuration, see [Database](system/database.md).
## Loading
```lua
local sql = require("sql")
```
## `sql.get`
Acquire a database connection from the resource registry:
```lua
local db, err = sql.get("app.db:main")
if err then
return nil, err
end
local function finish(value, primary_err)
local _, release_err = db:release()
if primary_err then return nil, primary_err end
if release_err then return nil, release_err end
return value
end
local rows, err = db:query("SELECT * FROM users WHERE active = ?", {1})
if err then
return finish(nil, err)
end
return finish(rows)
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `id` | string | Resource ID (e.g., "app.db:main") |
**Returns:** `DB, error`
Database leases are released during execution-frame cleanup. Call `db:release()` explicitly when database work finishes, especially in long-running operations.
Placeholders are passed to the database driver unchanged; the runtime does not rewrite them. SQLite and MySQL use `?`, PostgreSQL uses `$1, $2` — write them in the form your driver expects. The examples below use `?` (SQLite/MySQL). For queries that target more than one engine, build them with the [query builder](#query-builder): `run_with` rewrites placeholders to `$1, $2` when the handle is PostgreSQL, and `to_sql` uses the builder's `placeholder_format`.
### Database Types
```lua
sql.type.POSTGRES -- "postgres"
sql.type.MYSQL -- "mysql"
sql.type.SQLITE -- "sqlite"
sql.type.UNKNOWN -- "unknown"
```
### Isolation Levels
```lua
sql.isolation.DEFAULT -- "default"
sql.isolation.READ_UNCOMMITTED -- "read_uncommitted"
sql.isolation.READ_COMMITTED -- "read_committed"
sql.isolation.WRITE_COMMITTED -- "write_committed"
sql.isolation.REPEATABLE_READ -- "repeatable_read"
sql.isolation.SERIALIZABLE -- "serializable"
```
### NULL Value
```lua
local insert = sql.builder.insert("users")
:columns("name", "email")
:values("alice", sql.NULL)
```
### `sql.as.int`
Coerce a value to the SQL integer type.
```lua
local value = sql.as.int(42)
```
**Returns:** `userdata`
### `sql.as.float`
Coerce a value to the SQL float type.
```lua
local value = sql.as.float(19.99)
```
**Returns:** `userdata`
### `sql.as.text`
Coerce a value to the SQL text type.
```lua
local value = sql.as.text("hello")
```
**Returns:** `userdata`
### `sql.as.binary`
Coerce a value to the SQL binary type.
```lua
local value = sql.as.binary("binary data")
```
**Returns:** `userdata`
### `sql.as.null`
Return the SQL `NULL` marker.
```lua
local value = sql.as.null()
```
**Returns:** `userdata`
### `sql.builder.select`
Create a `SELECT` query builder.
```lua
local query = sql.builder.select("id", "name")
:from("users")
:where({active = 1})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `columns` | ...string | Column names (optional) |
**Returns:** `SelectBuilder`
### `sql.builder.insert`
Create an `INSERT` query builder.
```lua
local query = sql.builder.insert("users")
:columns("name", "email")
:values("alice", "alice@example.com")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `table` | string | Table name (optional) |
**Returns:** `InsertBuilder`
### `sql.builder.update`
Create an `UPDATE` query builder.
```lua
local query = sql.builder.update("users")
:set("status", "active")
:where({id = 123})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `table` | string | Table name (optional) |
**Returns:** `UpdateBuilder`
### `sql.builder.delete`
Create a `DELETE` query builder.
```lua
local query = sql.builder.delete("users")
:where({active = 0})
:limit(100)
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `table` | string | Table name (optional) |
**Returns:** `DeleteBuilder`
### `sql.builder.expr`
Create a raw SQL expression for use in `WHERE` or `HAVING` clauses.
```lua
local expr = sql.builder.expr("score BETWEEN ? AND ?", 80, 90)
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `sql` | string | SQL expression with ? placeholders |
| `args` | ...any | Bind arguments (optional) |
**Returns:** `Sqlizer`
### `sql.builder.eq`
Create equality conditions from a table.
```lua
local cond = sql.builder.eq({active = 1, status = "open"})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `map` | table | {column = value} pairs |
**Returns:** `Sqlizer`
### `sql.builder.not_eq`
Create inequality conditions from a table.
```lua
local cond = sql.builder.not_eq({status = "closed"})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `map` | table | {column = value} pairs |
**Returns:** `Sqlizer`
### `sql.builder.lt`
Create less-than conditions from a table.
```lua
local cond = sql.builder.lt({age = 18})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `map` | table | {column = value} pairs |
**Returns:** `Sqlizer`
### `sql.builder.lte`
Create less-than-or-equal conditions from a table.
```lua
local cond = sql.builder.lte({price = 100})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `map` | table | {column = value} pairs |
**Returns:** `Sqlizer`
### `sql.builder.gt`
Create greater-than conditions from a table.
```lua
local cond = sql.builder.gt({score = 80})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `map` | table | {column = value} pairs |
**Returns:** `Sqlizer`
### `sql.builder.gte`
Create greater-than-or-equal conditions from a table.
```lua
local cond = sql.builder.gte({age = 21})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `map` | table | {column = value} pairs |
**Returns:** `Sqlizer`
### `sql.builder.like`
Create `LIKE` conditions from a table.
```lua
local cond = sql.builder.like({name = "john%"})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `map` | table | {column = value} pairs |
**Returns:** `Sqlizer`
### `sql.builder.not_like`
Create `NOT LIKE` conditions from a table.
```lua
local cond = sql.builder.not_like({email = "%@spam.com"})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `map` | table | {column = value} pairs |
**Returns:** `Sqlizer`
### `sql.builder.and_`
Combine multiple conditions with `AND`.
```lua
local cond = sql.builder.and_({
sql.builder.eq({active = 1}),
sql.builder.gt({score = 80})
})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `conditions` | table | Array of Sqlizer or table conditions |
**Returns:** `Sqlizer`
### `sql.builder.or_`
Combine multiple conditions with `OR`.
```lua
local cond = sql.builder.or_({
sql.builder.eq({status = "pending"}),
sql.builder.eq({status = "active"})
})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `conditions` | table | Array of Sqlizer or table conditions |
**Returns:** `Sqlizer`
## sqlizer:to_sql
Generates the SQL fragment and bind arguments of a condition.
```lua
local frag, args = sql.builder.eq({active = 1}):to_sql()
```
**Returns:** `string, table`
## builder.question
Use `?` placeholders (default). This format is also available as `sql.builder.default_placeholder`.
```lua
local query = sql.builder.select("*")
:from("users")
:placeholder_format(sql.builder.question)
```
### `sql.builder.dollar`
Use `$1, $2, ...` placeholders.
```lua
local query = sql.builder.select("*")
:from("users")
:placeholder_format(sql.builder.dollar)
```
### `sql.builder.at`
Use `@p1, @p2, ...` placeholders (SQL Server style). Pass this format to `placeholder_format` like the formats above.
### `sql.builder.colon`
Use `:1, :2, ...` placeholders. Pass this format to `placeholder_format` like the formats above.
## Connection Methods
A connection handle returned by `sql.get()` provides query, transaction, statement, and pool operations.
### `db:type`
Return the database type constant.
```lua
local dbtype, err = db:type()
```
**Returns:** `string, error`
### `db:query`
Run a `SELECT` query and return its rows.
Native timestamps returned by the driver become UTC RFC3339 strings with up to nanosecond precision. Zero fractions are omitted and trailing fractional zeros are trimmed. Text columns remain unchanged. This applies to prepared and transactional queries too; compare timestamps as instants or in SQL, not by string ordering.
```lua
local rows, err = db:query("SELECT id, name FROM users WHERE active = ?", {1})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `sql` | string | SQL query with ? placeholders |
| `params` | table | Array of bind parameters (optional) |
**Returns:** `table[], error`
### `db:execute`
Run an `INSERT`, `UPDATE`, or `DELETE` statement.
```lua
local result, err = db:execute("INSERT INTO users (name) VALUES (?)", {"alice"})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `sql` | string | SQL statement with ? placeholders |
| `params` | table | Array of bind parameters (optional) |
**Returns:** `table, error`
Returns table with fields:
- `last_insert_id` - Last inserted ID
- `rows_affected` - Number of rows affected
### `db:prepare`
Create a prepared statement for repeated execution.
```lua
local stmt, err = db:prepare("SELECT * FROM users WHERE id = ?")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `sql` | string | SQL with ? placeholders |
**Returns:** `Statement, error`
### `db:begin`
Begin a database transaction.
```lua
local tx, err = db:begin({
isolation = sql.isolation.SERIALIZABLE,
read_only = false
})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `options` | table | Transaction options (optional) |
Options table fields:
- `isolation` - Isolation level from sql.isolation.* (default: DEFAULT)
- `read_only` - Read-only transaction flag (default: false)
**Returns:** `Transaction, error`
### `db:release`
Release the database resource back to the pool.
```lua
local ok, err = db:release()
```
**Returns:** `boolean, error`
The operation is idempotent.
### `db:stats`
Return connection-pool statistics.
```lua
local stats, err = db:stats()
```
**Returns:** `table, error`
Returns table with fields:
- `max_open_connections` - Max allowed open connections
- `open_connections` - Current open connections
- `in_use` - Connections currently in use
- `idle` - Idle connections in pool
- `wait_count` - Total connection wait count
- `wait_duration` - Total wait duration
- `max_idle_closed` - Connections closed due to max idle
- `max_idle_time_closed` - Connections closed due to idle timeout
- `max_lifetime_closed` - Connections closed due to max lifetime
## Prepared Statements
A prepared statement returned by `db:prepare()` can be queried or executed repeatedly.
### `stmt:query`
Run the prepared statement as a `SELECT` query.
```lua
local rows, err = stmt:query({123})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `params` | table | Array of bind parameters (optional) |
**Returns:** `table[], error`
### `stmt:execute`
Run the prepared statement as an `INSERT`, `UPDATE`, or `DELETE` statement.
```lua
local result, err = stmt:execute({"alice"})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `params` | table | Array of bind parameters (optional) |
**Returns:** `table, error`
Returns table with fields:
- `last_insert_id` - Last inserted ID
- `rows_affected` - Number of rows affected
### `stmt:close`
Close the prepared statement.
```lua
local ok, err = stmt:close()
```
**Returns:** `boolean, error`
## Transactions
A transaction returned by `db:begin()` provides query, statement, savepoint, commit, and rollback operations.
An active transaction is rolled back automatically during execution-frame cleanup. Commit or roll it back explicitly as soon as its work is complete.
### `tx:db_type`
Return the database type constant.
```lua
local dbtype, err = tx:db_type()
```
**Returns:** `string, error`
### `tx:query`
Run a `SELECT` query within the transaction.
```lua
local rows, err = tx:query("SELECT id, name FROM users WHERE active = ?", {1})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `sql` | string | SQL query with ? placeholders |
| `params` | table | Array of bind parameters (optional) |
**Returns:** `table[], error`
### `tx:execute`
Run an `INSERT`, `UPDATE`, or `DELETE` statement within the transaction.
```lua
local result, err = tx:execute("INSERT INTO users (name) VALUES (?)", {"alice"})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `sql` | string | SQL statement with ? placeholders |
| `params` | table | Array of bind parameters (optional) |
**Returns:** `table, error`
Returns table with fields:
- `last_insert_id` - Last inserted ID
- `rows_affected` - Number of rows affected
### `tx:prepare`
Create a prepared statement within the transaction.
```lua
local stmt, err = tx:prepare("SELECT * FROM users WHERE id = ?")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `sql` | string | SQL with ? placeholders |
**Returns:** `Statement, error`
### `tx:commit`
Commit the transaction.
```lua
local ok, err = tx:commit()
```
**Returns:** `boolean, error`
### `tx:rollback`
Roll back the transaction.
```lua
local ok, err = tx:rollback()
```
**Returns:** `boolean, error`
### `tx:savepoint`
Create a named savepoint within the transaction.
```lua
local ok, err = tx:savepoint("sp1")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `name` | string | Savepoint name (alphanumeric and underscore only) |
**Returns:** `boolean, error`
### `tx:rollback_to`
Roll back to a named savepoint.
```lua
local ok, err = tx:rollback_to("sp1")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `name` | string | Savepoint name |
**Returns:** `boolean, error`
### `tx:release`
Release a savepoint.
```lua
local ok, err = tx:release("sp1")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `name` | string | Savepoint name |
**Returns:** `boolean, error`
## SELECT Builder
Build a `SELECT` query one clause at a time.
### `select:from`
Set the `FROM` clause.
```lua
local query = sql.builder.select("id", "name"):from("users")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `table` | string | Table name |
**Returns:** `SelectBuilder`
### `select:join`
Add a `JOIN` clause.
```lua
local query = sql.builder.select("*")
:from("users")
:join("orders ON orders.user_id = users.id")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `join` | string | JOIN clause with ? placeholders |
| `args` | ...any | Bind arguments (optional) |
**Returns:** `SelectBuilder`
### `select:left_join`
Add a `LEFT JOIN` clause.
```lua
local query = sql.builder.select("*")
:from("users")
:left_join("orders ON orders.user_id = users.id")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `join` | string | JOIN clause with ? placeholders |
| `args` | ...any | Bind arguments (optional) |
**Returns:** `SelectBuilder`
### `select:right_join`
Add a `RIGHT JOIN` clause.
```lua
local query = sql.builder.select("*")
:from("users")
:right_join("orders ON orders.user_id = users.id")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `join` | string | JOIN clause with ? placeholders |
| `args` | ...any | Bind arguments (optional) |
**Returns:** `SelectBuilder`
### `select:inner_join`
Add an `INNER JOIN` clause.
```lua
local query = sql.builder.select("*")
:from("users")
:inner_join("orders ON orders.user_id = users.id")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `join` | string | JOIN clause with ? placeholders |
| `args` | ...any | Bind arguments (optional) |
**Returns:** `SelectBuilder`
### `select:where`
Add a `WHERE` condition.
```lua
local query = sql.builder.select("*")
:from("users")
:where({active = 1})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `condition` | string\|table\|Sqlizer | WHERE condition |
| `args` | ...any | Bind arguments (optional, when using string) |
The method accepts three formats:
- String: `where("status = ?", "active")`
- Table: `where({status = "active"})`
- Sqlizer: `where(sql.builder.gt({score = 80}))`
**Returns:** `SelectBuilder`
### `select:order_by`
Add an `ORDER BY` clause.
```lua
local query = sql.builder.select("*")
:from("users")
:order_by("name ASC", "created_at DESC")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `columns` | ...string | Column names with optional ASC/DESC |
**Returns:** `SelectBuilder`
### `select:group_by`
Add a `GROUP BY` clause.
```lua
local query = sql.builder.select("status", "COUNT(*)")
:from("users")
:group_by("status")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `columns` | ...string | Column names |
**Returns:** `SelectBuilder`
### `select:having`
Add a `HAVING` condition.
```lua
local query = sql.builder.select("status", "COUNT(*) as cnt")
:from("users")
:group_by("status")
:having(sql.builder.gt({cnt = 10}))
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `condition` | string\|table\|Sqlizer | HAVING condition |
| `args` | ...any | Bind arguments (optional, when using string) |
**Returns:** `SelectBuilder`
### `select:limit`
Set the `LIMIT` value.
```lua
local query = sql.builder.select("*")
:from("users")
:limit(10)
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `n` | integer | Limit value |
**Returns:** `SelectBuilder`
### `select:offset`
Set the `OFFSET` value.
```lua
local query = sql.builder.select("*")
:from("users")
:offset(20)
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `n` | integer | Offset value |
**Returns:** `SelectBuilder`
### `select:columns`
Add columns to the `SELECT` list.
```lua
local query = sql.builder.select():columns("id", "name", "email")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `columns` | ...string | Column names |
**Returns:** `SelectBuilder`
### `select:distinct`
Add the `DISTINCT` modifier.
```lua
local query = sql.builder.select("status")
:from("users")
:distinct()
```
**Returns:** `SelectBuilder`
### `select:suffix`
Add an SQL suffix.
```lua
local query = sql.builder.select("*")
:from("users")
:suffix("FOR UPDATE")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `sql` | string | SQL suffix with ? placeholders |
| `args` | ...any | Bind arguments (optional) |
**Returns:** `SelectBuilder`
### `select:placeholder_format`
Set the placeholder format.
```lua
local query = sql.builder.select("*")
:from("users")
:placeholder_format(sql.builder.dollar)
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `format` | userdata | Placeholder format (sql.builder.*) |
**Returns:** `SelectBuilder`
### `select:to_sql`
Generate the SQL string and bind arguments.
```lua
local sql_str, args = query:to_sql()
```
**Returns:** `string, table` on success; `nil, error` for an invalid builder state
### `select:run_with`
Create an executor for the query.
```lua
local executor, err = query:run_with(db)
if err then
return nil, err
end
local rows, err = executor:query()
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `db` | DB\|Transaction | Database or transaction handle |
**Returns:** `QueryExecutor, error`
## INSERT Builder
Build an `INSERT` query one clause at a time.
### `insert:into`
Set the table name.
```lua
local query = sql.builder.insert():into("users")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `table` | string | Table name |
**Returns:** `InsertBuilder`
### `insert:columns`
Set the column names.
```lua
local query = sql.builder.insert("users"):columns("name", "email")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `columns` | ...string | Column names |
**Returns:** `InsertBuilder`
### `insert:values`
Add row values.
```lua
local query = sql.builder.insert("users")
:columns("name", "email")
:values("alice", "alice@example.com")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `values` | ...any | Row values |
**Returns:** `InsertBuilder`
### `insert:set_map`
Set columns and values from a table.
```lua
local query = sql.builder.insert("users")
:set_map({name = "alice", email = "alice@example.com"})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `map` | table | {column = value} pairs |
**Returns:** `InsertBuilder`
### `insert:select`
Insert rows from a `SELECT` query.
```lua
local select_query = sql.builder.select("name", "email"):from("temp_users")
local query = sql.builder.insert("users")
:columns("name", "email")
:select(select_query)
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `query` | SelectBuilder | SELECT query |
**Returns:** `InsertBuilder`
### `insert:prefix`
Add an SQL prefix.
```lua
local query = sql.builder.insert("users")
:prefix("/* audit import */")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `sql` | string | SQL prefix with ? placeholders |
| `args` | ...any | Bind arguments (optional) |
**Returns:** `InsertBuilder`
### `insert:suffix`
Add an SQL suffix.
```lua
local query = sql.builder.insert("users")
:columns("name")
:values("alice")
:suffix("RETURNING id")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `sql` | string | SQL suffix with ? placeholders |
| `args` | ...any | Bind arguments (optional) |
**Returns:** `InsertBuilder`
### `insert:options`
Add `INSERT` options.
```lua
local query = sql.builder.insert("users")
:options("DELAYED", "IGNORE")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `options` | ...string | INSERT options |
**Returns:** `InsertBuilder`
### `insert:placeholder_format`
Set the placeholder format.
```lua
local query = sql.builder.insert("users")
:placeholder_format(sql.builder.dollar)
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `format` | userdata | Placeholder format (sql.builder.*) |
**Returns:** `InsertBuilder`
### `insert:to_sql`
Generate the SQL string and bind arguments.
```lua
local sql_str, args = query:to_sql()
```
**Returns:** `string, table` on success; `nil, error` for an invalid builder state
### `insert:run_with`
Create an executor for the query.
```lua
local executor, err = query:run_with(db)
if err then
return nil, err
end
local result, err = executor:exec()
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `db` | DB\|Transaction | Database or transaction handle |
**Returns:** `QueryExecutor, error`
## UPDATE Builder
Build an `UPDATE` query one clause at a time.
### `update:table`
Set the table name.
```lua
local query = sql.builder.update():table("users")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `table` | string | Table name |
**Returns:** `UpdateBuilder`
### `update:set`
Set a column value.
```lua
local query = sql.builder.update("users")
:set("status", "active")
:set("updated_at", sql.builder.expr("NOW()"))
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `column` | string | Column name |
| `value` | any | Column value |
**Returns:** `UpdateBuilder`
### `update:set_map`
Set multiple columns from a table.
```lua
local query = sql.builder.update("users")
:set_map({status = "active", login_count = 0})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `map` | table | {column = value} pairs; values are plain values, `sql.NULL`, or `sql.as.*` (use `set` for expressions) |
**Returns:** `UpdateBuilder`
### `update:where`
Add a `WHERE` condition.
```lua
local query = sql.builder.update("users")
:set("status", "active")
:where({id = 123})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `condition` | string\|table\|Sqlizer | WHERE condition |
| `args` | ...any | Bind arguments (optional, when using string) |
**Returns:** `UpdateBuilder`
### `update:order_by`
Add an `ORDER BY` clause.
```lua
local query = sql.builder.update("users")
:set("rank", 1)
:order_by("score DESC")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `columns` | ...string | Column names with optional ASC/DESC |
**Returns:** `UpdateBuilder`
### `update:limit`
Set the `LIMIT` value.
```lua
local query = sql.builder.update("users")
:set("status", "active")
:limit(10)
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `n` | integer | Limit value |
**Returns:** `UpdateBuilder`
### `update:offset`
Set the `OFFSET` value.
```lua
local query = sql.builder.update("users")
:set("status", "active")
:offset(5)
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `n` | integer | Offset value |
**Returns:** `UpdateBuilder`
### `update:suffix`
Add an SQL suffix.
```lua
local query = sql.builder.update("users")
:set("status", "active")
:suffix("RETURNING id")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `sql` | string | SQL suffix with ? placeholders |
| `args` | ...any | Bind arguments (optional) |
**Returns:** `UpdateBuilder`
### `update:from`
Add a `FROM` clause.
```lua
local query = sql.builder.update("users")
:set("status", "active")
:from("other_table")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `table` | string | Table name |
**Returns:** `UpdateBuilder`
### `update:from_select`
Update rows from a `SELECT` query.
```lua
local select_query = sql.builder.select("*"):from("temp_users")
local query = sql.builder.update("users")
:set("status", "active")
:from_select(select_query, "t")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `query` | SelectBuilder | SELECT query |
| `alias` | string | Table alias |
**Returns:** `UpdateBuilder`
### `update:placeholder_format`
Set the placeholder format.
```lua
local query = sql.builder.update("users")
:placeholder_format(sql.builder.dollar)
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `format` | userdata | Placeholder format (sql.builder.*) |
**Returns:** `UpdateBuilder`
### `update:to_sql`
Generate the SQL string and bind arguments.
```lua
local sql_str, args = query:to_sql()
```
**Returns:** `string, table` on success; `nil, error` for an invalid builder state
### `update:run_with`
Create an executor for the query.
```lua
local executor, err = query:run_with(db)
if err then
return nil, err
end
local result, err = executor:exec()
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `db` | DB\|Transaction | Database or transaction handle |
**Returns:** `QueryExecutor, error`
## DELETE Builder
Build a `DELETE` query one clause at a time.
### `delete:from`
Set the table name.
```lua
local query = sql.builder.delete():from("users")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `table` | string | Table name |
**Returns:** `DeleteBuilder`
### `delete:where`
Add a `WHERE` condition.
```lua
local query = sql.builder.delete("users")
:where({active = 0})
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `condition` | string\|table\|Sqlizer | WHERE condition |
| `args` | ...any | Bind arguments (optional, when using string) |
**Returns:** `DeleteBuilder`
### `delete:order_by`
Add an `ORDER BY` clause.
```lua
local query = sql.builder.delete("users")
:where({active = 0})
:order_by("created_at ASC")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `columns` | ...string | Column names with optional ASC/DESC |
**Returns:** `DeleteBuilder`
### `delete:limit`
Set the `LIMIT` value.
```lua
local query = sql.builder.delete("users")
:where({active = 0})
:limit(100)
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `n` | integer | Limit value |
**Returns:** `DeleteBuilder`
### `delete:offset`
Set the `OFFSET` value.
```lua
local query = sql.builder.delete("users")
:where({active = 0})
:offset(10)
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `n` | integer | Offset value |
**Returns:** `DeleteBuilder`
### `delete:suffix`
Add an SQL suffix.
```lua
local query = sql.builder.delete("users")
:where({active = 0})
:suffix("RETURNING id")
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `sql` | string | SQL suffix with ? placeholders |
| `args` | ...any | Bind arguments (optional) |
**Returns:** `DeleteBuilder`
### `delete:placeholder_format`
Set the placeholder format.
```lua
local query = sql.builder.delete("users")
:placeholder_format(sql.builder.dollar)
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `format` | userdata | Placeholder format (sql.builder.*) |
**Returns:** `DeleteBuilder`
### `delete:to_sql`
Generate the SQL string and bind arguments.
```lua
local sql_str, args = query:to_sql()
```
**Returns:** `string, table` on success; `nil, error` for an invalid builder state
### `delete:run_with`
Create an executor for the query.
```lua
local executor, err = query:run_with(db)
if err then
return nil, err
end
local result, err = executor:exec()
```
| Parameter | Type | Description |
|-----------|------|-------------|
| `db` | DB\|Transaction | Database or transaction handle |
**Returns:** `QueryExecutor, error`
## Executing Queries
The query executor runs builder-generated queries.
### `executor:query`
Run the query and return rows for a `SELECT` statement.
```lua
local rows, err = executor:query()
```
**Returns:** `table[], error`
### `executor:exec`
Run the query and return the result of an `INSERT`, `UPDATE`, or `DELETE` statement.
```lua
local result, err = executor:exec()
```
**Returns:** `table, error`
Returns table with fields:
- `last_insert_id` - Last inserted ID
- `rows_affected` - Number of rows affected
### `executor:to_sql`
Return the generated SQL and arguments without executing the query.
```lua
local sql_str, args = executor:to_sql()
```
**Returns:** `string, table`
## Permissions
Database access is subject to security policy evaluation.
| Action | Resource | Description |
|--------|----------|-------------|
| `db.get` | Database ID | Acquire database connection |
## Errors
| Condition | Kind | Retryable |
|-----------|------|-----------|
| Empty resource ID | `errors.INVALID` | no |
| Permission denied | `errors.PERMISSION_DENIED` | no |
| Resource not found | `errors.NOT_FOUND` | no |
| Resource not database | `errors.INVALID` | no |
| Invalid parameters | `errors.INVALID` | no |
| PostgreSQL syntax/input error | `errors.INVALID` | no |
| SQLite syntax error or corrupt database | `errors.INTERNAL` | no |
| Unique/primary-key violation | `errors.CONFLICT` | no |
| PostgreSQL serialization/deadlock or SQLite busy/locked | `errors.CONFLICT` | yes |
| Driver context canceled or deadline exceeded | `errors.CANCELED` / `errors.TIMEOUT` | no |
| Statement closed | `errors.INVALID` | no |
| Transaction not active | `errors.INVALID` | no |
| Invalid savepoint name | `errors.INVALID` | no |
| Unclassified driver error | `errors.UNKNOWN` | nil |
PostgreSQL errors include `err:details().sqlstate` and available `schema`, `table`, `column`, `constraint`, and `data_type` fields. SQLite errors include numeric `sqlite_code` and `sqlite_extended_code`. Connection and I/O failures can be `errors.UNAVAILABLE`; a non-retryable failure may leave a write's outcome unknown, so do not retry every unavailable error automatically.
See [Error Handling](lua/core/errors.md) for working with errors.
## Combined Partial Recipe
This recipe assumes `app.db:main` is a configured SQLite or MySQL database and already contains `users`, `orders`, and `logs` tables with the referenced columns. It uses `?` placeholders; use `$1`, `$2`, and so on for a PostgreSQL resource. Returned rows depend on the application's data. The surrounding application supplies `report_cleanup_error(err)` so rollback or close failures are observable without replacing the initiating operation error.
```lua
local sql = require("sql")
local db, err = sql.get("app.db:main")
if err then return nil, err end
local function finish(value, primary_err)
local _, release_err = db:release()
if primary_err then return nil, primary_err end
if release_err then return nil, release_err end
return value
end
-- Direct query
local users, err = db:query("SELECT id, name FROM users WHERE active = ?", {1})
if err then
return finish(nil, err)
end
for _, user in ipairs(users) do
print(user.id, user.name)
end
-- Builder pattern
local query = sql.builder.select("u.id", "u.name", "COUNT(o.id) as order_count")
:from("users u")
:left_join("orders o ON o.user_id = u.id")
:where(sql.builder.and_({
sql.builder.eq({["u.active"] = 1}),
sql.builder.gte({["u.score"] = 80})
}))
:group_by("u.id", "u.name")
:having(sql.builder.gt({["COUNT(o.id)"] = 0}))
:order_by("order_count DESC")
:limit(10)
local executor, build_err = query:run_with(db)
if build_err then
return finish(nil, build_err)
end
local results, err = executor:query()
if err then
return finish(nil, err)
end
-- Transaction
local tx, err = db:begin({isolation = sql.isolation.SERIALIZABLE})
if err then
return finish(nil, err)
end
local _, err = tx:execute("INSERT INTO users (name) VALUES (?)", {"alice"})
if err then
local _, rollback_err = tx:rollback()
if rollback_err then report_cleanup_error(rollback_err) end
return finish(nil, err)
end
local _, commit_err = tx:commit()
if commit_err then
return finish(nil, commit_err)
end
-- Prepared statements
local stmt, err = db:prepare("INSERT INTO logs (message, level) VALUES (?, ?)")
if err then
return finish(nil, err)
end
for i = 1, 3 do
local _, err = stmt:execute({"log message " .. i, "info"})
if err then
local _, close_err = stmt:close()
if close_err then report_cleanup_error(close_err) end
return finish(nil, err)
end
end
local _, close_err = stmt:close()
if close_err then
return finish(nil, close_err)
end
return finish({users = users, ranked_users = results})
```
## Navigation
Previous: "HTML Sanitization" (lua/http/html)
Next: "Key-Value Store" (lua/storage/store)