sql.transaction

import sql.transaction

sql lifts part of this module out to its own top level; each name below is shown with the path that reaches it. Anything still spelled sql.transaction.* needs import sql.transaction.

Transactions, and the nesting that savepoints make possible.

The shape worth using is the closure form, because it cannot be got wrong:

db.transaction(@(tx) {
  tx.exec('update accounts set balance = balance - ? where id = ?', [100, 1])
  tx.exec('update accounts set balance = balance + ? where id = ?', [100, 2])
})

It commits when the closure returns and rolls back when the closure raises, and there is no path through it that leaves a transaction open. The manual form exists for the cases the closure form cannot express, such as a transaction whose lifetime is a request rather than a block.

Nesting

No engine here supports a transaction inside a transaction, but both support savepoints, which is the same thing under a different name. A transaction() inside another takes a savepoint, so a helper that opens one works the same whether it was called on its own or from inside a larger piece of work. Only the outermost actually commits.

Classes

Transaction

class sql.Transaction

An open transaction.

It carries the same query, exec and CRUD methods a connection does, so code inside a transaction reads the same as code outside one. Every statement it runs goes to the connection the transaction is on.

• printable — has a @to_string(), so echo and print() show something useful

Fields

Constructor

sql.Transaction(connection, nested)

Parameters

• connection (Connection)
• nested (bool)

Transaction.is_finished()

sql.Transaction.is_finished() -> bool

Whether this transaction has already committed or rolled back.

Returns bool

Transaction.query()

sql.Transaction.query(sql: string, params) -> ResultSet

Runs a query on this transaction’s connection.

Parameters

• sql (string)
• params (list|dict|nil)

Returns ResultSet

Transaction.exec()

sql.Transaction.exec(sql: string, params) -> ExecResult

Runs a statement for its effect.

Parameters

• sql (string)
• params (list|dict|nil)

Returns ExecResult

Transaction.fetch_one()

sql.Transaction.fetch_one(sql: string, params) -> dict|nil

The first row of a query, or nil.

Parameters

• sql (string)
• params (list|dict|nil)

Returns dict|nil

Transaction.fetch_all()

sql.Transaction.fetch_all(sql: string, params) -> list[dict]

Every row of a query.

Parameters

• sql (string)
• params (list|dict|nil)

Returns list[dict]

Transaction.fetch_value()

sql.Transaction.fetch_value(sql: string, params, fallback) -> any

The first column of the first row.

Parameters

• sql (string)
• params (list|dict|nil)
• fallback (any)

Returns any

Transaction.fetch_column()

sql.Transaction.fetch_column(sql: string, params, column) -> list

One column’s values, as a list.

Parameters

• sql (string)
• params (list|dict|nil)
• column (string|number|nil) — Which column, the first by default.

Returns list

Transaction.stream()

sql.Transaction.stream(sql: string, params, options) -> Cursor

Runs a query and returns a cursor over its result rather than reading all of it. The cursor belongs to the transaction’s connection, so it has to be read to the end or closed before the transaction commits.

Parameters

• sql (string)
• params (list|dict|nil)
• options (dict|nil) — { batch } on engines that fetch in batches.

Returns Cursor

Transaction.prepare()

sql.Transaction.prepare(sql: string) -> Statement

Compiles a statement for repeated use inside this transaction.

Parameters

• sql (string)

Returns Statement

Transaction.exec_script()

sql.Transaction.exec_script(sql: string)

Runs one or more statements for their effect, as a script: a schema file, a migration, or a batch of settings. It binds no parameters and returns no rows.

On PostgreSQL and SQLite the whole script commits or rolls back with the transaction. MySQL commits a statement that changes a table’s definition as it runs it, whatever transaction it is in, so a script of those is only as atomic as MySQL makes it.

Parameters

• sql (string)

Transaction.insert()

sql.Transaction.insert(table: string, values: dict, options) -> any

Inserts a row and returns its id.

Parameters

• table (string)
• values (dict)
• options (dict|nil)

Returns any

Transaction.insert_many()

sql.Transaction.insert_many(table: string, rows: list) -> number

Inserts several rows in one statement.

Parameters

• table (string)
• rows (list) — Dictionaries of column to value.

Returns number — How many rows were inserted.

Transaction.update()

sql.Transaction.update(table: string, values: dict, where) -> number

Updates the rows matching where.

Parameters

• table (string)
• values (dict)
• where (dict|nil)

Returns number

Transaction.delete()

sql.Transaction.delete(table: string, where) -> number

Deletes the rows matching where.

Parameters

• table (string)
• where (dict|nil)

Returns number

Transaction.find()

sql.Transaction.find(table: string, where, options) -> ResultSet

Selects the rows matching where.

Parameters

• table (string)
• where (dict|nil)
• options (dict|nil) — columns, order, limit and offset.

Returns ResultSet

Transaction.find_one()

sql.Transaction.find_one(table: string, where, options) -> dict|nil

The first row matching where, or nil.

Parameters

• table (string)
• where (dict|nil)
• options (dict|nil)

Returns dict|nil

Transaction.count()

sql.Transaction.count(table: string, where) -> number

How many rows match where.

Parameters

• table (string)
• where (dict|nil)

Returns number

Transaction.commit()

sql.Transaction.commit()

Commits this transaction, or releases its savepoint when it is nested.

Raises TransactionError if it has already finished.

Transaction.rollback()

sql.Transaction.rollback()

Rolls this transaction back, or undoes its savepoint when it is nested.

Raises TransactionError if it has already finished.

Transaction.to_string()

sql.Transaction.to_string()

2026, Richard Ore and Zuri contributors