SQLite API

Embedded SQL database modeled on better-sqlite3, with prepared statements, transactions, and databases that serialize to and from memory.

Overview

The global identifier SQLite is a small namespace; the real work happens on the Database and Statement handles returned from SQLite.open and db.prepare. The native ships with the vendored SQLite 3.50.2 amalgamation built single-threaded — there are no external runtime dependencies and no libsqlite3 to install on the host.

The binding follows Node's better-sqlite3 closely, with three small departures for consistency with the rest of zym: SQLite.open(...) is a factory function, since zym has no new; db.transaction takes the transaction mode as a second argument instead of attaching .deferred / .immediate / .exclusive to the returned function; and stmt.iterate returns an explicit next() / return() object, since zym has no for...of construct — iteration is always while (row != null).

Conventions

Databases open from three source forms: a filesystem path, the literal ":memory:", or a Buffer containing a serialized database image. The buffer form runs SQLite's sqlite3_deserialize under the hood, so a database can travel through ZPK archives or over a socket without ever touching disk.

Type Mapping

Booleans bind as INTEGER 0/1 (matching better-sqlite3) and come back as numbers — SQLite has no boolean column type.

Zym numbers are doubles, so SQLite integers whose magnitude exceeds 2⁵³ silently lose precision by default. Opt in to safeIntegers on a statement (or set db.defaultSafeIntegers(true)) to receive out-of-range integers as decimal strings instead.

Parameter Binding

Each run / get / all / iterate / bind call accepts either positional arguments (one per ? placeholder) or a single map argument keyed by the parameter name (binds to @name, :name, or $name in the SQL). Mixing positional and named forms in one call is rejected.

Lifetimes & Errors

Database and Statement handles each carry a finalizer; closing the parent Database also finalizes every prepared statement attached to it. Explicit stmt.finalize() and db.close() calls are rarely needed — they exist for scripts that want deterministic resource release.

SQLite errors raise Zym runtime errors of the form Database.method: <sqlite message> (<error name>, code <n>). There is no per-call success boolean; if the call returns, it succeeded.

Capabilities

SQLite is a grantable CLI native alongside File, Process, Pack, and others. The root VM gets it by default; a child VM created via Zym.newVM(...) receives it only if its parent grants it (e.g. registerCliNative("SQLite")).

Statics

Opens a database and returns a Database handle. The file form creates the database if the file is missing, unless fileMustExist is set. The ":memory:" form opens a fresh in-memory database. The buffer form opens an in-memory database loaded from a serialized SQLite image (see db.serialize()); the bytes are copied into SQLite-owned memory on open, so the original Buffer can be freed or overwritten immediately afterward.

• source (string or Buffer) — a filesystem path, the literal ":memory:", or a Buffer containing a serialized database image
• opts (map, optional) — recognised keys: readonly (bool), fileMustExist (bool, file form only)

Returns: A Database handle.

var db  = SQLite.open("app.db")                      // file on disk, created if missing
var mem = SQLite.open(":memory:")                    // fresh in-memory database
var img = SQLite.open(bytes)                          // Buffer with a serialized image
var ro  = SQLite.open("app.db", { readonly: true })  // options map

The linked SQLite library version as a string, e.g. "3.50.2". A property, not a method.

Database

Returned by SQLite.open(...).

Properties

Returns true for ":memory:" and buffer-loaded databases.

Returns true when the database was opened with { readonly: true }.

Returns the path or ":memory:" token passed to open. Stable for the life of the handle.

Returns true while the database is open; false after db.close().

Returns true if any transaction (top-level or savepoint) is currently active.

Statements

Compiles sql into a reusable prepared statement. Prepared statements survive the call that created them and may be reused across many run / get / all invocations.

• sql (string) — the SQL to compile, with ? or named placeholders

Returns: A Statement handle.

Executes sql as one or more semicolon-separated statements with no parameters and no result rows. Use this for DDL and bulk script execution. Returns null.

