> Complete content index: https://sirannon.sondelali.com/llms.txt

# Queries and transactions

> Run parameterised SQL, batch statements, wrap work in transactions, and tune the connection pool behind every database.

URL: https://sirannon.sondelali.com/docs/queries-and-transactions
Section: Core features
Version: 0.3 (@delali/sirannon-db@0.3.0, latest)

Every database handle exposes the same query API regardless of driver. Sirannon always keeps parameters separate from the SQL text, and reads and writes use separate connection pools. This page builds one file, `accounts.ts`, step by step. You will open a database, create a table, load rows, read them back, and move money between two accounts inside a transaction. Every step marks the lines that changed.

Sirannon writes the database file into a directory you create yourself, so make one before you run any of the code below.

```bash
mkdir -p data
```

## 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.

```ts title="accounts.ts"

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 <Tooltip tip="WAL mode makes SQLite append each change to a write-ahead log file before updating the main database file, which lets reads run while a write is in progress.">WAL mode</Tooltip>, covered further down.

## Create a table

Use `execute` for statements that change the database or its schema. It returns `changes`, the number of rows the statement touched, and `lastInsertRowId`, the row id of the last insert. Add an `accounts` table to `accounts.ts`.

```ts title="accounts.ts"

const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })

const db = await sirannon.open('ledger', './data/ledger.db')
// [!code ++:8]

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 that they never touch the SQL text. Open two accounts, one for Emma and one for Kenji.

```ts title="accounts.ts"

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
  )`,
)
// [!code ++:7]

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))
```

```json result
{
  "changes": 1,
  "lastInsertRowId": 1
}
```

## 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.

```ts title="accounts.ts"

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
  )`,
)

// [!code --:6]
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))
// [!code ++:4]
await db.executeBatch('INSERT INTO accounts (holder, balance) VALUES (?, ?)', [
  ['Emma Wright', 5000],
  ['Kenji Tanaka', 2000],
])
```

`executeBatch` reuses one <Tooltip tip="A prepared statement is a SQL statement the engine parses and compiles once, then runs many times with different bound values.">prepared statement</Tooltip> 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.

```ts title="accounts.ts"

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],
])
// [!code ++:7]

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))
```

```json result
{
  "accounts": [
    {
      "id": 1,
      "holder": "Emma Wright",
      "balance": 5000
    },
    {
      "id": 2,
      "holder": "Kenji Tanaka",
      "balance": 2000
    }
  ],
  "emma": {
    "id": 1,
    "holder": "Emma Wright",
    "balance": 5000
  }
}
```

`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 exposes `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.

```ts title="accounts.ts"

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)) // [!code --]
// [!code ++:8]
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)
```

```json result
3000
```

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. Sirannon enables WAL mode by default, which lets reads run while a write is in progress. Pass `DatabaseOptions` to `open` to change these for a read-heavy database. `pools.ts` is a file of its own, because it opens databases the rest of this page never uses.

```ts title="pools.ts"

const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })

const analytics = await sirannon.open('analytics', './data/analytics.db', {
  readPoolSize: 8,
  walMode: true,
})

console.log(`${analytics.id} is open`)

await sirannon.close('analytics')
```

```txt result open
analytics is open
```

`DatabaseOptions` also accepts `readOnly` to enforce immutability at the connection level, and `cdcPollInterval` and `cdcRetention` for [change data capture](/docs/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 reach the disk before it returns, and keep `'normal'` for WAL mode's balance of safety and speed. A [bulk load](/docs/bulk-load) relaxes this level while it imports and restores the level you set here when it finishes.

```ts title="pools.ts"

const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })

const analytics = await sirannon.open('analytics', './data/analytics.db', {
  readPoolSize: 8,
  walMode: true,
})

// [!code --:3]
console.log(`${analytics.id} is open`)

await sirannon.close('analytics')
// [!code ++:8]
const ledger = await sirannon.open('ledger', './data/ledger.db', {
  synchronous: 'full',
})

console.log(`${analytics.id} and ${ledger.id} are open`)

await sirannon.close('analytics')
await sirannon.close('ledger')
```

```txt result open
analytics and ledger are open
```

## 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. `errors.ts` writes to a database of its own and inserts the same primary key twice, so the second insert breaks the unique constraint.

```ts title="errors.ts"

const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })

const db = await sirannon.open('errors', './data/errors.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.execute('INSERT OR REPLACE INTO accounts (id, holder) VALUES (?, ?)', [1, 'Emma Wright'])

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}`)
  }
}

await sirannon.close('errors')
```

```txt result open
SQL failed [QUERY_ERROR]: UNIQUE constraint failed: accounts.id
```

Remote errors use the same codes, with stack traces and internal details stripped, so client-side handling works the same against a networked database.