Every database handle exposes the same query API regardless of driver. Parameters are always kept separate from the SQL text, and reads and writes use separate connection pools. This page builds one file, accounts.ts, step by step. You open a database, create a table, load rows, read them back, and move money between two accounts inside a transaction. Each step highlights exactly what changed.
Open a database
Start accounts.ts with a driver, a Sirannon instance, and an open database. The driver you pick matches your runtime; this example uses better-sqlite3 on Node.
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('ledger', './data/ledger.db')open(id, path, options?) returns a database handle. The id names the database for the registry, hooks, and metrics, and the path is the file's location on disk. Options let you size the read pool and toggle WAL mode, covered further down.
Create a table
Use execute for statements that change the database or its schema. It returns the number of rows changes touched and the lastInsertRowId of the last insert. Add an accounts table to accounts.ts.
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('ledger', './data/ledger.db')
await db.execute(
`CREATE TABLE IF NOT EXISTS accounts (
id INTEGER PRIMARY KEY,
holder TEXT NOT NULL,
balance INTEGER NOT NULL DEFAULT 0
)`,
)Insert rows
execute also runs a single insert. Pass values as a parameter array so they never touch the SQL text. Open two accounts, one for Emma and one for Kenji.
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('ledger', './data/ledger.db')
await db.execute(
`CREATE TABLE IF NOT EXISTS accounts (
id INTEGER PRIMARY KEY,
holder TEXT NOT NULL,
balance INTEGER NOT NULL DEFAULT 0
)`,
)
const opened = await db.execute(
'INSERT INTO accounts (holder, balance) VALUES (?, ?)',
['Emma Wright', 5000],
)
console.log(JSON.stringify(opened, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2))Load many rows in one call
Opening one account at a time is clear, but seeding several that way sends a separate call per row. executeBatch runs one statement across many parameter sets in a single call. Swap the single insert for a batch that opens both accounts at once.
const opened = await db.execute(
'INSERT INTO accounts (holder, balance) VALUES (?, ?)',
['Emma Wright', 5000],
)
// opened.changes === 1, opened.lastInsertRowId === 1
await db.executeBatch('INSERT INTO accounts (holder, balance) VALUES (?, ?)', [
['Emma Wright', 5000],
['Kenji Tanaka', 2000],
])executeBatch reuses one prepared statement for every parameter set, so a bulk seed avoids per-row overhead and the values remain parameterised.
Read rows back
With both accounts on disk, read them. query<T> returns every matching row as a typed array, and queryOne<T> returns the first row or undefined when nothing matches. Read the full list and one account by id.
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('ledger', './data/ledger.db')
await db.execute(
`CREATE TABLE IF NOT EXISTS accounts (
id INTEGER PRIMARY KEY,
holder TEXT NOT NULL,
balance INTEGER NOT NULL DEFAULT 0
)`,
)
await db.executeBatch('INSERT INTO accounts (holder, balance) VALUES (?, ?)', [
['Emma Wright', 5000],
['Kenji Tanaka', 2000],
])
type Account = { id: number; holder: string; balance: number }
const accounts = await db.query<Account>('SELECT * FROM accounts ORDER BY id')
const emma = await db.queryOne<Account>('SELECT * FROM accounts WHERE id = ?', [1])
console.log(JSON.stringify({ accounts, emma }, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2))query gives you the whole result set, queryOne saves you indexing into an array when you expect a single row, and both accept the same parameter array as execute.
Move money in a transaction
Transferring between accounts touches two rows, and a half-applied transfer would lose money. transaction runs a callback against a dedicated transaction handle tx that carries query, execute, and executeBatch, the same reads and writes as db. The work commits when the callback resolves and rolls back when it throws, and whatever the callback returns becomes the call's result. Move 1000 from Emma to Kenji and return Kenji's new balance.
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
const db = await sirannon.open('ledger', './data/ledger.db')
await db.execute(
`CREATE TABLE IF NOT EXISTS accounts (
id INTEGER PRIMARY KEY,
holder TEXT NOT NULL,
balance INTEGER NOT NULL DEFAULT 0
)`,
)
await db.executeBatch('INSERT INTO accounts (holder, balance) VALUES (?, ?)', [
['Emma Wright', 5000],
['Kenji Tanaka', 2000],
])
type Account = { id: number; holder: string; balance: number }
const accounts = await db.query<Account>('SELECT * FROM accounts ORDER BY id')
const emma = await db.queryOne<Account>('SELECT * FROM accounts WHERE id = ?', [1])
const kenjiBalance = await db.transaction(async (tx) => {
await tx.execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', [1000, 1])
await tx.execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', [1000, 2])
const [kenji] = await tx.query<Account>('SELECT balance FROM accounts WHERE id = ?', [2])
return kenji?.balance
})
console.log(kenjiBalance)If either update throws, neither one persists, so the two accounts never disagree on the total.
Tune the connection pool
Every database opens with one dedicated write connection and N read connections, four by default. WAL mode is enabled by default, which lets reads run concurrently with a write. Pass DatabaseOptions to open to change these for a read-heavy database.
const analytics = await sirannon.open('analytics', './data/analytics.db', {
readPoolSize: 8,
walMode: true,
})DatabaseOptions also accepts readOnly to enforce immutability at the connection level, and cdcPollInterval and cdcRetention for change data capture.
synchronous sets the writer durability, the PRAGMA synchronous level at which SQLite commits. It takes 'off', 'normal', 'full', or 'extra' and defaults to 'normal'. Raise it to 'full' when a commit must survive a power loss, and keep 'normal' for WAL mode's balance of safety and speed. A bulk load relaxes this level while it imports and restores the level you set here when it finishes.
const ledger = await sirannon.open('ledger', './data/ledger.db', {
synchronous: 'full',
})Handle errors
Every failure throws an error extending SirannonError with a machine-readable code, covering the registry (DATABASE_NOT_FOUND, DATABASE_ALREADY_EXISTS), execution (QUERY_ERROR, TRANSACTION_ERROR, READ_ONLY), and the feature areas (MIGRATION_ERROR, CDC_ERROR, BACKUP_ERROR, HOOK_DENIED, CONNECTION_POOL_ERROR). Catch QueryError around a statement to read the failing code and message.
import { QueryError } from '@delali/sirannon-db'
try {
await db.execute('INSERT INTO accounts (id, holder) VALUES (?, ?)', [1, 'Emma Wright'])
} catch (err) {
if (err instanceof QueryError) {
console.error(`SQL failed [${err.code}]: ${err.message}`)
}
}Remote errors carry the same codes with stack traces and internal details stripped, so client-side handling works the same against a networked database.