• sql (string) — one or more semicolon-separated SQL statements

Pragmas

Runs PRAGMA <name> and returns the result as a list of maps, one per row. When opts.simple is true, returns just the first column of the first row instead (or null for empty results).

• name (string) — the pragma body, e.g. "journal_mode" or "journal_mode = WAL"
• opts (map, optional) — simple (bool): return a scalar instead of a row list

print(db.pragma("journal_mode", { simple: true }))   // "memory"
print(db.pragma("foreign_keys", { simple: true }))   // 0

db.pragma("journal_mode = WAL")   // returns the new mode as a row
db.pragma("foreign_keys = ON")

Transactions

Wraps fn so that calling the returned function runs fn inside a BEGIN / COMMIT. Nested calls open a SAVEPOINT instead. If fn raises a runtime error, the wrapper rolls back and re-raises. The optional mode uses BEGIN <mode> for the outermost transaction.

• fn (function) — the body to run transactionally
• mode (string, optional) — "deferred" (default), "immediate", or "exclusive"

Returns: A function; call it to run fn inside the transaction.

var insertOne = db.prepare("INSERT INTO users (name, age) VALUES (?, ?)")

var insertMany = db.transaction(func(rows) {
    var i = 0
    while (i < length(rows)) {
        insertOne.run(rows[i][0], rows[i][1])
        i = i + 1
    }
})

insertMany([["Dave", 18], ["Eve", 35], ["Frank", 50]])

If the callback raises a runtime error, the wrapper issues a ROLLBACK and re-raises. For locking-sensitive bulk inserts, pass "immediate" as the second argument to request an immediate lock up front.

Buffer Serialization

Returns the full database image as a fresh Buffer. The buffer can be stored in a ZPK entry, sent over the network, or reopened with SQLite.open(buffer).

Big Integers

With no argument, toggles the default safeIntegers mode used by every statement subsequently prepared on this database; with b, sets the default explicitly. Returns null. See stmt.safeIntegers for what the mode does.

• b (bool, optional) — the new default; omit to toggle

Lifecycle

Closes the database and finalizes every prepared statement attached to it. After close, every method on the handle raises. Returns null. GC will also close the database as a safety net.

Statement

Returned by db.prepare(sql). Prepared statements are reusable; each run / get / all / iterate call resets the statement and rebinds its parameters.

Properties

Returns the original SQL text.

Returns true if the statement produces result rows (i.e. SELECT / PRAGMA / RETURNING ...).

Returns true if the statement does not modify the database.

Returns true while a stmt.iterate(...) cursor is still being consumed.

Execution

Each method below accepts either zero or more positional arguments (one per ?) or a single map argument for named bindings. Mixed calls are rejected.

Executes the statement and discards any result rows.

Returns: A map { changes, lastInsertRowid } — changes is the number of rows affected; lastInsertRowid is the rowid of the most recent successful INSERT on the parent database.

Executes the statement, returns the first row, and resets. Returns null if there are no rows.

Executes the statement and returns every row as a list.

Returns an iterator object with .next() and .return() methods. Call .next() until it returns null. The statement is marked busy until iteration completes or .return() is called.

var iter = db.prepare("SELECT id, name FROM users ORDER BY id").iterate()
var row = iter.next()
while (row != null) {
    print("%v: %v\n", row.id, row.name)
    row = iter.next()
}

Calling iter.return() before exhaustion is safe and releases the underlying cursor early.

Permanently binds parameters so subsequent execution methods don't need them. Returns null. In better-sqlite3, calling bind a second time is an error; here a second call is allowed and simply rebinds.

• params (positional values or map) — the bindings to attach to the statement

Row Shape Toggles

Toggles are sticky on the statement; each call returns null, not the statement itself.

When on, single-column rows are returned as bare values instead of { column: value } maps.

