Skip to content
Reflex Reflex Reflex Docs

SqlConnection

object

An open JDBC connection, returned by Sql.connect. Run SELECTs with query and everything else with execute; both take ? placeholders and a list of values. A new connection is in auto-commit mode (every statement is committed on its own) unless connect was given autoCommit = false; use begin, commit and rollback to group statements. Close the connection when you are done.

How to get one

  1. Sql .connect()
  2. SqlConnection 
local db = Sql.connect(url)

Returned by

Functions

  • query table Runs a statement that returns rows (SELECT) and returns all rows at once.
  • execute number Runs a statement that does not return rows: INSERT, UPDATE, DELETE or DDL such as CREATE TABLE.
  • begin self Starts a transaction by switching auto-commit off.
  • commit self Commits the changes made since the last commit or rollback.
  • rollback self Discards the changes made since the last commit or rollback.
  • autoCommit self Switches auto-commit on or off.
  • close self Closes the connection and releases it.

query()

Section titled “query()”
db.query(sql: string)→tabledb.query(sql: string, params: table)→table

Runs a statement that returns rows (SELECT) and returns all rows at once. The result is a list: ipairs(rows) walks the rows, rows.count (also #rows) is the number of rows, and rows.columns lists the columns as {index, label, name, type} tables. A row can be read by column label (row.name, where the label is the alias when the query has one) or by position (row[1]); when two columns share a label, the last one wins by label. Values are converted as follows: integers and floating point numbers become numbers; DECIMAL becomes a number when it is a whole number that fits a long, or has decimals and at most 15 digits, otherwise a string with the exact value (so no precision is lost); dates and times become ISO 8601 strings, and timestamps become UTC instants like 2025-03-01T08:30:00Z (a timestamp without time zone is first read in the agent's time zone); binary data becomes a Base64 string; booleans stay booleans; anything else is converted with its text form. SQL NULL becomes the marker table {__sqlNull = true} (see Sql.nullValue), not nil. Parameters replace the ? placeholders, in order, and must be a list without gaps: strings, numbers and booleans bind as such, Sql.nullValue() binds NULL, and a table is encoded with Json.encode and bound as a string (useful for JSON columns). Named parameters are not supported. The whole result is held in memory, so add a LIMIT for large tables. Raises "SQL query failed: <driver message>" when the statement fails or returns no result set.

Parameters

sql string
SQL statement that returns rows, with ? placeholders
params table optional
Values for the ? placeholders, as a list without gaps; use Sql.nullValue() for NULL

Returns

table List of rows, each a table of column label and position to value

Example 

local rows = db.query("select o.id, o.total, c.name as customer from orders o join customers c on c.id = o.customer_id where o.status = ? and o.total > ?", { "open", 100 })
print("open orders: " .. rows.count)
for _, row in ipairs(rows) do
    print(row.id, row.customer, row.total)
end

execute()

Section titled “execute()”
db.execute(sql: string)→numberdb.execute(sql: string, params: table)→number

Runs a statement that does not return rows: INSERT, UPDATE, DELETE or DDL such as CREATE TABLE. Returns the number of affected rows (0 for DDL). Parameters work as in query. In auto-commit mode the change is committed immediately, otherwise it is part of the open transaction until commit() or rollback(). Raises "SQL execute failed: <driver message>" when the statement fails.

Parameters

sql string
SQL statement that returns no rows, with ? placeholders
params table optional
Values for the ? placeholders, as a list without gaps; use Sql.nullValue() for NULL

Returns

number The number of affected rows

Example 

local changed = db.execute("update orders set status = ? where status = ? and created < ?", { "expired", "open", "2025-01-01" })
Assert.equals(3, changed)

begin()

Section titled “begin()”
db.begin()→self

Starts a transaction by switching auto-commit off. Nothing is committed until commit(); rollback() discards the changes. Auto-commit stays off after commit and rollback, so the next statements start a new transaction; switch back with autoCommit(true).

Returns

self chainable The db itself, so calls can be chained.

Example 

db.begin()
db.execute("insert into audit(event) values (?)", { "start import" })
db.execute("insert into items(sku) values (?)", { "A-100" })
db.commit()

commit()

Section titled “commit()”
db.commit()→self

Commits the changes made since the last commit or rollback. Raises an error when the connection is closed or in auto-commit mode (JDBC drivers reject a commit there), so call begin() or autoCommit(false) first.

Returns

self chainable The db itself, so calls can be chained.

rollback()

Section titled “rollback()”
db.rollback()→self

Discards the changes made since the last commit or rollback. Raises an error when the connection is closed or in auto-commit mode (JDBC drivers reject a rollback there), so call begin() or autoCommit(false) first.

Returns

self chainable The db itself, so calls can be chained.

Example 

db.begin()
local ok, err = pcall(function()
    db.execute("delete from items where batch = ?", { 7 })
    db.execute("insert into items(sku, batch) values (?, ?)", { "A-100", 7 })
end)
if ok then db.commit() else db.rollback() end

autoCommit()

Section titled “autoCommit()”
db.autoCommit(enabled: boolean)→self

Switches auto-commit on or off. With auto-commit on every statement is committed on its own; with it off statements are collected in a transaction (same as begin()). As JDBC specifies, switching it on while a transaction is open commits that transaction.

Parameters

enabled boolean
true for auto-commit, false for manual transactions

Returns

self chainable The db itself, so calls can be chained.

close()

Section titled “close()”
db.close()→self

Closes the connection and releases it. Pending changes are not committed by Reflex (what the driver does with them is up to the driver), so commit() first when you need them. Closing twice is harmless, errors while closing are ignored, and any other call on a closed connection raises "SQL connection is already closed".

Returns

self chainable The db itself, so calls can be chained.

Example 

local db = Sql.connect("jdbc:postgresql://db.example.com/shop", "reporter", secret("db_password"))
local ok, err = pcall(function()
    Assert.equals(1, db.query("select count(*) as n from customers where id = ?", { 42 })[1].n)
end)
db.close()
if not ok then error(err) end