PostgreSQL State Adapter
Store chains, jobs and workers in PostgreSQL with pg, postgres.js, Bun SQL, Drizzle, Kysely, Prisma, Knex or MikroORM.
@queuert/postgres supports horizontal scaling with database-level locking (FOR UPDATE SKIP LOCKED), writeable CTEs for atomic batch operations, and all Queuert features including concurrent multi-worker deployments that share one database; see Horizontal Scaling.
Clients
Install
Terminal
npm install @queuert/postgresRequires PostgreSQL 14 or later, which migrateToLatest() checks. The same package includes the PostgreSQL notify adapter (LISTEN/NOTIFY).
Setup
Each tab shows the example's PgStateProvider and the lines that create the adapter:
src/provider.ts
import type { PgStateProvider } from "@queuert/postgres";
import type { Pool, PoolClient } from "pg";
import { type TxHooks, withTxHooks } from "queuert";
export type PgPoolContext = { poolClient: PoolClient; txHooks: TxHooks };
export const createPgPoolStateProvider = ({
pool,
prepareStatements = true,
}: {
pool: Pool;
/**
* When true (default), queries that arrive with an `id` are sent as named
* prepared statements (`name = id`), letting pg cache the parsed plan per
* connection. The adapter folds template variants into `id`, so it uniquely
* identifies the resolved SQL. Set to `false` for transaction-mode poolers
* (PgBouncer, Supavisor) where named statements break across pooled sessions.
*/
prepareStatements?: boolean;
}): PgStateProvider<PgPoolContext> => {
const exec = async (
client: PoolClient,
id: string | undefined,
sql: string,
params: unknown[],
): Promise<unknown[]> => {
if (id !== undefined && prepareStatements) {
const result = await client.query({ name: id, text: sql, values: params });
return result.rows;
}
const result = await client.query(sql, params);
return result.rows;
};
return {
withTransaction: async (cb) =>
withTxHooks(async (txHooks) => {
const poolClient = await pool.connect();
try {
await poolClient.query("BEGIN ISOLATION LEVEL READ COMMITTED");
const result = await cb({ poolClient, txHooks });
await poolClient.query("COMMIT");
return result;
} catch (error) {
await poolClient.query("ROLLBACK").catch(() => {});
throw error;
} finally {
poolClient.release();
}
}),
isTransaction: (txCtx) => {
const status = txCtx.poolClient.getTransactionStatus();
return status === "T" || status === "E";
},
onTransactionCommit: (txCtx, callback) => {
txCtx.txHooks.onCommit(callback);
},
onTransactionRollback: (txCtx, callback) => {
txCtx.txHooks.onRollback(callback);
},
classifyError: ({ error }) => {
const driverError = error as { code?: unknown; message?: unknown } | undefined;
if (driverError?.code !== "42P01") return undefined;
const tableName = /relation "([^"]+)" does not exist/.exec(String(driverError.message))?.[1];
return tableName === undefined ? undefined : { kind: "undefined_table", tableName };
},
executeSql: async ({ txCtx, id, sql, params }) => {
if (txCtx) return exec(txCtx.poolClient, id, sql, params);
const poolClient = await pool.connect();
try {
return await exec(poolClient, id, sql, params);
} finally {
poolClient.release();
}
},
};
};src/index.ts
const stateProvider = createPgPoolStateProvider({ pool: db });
await using stateAdapter = createPgStateAdapter({
stateProvider,
});
await stateAdapter.migrateToLatest();src/provider.ts
import type { PgStateProvider, RuntimeType } from "@queuert/postgres";
import type postgres from "postgres";
import { type TxHooks, withTxHooks } from "queuert";
export type PostgresJsContext = { txSql: postgres.TransactionSql; txHooks: TxHooks };
const TEXT_OID = 25;
const UUID_OID = 2950;
const JSONB_OID = 3802;
const PG_OID: Partial<Record<RuntimeType, number>> = {
uuid: UUID_OID,
"uuid?": UUID_OID,
string: TEXT_OID,
"string?": TEXT_OID,
json: JSONB_OID,
"json?": JSONB_OID,
};
export const createPostgresJsStateProvider = ({
sql,
prepareStatements = true,
}: {
sql: postgres.Sql;
/**
* When true (default), queries that arrive with an `id` are sent with
* `{ prepare: true }` so postgres.js caches the parsed plan per connection.
* Set to `false` for transaction-mode connection poolers (PgBouncer < 1.21,
* Supavisor without per-client tracking) where server-side prepared statements
* break across pooled sessions.
*/
prepareStatements?: boolean;
}): PgStateProvider<PostgresJsContext> => {
const typed = sql.typed.bind(sql);
const pgArray = sql.array.bind(sql);
const serializeParam = (value: unknown, type: RuntimeType | undefined): unknown => {
if (value === undefined || value === null) return null;
if (type === "array") return pgArray(value as any[]);
if (type === "jsonArray") return pgArray((value as unknown[]).map((el) => JSON.stringify(el)));
if (type === "json" || type === "json?") return typed(value, JSONB_OID);
if (typeof value === "string") return typed(value, PG_OID[type!] ?? TEXT_OID);
return value;
};
return {
withTransaction: async (cb) =>
withTxHooks(async (txHooks) =>
sql.begin("isolation level read committed", async (txSql) => cb({ txSql, txHooks }) as any),
),
isTransaction: (txCtx) => typeof txCtx.txSql.savepoint === "function",
onTransactionCommit: (txCtx, callback) => {
txCtx.txHooks.onCommit(callback);
},
onTransactionRollback: (txCtx, callback) => {
txCtx.txHooks.onRollback(callback);
},
classifyError: ({ error }) => {
const driverError = error as { code?: unknown; message?: unknown } | undefined;
if (driverError?.code !== "42P01") return undefined;
const tableName = /relation "([^"]+)" does not exist/.exec(String(driverError.message))?.[1];
return tableName === undefined ? undefined : { kind: "undefined_table", tableName };
},
executeSql: async ({ txCtx, id, sql: query, params, paramTypes }) => {
const client = txCtx?.txSql ?? sql;
const prepare = prepareStatements && id !== undefined;
if (!params || params.length === 0) {
return client.unsafe(query, [], { prepare }) as any;
}
const serialized = params.map((value, i) => serializeParam(value, paramTypes[i]));
return client.unsafe(query, serialized as any[], { prepare }) as any;
},
};
};src/index.ts
const stateProvider = createPostgresJsStateProvider({ sql });
await using stateAdapter = createPgStateAdapter({ stateProvider });
await stateAdapter.migrateToLatest();src/provider.ts
import type { PgStateProvider, RuntimeType } from "@queuert/postgres";
import type { SQL, TransactionSQL } from "bun";
import { type TxHooks, withTxHooks } from "queuert";
export type BunSqlContext = { txSql: TransactionSQL; txHooks: TxHooks };
export const createBunSqlStateProvider = ({
sql,
}: {
sql: SQL;
}): PgStateProvider<BunSqlContext> => {
// Bun infers each parameter's type from its JS value and has no per-parameter OID override,
// so dates, JSON, and arrays are sent as text and the explicit casts in the adapter's SQL coerce them.
type ArrayElement = string | number | boolean | bigint | Date | null | undefined;
const toText = (value: ArrayElement): string | null => {
if (value === undefined || value === null) return null;
if (value instanceof Date) return value.toISOString();
return String(value);
};
// Bun's `sql.array()` encodes `null` elements as the string "null", so build the literal by hand.
const toArrayLiteral = (values: (string | null)[]): string =>
`{${values
.map((value) =>
value === null ? "NULL" : `"${value.replace(/\\/g, "\\\\").replace(/"/g, '\\"')}"`,
)
.join(",")}}`;
const serializeParam = (value: unknown, type: RuntimeType | undefined): unknown => {
if (value === undefined || value === null) return null;
if (type === "array") return toArrayLiteral((value as ArrayElement[]).map(toText));
if (type === "jsonArray")
return toArrayLiteral(
(value as unknown[]).map((el) => (el === undefined ? null : JSON.stringify(el))),
);
if (type === "json" || type === "json?") return JSON.stringify(value);
if (value instanceof Date) return value.toISOString();
return value;
};
return {
withTransaction: async (cb) =>
withTxHooks(async (txHooks) =>
sql.begin("isolation level read committed", async (txSql) => cb({ 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 driverError = error as { errno?: unknown; message?: unknown } | undefined;
if (driverError?.errno !== "42P01") return undefined;
const tableName = /relation "([^"]+)" does not exist/.exec(String(driverError.message))?.[1];
return tableName === undefined ? undefined : { kind: "undefined_table", tableName };
},
executeSql: async ({ txCtx, sql: query, params, paramTypes }) => {
const client = txCtx?.txSql ?? sql;
const serialized = (params ?? []).map((value, i) => serializeParam(value, paramTypes[i]));
return client.unsafe(query, serialized);
},
};
};src/index.ts
const stateProvider = createBunSqlStateProvider({ sql });
await using stateAdapter = createPgStateAdapter({ stateProvider });
await stateAdapter.migrateToLatest();src/provider.ts
import type { PgStateProvider } from "@queuert/postgres";
import type { ExtractTablesWithRelations } from "drizzle-orm";
import type { NodePgDatabase, NodePgQueryResultHKT } from "drizzle-orm/node-postgres";
import { PgTransaction } from "drizzle-orm/pg-core";
import { type TxHooks, withTxHooks } from "queuert";
export type DrizzlePgTransaction<TSchema extends Record<string, unknown>> = PgTransaction<
NodePgQueryResultHKT,
TSchema,
ExtractTablesWithRelations<TSchema>
>;
export type DrizzlePgContext<TSchema extends Record<string, unknown>> = {
tx: DrizzlePgTransaction<TSchema>;
txHooks: TxHooks;
};
export const createDrizzlePgStateProvider = <TSchema extends Record<string, unknown>>({
db,
prepareStatements = true,
}: {
db: NodePgDatabase<TSchema>;
/**
* When true (default), queries that arrive with an `id` are sent as named
* prepared statements via the underlying pg client (`name = id`). Set to
* `false` for transaction-mode poolers (PgBouncer, Supavisor).
*/
prepareStatements?: boolean;
}): PgStateProvider<DrizzlePgContext<TSchema>> => {
return {
withTransaction: async (cb) =>
withTxHooks(async (txHooks) =>
db.transaction(async (tx) => cb({ tx, txHooks }), { isolationLevel: "read committed" }),
),
isTransaction: (txCtx) => txCtx.tx instanceof PgTransaction,
onTransactionCommit: (txCtx, callback) => {
txCtx.txHooks.onCommit(callback);
},
onTransactionRollback: (txCtx, callback) => {
txCtx.txHooks.onRollback(callback);
},
classifyError: ({ error }) => {
type DriverError = { code?: unknown; message?: unknown; cause?: DriverError };
const driverError = [
error as DriverError | undefined,
(error as DriverError | undefined)?.cause,
].find((candidate) => candidate?.code === "42P01");
if (driverError === undefined) return undefined;
const tableName = /relation "([^"]+)" does not exist/.exec(String(driverError.message))?.[1];
return tableName === undefined ? undefined : { kind: "undefined_table", tableName };
},
executeSql: async ({ txCtx, id, sql, params }) => {
// Inside transaction: access Drizzle's internal pg client
// Outside transaction (migrations): use db.$client (the pool)
const client = txCtx ? (txCtx.tx as any).session.client : (db as any).$client;
if (id !== undefined && prepareStatements) {
const result = await client.query({ name: id, text: sql, values: params });
return result.rows;
}
const result = await client.query(sql, params);
return result.rows;
},
};
};src/index.ts
const stateProvider = createDrizzlePgStateProvider({ db });
await using stateAdapter = createPgStateAdapter({
stateProvider,
});
await stateAdapter.migrateToLatest();src/provider.ts
import type { PgStateProvider } from "@queuert/postgres";
import { CompiledQuery, type Kysely } from "kysely";
import { type TxHooks, withTxHooks } from "queuert";
export type KyselyPgContext<TDatabase> = { db: Kysely<TDatabase>; txHooks: TxHooks };
export const createKyselyPgStateProvider = <TDatabase>({
db,
}: {
db: Kysely<TDatabase>;
}): PgStateProvider<KyselyPgContext<TDatabase>> => {
return {
withTransaction: async (cb) =>
withTxHooks(async (txHooks) =>
db
.transaction()
.setIsolationLevel("read committed")
.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 driverError = error as { code?: unknown; message?: unknown } | undefined;
if (driverError?.code !== "42P01") return undefined;
const tableName = /relation "([^"]+)" does not exist/.exec(String(driverError.message))?.[1];
return tableName === undefined ? undefined : { kind: "undefined_table", tableName };
},
// `id` not forwarded: Kysely's CompiledQuery.raw has no name field; the pg
// dialect calls `client.query(sql, params)` unprepared. Bypass Kysely for
// server-side prepared statements (see postgresql/state-pg).
executeSql: async ({ txCtx, sql, params }) => {
if (txCtx && !txCtx.db.isTransaction) {
throw new Error("Provided context is not in a transaction");
}
const result = await (txCtx?.db ?? db).executeQuery(CompiledQuery.raw(sql, params));
return result.rows;
},
};
};src/index.ts
const stateProvider = createKyselyPgStateProvider({ db });
await using stateAdapter = createPgStateAdapter({
stateProvider,
});
await stateAdapter.migrateToLatest();src/provider.ts
import type { PgStateProvider } from "@queuert/postgres";
import { type TxHooks, withTxHooks } from "queuert";
// Prisma wraps a raw query's driver error; the SQLSTATE is `originalCode` on its cause.
type PrismaDriverAdapterError = {
meta?: {
driverAdapterError?: {
cause?: { originalCode?: unknown; originalMessage?: unknown; table?: unknown };
};
};
};
export type PrismaLikeClient = {
$transaction: <T>(
fn: (prisma: any) => Promise<T>,
options: { isolationLevel: "ReadCommitted" },
) => Promise<T>;
$queryRawUnsafe: (sql: string, ...params: unknown[]) => Promise<unknown[]>;
$executeRawUnsafe: (sql: string, ...params: unknown[]) => Promise<unknown>;
};
export type PrismaPgContext<TPrisma> = { prisma: TPrisma; txHooks: TxHooks };
export const createPrismaPgStateProvider = <TPrisma extends PrismaLikeClient>({
prisma,
}: {
prisma: TPrisma;
}): PgStateProvider<PrismaPgContext<TPrisma>> => {
return {
withTransaction: async (cb) =>
withTxHooks(async (txHooks) =>
prisma.$transaction(async (prisma: TPrisma) => cb({ prisma, txHooks }), {
isolationLevel: "ReadCommitted",
}),
),
// Prisma hides `$connect` on the interactive transaction client.
isTransaction: (txCtx) => !("$connect" in txCtx.prisma),
onTransactionCommit: (txCtx, callback) => {
txCtx.txHooks.onCommit(callback);
},
onTransactionRollback: (txCtx, callback) => {
txCtx.txHooks.onRollback(callback);
},
classifyError: ({ error }) => {
const cause = (error as PrismaDriverAdapterError | undefined)?.meta?.driverAdapterError
?.cause;
if (cause?.originalCode !== "42P01") return undefined;
const tableName =
typeof cause.table === "string"
? cause.table
: /relation "([^"]+)" does not exist/.exec(String(cause.originalMessage))?.[1];
return tableName === undefined ? undefined : { kind: "undefined_table", tableName };
},
// `id` not forwarded: Prisma's engine caches plans by SQL text per connection.
executeSql: async ({ txCtx, sql, params, columnTypes }) => {
const prismaClient = (txCtx?.prisma ?? prisma) as PrismaLikeClient;
const result = await prismaClient.$queryRawUnsafe(sql, ...params);
// Prisma returns BigInt for PostgreSQL bigint/count 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 stateProvider = createPrismaPgStateProvider({ prisma: prisma as any });
await using stateAdapter = createPgStateAdapter({
stateProvider,
// Prisma v4+ removed implicit type coercion, so $queryRawUnsafe doesn't cast
// string parameters to UUID, causing "operator does not exist: uuid = text".
// Using TEXT is the recommended workaround. See: https://github.com/prisma/prisma/issues/16969
jobIdType: "text",
});
await stateAdapter.migrateToLatest();src/provider.ts
import type { PgStateProvider } from "@queuert/postgres";
import type { Knex } from "knex";
import { type TxHooks, withTxHooks } from "queuert";
export type KnexPgContext = { trx: Knex.Transaction; txHooks: TxHooks };
const POSITIONAL_PARAM = /\$(\d+)/g;
// Knex only understands `?` placeholders and throws when the binding count
// doesn't match, so the adapter's `$n` form has to be rewritten. Bindings are
// rebuilt in occurrence order, which also covers templates that reference the
// same `$n` twice (e.g. keyset pagination predicates). Dollar-quoted blocks
// (`DO $$ ... $$`) carry no digits and are left untouched.
const toKnexQuery = (
sql: string,
params: unknown[],
): { sql: string; bindings: Knex.RawBinding[] } => {
const bindings: Knex.RawBinding[] = [];
const rewritten = sql.replace(POSITIONAL_PARAM, (_match, position: string) => {
// Knex rejects `undefined` bindings outright; pg treats them as NULL.
bindings.push((params[Number(position) - 1] ?? null) as Knex.RawBinding);
return "?";
});
return { sql: rewritten, bindings };
};
export const createKnexPgStateProvider = ({
knex,
}: {
knex: Knex;
}): PgStateProvider<KnexPgContext> => {
return {
withTransaction: async (cb) =>
withTxHooks(async (txHooks) =>
knex.transaction(async (trx) => cb({ trx, txHooks }), {
isolationLevel: "read committed",
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 driverError = error as { code?: unknown; message?: unknown } | undefined;
if (driverError?.code !== "42P01") return undefined;
const tableName = /relation "([^"]+)" does not exist/.exec(String(driverError.message))?.[1];
return tableName === undefined ? undefined : { kind: "undefined_table", tableName };
},
// `id` not forwarded: knex has no server-side prepared statement support —
// every `raw` call is sent unprepared. Bypass knex for prepared statements
// (see postgresql/state-pg).
executeSql: async ({ txCtx, sql, params }) => {
const query = toKnexQuery(sql, params);
const result = await (txCtx?.trx ?? knex).raw(query.sql, query.bindings);
return result.rows;
},
};
};src/index.ts
const stateProvider = createKnexPgStateProvider({ knex });
await using stateAdapter = createPgStateAdapter({
stateProvider,
});
await stateAdapter.migrateToLatest();src/provider.ts
import {
type EntityManager,
IsolationLevel,
type MikroORM,
type Transaction,
} from "@mikro-orm/postgresql";
import type { PgStateProvider } from "@queuert/postgres";
import { CompiledQuery, type Kysely } from "kysely";
import { type TxHooks, createTxHooks } from "queuert";
export type MikroOrmPgContext = { em: EntityManager };
export const createMikroOrmPgStateProvider = ({
orm,
}: {
orm: MikroORM;
}): PgStateProvider<MikroOrmPgContext> => {
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: MikroOrmPgContext): 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 });
},
{ isolationLevel: IsolationLevel.READ_COMMITTED },
);
} 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 driverError = error as { code?: unknown; message?: unknown } | undefined;
if (driverError?.code !== "42P01") return undefined;
const tableName = /relation "([^"]+)" does not exist/.exec(String(driverError.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],
clientUrl: pg.connectionString,
schemaGenerator: { skipTables: [/^queuert_/] },
});src/index.ts
const stateProvider = createMikroOrmPgStateProvider({ orm });
await using stateAdapter = createPgStateAdapter({ stateProvider });
await stateAdapter.migrateToLatest();Configuration
createPgStateAdapter
- stateProvider requiredThe
PgStateProviderfor your client; see State provider. - schema "public"PostgreSQL schema that holds all objects.
- tablePrefix "queuert_"Table name prefix for namespace isolation.
- jobIdType "uuid"Type of the job id columns.
- workerIdType "uuid"Type of the worker id columns.
- 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.
TypeScript
const stateAdapter = createPgStateAdapter({
stateProvider,
maxBlockersPerJob: 500,
countCap: 50_000,
});maxBlockersPerJob and countCap must be positive integers; anything else throws when the adapter is created. The resolved values are readable on stateAdapter.config.
State provider
A PgStateProvider 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:
PgStateProvider
- 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. It states READ COMMITTED explicitly (BEGIN ISOLATION LEVEL READ COMMITTED, or the client's isolation-level option), so a higherdefault_transaction_isolationcannot apply; see Isolation level. - 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. - 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 route it to a read replica or a separate reader pool. - Returns
{ kind: "undefined_table", tableName }for a missing-table error (SQLSTATE42P01; most drivers expose it ascode), withtableNameread from therelation "<table>" does not existmessage, 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.
Testing your provider
@queuert/postgres/conformance checks a provider you write. pgStateProviderConformanceCases are every case a state adapter must pass, the cases that hold two transactions open at once, checks of the schema write fence, and checks that classifyError names a missing table. createPgStateProviderConformanceContext 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, as each example does:
src/provider.spec.ts
describe("postgresql/state-pg provider conformance", () => {
let pg: AcquiredPostgres;
let pool: Pool;
let context: PgStateProviderConformanceContext;
beforeAll(async () => {
pg = await acquirePostgres("postgres:18", import.meta.url);
pool = new Pool({ connectionString: pg.connectionString, idleTimeoutMillis: 0 });
context = await createPgStateProviderConformanceContext({
createStateProvider: async () => createPgPoolStateProvider({ pool }),
withNonTransactionContext: async (fn) => {
const poolClient = await pool.connect();
try {
return await fn({ poolClient });
} finally {
poolClient.release();
}
},
});
}, 60_000);
afterAll(async () => {
await pool.end();
await pg[Symbol.asyncDispose]();
});
for (const conformanceCase of pgStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("postgresql/state-postgres-js provider conformance", () => {
let pg: AcquiredPostgres;
let sql: postgres.Sql;
let context: PgStateProviderConformanceContext;
beforeAll(async () => {
pg = await acquirePostgres("postgres:18", import.meta.url);
sql = postgres(pg.connectionString, { max: 10 });
context = await createPgStateProviderConformanceContext({
createStateProvider: async () => createPostgresJsStateProvider({ sql }),
withNonTransactionContext: async (fn) => fn({ txSql: sql }),
});
}, 60_000);
afterAll(async () => {
await sql.end();
await pg[Symbol.asyncDispose]();
});
for (const conformanceCase of pgStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("postgresql/state-bun-sql provider conformance", () => {
let pg: AcquiredPostgres;
let sql: SQL;
let context: PgStateProviderConformanceContext;
beforeAll(async () => {
pg = await acquirePostgres("postgres:18", import.meta.url);
sql = new SQL(pg.connectionString, { max: 10 });
context = await createPgStateProviderConformanceContext({
createStateProvider: async () => createBunSqlStateProvider({ sql }),
withNonTransactionContext: async (fn) => fn({ txSql: sql }),
});
}, 60_000);
afterAll(async () => {
await sql.close();
await pg[Symbol.asyncDispose]();
});
for (const conformanceCase of pgStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("postgresql/state-drizzle provider conformance", () => {
let pg: AcquiredPostgres;
let pool: Pool;
let context: PgStateProviderConformanceContext;
beforeAll(async () => {
pg = await acquirePostgres("postgres:18", import.meta.url);
pool = new Pool({ connectionString: pg.connectionString, idleTimeoutMillis: 0 });
const db = drizzle(pool, { schema: {} });
context = await createPgStateProviderConformanceContext({
createStateProvider: async () => createDrizzlePgStateProvider({ db }),
withNonTransactionContext: async (fn) => fn({ tx: db }),
});
}, 60_000);
afterAll(async () => {
await pool.end();
await pg[Symbol.asyncDispose]();
});
for (const conformanceCase of pgStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("postgresql/state-kysely provider conformance", () => {
let pg: AcquiredPostgres;
let db: Kysely<Database>;
let context: PgStateProviderConformanceContext;
beforeAll(async () => {
pg = await acquirePostgres("postgres:18", import.meta.url);
const pool = new Pool({ connectionString: pg.connectionString, idleTimeoutMillis: 0 });
const kysely = new Kysely<Database>({ dialect: new PostgresDialect({ pool }) });
db = kysely;
context = await createPgStateProviderConformanceContext({
createStateProvider: async () => createKyselyPgStateProvider({ db: kysely }),
withNonTransactionContext: async (fn) => fn({ db: kysely }),
});
}, 60_000);
afterAll(async () => {
await db.destroy();
await pg[Symbol.asyncDispose]();
});
for (const conformanceCase of pgStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("postgresql/state-prisma provider conformance", () => {
let pg: AcquiredPostgres;
let prisma: PrismaLike;
let context: PgStateProviderConformanceContext;
beforeAll(async () => {
pg = await acquirePostgres("postgres:18", import.meta.url);
execSync("npx prisma db push", {
cwd: EXAMPLE_DIR,
env: { ...process.env, DATABASE_URL: pg.connectionString },
stdio: "inherit",
});
const { PrismaClient } = await import("../prisma/generated/prisma/client.ts");
const prismaAdapter = new PrismaPg({ connectionString: pg.connectionString });
prisma = new PrismaClient({ adapter: prismaAdapter });
context = await createPgStateProviderConformanceContext({
createStateProvider: async () => createPrismaPgStateProvider<PrismaLike>({ prisma }),
withNonTransactionContext: async (fn) => fn({ prisma }),
});
}, 60_000);
afterAll(async () => {
await prisma?.$disconnect();
await pg[Symbol.asyncDispose]();
});
for (const conformanceCase of pgStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("postgresql/state-knex provider conformance", () => {
let pg: AcquiredPostgres;
let knex: Knex;
let context: PgStateProviderConformanceContext;
beforeAll(async () => {
pg = await acquirePostgres("postgres:18", import.meta.url);
knex = knexFactory({
client: "pg",
connection: pg.connectionString,
pool: { min: 0, max: 10 },
});
context = await createPgStateProviderConformanceContext({
createStateProvider: async () => createKnexPgStateProvider({ knex }),
withNonTransactionContext: async (fn) => fn({ trx: knex }),
});
}, 60_000);
afterAll(async () => {
await knex.destroy();
await pg[Symbol.asyncDispose]();
});
for (const conformanceCase of pgStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("postgresql/state-mikro-orm provider conformance", () => {
let context: PgStateProviderConformanceContext;
beforeAll(async () => {
context = await createPgStateProviderConformanceContext({
createStateProvider: async () => stateProvider,
withNonTransactionContext: async (fn) => fn({ em: orm.em.fork() }),
withSavepoint: async (txCtx, fn) =>
(txCtx as MikroOrmPgContext).em.transactional(async (em) => fn({ em })),
});
});
for (const conformanceCase of pgStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});The full API is in the conformance reference.
Isolation level
Queuert supports only READ COMMITTED, PostgreSQL's default isolation level. This covers every transaction Queuert writes in, including the ones you pass to complete and to client methods. Its conditional and multi-statement writes, such as unblockJobs locking the blocked jobs and then recomputing their blockers, rely on each statement taking a fresh snapshot after it waits on a row lock. Every provider above opens its transactions with the level stated, so a server or role whose default_transaction_isolation is higher cannot change it; open the transactions you pass to Queuert the same way.
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 objects live under a configurable PostgreSQL schema (default: public) with a table name prefix (default: queuert_, shown as {prefix}) for namespace isolation.
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/postgres 0.17 first.
The migrator behind migrateToLatest() is exported for your own tables too; see Application Migrations.
job — Stores all job state.
{prefix}job
- idpkjobIdType (default: uuid)Job identifier
- type_nametextJob type identifier
- chain_idsame as idThe head job's id — every job in a chain points to the head
- chain_indexintegerPosition in chain (0 for the head, incrementing for continuations)
- continued_to_idsame as idThe next job's id in the chain
- inputjsonbJob input data
- outputjsonbCompletion output (null until completed)
- statustextblocked, pending, running or completed (CHECK-constrained)
- created_attimestamptzWhen the job was created
- scheduled_attimestamptzEarliest time the job can be acquired
- completed_attimestamptzWhen the job completed (null until completed)
- completed_bysame as worker.idWorker ID that completed the job (null for workerless)
- attemptintegerNumber of processing attempts (starts at 0)
- last_attempt_attimestamptzWhen the last failed attempt ended
- last_attempt_errorjsonbError from the last failed or reclaimed attempt
- attempt_attimestamptzWhen the current attempt started (null when idle)
- attempt_bysame as worker.idWorker ID holding the current attempt
- attempt_tokenuuidToken of the current attempt
- trace_contexttextW3C traceparent for this job
- chain_statushead rows onlytextrunning or completed (CHECK-constrained)
- chain_completed_athead rows onlytimestamptzWhen the chain completed (null until completed)
- chain_deduplication_keyhead rows onlytextKey for chain deduplication
- chain_trace_contexthead rows onlytextW3C traceparent for the chain
job_blocker — Tracks dependencies between jobs and chains. Each blocker slot is unique.
{prefix}job_blocker
- job_idpksame as idThe blocked job
- blocked_by_chain_idpksame as idHead job ID of the blocker chain
- indexpkintegerPosition in the blockers array
- trace_contexttextW3C traceparent
worker — Holds one row per worker, each with a lease.
{prefix}worker
- started_attimestamptzWhen the worker started
- lease_untiltimestamptzLease expiry — the worker is live while it is in the future
- tokenuuidIdentifies this registration, new on every insert
- concurrencyintegerMost attempts the worker runs at once
- idpkworkerIdType (default: uuid)Worker ID, the worker runner's workerId
- type_namestext[]Job types the worker processes
migration — Tracks applied schema migrations.
{prefix}migration
- nametextMigration identifier (e.g., 001_schema_v1)
- applied_attimestamptzWhen the migration was applied
migration_lock — Holds a single-row lease that gives migrateToLatest() cross-process mutual exclusion.
{prefix}migration_lock
- idintegerAlways 1 (single-row constraint)
- locked_bytextOwner id of the current migration run
- locked_untiltimestamptzLease expiry (heartbeat-extended)
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_idxChain 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)
Vacuum tuning
The schema configures aggressive autovacuum and storage settings on the job tables:
storage parameters
- fillfactor = 75 jobReserves free space per heap page for HOT updates. job_blocker skips it: blockers are inserted and deleted without intermediate updates.
- fillfactor = 50 workerLeaves room for each lease renewal to be a HOT update on the same page.
- autovacuum_vacuum_threshold / autovacuum_analyze_threshold = 5000, scale_factor = 0 job job_blockerPin autovacuum to a fixed dead-tuple budget.
- autovacuum_vacuum_cost_delay = 0 job job_blockerRemoves I/O throttling.
Examples
@queuert/postgres
- GitHub PostgreSQL state storage via @queuert/postgres with the pg (node-postgres) driver — job creation and completion inside application transactions.
$ pnpm --filter example-postgresql-state-pg startsrc/provider.tssrc/index.tssrc/migrations.ts - GitHub PostgreSQL state storage via @queuert/postgres with postgres.js — job creation and completion inside application transactions.
$ pnpm --filter example-postgresql-state-postgres-js startsrc/provider.tssrc/index.tssrc/migrations.ts - GitHub PostgreSQL state storage via @queuert/postgres using Bun's built-in SQL client — job creation and completion inside application transactions, no external PostgreSQL dependencies.
$ pnpm --filter example-postgresql-state-bun-sql startsrc/provider.tssrc/index.tssrc/migrations.ts - GitHub PostgreSQL state storage via @queuert/postgres with Drizzle ORM — job creation and completion inside application transactions.
$ pnpm --filter example-postgresql-state-drizzle startsrc/provider.tssrc/index.tssrc/migrations.tssrc/schema.ts - GitHub PostgreSQL state storage via @queuert/postgres with Kysely — job creation and completion inside application transactions.
$ pnpm --filter example-postgresql-state-kysely startsrc/provider.tssrc/index.tssrc/migrations.tssrc/database.ts - GitHub PostgreSQL state storage via @queuert/postgres with Prisma ORM — job creation and completion inside application transactions.
$ pnpm --filter example-postgresql-state-prisma startsrc/provider.tssrc/index.tssrc/migrations.tsprisma/schema.prisma - GitHub PostgreSQL state storage via @queuert/postgres with Knex — job creation and completion inside application transactions.
$ pnpm --filter example-postgresql-state-knex startsrc/provider.tssrc/index.tssrc/migrations.ts - GitHub PostgreSQL state storage via @queuert/postgres with MikroORM — commit and rollback callbacks from MikroORM's transaction events, so calls take `{ em }` alone.
$ pnpm --filter example-postgresql-state-mikro-orm startsrc/provider.tssrc/index.ts - GitHub Three worker processes (forked from the main process) share the same PostgreSQL database and compete for jobs — the main process creates orders and their chains in one transaction, and each order is processed by whichever worker acquires its job first. Horizontal Scaling guide →
$ pnpm --filter example-postgresql-multi-worker startsrc/index.tssrc/worker.ts
The API reference for every export is in @queuert/postgres.