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 hands work over and stays free; reads keep running on the calling thread. This page builds one file, process-orders.ts, step by step. You enable the worker, bound its queue, handle the two errors a stalled write can raise, and learn why large DDL belongs in a maintenance window.

Enable the worker

Pass writerWorker: true in DatabaseOptions. The option needs 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 (?, ?)', ['Amara Okafor', 129.5])
 
console.log(JSON.stringify(result, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2))

The API does not change: execute, transaction, executeBatch, and bulkLoad keep their signatures and results, and the worker applies them in the order they were issued. You can also set writerWorker once on the Sirannon instance instead of per database; a database that does not set the option inherits the instance value, and a database that does set it wins. 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 (?, ?)', ['Amara Okafor', 129.5])
 
console.log(JSON.stringify(result, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2))

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, then the respawned worker picks up new work; one successful write resets the restart count.

Handle back-pressure

When maxPendingWrites writes are already in flight, the next write is rejected immediately with code WRITE_OVERLOADED. This is definite back-pressure: the write never reached the worker, nothing was applied, and retrying it is 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 (?, ?)', ['Amara Okafor', 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))
}

The error carries retryAfterMs as a hint for when to try again. Over the server, the same rejection becomes an HTTP 503 with a Retry-After header, so a remote client backs off the same way a local caller does. Treat WRITE_OVERLOADED as a signal to retry with backoff, never as data loss.

Know 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 can abort the whole process. The host instead asks the worker to cancel the operation, and the outcome splits three ways.

• Work the worker has not started yet is skipped and rejected with WRITE_OVERLOADED. The write never began, so it is safe to retry.
• A result arriving within one further deadline is delivered normally, as if nothing expired.
• An operation still unresolved after that grace window is rejected with WRITER_WORKER_TIMEOUT, at twice writeTimeoutMs after it was sent.

The two codes call for different handling. WRITE_OVERLOADED is definite: the write was not applied. WRITER_WORKER_TIMEOUT is indeterminate: 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, so 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 one function, setOrderTotal, that owns both codes: it retries WRITE_OVERLOADED after the hinted delay, and on WRITER_WORKER_TIMEOUT it reads the row back and reruns the write only when the row does not hold the intended total. The queue limit returns to its default, and the deadline is stated 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, 'Amara Okafor', 129.5)
await setOrderTotal(1, 'Amara Okafor', 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))

The upsert is what makes both recoveries safe. Because the statement carries the full intended row and conflicts on the primary key, running it twice produces the same state as running it once, which the two back-to-back calls demonstrate: 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, so the retry waits exactly as long as the engine suggested. The WRITER_WORKER_TIMEOUT branch is the reconcile step: with an idempotent write it could retry blindly, but the read shows the pattern a non-idempotent write needs, 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. Measured on a 10-million-row table (13.9 GB on disk) under a 2 GB memory cap, one DROP TABLE held the writer for 16 seconds on a freshly seeded table and up to 114 seconds when the operating system's page cache was cold. Every write behind it waits in the queue, and once maxPendingWrites writes are queued, new writes are shed 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. And read WRITE_OVERLOADED during such a statement as back-pressure to retry with backoff, never as a fault; the moment the DDL commits, the queue drains and retries succeed.