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

# Writer worker

> Move writes onto a dedicated worker thread so that a slow disk write never blocks the event loop, with a bounded queue, a write deadline, and error codes separating a rejected write from one whose outcome is unknown.

URL: https://sirannon.sondelali.com/docs/writer-worker
Section: Core features
Version: 0.3 (@delali/sirannon-db@0.3.4, latest)

The SQLite engine is synchronous, so every write, and every `fsync` behind it, blocks the thread it runs on. On a loaded server that thread is the event loop, and one slow flush freezes reads, subscriptions, and new connections until the disk confirms. The `writerWorker` option moves every write onto a dedicated worker thread. The event loop passes each write to that worker and stays free, while reads keep running on the calling thread. This page builds one file, `process-orders.ts`, step by step. Enable the worker, bound its queue, handle the two errors a stalled write can raise, and read why a large <Tooltip tip="DDL (data definition language) covers the statements that change a database's schema, such as CREATE TABLE and DROP INDEX.">DDL</Tooltip> statement needs a maintenance window.

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

## Enable the worker

Pass `writerWorker: true` in `DatabaseOptions`. The option requires a driver with a worker entry; the `better-sqlite3` and `node` drivers have one, and opening with any other driver throws a `SirannonError` with code `WRITER_WORKER_UNSUPPORTED`. Start `process-orders.ts` with a database whose writes run off the event loop.

```ts title="process-orders.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('orders', './data/orders.db', {
  writerWorker: true,
})

await db.execute('CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total REAL NOT NULL)')

const result = await db.execute('INSERT INTO orders (customer, total) VALUES (?, ?)', ['Sarah Mitchell', 129.5])

console.log(JSON.stringify(result, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2))

await sirannon.shutdown()
```

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

The API stays the same, and `execute`, `transaction`, `executeBatch`, and `bulkLoad` keep their signatures and results. The worker applies them in the order you issued them. You can also set `writerWorker` once on the `Sirannon` instance. A database that sets no option of its own then inherits that instance value, while a database that sets one keeps its own. A read-only database never starts a worker.

## Bound the queue and set the deadline

`writerWorker` also accepts an options object. Replace the `true` with explicit settings.

```ts title="process-orders.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('orders', './data/orders.db', {
  writerWorker: true, // [!code --]
  // [!code ++:5]
  writerWorker: {
    maxPendingWrites: 1024,
    writeTimeoutMs: 30_000,
    maxRestarts: 5,
  },
})

await db.execute('CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total REAL NOT NULL)')

const result = await db.execute('INSERT INTO orders (customer, total) VALUES (?, ?)', ['Sarah Mitchell', 129.5])

console.log(JSON.stringify(result, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2))

await sirannon.shutdown()
```

| Option | Default | Description |
| --- | --- | --- |
| `maxPendingWrites` | `1024` | Writes allowed in flight before new writes are rejected with `WRITE_OVERLOADED`. A slow disk can otherwise let the queue grow without limit while the event loop keeps accepting work. |
| `writeTimeoutMs` | `30000` | Per-operation deadline in milliseconds. `0` disables it. The section below covers what happens when it expires. |
| `maxRestarts` | `5` | Times the host respawns the worker after it crashes on its own. Past the limit, writes fail permanently with `WRITER_WORKER_FATAL`. |

Each value must be an integer, and an invalid one makes `sirannon.open` throw `INVALID_WRITER_WORKER`. A worker crash rejects the writes in flight at that moment with `WRITER_WORKER_EXIT`, and the respawned worker then picks up new work. One successful write resets the restart count.

## Handle back-pressure

When `maxPendingWrites` writes are already in flight, the next write fails immediately with code `WRITE_OVERLOADED`. That back-pressure is definite, because the write never reached the worker and nothing applied, which makes retrying it safe. Lower the limit to `1` and fire ten inserts at once to see the rejection.