When on, rows are returned as nested maps keyed by source table: row.users.id, row.posts.title. Columns that don't trace to a source table (expressions, constants) go under the synthetic $ key.

When on, rows are returned as lists of values in column order, with no names. raw wins over pluck / expand when more than one is set.

// Pluck a single column
var names = db.prepare("SELECT name FROM users ORDER BY id")
names.pluck(true)
print(names.all())   // ["Alice", "Bob", "Carol", ...]

// Raw rows as lists
var s = db.prepare("SELECT id, name, age FROM users")
s.raw(true)
print(s.all())       // [[1, "Alice", 30], [2, "Bob", 25], ...]

// Expand by source table (useful with JOINs)
var j = db.prepare("SELECT u.id, u.name, p.title FROM users u JOIN posts p ON p.author_id = u.id")
j.expand(true)
var row = j.get()
print(row.users.name)
print(row.posts.title)

Reflection

Returns a list with one entry per result column: { name, column, table, database, type }. name is the SELECT alias; column is the underlying column name from the source table (or null for expressions); table and database identify the source; type is the declared SQL type or null.

var stmt = db.prepare("SELECT id, name AS who FROM users")
print(stmt.columns())
// [{"name":"id","column":"id","table":"users","database":"main","type":"INTEGER"},
//  {"name":"who","column":"name","table":"users","database":"main","type":"TEXT"}]
print(stmt.reader())     // true
print(stmt.readonly())   // true

Big Integers

Toggle. When on, INTEGER columns whose value exceeds 2⁵³ in magnitude are returned as decimal strings instead of numbers. On the bind side, decimal-string arguments that parse to an integer outside the double range are bound as INTEGER. Without it, the integer value is squashed to the nearest double on the way in and out; switching it on costs nothing for values that already fit in 53 bits.

var stmt = db.prepare("SELECT 9223372036854775807 AS big")

print(stmt.get().big)     // 9.2233720368548e+18 — precision lost
stmt.safeIntegers(true)
print(stmt.get().big)     // "9223372036854775807" — exact, as a string

// To bind a string back as an int64, use safeIntegers on the binding side too:
var q = db.prepare("SELECT * FROM events WHERE id = ?")
q.safeIntegers(true)
q.get("9223372036854775807")

Lifecycle

Releases the underlying prepared statement. Returns null. Optional — GC and db.close() will both finalize the statement on your behalf.

Examples

Quick Start

var db = SQLite.open(":memory:")

db.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")

var insert = db.prepare("INSERT INTO users (name, age) VALUES (?, ?)")
insert.run("Alice", 30)
insert.run("Bob", 25)

var users = db.prepare("SELECT * FROM users ORDER BY age").all()
print(users)   // [{"id":2,"name":"Bob","age":25}, {"id":1,"name":"Alice","age":30}]

Named Bindings

var stmt = db.prepare("INSERT INTO users (name, age) VALUES (@name, @age)")
stmt.run({ name: "Carol", age: 42 })

Any of @name, :name, or $name resolves to the same map key.

Buffer Round-Trip

Open a database from a Buffer (e.g. the bytes of a .sqlite file embedded in a ZPK), mutate it, and write the result back into a fresh Buffer.

// Load a config DB stored inside a ZPK archive.
var bytes = bundle.open("config.sqlite")     // returns a Buffer
var db    = SQLite.open(bytes)

db.prepare("UPDATE settings SET value = ? WHERE key = ?").run(newValue, "theme")

// Hand back a fresh buffer to whatever wants to persist the DB.
var updated = db.serialize()
db.close()

// updated can now be written into a ZPK entry, sent over a socket,
// or held in memory for later.

This path never touches the filesystem, so it is well suited to configuration storage, save files, and shipping small databases over the network. Snapshotting an in-memory database for later restoration works the same way.

SQLite.open(buffer) and db.serialize() are not part of the better-sqlite3 surface; they fall out of SQLite's own sqlite3_serialize / sqlite3_deserialize C API.