Opening a database connection.

A database connection is opened by calling the openDatabase procedure with the path to the database file as an argument. If the file doesn't exist, it will be created. An in-memory database can be created by using the special path ":memory:" as an argument. Once the database connection is no longer needed, close must be called to prevent memory leaks. Closing a connection finalizes its internally cached statements. Explicit statements created with stmt own their handles and must be finalized separately. Closing is rejected with SqliteUsageError while a connection operation is active, preventing callbacks and user-defined conversions from invalidating a statement that is being bound or executed. Database and extension paths containing embedded NUL bytes raise SqliteError before they are passed to SQLite.

let db = openDatabase("path/to/file.db")
# ... (do something with `db`)
db.close()

The compatibility overload opens or creates the database, uses a 100-entry statement cache, and keeps SQLite's normal connection settings. For explicit opening behavior, copy defaultOpenOptions and pass the resulting OpenOptions value:

var options = defaultOpenOptions
options.mode = OpenMode.readWriteExisting
options.cacheSize = 50
options.busyTimeoutMs = 5_000
options.noFollow = true
options.securityProfile = SecurityProfile.hardened
let db = openDatabase("path/to/existing.db", options)

OpenMode.readOnly and OpenMode.readWriteExisting never create the main database file; OpenMode.readWriteCreate creates it when missing. SQLite may fall back from read-write-existing to read-only access when operating-system permissions require it, so use isReadonly when writable access is mandatory. busyTimeoutMs gives ordinary lock contention a bounded retry period. uriFilename enables intentional SQLite file: URI interpretation, including URI parameters that can affect access and locking. noFollow rejects database paths containing symbolic links.

SecurityProfile.hardened enables SQLite defensive mode and disables trusted-schema behavior before initialization SQL runs. This can reject legitimate schemas that rely on application-defined functions or virtual tables. It is an additional defense rather than a sandbox, and it does not change WAL or durability policy. See SAFETY.md for the complete connection-opening and security contract.

Executing SQL

The exec procedure can be used to execute a single SQL statement. The execScript procedure is used to execute several statements, but it doesn't support parameter substitution.

Single-statement operations validate the complete input before execution:

• Trailing whitespace, extra semicolons, and complete SQLite comments are allowed. This includes a final -- comment without a newline.
• A second statement, malformed trailing SQL, or an incomplete block comment or quoted token raises SqliteError before anything executes.
• Empty, whitespace-only, semicolon-only, and comment-only input raises SqliteError.

Use execScript for multiple statements. Unlike single-statement operations, it treats empty and comment-only scripts as no-ops. Every SQL operation rejects embedded NUL bytes rather than letting SQLite silently truncate the input. execScript rejects explicit BEGIN, COMMIT, END, ROLLBACK, SAVEPOINT, and RELEASE statements before they execute, preventing a script from invalidating the transaction that protects its work. Use exec when transaction control must be managed manually.

If a statement produces rows, exec and execScript discard them but continue stepping until the statement completes. A runtime error on any row raises SqliteError. When execScript starts its transaction, such an error rolls back the script.

db.execScript("""
    CREATE TABLE Person(
        name TEXT,
        age INTEGER
    );

    CREATE TABLE Log(
        message TEXT
    );
""")

db.exec("""
    INSERT INTO Person(name, age)
    VALUES(?, ?);
""", "John Doe", 37)

Named parameters

For order-independent binding, use SQLite :name parameters and pass a named tuple. A tuple field named name binds :name regardless of the order of the fields or parameters:

db.exec("""
    UPDATE Person
    SET age = :age
    WHERE name = :name
""", (name: "John Doe", age: 38))

Named tuples can be used with exec, iterate, all, one, and value, including their prepared-statement versions. execMany accepts an array or sequence of named tuples. Repeated occurrences of a parameter share one tuple field. Missing parameters, unknown tuple fields, and positional ? parameters used with a named tuple raise SqliteError.

Reading data

Four different procedures for reading data are available:

• all: procedure returning all result rows
• iterate: iterator yielding each result row one by one
• one: procedure returning the first result row, or none if no result row exists
• value: procedure returning the first column of the first result row, or none if no result row exists

