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/sqliteRequires 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();src/provider.ts
import type { DatabaseSync, SQLInputValue, StatementSync } from "node:sqlite";
import type { SqliteStateProvider } from "@queuert/sqlite";
import { type TxHooks, withTxHooks } from "queuert";
export type NodeSqliteContext = { db: DatabaseSync; txHooks: TxHooks };
const requireTxHooks = (txCtx: Partial<NodeSqliteContext>): 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 createNodeSqliteStateProvider = ({
db,
}: {
db: DatabaseSync;
}): SqliteStateProvider<NodeSqliteContext, "sync"> => {
const stmtCache = new Map<string, StatementSync>();
const prepareCached = (id: string, sql: string): StatementSync => {
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.exec("BEGIN");
try {
const result = fn({ db, txHooks });
if (result instanceof Promise) {
throw new TypeError("withTransaction callback must not return a promise");
}
db.exec("COMMIT");
return result;
} catch (error) {
if (db.isTransaction) db.exec("ROLLBACK");
throw error;
}
}),
isTransaction: (txCtx) => txCtx.db.isTransaction,
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 }) => {
if (!txCtx && db.isTransaction) {
throw new Error("executeSql without txCtx ran inside an open transaction");
}
const statement = id !== undefined ? prepareCached(id, sql) : db.prepare(sql);
return statement.all(...(params as SQLInputValue[]));
},
close: () => {
stmtCache.clear();
},
};
};src/index.ts
const stateProvider = createNodeSqliteStateProvider({ db });
using stateAdapter = createSqliteStateAdapter({ stateProvider });
stateAdapter.migrateToLatest();src/provider.ts
import type { Database, SQLQueryBindings } from "bun:sqlite";
import type { SqliteStateProvider } from "@queuert/sqlite";
import { type TxHooks, withTxHooks } from "queuert";
export type BunSqliteContext = { db: Database; txHooks: TxHooks };
const requireTxHooks = (txCtx: Partial<BunSqliteContext>): 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 createBunSqliteStateProvider = ({
db,
}: {
db: Database;
}): SqliteStateProvider<BunSqliteContext, "sync"> => {
return {
sync: true,
withTransaction: (fn) =>
withTxHooks((txHooks) =>
db.transaction(() => {
const result = fn({ db, txHooks });
if (result instanceof Promise) {
throw new TypeError("withTransaction callback must not return a promise");
}
return result;
})(),
),
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 }) => {
if (!txCtx && db.inTransaction) {
throw new Error("executeSql without txCtx ran inside an open transaction");
}
const statement =
id !== undefined
? db.query<unknown, SQLQueryBindings[]>(sql)
: db.prepare<unknown, SQLQueryBindings[]>(sql);
return statement.all(...(params as SQLQueryBindings[]));
},
};
};src/index.ts
const stateProvider = createBunSqliteStateProvider({ db });
using stateAdapter = createSqliteStateAdapter({ stateProvider });
stateAdapter.migrateToLatest();src/provider.ts
import type { AsyncRwLock, SqliteStateProvider } from "@queuert/sqlite";
import type { SQL, TransactionSQL } from "bun";
import { type TxHooks, withTxHooks } from "queuert";
export type BunSqlSqliteContext = { txSql: TransactionSQL; txHooks: TxHooks };
export const createBunSqlSqliteStateProvider = ({
sql,
lock,
}: {
sql: SQL;
lock: AsyncRwLock;
}): SqliteStateProvider<BunSqlSqliteContext> => {
return {
// Bun.SQL runs SQLite on a single connection and does not queue transactions on it,
// so serialize them with the lock.
withTransaction: async (fn) =>
withTxHooks(async (txHooks) => {
using _h = await lock.acquireWrite();
return await sql.begin(async (txSql) => fn({ txSql, txHooks }));
}),
isTransaction: (txCtx) => typeof txCtx.txSql.savepoint === "function",
onTransactionCommit: (txCtx, callback) => {
txCtx.txHooks.onCommit(callback);
},
onTransactionRollback: (txCtx, callback) => {
txCtx.txHooks.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: async ({ txCtx, sql: query, params, readOnly }) => {
if (txCtx) return txCtx.txSql.unsafe(query, params ?? []);
using _h = readOnly ? await lock.acquireRead() : await lock.acquireWrite();
return await sql.unsafe(query, params ?? []);
},
};
};src/index.ts
const lock = createAsyncRwLock();
const stateProvider = createBunSqlSqliteStateProvider({ sql, lock });
await using stateAdapter = createSqliteStateAdapter({ stateProvider });
await stateAdapter.migrateToLatest();src/provider.ts
import type { SqliteStateProvider } from "@queuert/sqlite";
import type Database from "better-sqlite3";
import { type TxHooks, withTxHooks } from "queuert";
export type DrizzleSqliteContext = { db: Database.Database; txHooks: TxHooks };
const requireTxHooks = (txCtx: Partial<DrizzleSqliteContext>): 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 createDrizzleSqliteStateProvider = ({
db,
}: {
db: Database.Database;
}): SqliteStateProvider<DrizzleSqliteContext, "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 = createDrizzleSqliteStateProvider({ db: sqlite });
using stateAdapter = createSqliteStateAdapter({ stateProvider });
stateAdapter.migrateToLatest();src/provider.ts
import type { SqliteStateProvider } from "@queuert/sqlite";
import { CompiledQuery, type Kysely } from "kysely";
import { type TxHooks, withTxHooks } from "queuert";
export type KyselySqliteContext<TDatabase> = { db: Kysely<TDatabase>; txHooks: TxHooks };
// Kysely's dialect for better-sqlite3 uses a pool of size 1 and holds that
// connection for the duration of `db.transaction().execute()`. Non-tx queries
// block on `acquireConnection` until the tx releases it, so no extra lock is
// needed to serialize writers.
export const createKyselySqliteStateProvider = <TDatabase>({
db,
}: {
db: Kysely<TDatabase>;
}): SqliteStateProvider<KyselySqliteContext<TDatabase>> => {
return {
withTransaction: async (cb) =>
withTxHooks(async (txHooks) =>
db.transaction().execute(async (txDb) => cb({ db: txDb, txHooks })),
),
isTransaction: (txCtx) => txCtx.db.isTransaction,
onTransactionCommit: (txCtx, callback) => {
txCtx.txHooks.onCommit(callback);
},
onTransactionRollback: (txCtx, callback) => {
txCtx.txHooks.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 };
},
// `id` not forwarded: Kysely's better-sqlite3 dialect re-prepares every raw
// query; no hook to cache through the dialect. Bypass Kysely for statement
// caching (see sqlite/state-better-sqlite3).
executeSql: async ({ txCtx, sql, params }) => {
const database = txCtx?.db ?? db;
const result = await database.executeQuery(CompiledQuery.raw(sql, params));
return result.rows;
},
};
};src/index.ts
const stateProvider = createKyselySqliteStateProvider({ db });
await using stateAdapter = createSqliteStateAdapter({
stateProvider,
});
await stateAdapter.migrateToLatest();src/provider.ts
import type { AsyncRwLock, SqliteStateProvider } from "@queuert/sqlite";
import { type TxHooks, withTxHooks } from "queuert";
type PrismaDriverAdapterError = {
meta?: { driverAdapterError?: { cause?: { kind?: unknown; table?: unknown } } };
};
export type PrismaLikeClient = {
$transaction: <T>(fn: (prisma: any) => Promise<T>) => Promise<T>;
$queryRawUnsafe: (sql: string, ...params: unknown[]) => Promise<unknown[]>;
};
export type PrismaSqliteContext<TPrisma> = { prisma: TPrisma; txHooks: TxHooks };
export const createPrismaSqliteStateProvider = <TPrisma extends PrismaLikeClient>({
prisma,
lock,
}: {
prisma: TPrisma;
lock: AsyncRwLock;
}): SqliteStateProvider<PrismaSqliteContext<TPrisma>> => {
return {
withTransaction: async (cb) =>
withTxHooks(async (txHooks) => {
using _h = await lock.acquireWrite();
return await prisma.$transaction(async (prisma: TPrisma) => cb({ prisma, txHooks }));
}),
isTransaction: (txCtx) => !("$connect" in txCtx.prisma),
onTransactionCommit: (txCtx, callback) => {
txCtx.txHooks.onCommit(callback);
},
onTransactionRollback: (txCtx, callback) => {
txCtx.txHooks.onRollback(callback);
},
// Prisma wraps a raw query's driver error and reports a missing table on its cause.
classifyError: ({ error }) => {
const cause = (error as PrismaDriverAdapterError | undefined)?.meta?.driverAdapterError
?.cause;
return cause?.kind === "TableDoesNotExist" && typeof cause.table === "string"
? { kind: "undefined_table", tableName: cause.table }
: undefined;
},
// `id` not forwarded: Prisma's engine caches plans by SQL text per connection.
executeSql: async ({ txCtx, sql, params, columnTypes, readOnly }) => {
const runQuery = async (): Promise<unknown[]> => {
const prismaClient = (txCtx?.prisma ?? prisma) as PrismaLikeClient;
return prismaClient.$queryRawUnsafe(sql, ...params);
};
let result: unknown[];
if (txCtx) {
result = await runQuery();
} else {
using _h = readOnly ? await lock.acquireRead() : await lock.acquireWrite();
result = await runQuery();
}
// Prisma returns BigInt for SQLite INTEGER columns; narrow back to number
// only for columns declared as numeric so string/json values pass through.
const numericColumns = Object.entries(columnTypes)
.filter(([, type]) => type === "number" || type === "number?")
.map(([name]) => name);
if (numericColumns.length === 0) return result;
return result.map((row) => {
if (!row || typeof row !== "object") return row;
const r = row as Record<string, unknown>;
for (const col of numericColumns) {
if (typeof r[col] === "bigint") r[col] = Number(r[col]);
}
return r;
});
},
};
};src/index.ts
const lock = createAsyncRwLock();
const stateProvider = createPrismaSqliteStateProvider({ prisma: prisma as any, lock });
await using stateAdapter = createSqliteStateAdapter({
stateProvider,
});
await stateAdapter.migrateToLatest();src/provider.ts
import type { SqliteStateProvider } from "@queuert/sqlite";
import type { Knex } from "knex";
import { type TxHooks, withTxHooks } from "queuert";
export type KnexSqliteContext = { trx: Knex.Transaction; txHooks: TxHooks };
// Knex's sqlite dialects default to a pool of size 1 and hold that connection
// for the duration of `knex.transaction()`. Non-tx queries block on connection
// acquisition until the tx releases it, so no extra lock is needed to
// serialize writers.
export const createKnexSqliteStateProvider = ({
knex,
}: {
knex: Knex;
}): SqliteStateProvider<KnexSqliteContext> => {
return {
withTransaction: async (cb) =>
withTxHooks(async (txHooks) =>
knex.transaction(async (trx) => cb({ trx, txHooks }), { doNotRejectOnRollback: false }),
),
isTransaction: (txCtx) => txCtx.trx.isTransaction === true,
onTransactionCommit: (txCtx, callback) => {
txCtx.txHooks.onCommit(callback);
},
onTransactionRollback: (txCtx, callback) => {
txCtx.txHooks.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 };
},
// `id` not forwarded: knex re-prepares every raw query and exposes no hook
// to cache statement handles through the dialect. Bypass knex for statement
// caching (see sqlite/state-better-sqlite3).
executeSql: async ({ txCtx, sql, params }) => {
// The better-sqlite3 dialect returns rows for read statements and a
// `{ changes, lastInsertRowid }` summary for writes.
const result: unknown = await (txCtx?.trx ?? knex).raw(sql, params as Knex.RawBinding[]);
return Array.isArray(result) ? result : [];
},
};
};src/index.ts
const stateProvider = createKnexSqliteStateProvider({ knex });
await using stateAdapter = createSqliteStateAdapter({
stateProvider,
});
await stateAdapter.migrateToLatest();src/provider.ts
import type { EntityManager, MikroORM, Transaction } from "@mikro-orm/sqlite";
import type { SqliteStateProvider } from "@queuert/sqlite";
import { CompiledQuery, type Kysely } from "kysely";
import { type TxHooks, createTxHooks } from "queuert";
export type MikroOrmSqliteContext = { em: EntityManager };
export const createMikroOrmSqliteStateProvider = ({
orm,
}: {
orm: MikroORM;
}): SqliteStateProvider<MikroOrmSqliteContext> => {
const settlers = new WeakMap<
Transaction,
{ txHooks: TxHooks; commit: () => Promise<void> | void; rollback: () => void }
>();
const parents = new WeakMap<object>();
const takeSettler = (transaction: Transaction) => {
const settler = settlers.get(transaction);
settlers.delete(transaction);
return settler;
};
orm.em.getEventManager().registerSubscriber({
beforeTransactionStart: ({ em, transaction }) => {
if (transaction === undefined) parents.delete(em);
else parents.set(em, transaction);
},
afterTransactionStart: ({ em, transaction }) => {
if (transaction === undefined) return;
const parent = parents.get(em);
parents.delete(em);
const parentSettler = parent === undefined ? undefined : settlers.get(parent);
if (parentSettler === undefined) {
const { txHooks, flush, discard } = createTxHooks();
settlers.set(transaction, { txHooks, commit: flush, rollback: discard });
return;
}
const savepoint = parentSettler.txHooks.createSavepoint();
settlers.set(transaction, {
txHooks: savepoint.txHooks,
commit: () => {
savepoint.release();
},
rollback: () => {
savepoint.rollback();
},
});
},
afterTransactionCommit: async ({ transaction }) => {
if (transaction === undefined) return;
await takeSettler(transaction)?.commit();
},
beforeTransactionRollback: ({ transaction }) => {
if (transaction === undefined) return;
takeSettler(transaction)?.rollback();
},
});
const getTxHooks = (txCtx: MikroOrmSqliteContext): TxHooks => {
const transaction = txCtx.em.getTransactionContext();
const settler = transaction === undefined ? undefined : settlers.get(transaction);
if (settler === undefined) {
throw new Error("The context is not in a transaction opened through MikroORM");
}
return settler.txHooks;
};
return {
withTransaction: async (fn) => {
let transaction: Transaction = undefined;
try {
return await orm.em.transactional(async (em) => {
transaction = em.getTransactionContext();
return fn({ em });
});
} catch (error) {
if (transaction !== undefined) takeSettler(transaction)?.rollback();
throw error;
}
},
isTransaction: (txCtx) => txCtx.em.isInTransaction(),
onTransactionCommit: (txCtx, callback) => {
getTxHooks(txCtx).onCommit(callback);
},
onTransactionRollback: (txCtx, callback) => {
getTxHooks(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: async ({ txCtx, sql, params }) => {
const transaction = txCtx?.em.getTransactionContext<Kysely<unknown>>();
if (txCtx !== undefined && transaction === undefined) {
throw new Error("Provided context is not in a transaction");
}
const client = transaction ?? orm.em.getConnection().getClient<unknown>();
const result = await client.executeQuery(CompiledQuery.raw(sql, params));
return result.rows;
},
};
};skipTables keeps MikroORM's schema diff from dropping Queuert's tables, which migrateToLatest() manages:
src/index.ts
await using orm = await MikroORM.init({
entities: [User],
dbName: ":memory:",
schemaGenerator: { skipTables: [/^queuert_/] },
});src/index.ts
const stateProvider = createMikroOrmSqliteStateProvider({ orm });
await using stateAdapter = createSqliteStateAdapter({ stateProvider });
await stateAdapter.migrateToLatest();Configuration
createSqliteStateAdapter
- stateProvider requiredThe
SqliteStateProviderfor your client; see State provider. - tablePrefix "queuert_"Prefix of every table name.
- jobIdType "TEXT"Type of the job id columns.
- workerIdType "TEXT"Type of the worker id column.
- The most blocker chains a single job may declare. Declaring more throws
BlockerLimitExceededError; see Job Blockers. - countCap 10_000The per-status count at which
countChainsByTypeNameandcountJobsByTypeName(and the dashboard's type counts) stop counting and reporthasMore. A higher cap means a longer index scan per count. - generateJobId crypto.randomUUIDCalled for every job created without a caller-supplied
id. - validateJobId accept allRuns on every caller-supplied and generated ID before writing, throwing
InvalidJobIdErroron failure. - generateWorkerId crypto.randomUUIDCalled once for each worker runner created without a
workerId; must return a new id on every call. - validateWorkerId accept allRuns on the configured or generated worker id;
createWorkerRunnerthrowsInvalidWorkerIdErroron 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
trueon a synchronous provider, whose members return their results directly (see Synchronous providers); absent orfalseon an asynchronous one. - Manages connection acquisition and transaction lifecycle. The callback receives a transaction context representing an active transaction. A provider that carries
txHooksin its context creates them here withwithTxHooks, 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
txHooksin 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.
readOnlymarks 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 ano such table: <table>error, withtableNameread from the message (wrappers such as Prisma report it in their own error fields), andundefinedfor 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 intoSchemaFencedError. - close optionalOnly 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);
});
}
});src/provider.spec.ts
void describe("sqlite/state-node-sqlite provider conformance", () => {
const db = new DatabaseSync(":memory:");
let context: SqliteStateProviderConformanceContext;
before(() => {
context = createSqliteStateProviderConformanceContext({
createStateProvider: () => createNodeSqliteStateProvider({ db }),
withNonTransactionContext: (fn) => fn({ db }),
});
});
after(() => {
db.close();
});
for (const conformanceCase of sqliteStateProviderConformanceCases) {
void test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("sqlite/state-bun-sqlite provider conformance", () => {
const db = new Database(":memory:");
let context: SqliteStateProviderConformanceContext;
beforeAll(() => {
context = createSqliteStateProviderConformanceContext({
createStateProvider: () => createBunSqliteStateProvider({ db }),
withNonTransactionContext: (fn) => fn({ db }),
});
});
afterAll(() => {
db.close();
});
for (const conformanceCase of sqliteStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("sqlite/state-bun-sql provider conformance", () => {
const sql = new SQL("sqlite://:memory:");
const lock = createAsyncRwLock();
let context: SqliteStateProviderConformanceContext;
beforeAll(async () => {
context = await createSqliteStateProviderConformanceContext({
createStateProvider: async () => createBunSqlSqliteStateProvider({ sql, lock }),
withNonTransactionContext: async (fn) => fn({ txSql: sql }),
});
});
afterAll(async () => {
await sql.close();
});
for (const conformanceCase of [
...sqliteStateProviderConformanceCases,
...asyncSqliteStateProviderConformanceCases,
]) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("sqlite/state-drizzle provider conformance", () => {
const db = new Database(":memory:");
let context: SqliteStateProviderConformanceContext;
beforeAll(() => {
context = createSqliteStateProviderConformanceContext({
createStateProvider: () => createDrizzleSqliteStateProvider({ db }),
withNonTransactionContext: (fn) => fn({ db }),
});
});
afterAll(async () => {
db.close();
});
for (const conformanceCase of sqliteStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("sqlite/state-kysely provider conformance", () => {
const db = new Kysely<Database>({
dialect: new SqliteDialect({ database: new BetterSqlite3(":memory:") }),
});
let context: SqliteStateProviderConformanceContext;
beforeAll(async () => {
context = await createSqliteStateProviderConformanceContext({
createStateProvider: async () => createKyselySqliteStateProvider({ db }),
withNonTransactionContext: async (fn) => fn({ db }),
});
});
afterAll(async () => {
await db.destroy();
});
for (const conformanceCase of [
...sqliteStateProviderConformanceCases,
...asyncSqliteStateProviderConformanceCases,
]) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("sqlite/state-prisma provider conformance", () => {
const tempDir = mkdtempSync(join(tmpdir(), "queuert-sqlite-prisma-spec-"));
let prisma: PrismaClient;
const lock = createAsyncRwLock();
let context: SqliteStateProviderConformanceContext;
beforeAll(async () => {
const dbPath = join(tempDir, "test.db");
process.env.DATABASE_URL = `file:${dbPath}`;
execSync("npx prisma db push", { stdio: "inherit", cwd: EXAMPLE_DIR });
const { PrismaClient } = await import("../prisma/generated/prisma/client.ts");
const prismaAdapter = new PrismaBetterSqlite3({ url: `file:${dbPath}` });
prisma = new PrismaClient({ adapter: prismaAdapter });
context = await createSqliteStateProviderConformanceContext({
createStateProvider: async () => createPrismaSqliteStateProvider({ prisma, lock }),
withNonTransactionContext: async (fn) => fn({ prisma }),
});
}, 60_000);
afterAll(async () => {
await prisma.$disconnect();
rmSync(tempDir, { recursive: true, force: true });
});
for (const conformanceCase of [
...sqliteStateProviderConformanceCases,
...asyncSqliteStateProviderConformanceCases,
]) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("sqlite/state-knex provider conformance", () => {
const knex = knexFactory({
client: "better-sqlite3",
connection: { filename: ":memory:" },
useNullAsDefault: true,
});
let context: SqliteStateProviderConformanceContext;
beforeAll(async () => {
context = await createSqliteStateProviderConformanceContext({
createStateProvider: async () => createKnexSqliteStateProvider({ knex }),
withNonTransactionContext: async (fn) => fn({ trx: knex }),
});
});
afterAll(async () => {
await knex.destroy();
});
for (const conformanceCase of [
...sqliteStateProviderConformanceCases,
...asyncSqliteStateProviderConformanceCases,
]) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("sqlite/state-mikro-orm provider conformance", () => {
let context: SqliteStateProviderConformanceContext;
beforeAll(async () => {
context = await createSqliteStateProviderConformanceContext({
createStateProvider: async () => stateProvider,
withNonTransactionContext: async (fn) => fn({ em: orm.em.fork() }),
withSavepoint: async (txCtx, fn) =>
(txCtx as MikroOrmSqliteContext).em.transactional(async (em) => fn({ em })),
});
});
for (const conformanceCase of [
...sqliteStateProviderConformanceCases,
...asyncSqliteStateProviderConformanceCases,
]) {
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 },
});
})(),
);src/index.ts
const chain = withTxHooks((txHooks) => {
db.exec("BEGIN");
try {
const insertStmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?) RETURNING id");
const user = insertStmt.get("Alice", "alice@example.com") as { id: number };
// Queue welcome email - if user creation fails, no email job is created
const result = client.createChain({
db,
txHooks,
typeName: "send_welcome_email",
input: { userId: user.id },
});
db.exec("COMMIT");
return result;
} catch (error) {
if (db.isTransaction) db.exec("ROLLBACK");
throw error;
}
});src/index.ts
const chain = withTxHooks((txHooks) =>
db.transaction(() => {
const user = db
.query<{ id: number }, [string, string]>(
"INSERT INTO users (name, email) VALUES (?, ?) RETURNING id",
)
.get("Alice", "alice@example.com");
if (!user) throw new Error("Failed to insert user");
return client.createChain({
db,
txHooks,
typeName: "send_welcome_email",
input: { userId: user.id },
});
})(),
);src/index.ts
const chain = await withTxHooks(async (txHooks) => {
using _h = await lock.acquireWrite();
return await sql.begin(async (txSql) => {
const [user]: { id: number }[] = await txSql`
INSERT INTO users (name, email) VALUES (${"Alice"}, ${"alice@example.com"}) RETURNING id
`;
return client.createChain({
txSql,
txHooks,
typeName: "send_welcome_email",
input: { userId: user.id },
});
});
});src/index.ts
const chain = withTxHooks((txHooks) =>
db.transaction((tx) => {
// Use Drizzle to insert the user
const [user] = tx
.insert(users)
.values({ name: "Alice", email: "alice@example.com" })
.returning({ id: users.id })
.all();
// Queue welcome email - if user creation fails, no email job is created
return client.createChain({
db: sqlite,
txHooks,
typeName: "send_welcome_email",
input: { userId: user.id },
});
}),
);src/index.ts
const chain = await withTxHooks(async (txHooks) =>
db.transaction().execute(async (txDb) => {
const user = await txDb
.insertInto("users")
.values({ name: "Alice", email: "alice@example.com" })
.returning("id")
.executeTakeFirstOrThrow();
// Queue welcome email - if user creation fails, no email job is created
return client.createChain({
db: txDb,
txHooks,
typeName: "send_welcome_email",
input: { userId: user.id },
});
}),
);src/index.ts
const chain = await withTxHooks(async (txHooks) => {
using _h = await lock.acquireWrite();
return await prisma.$transaction(async (prisma) => {
const user = await prisma.user.create({
data: { name: "Alice", email: "alice@example.com" },
});
// Queue welcome email - if user creation fails, no email job is created
return client.createChain({
prisma,
txHooks,
typeName: "send_welcome_email",
input: { userId: user.id },
});
});
});src/index.ts
const chain = await withTxHooks(async (txHooks) =>
knex.transaction(
async (trx) => {
const [user] = await trx<{ id: number; name: string; email: string }>("users")
.insert({ name: "Alice", email: "alice@example.com" })
.returning(["id"]);
// Queue welcome email - if user creation fails, no email job is created
return client.createChain({
trx,
txHooks,
typeName: "send_welcome_email",
input: { userId: user.id },
});
},
{ doNotRejectOnRollback: false },
),
);src/index.ts
const chain = await orm.em.transactional(async (em) => {
const user = em.create(User, { name: "Alice", email: "alice@example.com" });
await em.flush();
return client.createChain({
em,
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 uniqueness
UNIQUE (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
- sqlite/state-better-sqlite3 better-sqlite3GitHubSQLite 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 - sqlite/state-node-sqlite node:sqliteGitHubSQLite 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 - sqlite/state-drizzle Drizzle ORMGitHubSQLite 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 - sqlite/state-kysely KyselyGitHubSQLite 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 - sqlite/state-prisma PrismaGitHubSQLite 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 - sqlite/state-knex KnexGitHubSQLite 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 - sqlite/state-mikro-orm MikroORMGitHubSQLite 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 SQLiteGitHubThree 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.