`sql.sqlite.connection`

import sql.sqlite.connection

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.sqlite.connection.* needs import sql.sqlite.connection.

A SQLite connection, as the layer above the adapters sees one.

SQLite has no server, so a connection here is an open database file (or an in-memory database) and the handle onto it. That difference shows through in a few places, and this is where each of them is dealt with.

Transactions

SQLite has one transaction per connection and no nesting, so nested transactions are savepoints, as they are for every other adapter. Its isolation levels are not levels but locking modes: BEGIN DEFERRED takes no lock until the first statement, BEGIN IMMEDIATE takes the write lock at once. Serializable maps onto IMMEDIATE, which is what actually delivers it, and the levels SQLite cannot approximate raise rather than being quietly accepted.

One writer

A SQLite database takes one writer at a time. Where another engine would queue, SQLite returns SQLITE_BUSY, which this adapter turns into a TimeoutError once the busy timeout expires. Setting a busy timeout is what turns a burst of contention into a wait rather than an error, so connections open with one already set.

Constants

`CACHE_LIMIT`

sql.sqlite.connection.CACHE_LIMIT = 64

How many compiled statements a connection keeps for reuse.

Statements run through execute() and select() are cached by their text, since those compile, run and reset within the one call and are never handed out. A cursor’s statement is never cached: it stays live between calls, and lending the same handle to two readers would have them stepping each other’s result.

Classes

`SqliteConnection`

class sql.sqlite.SqliteConnection < DriverConnection

An open SQLite database.

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

Constructor

sql.sqlite.SqliteConnection(driver, handle, options)

Parameters

driver (Driver) — The driver that opened this.
ptr — handle The native connection.
options (dict) — The options it was opened with.

`SqliteConnection.handle()`

sql.sqlite.SqliteConnection.handle()

The native handle, for the parts of SQLite that are not part of the shared contract: blobs, backups, user-defined functions and hooks.

Returns — ptr

`SqliteConnection.schema_adapter()`

sql.sqlite.SqliteConnection.schema_adapter() -> SqliteSchema

This connection’s introspection, built once and reused.

Returns SqliteSchema

`SqliteConnection.execute()`

sql.sqlite.SqliteConnection.execute(sql: string, values: list) -> dict

Runs a statement for its effect.

Parameters

sql (string)
values (list)

Returns dict — { rows_affected, last_insert_id }

`SqliteConnection.select()`

sql.sqlite.SqliteConnection.select(sql: string, values: list) -> dict

Runs a statement and reads its whole result.

Parameters

sql (string)
values (list)

Returns dict — { columns, rows }

`SqliteConnection.open_cursor()`

sql.sqlite.SqliteConnection.open_cursor(sql: string, values: list, options: dict) -> SqliteCursor

Runs a statement and hands back a cursor over its result.

The statement is compiled fresh rather than taken from the cache, because the cursor keeps it for as long as it is reading.

Parameters

sql (string)
values (list)
options (dict) — Unused; SQLite produces rows on demand already, so there is no batch size to choose.

Returns SqliteCursor

`SqliteConnection.prepare()`

sql.sqlite.SqliteConnection.prepare(sql: string) -> SqliteStatement

Compiles a statement for repeated use.

Parameters

sql (string)

Returns SqliteStatement

`SqliteConnection.begin()`

sql.sqlite.SqliteConnection.begin(isolation)

Opens a transaction, or takes a savepoint when one is already open.

Parameters

isolation (string|nil)

Raises NotSupportedError for a level SQLite has no analogue for.

`SqliteConnection.commit()`

sql.sqlite.SqliteConnection.commit()

Commits the transaction, or releases the innermost savepoint.

`SqliteConnection.rollback()`

sql.sqlite.SqliteConnection.rollback()

Rolls the transaction back, or undoes the innermost savepoint.

`SqliteConnection.savepoint()`

sql.sqlite.SqliteConnection.savepoint(name: string)

`SqliteConnection.release_savepoint()`

sql.sqlite.SqliteConnection.release_savepoint(name: string)

`SqliteConnection.rollback_to()`

sql.sqlite.SqliteConnection.rollback_to(name: string)

`SqliteConnection.in_transaction()`

sql.sqlite.SqliteConnection.in_transaction() -> bool

Whether a transaction is open.

Returns bool

`SqliteConnection.depth()`

sql.sqlite.SqliteConnection.depth() -> number

How deeply transactions are currently nested: zero outside one, one inside the outermost, and one more per savepoint.

Returns number

`SqliteConnection.changes()`

sql.sqlite.SqliteConnection.changes() -> number

How many rows the last statement changed.

Returns number

`SqliteConnection.last_insert_id()`

sql.sqlite.SqliteConnection.last_insert_id() -> number|bigint|nil

The row id of the last insert on this connection, or nil if there has not been one.

Returns number|bigint|nil

`SqliteConnection.ping()`

sql.sqlite.SqliteConnection.ping() -> bool

Checks the connection is usable.

Returns bool

`SqliteConnection.server_version()`

sql.sqlite.SqliteConnection.server_version() -> string

SQLite’s version, such as '3.53.2'.

Returns string

`SqliteConnection.interrupt()`

sql.sqlite.SqliteConnection.interrupt()

Asks a running statement on this connection to stop.

The statement fails rather than being killed, and the connection stays usable. Meant for a long query on a connection another isolate is waiting on.

`SqliteConnection.set_busy_timeout()`