Note that the procedures one and value returns the result wrapped in an Option. See the standard library options module for documentation on how to deal with Option values. For convenience the nim_sqlite module exports the options.get, options.isSome, and options.isNone procedures so the options module doesn't need to be explicitly imported for typical usage.

for row in db.iterate("SELECT name, age FROM Person"):
    # The 'row' variable is of type ResultRow.
    # The column values can be accesed by both index and column name:
    echo row[0].strVal      # Prints the name
    echo row["name"].strVal # Prints the name
    echo row[1].intVal      # Prints the age
    # Above we're using the raw DbValue's directly. Instead, we can unpack the
    # DbValue using the fromDb procedure:
    echo fromDb(row[0], string) # Prints the name
    echo fromDb(row[1], int)    # Prints the age
    # Alternatively, the entire row can be unpacked at once:
    let (name, age) = row.unpack((string, int))
    # Unpacking the value is preferable as it makes it possible to handle
    # bools, enums, distinct types, nullable types and more. For example, nullable
    # types are handled using Option[T]:
    echo fromDb(row[0], Option[string]) # Will work even if the db value is NULL

# Example of reading a single value. In this case, 'value' will be of type `Option[DbValue]`.
let value = db.value("SELECT age FROM Person WHERE name = ?", "John Doe")
if value.isSome:
    echo fromDb(value.get, int) # Prints age of John Doe

Scoped resources, deadlines, and online backups

withDatabase injects a db variable and closes it on every exit path. withStatement similarly injects a statement variable and finalizes it. Use withDeadline(db, timeoutMs) to interrupt long-running SQL after a monotonic deadline. SQLite can roll back an interrupted write transaction; inspect SqliteError.primaryCode for SQLITE_INTERRUPT.

backupDatabase(destination, source) copies the source main database into the destination. For incremental transfers, withBackup injects a backup handle; call step with a positive page count and inspect remainingPages and totalPages after each step. The destination cannot be used for other operations until the scope exits.

One connection and its statements must not be used concurrently across threads. Coordinate interrupt with connection close when requesting cancellation from another thread. Extensions are trusted native code and require OpenOptions.allowExtensions to be enabled explicitly.

Inserting data in bulk

The exec procedure works fine for inserting single rows, but it gets awkward when inserting many rows. For this purpose the execMany procedure can be used instead. It executes the same SQL repeatedly, but with different parameters each time.

let parameters = [
    (name: "Person 1", age: 17),
    (name: "Person 2", age: 55)
]
# Will insert two rows
db.execMany("""
    INSERT INTO Person(name, age)
    VALUES(:name, :age);
""", parameters)

Transactions

The procedures that can execute multiple SQL statements (execScript and execMany) are wrapped in a transaction by nim_sqlite. Transactions can also be controlled manually by using one of these two options:

• Option 1: using the transaction template

db.transaction:
    # Anything inside here is executed inside a transaction which
    # will be rolled back in case of an error
    db.exec("DELETE FROM Person")
    db.exec("""INSERT INTO Person(name, age) VALUES("Jane Doe", 35)""")

Nested transaction blocks use SQLite savepoints. If an inner exception is caught by the outer block, only the inner block's work is rolled back. If an exception escapes the outer block, all nested work is rolled back.

The default mode is TransactionMode.deferred. Pass TransactionMode.immediate or TransactionMode.exclusive to select the corresponding SQLite BEGIN mode for an outermost transaction:

db.transaction(TransactionMode.immediate):
    db.exec("DELETE FROM Person")

Nested scopes inherit the outer mode. When SQL issued by the application has already started a transaction or savepoint, the template creates its own savepoint and leaves the manually managed outer scope open. Its caller remains responsible for the final COMMIT or ROLLBACK. Manually committing, rolling back, or releasing that outer scope from inside the template is unsupported because it invalidates the template's cleanup boundary.

Commit and savepoint-release failures trigger rollback cleanup. The original failure remains primary; every failure during scoped and fallback cleanup is retained in order through Nim's exception parent chain. See SAFETY.md for the complete failure contract.

• Option 2: using the exec procedure manually

