SQLite Pools and Transactions

std.db.sqlite is included in Nagi 0.2.0, and is absent from 0.1.11. SF05 removes Db/db_*; see migration before upgrading.

Docs index · SQLite and migration · SQL preflight checks

Rust embedding code that exhaustively matches standard-module/resource/operation metadata enums must handle the new Query/literal variants.

What this API provides

Pool manages SQLite connections and Tx reserves one connection. SQL structure uses opaque Query; actual values use typed owned Parameters. SF05 removes the legacy Db/db_* entry points, public runtime Db/Sql, and dynamic string factories. Old Nagi entry points produce concrete migration diagnostics, with no fallback to a parallel legacy API.

Run the example

Prerequisites

• A Rust toolchain (rustc and cargo) and a C toolchain that can build Rust crates with native dependencies.
• No SQLite command-line tool is needed. SQLite is bundled with the Nagi runtime.

See how to obtain the source and build the compiler. Use the 0.2.0 source and compiler. With an installed 0.2.0 compiler, replace ./target/release/nagic below with nagic and omit the compiler build.

If cargo build fails while compiling a native dependency, check that an OS C compiler and linker are available as well as Rust. If nagic is not found, invoke the release binary below or add target/release to PATH.

Build the compiler and run the sample

From the repository root, where Cargo.toml is located, build the compiler, check the sample, and then run it:

cargo build --release --locked -p nagic
./target/release/nagic check examples/sqlite_pool.nagi
./target/release/nagic run examples/sqlite_pool.nagi

A successful check exits without diagnostics. run writes the following to standard output:

7
closed

On Windows, use the release executable from the same repository root. If it is on PATH, you can use nagic.exe directly.

cargo build --release --locked -p nagic
./target/release/nagic.exe check examples/sqlite_pool.nagi
./target/release/nagic.exe run examples/sqlite_pool.nagi

The sample opens a one-connection :memory: database, creates a table, saves and reads back 7, then closes the pool. query returns None for zero rows and Some with the first row otherwise. On success, main prints 7 and closed. How nagic run formats a failure is not an API guarantee.

The sample uses std.result.map_error to explicitly convert different error types into the application's AppFailure. options and bind_f64 return the ordinary Error type; database operations return sqlite.Failure. Failure does not implicitly convert to Error.

import std.db.sqlite as sqlite
import std.result as result

class Total:
    amount: i64

enum AppFailure:
    Configuration(cause: Error)
    Database(cause: sqlite.Failure)

def configuration_error(cause: Error) -> AppFailure:
    return AppFailure.Configuration(cause)

def database_error(cause: sqlite.Failure) -> AppFailure:
    return AppFailure.Database(cause)

async def write_and_read(pool: view[sqlite.Pool]) -> Result[i64, sqlite.Failure]:
    tx = try await sqlite.begin(pool, sqlite.BeginMode.DEFERRED)
    try await sqlite.exec(tx, sqlite.literal("CREATE TABLE IF NOT EXISTS amounts(amount INTEGER NOT NULL)"), sqlite.parameters())
    values = sqlite.bind_i64(sqlite.parameters(), 7)
    try await sqlite.exec(tx, sqlite.literal("INSERT INTO amounts VALUES (?)"), values)
    try await sqlite.commit(tx)
    tx = try await sqlite.begin(pool, sqlite.BeginMode.DEFERRED)
    row = try await sqlite.query[Total](tx, sqlite.literal("SELECT amount FROM amounts"), sqlite.parameters())
    try await sqlite.rollback(tx)
    match row:
        case Some(total):
            return ok(total.amount)
        case None:
            return ok(0)

async def main() -> Result[unit, AppFailure]:
    config = try result.map_error(sqlite.options(1, 2, 1000, 0), configuration_error)
    pool = try result.map_error(await sqlite.open(":memory:", config), database_error)
    work = await write_and_read(view(pool))
    ending = await sqlite.close(pool, 1000)
    # Observe close even when the transaction failed; preserve the work error first.
    amount = try result.map_error(work, database_error)
    try result.map_error(ending, database_error)
    print(amount)
    return ok(print("closed"))

Even if write_and_read fails, main awaits close before returning the work error. If both work and close fail, the sample gives priority to the work error and does not return the close error. A production application should record both errors in logs or its own error type.