sql.sqlite.SqliteConnection.set_busy_timeout(milliseconds: number)

How long a statement waits for another writer before giving up.

Parameters

milliseconds (number) — Zero waits not at all.

`SqliteConnection.blob()`

sql.sqlite.SqliteConnection.blob(database: string, table: string, column: string, rowid: number, writable) -> Blob

Opens one blob-valued cell for piecewise reading and writing.

Parameters

database (string) — The attached database, usually 'main'.
table (string)
column (string)
rowid (number)
writable (bool|nil) — Read-only when nil or false.

Returns Blob

`SqliteConnection.backup()`

sql.sqlite.SqliteConnection.backup(destination, options) -> Backup

Starts a copy of this database into destination.

Parameters

destination (SqliteConnection) — An open connection to copy into, which must not be this one.
options (dict|nil) — source and target name which attached database on each side; both default to 'main'.

Returns Backup

`SqliteConnection.backup_to()`

sql.sqlite.SqliteConnection.backup_to(path: string, options)

Copies this database to a file, all in one call.

The file is created if it is not there. An existing one is copied over rather than appended to, so a snapshot taken repeatedly to the same path replaces itself.

db.native().backup_to('./snapshot.db', nil)

For a copy that is not a file, or one taken in steps, open the destination yourself and use backup().

Parameters

path (string)
options (dict|nil) — As backup() takes.

`SqliteConnection.create_function()`

sql.sqlite.SqliteConnection.create_function(name: string, arity: number, body: function, options)

Adds a function to this connection, written in Zuri.

db.native().create_function('initials', 1, @(name) {
  return ''.join(name.split(' ').map(@(part) => part[0, 1]))
})

db.query('select initials(name) from authors')

The function runs inside the engine, once per row, so it can be used anywhere an expression can. It must not touch the connection it was registered on; SQLite is in the middle of a statement when it calls, and reentering would deadlock.

Parameters

name (string)
arity (number) — How many arguments, or -1 for any number.
body (function)
options (dict|nil) — deterministic says the result depends only on the arguments, which lets SQLite use the function in an index and hoist it out of loops. True unless said otherwise.

`SqliteConnection.create_aggregate()`

sql.sqlite.SqliteConnection.create_aggregate(name: string, arity: number, step: function, finish: function)

Adds an aggregate function, written as a fold.

step is called once per row with the accumulator so far followed by the row’s arguments, and returns the next accumulator. finish is called once per group with the last accumulator and returns the group’s value. The accumulator starts as nil, which is also what finish sees for a group with no rows in it.

db.native().create_aggregate('longest', 1,
  @(longest, word) {
    if longest == nil or word.length() > longest.length() {
      return word
    }

    return longest
  },
  @(longest) => longest
)

Parameters

name (string)
arity (number)
step (function)
finish (function)

`SqliteConnection.create_collation()`

sql.sqlite.SqliteConnection.create_collation(name: string, compare: function)

Adds a collation, which is an ordering for text.

The callable is passed two strings and returns a negative number, zero or a positive number, the same shape a sort comparator takes. A statement reaches it with order by column collate name.

Parameters

name (string)
compare (function)

`SqliteConnection.delete_function()`

sql.sqlite.SqliteConnection.delete_function(name: string, arity: number)

Removes a function or aggregate registered under this name and arity.

Parameters

name (string)
arity (number)

`SqliteConnection.on_change()`

sql.sqlite.SqliteConnection.on_change(handler)

Called after each row an INSERT, UPDATE or DELETE changes, with the operation name, the database, the table and the row id.

Rows a trigger or a foreign key action changes are reported too. Each change is reported as it is made, so one a transaction later rolls back has still been reported. A DELETE with no WHERE clause empties the table without visiting its rows, and a WITHOUT ROWID table has no rowid to report, so neither is reported at all.

Parameters

handler (function|nil) — Nil clears it.

`SqliteConnection.on_commit()`

sql.sqlite.SqliteConnection.on_commit(handler)

Called just before each commit. Returning false turns the commit into a rollback.

Parameters

handler (function|nil) — Nil clears it.

`SqliteConnection.on_rollback()`

sql.sqlite.SqliteConnection.on_rollback(handler)

Called whenever a transaction rolls back, however it was caused.

Parameters

handler (function|nil) — Nil clears it.

`SqliteConnection.set_authorizer()`

sql.sqlite.SqliteConnection.set_authorizer(handler)

Consulted for every action a statement wants to take, as the statement is compiled rather than as it runs.

The callable is passed the action code and the four strings SQLite supplies about it, and answers 'allow', 'deny' or 'ignore'. Anything else counts as denial, so a handler that falls off the end fails closed.

Parameters

handler (function|nil) — Nil clears it.

`SqliteConnection.on_progress()`

sql.sqlite.SqliteConnection.on_progress(instructions: number, handler)

Called every instructions steps of a long statement. Returning false interrupts it.

Parameters

instructions (number)
handler (function|nil) — Nil clears it.

`SqliteConnection.close()`

sql.sqlite.SqliteConnection.close()

Closes the connection, releasing every statement it cached.

Safe to call more than once.

`SqliteConnection.is_closed()`

sql.sqlite.SqliteConnection.is_closed()

`SqliteConnection.to_string()`

sql.sqlite.SqliteConnection.to_string()

2026, Richard Ore and Zuri contributors

Keyboard shortcuts

The Zuri Standard Library