```ts title="process-orders.ts"
import { Sirannon } from '@delali/sirannon-db' // [!code --]
import { Sirannon, SirannonError } from '@delali/sirannon-db' // [!code ++]
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'

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

const db = await sirannon.open('orders', './data/orders.db', {
  // [!code --:5]
  writerWorker: {
    maxPendingWrites: 1024,
    writeTimeoutMs: 30_000,
    maxRestarts: 5,
  },
  writerWorker: { maxPendingWrites: 1 }, // [!code ++]
})

await db.execute('CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total REAL NOT NULL)')

// [!code --:3]
const result = await db.execute('INSERT INTO orders (customer, total) VALUES (?, ?)', ['Sarah Mitchell', 129.5])

console.log(JSON.stringify(result, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2))
// [!code ++:13]
const writes = Array.from({ length: 10 }, (_, index) =>
  db.execute('INSERT INTO orders (customer, total) VALUES (?, ?)', [`Customer ${index + 1}`, 10 * (index + 1)]),
)

const outcomes = await Promise.allSettled(writes)
const overloaded = outcomes.find(
  (outcome) => outcome.status === 'rejected' && outcome.reason instanceof SirannonError && outcome.reason.code === 'WRITE_OVERLOADED',
)

if (overloaded && overloaded.status === 'rejected') {
  const reason = overloaded.reason
  console.log(JSON.stringify({ code: reason.code, message: reason.message, retryAfterMs: reason.retryAfterMs }, null, 2))
}

await sirannon.shutdown()
```

```json result
{
  "code": "WRITE_OVERLOADED",
  "message": "Write rejected: 1 writes already pending",
  "retryAfterMs": 1000
}
```

The error includes `retryAfterMs` as a hint for when to try again. Over the [server](/docs/server), the same rejection becomes an HTTP 503 with a `Retry-After` header, which lets a remote client back off the same way a local caller does. Treat `WRITE_OVERLOADED` as a signal to retry with backoff, never as data loss.

## What the deadline means

When a write outlives `writeTimeoutMs`, the host cannot kill the worker to reclaim it. A thread inside a synchronous native SQLite call cannot be interrupted, and terminating it would leak the database file lock and could abort the whole process. The host therefore asks the worker to cancel the operation, and the outcome splits three ways.

- The worker skips work it has not started and rejects it with `WRITE_OVERLOADED`, and because that write never began, retrying it is safe.
- A result that comes back within one further deadline reaches the caller normally, as though nothing had expired.
- An operation still unresolved after that grace window fails with `WRITER_WORKER_TIMEOUT`, at twice `writeTimeoutMs` after the host sent it.

The two codes call for different handling. `WRITE_OVERLOADED` is definite, because the write never applied. `WRITER_WORKER_TIMEOUT` is indeterminate, because the write may have applied, may still apply, or may have failed, and the caller cannot know which. Blindly retrying a non-idempotent write after `WRITER_WORKER_TIMEOUT` risks applying it twice. Read the current state back and reconcile before retrying, or design the write to be idempotent, for example an `INSERT` with an explicit primary key and `ON CONFLICT DO NOTHING`.

Turn `process-orders.ts` into that shape. Replace the overload demonstration with an upsert keyed on the order id, and add one function, `setOrderTotal`, that handles both codes. It retries `WRITE_OVERLOADED` after the hinted delay. On `WRITER_WORKER_TIMEOUT` it reads the row back and reruns the write only where the row does not hold the intended total. The queue limit returns to its default, and the code states the deadline explicitly.

