SQL Database

Execute SQL queries against PostgreSQL, MySQL, SQLite, MSSQL, and Oracle databases. Features include parameterized queries, transactions, prepared statements, and a fluent query builder.

For database configuration, see Database.

Loading

local sql = require("sql")

Acquiring a Connection

Get a database connection from the resource registry:

local db, err = sql.get("app.db:main")
if err then
    return nil, err
end

local rows = db:query("SELECT * FROM users WHERE active = ?", {1})

db:release()

Returns: DB, error

Constants

Database Types

sql.type.POSTGRES    -- "postgres"
sql.type.MYSQL       -- "mysql"
sql.type.SQLITE      -- "sqlite"
sql.type.MSSQL       -- "mssql"
sql.type.ORACLE      -- "oracle"
sql.type.UNKNOWN     -- "unknown"

Isolation Levels

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

local insert = sql.builder.insert("users")
    :columns("name", "email")
    :values("alice", sql.NULL)

Type Coercion

as.int

local value = sql.as.int(42)

Returns: userdata

as.float

Coerces value to SQL float type.

local value = sql.as.float(19.99)

Returns: userdata

as.text

Coerces value to SQL text type.

local value = sql.as.text("hello")

Returns: userdata

as.binary

Coerces value to SQL binary type.

local value = sql.as.binary("binary data")

Returns: userdata

as.null

Returns SQL NULL marker.

local value = sql.as.null()

Returns: userdata

Query Builder

Creating Queries

local query = sql.builder.select("id", "name")
    :from("users")
    :where({active = 1})

Returns: SelectBuilder

builder.insert

Creates INSERT query builder.

local query = sql.builder.insert("users")
    :columns("name", "email")
    :values("alice", "alice@example.com")

Returns: InsertBuilder

builder.update

Creates UPDATE query builder.

local query = sql.builder.update("users")
    :set("status", "active")
    :where({id = 123})

Returns: UpdateBuilder

builder.delete

Creates DELETE query builder.

local query = sql.builder.delete("users")
    :where({active = 0})
    :limit(100)

Returns: DeleteBuilder

builder.expr

Creates raw SQL expression for use in where/having clauses.

local expr = sql.builder.expr("score BETWEEN ? AND ?", 80, 90)

Returns: Sqlizer

builder.eq

Creates equality condition from table.

local cond = sql.builder.eq({active = 1, status = "open"})

Returns: Sqlizer

builder.not_eq

Creates inequality condition from table.

local cond = sql.builder.not_eq({status = "closed"})

Returns: Sqlizer

builder.lt

Creates less-than condition from table.

local cond = sql.builder.lt({age = 18})

Returns: Sqlizer

builder.lte

Creates less-than-or-equal condition from table.

local cond = sql.builder.lte({price = 100})

Returns: Sqlizer

builder.gt

Creates greater-than condition from table.

local cond = sql.builder.gt({score = 80})

Returns: Sqlizer

builder.gte

Creates greater-than-or-equal condition from table.

local cond = sql.builder.gte({age = 21})

Returns: Sqlizer

builder.like

Creates LIKE condition from table.

local cond = sql.builder.like({name = "john%"})

Returns: Sqlizer

builder.not_like

Creates NOT LIKE condition from table.

local cond = sql.builder.not_like({email = "%@spam.com"})

Returns: Sqlizer

builder.and_

Combines multiple conditions with AND.

local cond = sql.builder.and_({
    sql.builder.eq({active = 1}),
    sql.builder.gt({score = 80})
})

Returns: Sqlizer

builder.or_

Combines multiple conditions with OR.

local cond = sql.builder.or_({
    sql.builder.eq({status = "pending"}),
    sql.builder.eq({status = "active"})
})

Returns: Sqlizer

builder.question

Placeholder format for ? placeholders (default).

local query = sql.builder.select("*")
    :from("users")
    :placeholder_format(sql.builder.question)

builder.dollar

Placeholder format for $1, $2, ... placeholders.

local query = sql.builder.select("*")
    :from("users")
    :placeholder_format(sql.builder.dollar)

builder.at

Placeholder format for @p1, @p2, ... placeholders.

local query = sql.builder.select("*")
    :from("users")
    :placeholder_format(sql.builder.at)

builder.colon

Placeholder format for :1, :2, ... placeholders.

local query = sql.builder.select("*")
    :from("users")
    :placeholder_format(sql.builder.colon)

Connection Methods

