Writer worker

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

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.

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

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.

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

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.

import { Sirannon } from '@delali/sirannon-db'
import { Sirannon, SirannonError } 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: {
    maxPendingWrites: 1024,
    writeTimeoutMs: 30_000,
    maxRestarts: 5,
  },
  writerWorker: { maxPendingWrites: 1 }, 
})
 
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))
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()

The error includes retryAfterMs as a hint for when to try again. Over the 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.

import { Sirannon, SirannonError } from '@delali/sirannon-db'
import { Sirannon, SirannonError, WriteOverloadError } 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: { maxPendingWrites: 1 }, 
  writerWorker: { maxPendingWrites: 1024, writeTimeoutMs: 30_000 }, 
})
 
await db.execute('CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total REAL NOT NULL)')
 
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))
}
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()

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