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