SQLite State Adapter

Store chains, jobs and workers in SQLite with better-sqlite3, node:sqlite, bun:sqlite, Bun SQL, Drizzle, Kysely, Prisma, Knex or MikroORM.

@queuert/sqlite is suitable for local development, CLI tools, and embedded applications. SQLite allows a single writer at a time, so all writes are serialized, so batch operations use sequential queries within a single transaction rather than writeable CTEs.

Clients

Install

Terminal

npm install @queuert/sqlite

Requires SQLite 3.42 or later, which migrateToLatest() checks against the version the driver bundles.

Setup

Each tab shows the example's provider and the lines that create the adapter. better-sqlite3, node:sqlite, bun:sqlite and Drizzle run statements synchronously, so their examples use a synchronous provider. Bun SQL and Prisma use an async SqliteStateProvider with an AsyncRwLock, and Kysely, Knex and MikroORM an async one whose single connection serializes writers:

src/provider.ts

import type { SqliteStateProvider } from "@queuert/sqlite";
import type Database from "better-sqlite3";
import { type TxHooks, withTxHooks } from "queuert";

export type BetterSqlite3Context = { db: Database.Database; txHooks: TxHooks };

const requireTxHooks = (txCtx: Partial<BetterSqlite3Context>): TxHooks => {
  if (txCtx.txHooks === undefined) {
    throw new Error(
      "The transaction context has no txHooks: open the transaction with withTransaction, or create txHooks with withTxHooks and pass them in the context",
    );
  }
  return txCtx.txHooks;
};

export const createBetterSqlite3StateProvider = ({
  db,
}: {
  db: Database.Database;
}): SqliteStateProvider<BetterSqlite3Context, "sync"> => {
  const stmtCache = new Map<string, Database.Statement>();
  const prepareCached = (id: string, sql: string): Database.Statement => {
    let stmt = stmtCache.get(id);
    if (!stmt) {
      stmt = db.prepare(sql);
      stmtCache.set(id, stmt);
    }
    return stmt;
  };

  return {
    sync: true,
    withTransaction: (fn) => withTxHooks((txHooks) => db.transaction(() => fn({ db, txHooks }))()),
    isTransaction: (txCtx) => txCtx.db.inTransaction,
    onTransactionCommit: (txCtx, callback) => {
      requireTxHooks(txCtx).onCommit(callback);
    },
    onTransactionRollback: (txCtx, callback) => {
      requireTxHooks(txCtx).onRollback(callback);
    },
    classifyError: ({ error }) => {
      const tableName = /no such table: (\S+)/.exec(
        String((error as { message?: unknown } | undefined)?.message),
      )?.[1];
      return tableName === undefined ? undefined : { kind: "undefined_table", tableName };
    },
    executeSql: ({ txCtx, id, sql, params, columnTypes }) => {
      if (!txCtx && db.inTransaction) {
        throw new Error("executeSql without txCtx ran inside an open transaction");
      }
      const statement = id !== undefined ? prepareCached(id, sql) : db.prepare(sql);
      if (Object.keys(columnTypes).length > 0) return statement.all(...params);
      statement.run(...params);
      return [];
    },
    close: () => {
      stmtCache.clear();
    },
  };
};

src/index.ts

const stateProvider = createBetterSqlite3StateProvider({ db });
using stateAdapter = createSqliteStateAdapter({ stateProvider });
stateAdapter.migrateToLatest();

Configuration

