std.db.sqlite

import std.db.sqlite stores and queries data in SQLite databases through prepared statements with typed parameters and typed column reads. It has one error type, DbError.

For named byte-key maps without application SQL, use std.db.kv, which shares this engine. Use this module for relational schemas, SQL queries, or reusable prepared statements. See the storage comparison.

SQLite is vendored C code compiled into the runtime. The Plenty module owns the error model, row state, value limits, and transaction ownership. It is not an ORM: SQL is written by the program, and values are bound, never spliced into SQL text. The module is available on the supported x86-64 Linux GNU target.

import std.db.sqlite as sqlite

def main() -> Result[(), AllocError | IoError | sqlite.DbError]:
    mut db = sqlite.connect("shop.db", sqlite.Mode.Create, 65536)?
    db.execute("CREATE TABLE items(name TEXT NOT NULL, price INTEGER NOT NULL)")?
    mut insert = db.prepare("INSERT INTO items VALUES (?, ?)")?
    for name, price in [("pen", 3), ("book", 12), ("lamp", 25)]?:
        insert.bind_text(1, name)?
        insert.bind_integer(2, price)?
        insert.run()?
    mut query = db.prepare("SELECT name, price FROM items WHERE price > ? ORDER BY price")?
    query.bind_integer(1, 5)?
    while query.step()?:
        print(query.text(0)?, query.integer(1)?)?
    Ok(())

book 12
lamp 25

Opening

Mode is ReadOnly, ReadWrite (the file must exist), or Create. A missing file without Create is SQLite’s cant_open (14). path is a file name, not a URI; "", ":memory:", and paths containing a NUL byte are InvalidPath.

max_length is the largest text, blob, or row, in bytes, that the connection stores or reads. It must lie in min_length_limit (1024) to max_length_limit (1,000,000,000, SQLite’s own maximum); otherwise OutOfRange. Binding, computing, or reading a larger value is TooLarge. Choose it from the memory the program can spend: every text or blob read is copied into a new str or Bytes.

Connection

execute takes no parameters and is meant for schema and fixed SQL. prepare rejects SQL with no statement (EmptySql) or more than one (TrailingSql); a trailing ;, whitespace, or comment is allowed.

close is InUse while a statement or transaction made from the connection is alive, and leaves it open. After a successful close, every method that reaches SQLite is Closed, changes and last_insert_rowid return 0, and a second close succeeds. Dropping a connection without close closes it once its last statement and transaction are gone.

Native extensions and full-text search

load_extension loads a native shared library for the supported target. Only load libraries you trust: their initialization and SQL functions execute native code in your process. The extension must use SQLite’s extension API table; it does not need a separately linked SQLite library. Use an explicit filesystem path to select the library. SQLite may try platform suffixes if it cannot open that path directly.

An empty entrypoint asks SQLite to discover the initialization symbol; otherwise it names the exact exported symbol (for example, sqlite3_vec_init). Empty or NUL-containing paths are InvalidPath; a NUL in the entrypoint is InvalidExtensionEntry. Loader failures return Sqlite with SQLite’s loader message. Closed is returned if the connection has been closed.

The native loading API is enabled only during the call, under the connection mutex, and disabled again on success or failure. SQL’s load_extension() stays disabled. Extensions belong to the connection and must be loaded again when reopening a database that uses them. There is no public enable/disable toggle.

FTS5 is built in: CREATE VIRTUAL TABLE ... USING fts5(...), MATCH, and bm25(...) work without loading an extension. Vector extensions such as sqlite-vec are installed separately; Plenty does not download them.

Statement

Parameters are numbered from 1 and columns from 0.

An index outside the statement’s parameters or columns is OutOfRange. Binding after a step rewinds the statement first, so a statement is reused by binding new values and stepping again. A statement that has finished keeps returning False from step until reset or a new binding. A failed step rewinds it.

Reads require a current row (NoRow otherwise) and never convert: SQLite’s storage class must match the read, or the error is TypeMismatch(found), where found is the stored class. ValueType is Integer, Real, Text, Blob, or Null. A column’s declared type does not decide its storage class; SQLite’s type affinity does, so an INTEGER column given the text '7' stores the integer 7, but a TEXT column given the integer 7 stores the text '7'. STRICT tables still allow lossless conversion, including '7' into an INTEGER column; they reject values that cannot be losslessly converted to the declared type. Text that is not valid UTF-8 is InvalidUtf8.

Every text and blob read is copied out, so values stay valid after the next step, reset, or the statement’s drop. There are no borrowed row views.