db.exec("BEGIN")
try:
    db.exec("DELETE FROM Person")
    db.exec("""INSERT INTO Person(name, age) VALUES("Jane Doe", 35)""")
    db.exec("COMMIT")
except:
    db.exec("ROLLBACK")

Prepared statements

All the procedures for executing SQL described above create and execute prepared statements internally. In addition to those procedures, nim_sqlite also offers an API for preparing SQL statements explicitly. Prepared statements are created with the stmt procedure, and the same procedures for executing SQL that are available directly on the connection object are also available for the prepared statement:

let stmt = db.stmt("INSERT INTO Person(name, age) VALUES (?, ?)")
stmt.exec("John Doe", 21)
# Once the statement is no longer needed it must be finalized
# to prevent memory leaks.
stmt.finalize()

An explicit statement owns its SQLite statement handle. Closing the database makes the statement unusable for further execution, but does not finalize its handle. It remains safe and necessary to finalize the statement afterward:

let stmt = db.stmt("SELECT name FROM Person")
db.close()
doAssert not stmt.isAlive
stmt.finalize()

The underlying SQLite connection is released after all explicit statements have been finalized. Prefer finalizing statements before closing the connection when practical; post-close finalization makes cleanup ordering safe.

There are performance benefits of reusing prepared statements, since the preparation only needs to be done once. However, nim_sqlite keeps an internal cache of prepared statements, so it's typically not necesarry to manage prepared statements manually. If you prefer if nim_sqlite doesn't perform this caching, you can disable it by setting the cacheSize parameter when opening the database:

let db = openDatabase(":memory:", cacheSize = 0)

Cached statements are leased to one connection-level operation at a time. If nested or reentrant code requests SQL whose cached statement is already leased or busy, nim_sqlite prepares a temporary statement and finalizes it afterward. Cache eviction skips leased and busy statements. This keeps nested queries independent, including when the same SQL is used with different parameters.

Explicit statements created with stmt are single-use for their complete binding and execution lifecycle. Reusing or finalizing the same statement, or closing its connection, from an active iterator or a user-defined named-parameter toDb conversion raises SqliteUsageError. The guard is released after successful execution, binding failures, exceptions, and iterator early exits, so the statement remains reusable afterward.

Error handling

SqliteError provides primaryCode, extendedCode, operation, and sqliteMessage fields for handling failures without parsing exception text. The operation is one of the stable SqliteOperation categories. SQLite result codes are captured before statement cleanup can replace the connection's current error. Library validation and conversion failures have zero result codes and an empty sqliteMessage.

SqliteUsageError is the catchable error for invalid public handle state, including use of a closed connection, use of a finalized statement, reentrant use of an active explicit statement, and close or finalize during an active operation. Internal invariant failures remain defects.

Neither exception type attaches SQL text or bound parameter values. SQLite's own message can identify schema objects or SQL tokens, so applications should apply their normal protection policy to logs and should bind secrets rather than placing them in SQL literals.

Supported types

For a type to be supported when using unpacking and parameter substitution the procedures toDb and fromDb must be implemented for the type. Below is table describing which types are supported by default and to which SQLite type they are mapped to:

Nim type	SQLite type
Ordinal	INTEGER
SomeFloat	REAL
string	TEXT
seq[byte]	BLOB
Option[T]	NULL if value is none(T), otherwise the type that T would use

Embedded NUL bytes remain valid in bound string (TEXT) and seq[byte] (BLOB) values; the SQL-input restriction does not apply to bound values.

SQLite INTEGER values are signed 64-bit integers. Binding an unsigned ordinal above high(int64), or decoding an integer into a narrower integer, range, boolean, character, or enum that cannot represent it, raises SqliteError instead of wrapping or depending on compiler range checks. Floating-point decoding returns the requested Nim floating-point type.

This can be extended by implementing toDb and fromDb for other types. Below is an example how support for times.Time can be added:

import times

proc toDb(t: Time): DbValue =
    DbValue(kind: sqliteInteger, intVal: toUnix(t))

proc fromDb(value: DbValue, T: typedesc[Time]): Time =
    fromUnix(value.fromDb(int))