```ts title="process-orders.ts"
import { Sirannon, SirannonError } from '@delali/sirannon-db' // [!code --]
import { Sirannon, SirannonError, WriteOverloadError } from '@delali/sirannon-db' // [!code ++]
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'

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

const db = await sirannon.open('orders', './data/orders.db', {
  writerWorker: { maxPendingWrites: 1 }, // [!code --]
  writerWorker: { maxPendingWrites: 1024, writeTimeoutMs: 30_000 }, // [!code ++]
})

await db.execute('CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total REAL NOT NULL)')

// [!code --:13]
const writes = Array.from({ length: 10 }, (_, index) =>
  db.execute('INSERT INTO orders (customer, total) VALUES (?, ?)', [`Customer ${index + 1}`, 10 * (index + 1)]),
)

const outcomes = await Promise.allSettled(writes)
const overloaded = outcomes.find(
  (outcome) => outcome.status === 'rejected' && outcome.reason instanceof SirannonError && outcome.reason.code === 'WRITE_OVERLOADED',
)

if (overloaded && overloaded.status === 'rejected') {
  const reason = overloaded.reason
  console.log(JSON.stringify({ code: reason.code, message: reason.message, retryAfterMs: reason.retryAfterMs }, null, 2))
}
// [!code ++:29]
const UPSERT_ORDER =
  'INSERT INTO orders (id, customer, total) VALUES (?, ?, ?) ON CONFLICT(id) DO UPDATE SET customer = excluded.customer, total = excluded.total'

const setOrderTotal = async (id: number, customer: string, total: number): Promise<void> => {
  try {
    await db.execute(UPSERT_ORDER, [id, customer, total])
  } catch (err) {
    if (err instanceof WriteOverloadError) {
      await new Promise((resolve) => setTimeout(resolve, err.retryAfterMs))
      await db.execute(UPSERT_ORDER, [id, customer, total])
      return
    }
    if (err instanceof SirannonError && err.code === 'WRITER_WORKER_TIMEOUT') {
      const row = await db.queryOne<{ total: number }>('SELECT total FROM orders WHERE id = ?', [id])
      if (row?.total !== total) {
        await db.execute(UPSERT_ORDER, [id, customer, total])
      }
      return
    }
    throw err
  }
}

await setOrderTotal(1, 'Sarah Mitchell', 129.5)
await setOrderTotal(1, 'Sarah Mitchell', 154.5)

const order = await db.queryOne<{ id: number; customer: string; total: number }>('SELECT id, customer, total FROM orders WHERE id = ?', [1])

console.log(JSON.stringify(order, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2))

await sirannon.shutdown()
```

```json result
{
  "id": 1,
  "customer": "Sarah Mitchell",
  "total": 154.5
}
```

The upsert is what makes both recoveries safe. Because the statement supplies the full intended row and conflicts on the primary key, running it twice produces the same state as running it once. The two back-to-back calls show that, since the second call finds the row already present and updates it to the new total. `WriteOverloadError` narrows the catch to the overload case and exposes `retryAfterMs`, which is what makes the retry wait exactly as long as the engine suggests. The `WRITER_WORKER_TIMEOUT` branch is the reconcile step. An idempotent write could retry blindly, and the read here shows the pattern a non-idempotent write requires, where you compare the row with what the write should have produced and only then decide.

## Keep large DDL in maintenance windows

The worker removes writes from the event loop, but the database still has a single writer, and one long statement holds it from start to finish. DDL over a large table is the common case. The [public benchmark run](/benchmarks) seeds a table of 10,000,000 rows and holds the engine under a 2 GB memory ceiling, and dropping that table takes minutes of random reads. Every write behind it waits in the queue, and once `maxPendingWrites` writes are queued, the database sheds new writes as `WRITE_OVERLOADED` until the statement finishes.

Two operational rules follow.

- Run large DDL such as `DROP TABLE`, `ALTER TABLE` rewrites, or big index builds in a maintenance window, when the write rate is low enough for the queue to absorb the pause.
- Read `WRITE_OVERLOADED` during such a statement as back-pressure to retry with backoff, never as a fault, because the queue drains and retries succeed the moment the DDL commits.

## What else the writer worker does

The worker holds the connection that writes, and two pieces of work other than statements need that same connection.

A [backup](/docs/backups) copies the database through SQLite's stepped backup interface on the writer, and a database you open with the writer worker therefore makes its copies inside that worker. Each step reports back to the calling thread as it finishes. The copy occupies the worker thread while the event loop stays free, which lets reads and live queries on the main thread carry on answering.

Every step also restarts the worker's write deadline, which lets a copy that keeps moving pages take as long as the database needs. Once a whole deadline passes with no step at all, the host cancels the copy. A worker that manages one further step after that cancellation unwinds the copy with `BACKUP_ABORTED`, and a worker that manages none reports `BACKUP_STALLED` when the grace window closes. Both codes differ from `WRITER_WORKER_TIMEOUT` because a copy that moves no pages has definitely failed, while a statement that misses the deadline may or may not have reached the disk. Sirannon removes the file either copy was writing, which leaves no half-written database at that path.

A [compiled extension](/docs/extensions) loads into that same connection, and the worker records the resolved path of every extension it loads. A respawned worker opens a fresh connection, and it loads each recorded extension onto that connection before it accepts any work. Writes that call an extension's functions therefore keep working across a crash and a respawn, and your application reloads nothing.