createSqliteStateAdapter

  • stateProvider required
    The SqliteStateProvider for your client; see State provider.
  • tablePrefix "queuert_"
    Prefix of every table name.
  • jobIdType "TEXT"
    Type of the job id columns.
  • Type of the worker id column.
  • The most blocker chains a single job may declare. Declaring more throws BlockerLimitExceededError; see Job Blockers.
  • countCap 10_000
    The per-status count at which countChainsByTypeName and countJobsByTypeName (and the dashboard's type counts) stop counting and report hasMore. A higher cap means a longer index scan per count.
  • generateJobId crypto.randomUUID
    Called for every job created without a caller-supplied id.
  • validateJobId accept all
    Runs on every caller-supplied and generated ID before writing, throwing InvalidJobIdError on failure.
  • generateWorkerId crypto.randomUUID
    Called once for each worker runner created without a workerId; must return a new id on every call.
  • validateWorkerId accept all
    Runs on the configured or generated worker id; createWorkerRunner throws InvalidWorkerIdError on failure.

maxBlockersPerJob and countCap must be positive integers; anything else throws when the adapter is created. The resolved values are readable on stateAdapter.config.

stateProvider also takes a SqliteStateProvider in the "sync" mode, with the same options (see Synchronous providers).

State provider

A SqliteStateProvider bridges your database client with the adapter: the adapter runs all its SQL and transactions through it. Each Setup tab is a complete provider for its driver or ORM; to use another client, implement the same members and serialize writes (see AsyncRwLock), or implement the synchronous variant (see Synchronous providers):

SqliteStateProvider

  • sync optional
    true on a synchronous provider, whose members return their results directly (see Synchronous providers); absent or false on an asynchronous one.
  • Manages connection acquisition and transaction lifecycle. The callback receives a transaction context representing an active transaction. A provider that carries txHooks in its context creates them here with withTxHooks, around the transaction, so they flush after COMMIT.
  • Reports whether a context is inside an open transaction, from the driver's own state and without a query. Mutating client methods reject a context that fails it, so passing a pool or the root ORM handle instead of the transaction throws instead of autocommitting each statement.
  • Runs a callback after the context's transaction commits, and awaits it before the call that opened the transaction settles. The client and the worker register every side effect (notify events, observability events) here. A provider over a client with transaction events, such as MikroORM, runs it from those events; any other provider carries txHooks in its transaction context and registers the callback on them. See Tx Hooks. A synchronous provider runs it synchronously, and the callback must not return a promise.
  • Runs a callback synchronously when the context's transaction, or the savepoint the context belongs to, rolls back; the commit callbacks registered there never run. Implemented the same way as onTransactionCommit.
  • Executes SQL statements. When a transaction context is provided, uses that connection; when omitted, acquires and releases its own connection. readOnly marks a statement that only reads; a provider may use it to pick between shared and exclusive locks or reader and writer connections.
  • Returns { kind: "undefined_table", tableName } for a no such table: <table> error, with tableName read from the message (wrappers such as Prisma report it in their own error fields), and undefined for any other error, or when it cannot read the table name. The adapter uses it to turn a write that hit a dropped write fence into SchemaFencedError.
  • close optional
    Only needed when the provider owns resources beyond the client or pool you pass in. Must be idempotent.

Synchronous providers

better-sqlite3, node:sqlite and bun:sqlite execute every statement synchronously. A SqliteStateProvider in the "sync" mode exposes that: it sets sync: true, withTransaction runs a synchronous callback and returns its value, executeSql returns rows, and the optional close returns nothing. Over it, createSqliteStateAdapter returns a "sync" SqliteStateAdapter whose methods and migrateToLatest return their results directly. The adapter's type follows the provider's type, so annotate the provider as SqliteStateProvider<TTxContext, "sync">: a provider object whose sync is typed as boolean gives an adapter typed for either mode. createClient, createProcessors and createWorkerRunner follow the state adapter's mode, so the client built over it is synchronous.

That client's state-touching methods, workerless completeChain, and the attempt handler's complete and fence return plain values and throw synchronously, so they run inside the driver's own transaction, such as db.transaction(() => …)(), and an error rolls it back. The provider's onTransactionCommit and onTransactionRollback are synchronous too; a provider without transaction events of its own carries TxHooks in its context. awaitChain, the attempt handler and the worker runner stay async. When you pass no notify adapter, the client uses an in-process one.

A synchronous transaction cannot interleave with anything else on the thread, so a synchronous provider takes no lock. It guards the one remaining hazard, a raw BEGIN held across an await: executeSql without txCtx throws when the connection is already inside a transaction, instead of running inside someone else's. This also rejects a client read without a context inside your own transaction; pass that transaction's context.

Testing your provider

@queuert/sqlite/conformance checks a provider you write. sqliteStateProviderConformanceCases are every case a state adapter must pass, checks of the schema write fence, and checks that classifyError names a missing table. createSqliteStateProviderConformanceContext prepares the context they run against: it takes a createStateProvider factory and a withNonTransactionContext that runs a callback with a context outside any transaction, and migrates the schema once. An optional withSavepoint runs a callback in a savepoint of a transaction, opened the way your application opens one, and passes it the savepoint's context; when you give it, the cases check that onTransactionCommit and onTransactionRollback follow savepoints. Each case then builds its own adapter over a new provider from your factory, with the tables cleared, and closes both when it ends. The suite drives no test runner: your test file owns the database in its own hooks and registers one test per case. An async provider also runs asyncSqliteStateProviderConformanceCases, the cases that hold a transaction open across an await, next to them. The node:sqlite example runs under node:test, the Bun examples under bun:test and the others under vitest. Over a synchronous provider, the same sqliteStateProviderConformanceCases run these cases synchronously through the adapter over it and add cases for the synchronous contract, including the autocommit guard. createSqliteStateProviderConformanceContext returns their context directly when given a createStateProvider that returns the provider directly, a synchronous withNonTransactionContext and an optional synchronous withSavepoint:

src/provider.spec.ts

describe("sqlite/state-better-sqlite3 provider conformance", () => {
  const db = new Database(":memory:");
  let context: SqliteStateProviderConformanceContext;

  beforeAll(() => {
    context = createSqliteStateProviderConformanceContext({
      createStateProvider: () => createBetterSqlite3StateProvider({ db }),
      withNonTransactionContext: (fn) => fn({ db }),
    });
  });

  afterAll(async () => {
    db.close();
  });

  for (const conformanceCase of sqliteStateProviderConformanceCases) {
    test(conformanceCase.title, async () => {
      await conformanceCase.run(context);
    });
  }
});

The full API is in the conformance reference.

Your transactions

With a synchronous provider, call the client and complete inside the driver's own transaction, wrapped in withTxHooks, and pass its txHooks in the context. With an async provider, transactions you pass to complete or to client methods must be serialized with the worker's own statements the way the provider's withTransaction is, for example by holding its AsyncRwLock write lock. They also supply the txHooks field the provider's context requires, created with withTxHooks around the transaction. The MikroORM provider takes them from MikroORM's transaction events and its single connection serializes writers, so a plain em.transactional needs neither:

src/index.ts

const chain = withTxHooks((txHooks) =>
  db.transaction(() => {
    const user = db
      .prepare("INSERT INTO users (name, email) VALUES (?, ?) RETURNING id")
      .get("Alice", "alice@example.com") as { id: number };

    return client.createChain({
      db,
      txHooks,
      typeName: "send_welcome_email",
      input: { userId: user.id },
    });
  })(),
);

AsyncRwLock

Async providers add an application-level AsyncRwLock to prevent concurrent write access from async code within the same process while allowing reads to run in parallel. The lock is writer-preference and FIFO to prevent writer starvation: once a writer is queued, new readers wait.

  • Outside a transaction: every SQL execution acquires the lock in the mode indicated by readOnly.
  • Inside a transaction: the write lock was already acquired by whoever opened the transaction (withTransaction, or your own code as above), so individual operations skip it.

Custom SqliteStateProvider implementations must serialize writes: use createAsyncRwLock(), or rely on a single-connection pool that holds its one connection for the whole transaction, as the Kysely, Knex and MikroORM examples do.

Write fence

Every insert, update or delete of a job, a blocker or a worker references an empty {prefix}schema_fence_v1 table, the write fence of the adapter's schema version. A breaking migration drops the fence tables of older versions, so code older than the schema fails each write with SchemaFencedError and changes nothing, instead of corrupting state. Reads still work, and older workers report the error through workerError and keep retrying. Stop them once you have deployed the newer version. Processes on 0.17 reference no fence, so stop them before the first breaking migration.

Schema

The adapter creates its schema via migrateToLatest(). All table names use a configurable prefix (default: queuert_, shown as {prefix}). The {prefix}write_lock table is always empty: every migration transaction starts with a no-op write to it, which takes SQLite's write lock before the transaction reads anything, whatever BEGIN the provider sends. Reads that lock chains, jobs or workers inside a transaction make the same no-op write first.

A database migrated by 0.17 is carried onto the schema by adding the {prefix}schema_fence_v1 table and recording 001_schema_v1 next to the 0.17 migration records, without touching job or worker rows, so 0.17 processes keep running while migrateToLatest() runs, and a 0.17 process that restarts afterwards finds no migration left to apply. A 0.16 database is refused: upgrade it with @queuert/sqlite 0.17 first.

The migrator behind migrateToLatest() is exported for your own tables too; see Application Migrations.

migration — Tracks applied schema migrations.

{prefix}migration

  • nameTEXTMigration identifier (e.g., 001_schema_v1)
  • applied_atTEXTISO 8601 timestamp — when the migration was applied

job — Stores all job state.

{prefix}job

  • idpkjobIdType (default: TEXT)Job identifier
  • type_nameTEXTJob type identifier — on a head row, also the chain's type
  • chain_idsame as idThe chain's head job id
  • chain_indexINTEGERPosition in chain (0 for the head)
  • continued_to_idsame as idThe next job's id in the chain
  • inputTEXTJob input as JSON string
  • outputTEXTCompletion output as JSON string
  • statusTEXTblocked, pending, running or completed (CHECK-constrained)
  • created_atTEXTISO 8601 timestamp
  • scheduled_atTEXTISO 8601 timestamp
  • completed_atTEXTISO 8601 timestamp
  • completed_byTEXTWorker ID
  • attemptINTEGERAttempt count
  • last_attempt_atTEXTISO 8601 timestamp
  • last_attempt_errorTEXTError as JSON string
  • attempt_atTEXTISO 8601 timestamp — set when a worker starts an attempt
  • attempt_byTEXTWorker ID holding the attempt
  • attempt_tokenTEXTToken of the current attempt
  • trace_contextTEXTW3C traceparent
  • chain_statushead rows onlyTEXTrunning or completed (CHECK-constrained)
  • chain_completed_athead rows onlyTEXTISO 8601 timestamp — the chain's completion
  • chain_deduplication_keyhead rows onlyTEXTDeduplication key
  • chain_trace_contexthead rows onlyTEXTW3C traceparent for the chain

job_blocker — Tracks dependencies between jobs and chains.

{prefix}job_blocker

  • job_idpksame as idThe blocked job
  • blocked_by_chain_idpksame as idHead job ID of the blocker chain
  • indexpkINTEGERPosition in blockers array
  • trace_contextTEXTW3C traceparent

worker — Holds one row per worker, each with a lease.

{prefix}worker

  • started_atTEXTISO 8601 timestamp — when the worker started
  • lease_untilTEXTISO 8601 timestamp — the worker is live while it is in the future
  • tokenTEXTIdentifies this registration, new on every insert
  • concurrencyINTEGERMost attempts the worker runs at once
  • idpkworkerIdType (default: TEXT)Worker ID, the worker runner's workerId
  • type_namesTEXTJSON array of the job types the worker processes

write_lock — Always empty; a no-op write to it takes SQLite's write lock at the start of every migration transaction and before every read that locks chains, jobs or workers.

{prefix}write_lock

  • idpkINTEGERNever written

schema_fence_v<N> — Always empty; one per schema version N whose code may write. Every insert or update of a job, blocker or worker references its version's table, and a breaking migration drops the older ones.

{prefix}schema_fence_v<N>

  • idpkINTEGERNever written

Indexes

{prefix}job

  • chain_deduplication_idxDeduplication lookup(chain_deduplication_key, created_at DESC) WHERE chain_deduplication_key IS NOT NULL AND chain_index = 0
  • chain_index_idxContinuation position uniquenessUNIQUE (chain_id, chain_index) WHERE chain_index > 0
  • job_idxAll jobs by type and time(type_name, created_at)
  • job_pending_idxJob acquisition and pending job listing(type_name, scheduled_at) WHERE status = 'pending'
  • job_blocked_idxBlocked job listing(type_name, scheduled_at) WHERE status = 'blocked'
  • job_running_idxRunning job listing(type_name, attempt_at) WHERE status = 'running'
  • job_attempt_by_idxWorker job listing and running job counts(attempt_by, attempt_at) WHERE status = 'running'
  • job_completed_idxCompleted job listing(type_name, completed_at) WHERE status = 'completed'
  • chain_idxChains by type and time(type_name, created_at) WHERE chain_index = 0
  • chain_running_idxRunning chain listing(type_name, created_at) WHERE chain_index = 0 AND chain_status = 'running'
  • chain_completed_idxCompleted chain listing(type_name, chain_completed_at) WHERE chain_index = 0 AND chain_status = 'completed'

{prefix}job_blocker

  • job_blocker_chain_idxBlocker resolution(blocked_by_chain_id)

{prefix}worker

  • worker_idxWorker listing(started_at)

Examples

@queuert/sqlite

  • GitHub
    SQLite state storage via @queuert/sqlite with the better-sqlite3 driver through a synchronous SqliteStateProvider — job creation and completion inside better-sqlite3's own db.transaction, with no lock.
    $ pnpm --filter example-sqlite-state-better-sqlite3 startsrc/provider.tssrc/index.tssrc/migrations.ts
  • GitHub
    SQLite state storage via @queuert/sqlite using Node's built-in node:sqlite module through a synchronous SqliteStateProvider — no external SQLite dependencies, and job creation and completion inside a BEGIN/COMMIT transaction with no lock.
    $ pnpm --filter example-sqlite-state-node-sqlite startsrc/provider.tssrc/index.tssrc/migrations.ts
  • GitHub
    SQLite state storage via @queuert/sqlite using Bun's built-in bun:sqlite module through a synchronous SqliteStateProvider — no external SQLite dependencies, and job creation and completion inside bun:sqlite's db.transaction with no lock.
    $ pnpm --filter example-sqlite-state-bun-sqlite startsrc/provider.tssrc/index.tssrc/migrations.ts
  • GitHub
    SQLite state storage via @queuert/sqlite using Bun's built-in SQL client in its sqlite:// mode — no external SQLite dependencies. The handler holds the provider's createAsyncRwLock() write lock around the transaction it passes to complete.
    $ pnpm --filter example-sqlite-state-bun-sql startsrc/provider.tssrc/index.tssrc/migrations.ts
  • GitHub
    SQLite state storage via @queuert/sqlite with Drizzle ORM through a synchronous SqliteStateProvider — job creation and completion inside Drizzle's synchronous transaction over better-sqlite3, with no lock.
    $ pnpm --filter example-sqlite-state-drizzle startsrc/provider.tssrc/index.tssrc/migrations.ts
  • GitHub
    SQLite state storage via @queuert/sqlite with Kysely — job creation and completion inside application transactions. Kysely's better-sqlite3 dialect uses a size-1 connection pool that already serializes writers, so no external lock is needed, for client calls or for the transaction a handler passes to complete.
    $ pnpm --filter example-sqlite-state-kysely startsrc/provider.tssrc/index.tssrc/migrations.ts
  • GitHub
    SQLite state storage via @queuert/sqlite with Prisma ORM — job creation and completion inside application transactions, with createAsyncRwLock() for write serialization: the handler holds the same lock around the transaction it passes to complete.
    $ pnpm --filter example-sqlite-state-prisma startsrc/provider.tssrc/index.tssrc/migrations.tsprisma/schema.prisma
  • GitHub
    SQLite state storage via @queuert/sqlite with Knex — job creation and completion inside application transactions. Knex's better-sqlite3 dialect uses a size-1 connection pool that already serializes writers, so no external lock is needed, for client calls or for the transaction a handler passes to complete.
    $ pnpm --filter example-sqlite-state-knex startsrc/provider.tssrc/index.tssrc/migrations.ts
  • GitHub
    SQLite state storage via @queuert/sqlite with MikroORM — commit and rollback callbacks from MikroORM's transaction events, so calls take `{ em }` alone. MikroORM's SQLite driver holds its one better-sqlite3 connection for the whole transaction, so no external lock is needed.
    $ pnpm --filter example-sqlite-state-mikro-orm startsrc/provider.tssrc/index.ts
  • sqlite/multi-worker multiple workers on SQLite
    GitHub
    Three worker processes (forked from the main process) share one SQLite database file in WAL mode and compete for jobs, waking each other through the IPC notify adapter — the main process creates orders and their chains in one better-sqlite3 transaction. Horizontal Scaling guide →
    $ pnpm --filter example-sqlite-multi-worker startsrc/index.tssrc/worker.ts

The API reference for every export is in @queuert/sqlite.

esc

Search guides, integrations and every exported symbol.

↑ ↓ to navigate↵ to open