If a step fails

Types and ownership

Pool is non-Copy. Call sqlite.clone_pool(pool) to create another handle; all clones share close and failure state. Tx is non-Copy, non-Clone, non-shared, and bound to the same task. It cannot be saved in a class field. It can be used in same-task locals, Option / Result values, and owned function delegation. Standard SQLite operations borrow it as view[Tx]. When your own function accepts view[Tx], pass view(tx) explicitly at the call site.

commit(tx) and rollback(tx) consume the Tx as soon as their Future is created. You cannot use that handle after awaiting the operation. To continue with another transaction, call begin again instead of reusing the old handle.

Options and paths

All four arguments to options(connections, queue_capacity, acquire_ms, busy_ms) are required. There are no numeric defaults.

Capacity overflow, negative values, and time values outside the native range return Error from options. A timeout of 0ms means “do not wait,” not “always fail”: it can succeed if the condition already holds.

open(path, options) rejects an empty path and file: URIs. :memory: works only with one connection because each connection would otherwise have a separate database. Other paths must be ordinary filesystem paths; relative paths use the process's current working directory. open owns the path when its Future is created, but creates native connections lazily when begin is called. Therefore, a filesystem or native open error may first appear as a WORKER failure from the first begin. URI interpretation, shared-memory URIs, and automatic WAL setup are not provided.

sqlite.literal("SELECT id FROM items WHERE id = ?") accepts a direct literal by canonical identity. Variables, views, concatenation, formatting, and returned strings are rejected at their source positions; unrelated user functions with the same name remain available. Query is Copy and supports local/field storage and return values, but exposes no SQL text, JSON, equality, or direct construction. query/all/exec require Query. Ordinary check contains no SQL parser; runtime or explicit SQL preflight validates SQL shape.

Typed parameters and SQL shape

Start with parameters() and pass each builder's result to the next builder. The resulting order is the order of anonymous SQLite ? placeholders.

New API SQL must contain one statement at a time and use anonymous ? placeholders only. Named :name / @name and numbered ?1 placeholders are unsupported. Runtime checks bind counts, value types, and NULL decoding. Ordinary check does not execute SQL against database values. Bind values with Parameters rather than concatenating them into SQL text.

query, all, and exec

A write with RETURNING returns columns, so it is neither readonly for query / all nor column-free for exec. Do not put BEGIN / COMMIT / ROLLBACK (including their variants), SAVEPOINT, PRAGMA, ATTACH, or DETACH in user SQL. The module operations own transaction boundaries. Virtual tables, extensions, and raw connections are not part of the public API.

T must be a class with fields supported by the generated FromRow adapter: scalar i8/i16/i32/i64/u8/u16/u32/f32/f64/bool/str/bytes fields, their Option form, and supported owned[...] wrappers. Field names must match returned column names. Use T? for a field that accepts SQL NULL. Enums, resources, arbitrary generics, and unsupported field types are rejected by check. Runtime Result handles actual value types, NULL decoding, and integer-range errors. Handwritten Rust FromRow implementations do not bypass the new API's checker.

Public types and operation signatures

The module exposes nine types and 19 operations. view[T] borrows a value, Future[T] is asynchronous work to await, and Result[T, E] contains either a value or an error. try propagates Err from the current function. See views, async, and error handling for the language rules.

After awaiting an async operation, handle Result with try or match. options and bind_f64 return Error on failure; other database operations return Failure.

Building Parameters

bind_i64 and the other builders move the incoming Parameters and return an extended value. Pass that result to the next builder. query, all, and exec also consume Parameters, so it cannot be reused.

def make_values() -> sqlite.Parameters:
    values = sqlite.parameters()
    values = sqlite.bind_i64(values, 4)
    values = sqlite.bind_text(values, "Nagi")
    return values

The values bind in order to anonymous ? placeholders. To use values in another SQL statement after a query, build a new Parameters for that statement or build them again. copy cannot clone Parameters.

Read multiple rows

all[T] takes a row class and returns all rows. For the Total class above, reading amounts looks like this:

async def read_all(tx: view[sqlite.Tx]) -> Result[List[Total], sqlite.Failure]:
    return await sqlite.all[Total](tx, sqlite.literal("SELECT amount FROM amounts ORDER BY amount"), sqlite.parameters())