Database connection handle returned by sql.get().

db:type

Returns database type constant.

local dbtype, err = db:type()

Returns: string, error

db:query

Executes SELECT query and returns rows.

local rows, err = db:query("SELECT id, name FROM users WHERE active = ?", {1})

Returns: table[], error

db:execute

Executes INSERT/UPDATE/DELETE query.

local result, err = db:execute("INSERT INTO users (name) VALUES (?)", {"alice"})

Returns: table, error

Returns table with fields:

• last_insert_id - Last inserted ID
• rows_affected - Number of rows affected

db:prepare

Creates prepared statement for repeated execution.

local stmt, err = db:prepare("SELECT * FROM users WHERE id = ?")

Returns: Statement, error

db:begin

Begins database transaction.

local tx, err = db:begin({
    isolation = sql.isolation.SERIALIZABLE,
    read_only = false
})

Options table fields:

• isolation - Isolation level from sql.isolation.* (default: DEFAULT)
• read_only - Read-only transaction flag (default: false)

Returns: Transaction, error

db:release

Releases database resource back to pool.

local ok, err = db:release()

Returns: boolean, error

db:stats

Returns connection pool statistics.

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

Prepared statement returned by db:prepare().

stmt:query

Executes prepared statement as SELECT.

local rows, err = stmt:query({123})

Returns: table[], error

stmt:execute

Executes prepared statement as INSERT/UPDATE/DELETE.

local result, err = stmt:execute({"alice"})

Returns: table, error

Returns table with fields:

• last_insert_id - Last inserted ID
• rows_affected - Number of rows affected

stmt:close

Closes prepared statement.

local ok, err = stmt:close()

Returns: boolean, error

Transactions

Database transaction returned by db:begin().

tx:db_type

Returns database type constant.

local dbtype, err = tx:db_type()

Returns: string, error

tx:query

Executes SELECT query within transaction.

local rows, err = tx:query("SELECT id, name FROM users WHERE active = ?", {1})

Returns: table[], error

tx:execute

Executes INSERT/UPDATE/DELETE within transaction.

local result, err = tx:execute("INSERT INTO users (name) VALUES (?)", {"alice"})

Returns: table, error

Returns table with fields:

• last_insert_id - Last inserted ID
• rows_affected - Number of rows affected

tx:prepare

Creates prepared statement within transaction.

local stmt, err = tx:prepare("SELECT * FROM users WHERE id = ?")

Returns: Statement, error

tx:commit

Commits transaction.

local ok, err = tx:commit()

Returns: boolean, error

tx:rollback

Rolls back transaction.

local ok, err = tx:rollback()

Returns: boolean, error

tx:savepoint

Creates named savepoint within transaction.

local ok, err = tx:savepoint("sp1")

Returns: boolean, error

tx:rollback_to

Rolls back to named savepoint.

local ok, err = tx:rollback_to("sp1")

Returns: boolean, error

tx:release

Releases savepoint.

local ok, err = tx:release("sp1")

Returns: boolean, error

SELECT Builder

Fluent interface for building SELECT queries.

select:from

Sets FROM clause.

local query = sql.builder.select("id", "name"):from("users")

Returns: SelectBuilder

select:join

Adds JOIN clause.

local query = sql.builder.select("*")
    :from("users")
    :join("orders ON orders.user_id = users.id")

Returns: SelectBuilder

select:left_join

Adds LEFT JOIN clause.

local query = sql.builder.select("*")
    :from("users")
    :left_join("orders ON orders.user_id = users.id")

Returns: SelectBuilder

select:right_join

Adds RIGHT JOIN clause.

local query = sql.builder.select("*")
    :from("users")
    :right_join("orders ON orders.user_id = users.id")

Returns: SelectBuilder

select:inner_join

Adds INNER JOIN clause.

local query = sql.builder.select("*")
    :from("users")
    :inner_join("orders ON orders.user_id = users.id")

Returns: SelectBuilder

select:where

Adds WHERE condition.

local query = sql.builder.select("*")
    :from("users")
    :where({active = 1})

Supports three formats:

• String: where("status = ?", "active")
• Table: where({status = "active"})
• Sqlizer: where(sql.builder.gt({score = 80}))

Returns: SelectBuilder

select:order_by

Adds ORDER BY clause.

local query = sql.builder.select("*")
    :from("users")
    :order_by("name ASC", "created_at DESC")

Returns: SelectBuilder

select:group_by

Adds GROUP BY clause.