Transactions

Begin is Deferred (locks taken on first use), Immediate (write lock at once), or Exclusive.

A connection has at most one transaction: begin while one is open is TransactionActive. Dropping a transaction that was neither committed nor rolled back rolls it back, so ? out of a function undoes its work. A finished transaction, or one ended by SQL such as COMMIT run through execute, is TransactionFinished on commit and rollback. A failed commit that SQLite leaves open, such as one that is busy, may be retried or rolled back.

Savepoints are available through execute("SAVEPOINT name") inside a transaction.

Busy databases

A write needs a lock on the database file. When another connection holds it, the default is to fail at once with SQLite’s busy (5) code. set_busy_timeout(&budget) takes a std.time.Duration. Zero restores the default; positive fractional milliseconds round up. A rounded budget above 2,147,483,647 milliseconds is OutOfRange. Use Begin.Immediate for transactions that write, so a conflict is found at begin rather than partway through.

Errors

pub enum DbError:
    Sqlite(SqliteError)
    Closed
    InUse
    EmptySql
    TrailingSql
    TransactionActive
    TransactionFinished
    InvalidPath
    InvalidExtensionEntry
    OutOfRange
    NoRow
    TypeMismatch(ValueType)
    TooLarge
    InvalidUtf8
    Allocation(AllocError)

SqliteError.primary() is the primary result code, the extended code modulo 256, using Plenty modulo with a positive divisor (for example, a manually constructed SqliteError with code -1 has primary 255). Internal negative native sentinel codes are decoded into their existing DbError variants before primary-code classification. The module names the common ones as constants: error (1), busy (5), locked (6), read_only (8), cant_open (14), constraint (19), and mismatch (20). For example, a NOT NULL violation has code 1299 and primary code 19. SQLite’s own nomem, toobig, and range codes become Allocation, TooLarge, and OutOfRange.

Ownership and threads

A Connection, Statement, or Transaction is an owned value; none can be copied. A statement or transaction keeps its connection’s SQLite handle alive, so a statement may outlive the Connection value it came from. Dropping a statement finalizes it.

SQLite is compiled thread-safe and every connection is opened in serialized mode, so a connection, statement, or transaction may be moved to a worker thread and used there. Opening and loading an extension are path effects; other operations act on a handle the caller owns. Extension code must itself support the threads on which its functions are used. SQLite’s messages are copied per thread, so concurrent errors on different threads do not overwrite each other.

The handles are ByteStorage values. The __sqlite_* primitives are reserved for the standard library and exist only for this module. Given a handle not made by it, they stop the program with operand is not a std.db.sqlite connection (or statement, or transaction).

Costs

SQLite allocates through the C allocator, outside Plenty’s allocation accounting and failure injection; its own allocation failures are reported as Allocation.

Supply

The runtime compiles the SQLite amalgamation from an unmodified copy under plenty-runtime/native/sqlite-3.54.0, so building the compiler needs a C compiler (cc, or CC) but no network, and an installed compiler needs neither the sources nor a system SQLite:

It is built with SQLITE_THREADSAFE=1, SQLITE_DEFAULT_MEMSTATUS=0, SQLITE_DQS=0 (double-quoted strings are identifiers only), SQLITE_LIKE_DOESNT_MATCH_BLOBS, SQLITE_OMIT_DEPRECATED, SQLITE_ENABLE_FTS5, and SQLITE_OMIT_SHARED_CACHE. Native extension loading is available through Connection.load_extension; SQL loading stays disabled.

Updating it is the runtime maintainers’ job, with each SQLite release that fixes a defect: replace the directory with the new amalgamation, verify its published SHA3-256, update the directory name in build.rs and the table above, and rerun tests/test_std_sqlite.rs, which checks that the vendored header, this page, and sqlite_version() agree.

Executables are linked with --gc-sections, SQLite is compiled with function and data sections, and it is reached only through its own runtime entry symbols, so a program that does not use std.db.sqlite contains none of it. A program that uses SQLite includes the database engine and its enabled features.

Limits

• No connection pool, ORM, or query builder.
• No user-defined SQL functions, collations, hooks, or virtual tables.
• No incremental blob I/O, backup API, or online serialization.
• No URI file names.
• No borrowed row views: every text and blob read is a copy.
• SQLite’s allocations are outside Plenty’s allocation accounting.
• One target, x86-64 Linux GNU.

Acknowledgements

• Wraps SQLite 3.54.0, vendored as the C amalgamation and compiled into the runtime (public domain).