Choose query for the first row and all to iterate through every row. Use exec for DDL or writes that return no columns. Runtime rejects a mismatch between the operation and SQL shape, multiple statements, and named or numbered placeholders.

Common mistakes and fixes

An application helper can borrow a Tx and use it in the same task:

async def read_total(tx: view[sqlite.Tx]) -> Result[Total?, sqlite.Failure]:
    return await sqlite.query[Total](tx, sqlite.literal("SELECT amount FROM amounts"), sqlite.parameters())

async def call_read_total(tx: sqlite.Tx) -> Result[Total?, sqlite.Failure]:
    return await read_total(view(tx))

The SQLite authorizer also rejects BEGIN / COMMIT / ROLLBACK in SQL, SAVEPOINT, PRAGMA, ATTACH, and DETACH. Use the module operations for transaction boundaries.

Transaction and concurrency boundaries

Pool's logical slot wait uses Tokio's FIFO semaphore. The FIFO statement applies only to this logical permit wait. It does not promise FIFO completion for native worker startup, native slot registration, or SQLite BEGIN.

acquire_ms uses one budget from logical permit wait through native record registration. Connection startup and ready/BEGIN SQL busy waits after registration are outside that budget. busy_ms controls SQLite busy waiting. There is no separate interrupt deadline for SQL execution or commit.

One Tx reserves one connection. Pool handles may be shared, but a Tx cannot be moved or captured by a child task or used by another task. If a child needs a transaction, call begin with a Pool inside that child. An outer TaskFailure (failure of the child itself) is separate from Result[..., sqlite.Failure] returned by the child's DB work.

An ordinary SQL, bind, or decode error can leave a Tx usable when native SQLite confirms that the transaction is still active. The failing statement may still have made earlier changes, so an error does not promise no changes. If SQLite automatically rolls the transaction back, later operations are rejected with ABORTED / ROLLED_BACK. Cancellation does not undo accepted SQL or external side effects. Do not infer that retry or rollback is safe; design idempotency and explicit rollback for the application.

Read a Failure

Read the failure category separately from the transaction outcome. outcome reports what could be observed; it does not mean that retrying is safe.

ALLOCATION reports explicit fallible reservations. The API does not promise recovery from every OOM or allocator abort.

retired reports whether retirement of the connection was confirmed for this Failure. false only means retirement was not confirmed from this failure; it does not prove the worker or Pool is healthy. In particular, do not infer that REPLY_LOST / UNKNOWN means rollback completed, the connection can be reused, or retry is safe. Debug output is limited to kind / outcome / retired, but the public message string is not a stable protocol.

Failure.message is a view[str] borrowing from the Failure owner. To keep it longer than the Failure, use copy(problem.message) to make an owned string. Use copy_primary_error(problem) and copy_cleanup_error(problem) to explicitly copy native causes into Option[Error]. They return None when that cause is absent; otherwise they copy its kind and message. A failure can carry distinct primary and cleanup information, such as when commit completed but cleanup failed.

Close the Pool

close(pool, timeout_ms) starts a closing state shared by every clone, stops new begin calls, and waits for active Tx cleanup, native connection close, and actual worker joins. A timeout of zero can still succeed if shutdown is already complete. Timeout or cancellation after closing starts does not clear the closing state. Active transactions are not killed; a later close can wait for completion.

Dropping the last Pool handle only requests shutdown. It does not confirm async cleanup or worker joins. If the application needs to know that shutdown completed, an owner must await close. Successful close confirms native close and actual worker-thread join. There is no guarantee that close returns if SQL blocks indefinitely or the process is killed or aborts on allocation failure.

SQL preflight checks

Ordinary check validates the Query literal boundary, types, ownership, and row fields without starting SQLite. Opt-in checks with an explicit schema prepare direct Query constructors, validating SQL shape, schema, and required columns. Direct Parameters builder chains provide known bind counts; Query structure or Parameters counts hidden by variables/functions are reported for runtime checks. Actual value types, NULL, numeric ranges, and database data remain runtime checks. See SQL preflight.

Choosing an API and related pages

• For schema-based offline checks, see SQL preflight checks. For Nagi Result, try, nullable values, view, and ownership, see error handling, Option and nullable types, ownership, views, and async.