local query = sql.builder.select("status", "COUNT(*)")
    :from("users")
    :group_by("status")

Returns: SelectBuilder

select:having

Adds HAVING condition.

local query = sql.builder.select("status", "COUNT(*) as cnt")
    :from("users")
    :group_by("status")
    :having(sql.builder.gt({cnt = 10}))

Returns: SelectBuilder

select:limit

Sets LIMIT.

local query = sql.builder.select("*")
    :from("users")
    :limit(10)

Returns: SelectBuilder

select:offset

Sets OFFSET.

local query = sql.builder.select("*")
    :from("users")
    :offset(20)

Returns: SelectBuilder

select:columns

Adds columns to SELECT.

local query = sql.builder.select():columns("id", "name", "email")

Returns: SelectBuilder

select:distinct

Adds DISTINCT modifier.

local query = sql.builder.select("status")
    :from("users")
    :distinct()

Returns: SelectBuilder

select:suffix

Adds SQL suffix.

local query = sql.builder.select("*")
    :from("users")
    :suffix("FOR UPDATE")

Returns: SelectBuilder

select:placeholder_format

Sets placeholder format.

local query = sql.builder.select("*")
    :from("users")
    :placeholder_format(sql.builder.dollar)

Returns: SelectBuilder

select:to_sql

Generates SQL string and bind arguments.

local sql_str, args = query:to_sql()

Returns: string, table

select:run_with

Creates executor for query.

local executor = query:run_with(db)
local rows, err = executor:query()

Returns: QueryExecutor

INSERT Builder

Fluent interface for building INSERT queries.

insert:into

Sets table name.

local query = sql.builder.insert():into("users")

Returns: InsertBuilder

insert:columns

Sets column names.

local query = sql.builder.insert("users"):columns("name", "email")

Returns: InsertBuilder

insert:values

Adds row values.

local query = sql.builder.insert("users")
    :columns("name", "email")
    :values("alice", "alice@example.com")

Returns: InsertBuilder

insert:set_map

Sets columns and values from table.

local query = sql.builder.insert("users")
    :set_map({name = "alice", email = "alice@example.com"})

Returns: InsertBuilder

insert:select

Inserts from SELECT query.

local select_query = sql.builder.select("name", "email"):from("temp_users")
local query = sql.builder.insert("users")
    :columns("name", "email")
    :select(select_query)

Returns: InsertBuilder

insert:prefix

Adds SQL prefix.

local query = sql.builder.insert("users")
    :prefix("INSERT IGNORE INTO")

Returns: InsertBuilder

insert:suffix

Adds SQL suffix.

local query = sql.builder.insert("users")
    :columns("name")
    :values("alice")
    :suffix("RETURNING id")

Returns: InsertBuilder

insert:options

Adds INSERT options.

local query = sql.builder.insert("users")
    :options("DELAYED", "IGNORE")

Returns: InsertBuilder

insert:placeholder_format

Sets placeholder format.

local query = sql.builder.insert("users")
    :placeholder_format(sql.builder.dollar)

Returns: InsertBuilder

insert:to_sql

Generates SQL string and bind arguments.

local sql_str, args = query:to_sql()

Returns: string, table

insert:run_with

Creates executor for query.

local executor = query:run_with(db)
local result, err = executor:exec()

Returns: QueryExecutor

UPDATE Builder

Fluent interface for building UPDATE queries.

update:table

Sets table name.

local query = sql.builder.update():table("users")

Returns: UpdateBuilder

update:set

Sets column value.

local query = sql.builder.update("users")
    :set("status", "active")
    :set("updated_at", sql.builder.expr("NOW()"))

Returns: UpdateBuilder

update:set_map

Sets multiple columns from table.

local query = sql.builder.update("users")
    :set_map({status = "active", updated_at = sql.builder.expr("NOW()")})

Returns: UpdateBuilder

update:where

Adds WHERE condition.

local query = sql.builder.update("users")
    :set("status", "active")
    :where({id = 123})

Returns: UpdateBuilder

update:order_by

Adds ORDER BY clause.

local query = sql.builder.update("users")
    :set("rank", 1)
    :order_by("score DESC")

Returns: UpdateBuilder

update:limit

Sets LIMIT.

local query = sql.builder.update("users")
    :set("status", "active")
    :limit(10)

Returns: UpdateBuilder

update:offset

Sets OFFSET.

local query = sql.builder.update("users")
    :set("status", "active")
    :offset(5)

Returns: UpdateBuilder

update:suffix

Adds SQL suffix.

local query = sql.builder.update("users")
    :set("status", "active")
    :suffix("RETURNING id")

Returns: UpdateBuilder

update:from

Adds FROM clause.

local query = sql.builder.update("users")
    :set("status", "active")
    :from("other_table")

Returns: UpdateBuilder

update:from_select

Updates from SELECT query.

local select_query = sql.builder.select("*"):from("temp_users")
local query = sql.builder.update("users")
    :set("status", "active")
    :from_select(select_query, "t")

Returns: UpdateBuilder

update:placeholder_format

Sets placeholder format.

local query = sql.builder.update("users")
    :placeholder_format(sql.builder.dollar)

Returns: UpdateBuilder

update:to_sql

Generates SQL string and bind arguments.

local sql_str, args = query:to_sql()

Returns: string, table

update:run_with

Creates executor for query.

local executor = query:run_with(db)
local result, err = executor:exec()

Returns: QueryExecutor

DELETE Builder

Fluent interface for building DELETE queries.

delete:from

Sets table name.

local query = sql.builder.delete():from("users")

Returns: DeleteBuilder

delete:where

Adds WHERE condition.

local query = sql.builder.delete("users")
    :where({active = 0})

Returns: DeleteBuilder

delete:order_by

Adds ORDER BY clause.

local query = sql.builder.delete("users")
    :where({active = 0})
    :order_by("created_at ASC")

Returns: DeleteBuilder

delete:limit

Sets LIMIT.

local query = sql.builder.delete("users")
    :where({active = 0})
    :limit(100)

Returns: DeleteBuilder

delete:offset

Sets OFFSET.

local query = sql.builder.delete("users")
    :where({active = 0})
    :offset(10)

Returns: DeleteBuilder

delete:suffix

Adds SQL suffix.

local query = sql.builder.delete("users")
    :where({active = 0})
    :suffix("RETURNING id")

Returns: DeleteBuilder

delete:placeholder_format

Sets placeholder format.

local query = sql.builder.delete("users")
    :placeholder_format(sql.builder.dollar)

Returns: DeleteBuilder

delete:to_sql

Generates SQL string and bind arguments.

local sql_str, args = query:to_sql()

Returns: string, table

delete:run_with

Creates executor for query.

local executor = query:run_with(db)
local result, err = executor:exec()

Returns: QueryExecutor

Executing Queries

The query executor runs builder-generated queries.

executor:query

Executes query and returns rows (for SELECT).

local rows, err = executor:query()

Returns: table[], error

executor:exec

Executes query and returns result (for INSERT/UPDATE/DELETE).

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

Returns generated SQL and arguments without executing.

local sql_str, args = executor:to_sql()

Returns: string, table

Permissions

Database access is subject to security policy evaluation.

Errors

See Error Handling for working with errors.

Example

local sql = require("sql")

-- Get database connection
local db, err = sql.get("app.db:main")
if err then error(err) end

-- Check database type
local dbtype, _ = db:type()
print("Database type:", dbtype)

-- Direct query
local users, err = db:query("SELECT id, name FROM users WHERE active = ?", {1})
if err then error(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 = query:run_with(db)
local results, err = executor:query()
if err then error(err) end

-- Transaction with savepoints
local tx, err = db:begin({isolation = sql.isolation.SERIALIZABLE})
if err then error(err) end

local _, err = tx:execute("INSERT INTO users (name) VALUES (?)", {"alice"})
if err then
    tx:rollback()
    error(err)
end

tx:savepoint("sp1")

local _, err = tx:execute("UPDATE users SET status = ? WHERE id = ?", {"active", 1})
if err then
    tx:rollback_to("sp1")
else
    tx:release("sp1")
end

local ok, err = tx:commit()
if err then error(err) end

-- Prepared statements
local stmt, err = db:prepare("INSERT INTO logs (message, level) VALUES (?, ?)")
if err then error(err) end

for i = 1, 100 do
    local _, err = stmt:execute({"log message " .. i, "info"})
    if err then
        stmt:close()
        error(err)
    end
end

stmt:close()

-- NULL and typed values
local insert = sql.builder.insert("products")
    :columns("name", "price", "description")
    :values("Widget", sql.as.float(19.99), sql.NULL)

local executor = insert:run_with(db)
local result, err = executor:exec()
if err then error(err) end

print("Inserted ID:", result.last_insert_id)

db:release()