MySQL / MariaDB State Adapter
Store chains, jobs and workers in MySQL or MariaDB.
@queuert/mysql stores chains, jobs and workers in MySQL or MariaDB (InnoDB), in the same transactions as your application's data. One adapter covers both servers; its dialect option selects the server. Workers claim jobs with FOR UPDATE SKIP LOCKED, so several workers can share one database; see Horizontal Scaling.
Experimental
@queuert/mysql is experimental: its API may change in a minor release.Clients
Install
Terminal
npm install @queuert/mysqlRequires MySQL 8.4 or later or MariaDB 11.4 or later, with InnoDB tables. MySQL and MariaDB have no LISTEN/NOTIFY, so the package has no notify adapter: pair it with the Redis or NATS notify adapter, or run without one and let workers poll.
Setup
Each tab shows the example's MysqlStateProvider and the lines that create the adapter:
src/provider.ts
import type { MysqlStateProvider } from "@queuert/mysql";
import type { Pool, PoolConnection } from "mysql2/promise";
import { type TxHooks, withTxHooks } from "queuert";
export type Mysql2PoolContext = { connection: PoolConnection; txHooks: TxHooks };
export const createMysql2PoolStateProvider = ({
pool,
}: {
pool: Pool;
}): MysqlStateProvider<Mysql2PoolContext> => {
const openTransactions = new WeakSet<PoolConnection>();
const exec = async (
connection: PoolConnection,
id: string | undefined,
sql: string,
params: unknown[],
): Promise<unknown[]> => {
const [rows] =
id !== undefined
? await connection.execute(sql, params as never[])
: await connection.query(sql, params);
return Array.isArray(rows) ? rows : [];
};
return {
withTransaction: async (cb) =>
withTxHooks(async (txHooks) => {
const connection = await pool.getConnection();
const txCtx: Mysql2PoolContext = { connection, txHooks };
try {
await connection.query("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
await connection.query("START TRANSACTION");
openTransactions.add(connection);
const result = await cb(txCtx);
await connection.query("COMMIT");
openTransactions.delete(connection);
connection.release();
return result;
} catch (error) {
openTransactions.delete(connection);
await connection.query("ROLLBACK").then(
() => {
connection.release();
},
() => {
connection.destroy();
},
);
throw error;
}
}),
isTransaction: (txCtx) => openTransactions.has(txCtx.connection),
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;
switch (driverError?.errno) {
case 1146: {
const tableName = /Table '([^']+)' doesn't exist/.exec(String(driverError.message))?.[1];
return tableName === undefined ? undefined : { kind: "undefined_table", tableName };
}
default:
return undefined;
}
},
executeSql: async ({ txCtx, id, sql, params }) => {
if (txCtx) return exec(txCtx.connection, id, sql, params);
const connection = await pool.getConnection();
try {
return await exec(connection, id, sql, params);
} finally {
connection.release();
}
},
};
};src/index.ts
const stateProvider = createMysql2PoolStateProvider({ pool: db });
await using stateAdapter = createMysqlStateAdapter({ stateProvider, dialect: "mysql" });
await stateAdapter.migrateToLatest();src/provider.ts
import type { MysqlStateProvider } from "@queuert/mysql";
import type { Pool, PoolConnection } from "mariadb";
import { type TxHooks, withTxHooks } from "queuert";
export type MariadbPoolContext = { connection: PoolConnection; txHooks: TxHooks };
export const createMariadbPoolStateProvider = ({
pool,
}: {
pool: Pool;
}): MysqlStateProvider<MariadbPoolContext> => {
const openTransactions = new WeakSet<PoolConnection>();
const exec = async (
connection: PoolConnection,
id: string | undefined,
sql: string,
params: unknown[],
): Promise<unknown[]> => {
const rows: unknown =
id !== undefined
? await connection.execute(sql, params)
: await connection.query(sql, params);
return Array.isArray(rows) ? rows : [];
};
return {
withTransaction: async (cb) =>
withTxHooks(async (txHooks) => {
const connection = await pool.getConnection();
const txCtx: MariadbPoolContext = { connection, txHooks };
try {
await connection.query("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
await connection.beginTransaction();
openTransactions.add(connection);
const result = await cb(txCtx);
await connection.commit();
openTransactions.delete(connection);
await connection.release();
return result;
} catch (error) {
openTransactions.delete(connection);
await connection.rollback().then(
async () => connection.release(),
() => {
connection.destroy();
},
);
throw error;
}
}),
isTransaction: (txCtx) => openTransactions.has(txCtx.connection),
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;
switch (driverError?.errno) {
case 1146: {
const tableName = /Table '([^']+)' doesn't exist/.exec(String(driverError.message))?.[1];
return tableName === undefined ? undefined : { kind: "undefined_table", tableName };
}
default:
return undefined;
}
},
executeSql: async ({ txCtx, id, sql, params }) => {
if (txCtx) return exec(txCtx.connection, id, sql, params);
const connection = await pool.getConnection();
try {
return await exec(connection, id, sql, params);
} finally {
await connection.release();
}
},
};
};src/index.ts
const stateProvider = createMariadbPoolStateProvider({ pool: db });
await using stateAdapter = createMysqlStateAdapter({ stateProvider, dialect: "mariadb" });
await stateAdapter.migrateToLatest();src/provider.ts
import type { MysqlStateProvider } from "@queuert/mysql";
import type { SQL, TransactionSQL } from "bun";
import { type TxHooks, withTxHooks } from "queuert";
export type BunSqlContext = { txSql: TransactionSQL; txHooks: TxHooks };
// Bun caches a statement whose prepare failed on the connection and replays the error on every
// later run, even after the table exists, so a statement that hit a missing table is sent with a
// new comment to prepare it again. The count is kept per client, as its connections hold the cache.
const missingTableFailuresByClient = new WeakMap<SQL, Map<string, number>>();
export const createBunSqlStateProvider = ({
sql,
}: {
sql: SQL;
}): MysqlStateProvider<BunSqlContext> => {
const missingTableFailures = missingTableFailuresByClient.get(sql) ?? new Map<string, number>();
missingTableFailuresByClient.set(sql, missingTableFailures);
const classifyError: MysqlStateProvider<BunSqlContext>["classifyError"] = ({ error }) => {
const driverError = error as { errno?: unknown; message?: unknown } | undefined;
if (driverError?.errno !== 1146) return undefined;
const tableName = /Table '([^']+)' doesn't exist/.exec(String(driverError.message))?.[1];
return tableName === undefined ? undefined : { kind: "undefined_table", tableName };
};
return {
// MySQL's START TRANSACTION takes no isolation level, so the level is set on a reserved
// connection just before `begin` opens the transaction on it.
withTransaction: async (cb) =>
withTxHooks(async (txHooks) => {
using reserved = await sql.reserve();
await reserved`SET TRANSACTION ISOLATION LEVEL READ COMMITTED`;
return await reserved.begin(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,
executeSql: async ({ txCtx, sql: query, params }) => {
const failures = missingTableFailures.get(query) ?? 0;
try {
return await (txCtx?.txSql ?? sql).unsafe(
failures === 0 ? query : `${query} /* reprepare ${failures} */`,
params,
);
} catch (error) {
if (classifyError({ error }) !== undefined) missingTableFailures.set(query, failures + 1);
throw error;
}
},
};
};src/index.ts
const stateProvider = createBunSqlStateProvider({ sql });
await using stateAdapter = createMysqlStateAdapter({ stateProvider, dialect: "mysql" });
await stateAdapter.migrateToLatest();src/provider.ts
import type { MysqlStateProvider } from "@queuert/mysql";
import type { ExtractTablesWithRelations } from "drizzle-orm";
import { MySqlTransaction } from "drizzle-orm/mysql-core";
import type {
MySql2Database,
MySql2PreparedQueryHKT,
MySql2QueryResultHKT,
} from "drizzle-orm/mysql2";
import type { Pool, PoolConnection } from "mysql2/promise";
import { type TxHooks, withTxHooks } from "queuert";
export type DrizzleMysqlTransaction<TSchema extends Record<string, unknown>> = MySqlTransaction<
MySql2QueryResultHKT,
MySql2PreparedQueryHKT,
TSchema,
ExtractTablesWithRelations<TSchema>
>;
export type DrizzleMysqlContext<TSchema extends Record<string, unknown>> = {
tx: DrizzleMysqlTransaction<TSchema>;
txHooks: TxHooks;
};
export const createDrizzleMysqlStateProvider = <TSchema extends Record<string, unknown>>({
db,
}: {
db: MySql2Database<TSchema> & { $client: Pool };
}): MysqlStateProvider<DrizzleMysqlContext<TSchema>> => {
const exec = async (
connection: Pool | PoolConnection,
id: string | undefined,
sql: string,
params: unknown[],
): Promise<unknown[]> => {
const [rows] =
id !== undefined
? await connection.execute(sql, params as never[])
: await connection.query(sql, params);
return Array.isArray(rows) ? rows : [];
};
return {
withTransaction: async (cb) =>
withTxHooks(async (txHooks) =>
db.transaction(async (tx) => cb({ tx, txHooks }), { isolationLevel: "read committed" }),
),
isTransaction: (txCtx) => txCtx.tx instanceof MySqlTransaction,
onTransactionCommit: (txCtx, callback) => {
txCtx.txHooks.onCommit(callback);
},
onTransactionRollback: (txCtx, callback) => {
txCtx.txHooks.onRollback(callback);
},
classifyError: ({ error }) => {
type DriverError = { errno?: unknown; message?: unknown; cause?: DriverError };
const driverError = [
error as DriverError | undefined,
(error as DriverError | undefined)?.cause,
].find((candidate) => candidate?.errno === 1146);
if (driverError === undefined) return undefined;
const tableName = /Table '([^']+)' doesn't exist/.exec(String(driverError.message))?.[1];
return tableName === undefined ? undefined : { kind: "undefined_table", tableName };
},
executeSql: async ({ txCtx, id, sql, params }) =>
// Inside transaction: the connection Drizzle's session checked out of the pool
// Outside transaction (migrations): db.$client (the pool)
exec(txCtx ? (txCtx.tx as any).session.client : db.$client, id, sql, params),
};
};src/index.ts
const stateProvider = createDrizzleMysqlStateProvider({ db });
await using stateAdapter = createMysqlStateAdapter({ stateProvider, dialect: "mysql" });
await stateAdapter.migrateToLatest();src/provider.ts
import type { MysqlStateProvider } from "@queuert/mysql";
import { CompiledQuery, type Kysely } from "kysely";
import { type TxHooks, withTxHooks } from "queuert";
export type KyselyMysqlContext<TDatabase> = { db: Kysely<TDatabase>; txHooks: TxHooks };
export const createKyselyMysqlStateProvider = <TDatabase>({
db,
}: {
db: Kysely<TDatabase>;
}): MysqlStateProvider<KyselyMysqlContext<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 { errno?: unknown; message?: unknown } | undefined;
switch (driverError?.errno) {
case 1146: {
const tableName = /Table '([^']+)' doesn't exist/.exec(String(driverError.message))?.[1];
return tableName === undefined ? undefined : { kind: "undefined_table", tableName };
}
default:
return undefined;
}
},
// `id` not forwarded: Kysely's MySQL dialect sends every query with mysql2's `query`
// (text protocol). Bypass Kysely for server-side prepared statements (see mysql/state-mysql2).
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 = createKyselyMysqlStateProvider({ db });
await using stateAdapter = createMysqlStateAdapter({ stateProvider, dialect: "mysql" });
await stateAdapter.migrateToLatest();src/provider.ts
import type { MysqlStateProvider } from "@queuert/mysql";
import { type TxHooks, withTxHooks } from "queuert";
// Prisma wraps a raw query's driver error; the MySQL error number is `originalCode` on its cause.
type PrismaDriverAdapterError = {
meta?: {
driverAdapterError?: {
cause?: { originalCode?: unknown; originalMessage?: unknown };
};
};
};
export type PrismaLikeClient = {
$transaction: <T>(
fn: (prisma: any) => Promise<T>,
options: { isolationLevel: "ReadCommitted" },
) => Promise<T>;
$queryRawUnsafe: (sql: string, ...params: unknown[]) => Promise<unknown[]>;
};
export type PrismaMysqlContext<TPrisma> = { prisma: TPrisma; txHooks: TxHooks };
export const createPrismaMysqlStateProvider = <TPrisma extends PrismaLikeClient>({
prisma,
}: {
prisma: TPrisma;
}): MysqlStateProvider<PrismaMysqlContext<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 !== "1146") return undefined;
const tableName = /Table '([^']+)' doesn't exist/.exec(String(cause.originalMessage))?.[1];
return tableName === undefined ? undefined : { kind: "undefined_table", tableName };
},
// `id` not forwarded: Prisma decides itself how each raw query is sent.
executeSql: async ({ txCtx, sql, params }) => {
const prismaClient = (txCtx?.prisma ?? prisma) as PrismaLikeClient;
return prismaClient.$queryRawUnsafe(sql, ...params);
},
};
};src/index.ts
const stateProvider = createPrismaMysqlStateProvider({ prisma: prisma as any });
await using stateAdapter = createMysqlStateAdapter({ stateProvider, dialect: "mysql" });
await stateAdapter.migrateToLatest();src/provider.ts
import type { MysqlStateProvider } from "@queuert/mysql";
import type { Knex } from "knex";
import { type TxHooks, withTxHooks } from "queuert";
export type KnexMysqlContext = { trx: Knex.Transaction; txHooks: TxHooks };
export const createKnexMysqlStateProvider = ({
knex,
}: {
knex: Knex;
}): MysqlStateProvider<KnexMysqlContext> => {
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 { errno?: unknown; sqlMessage?: unknown } | undefined;
if (driverError?.errno !== 1146) return undefined;
const tableName = /Table '([^']+)' doesn't exist/.exec(String(driverError.sqlMessage))?.[1];
return tableName === undefined ? undefined : { kind: "undefined_table", tableName };
},
// `id` not forwarded: knex sends every `raw` call unprepared.
executeSql: async ({ txCtx, sql, params }) => {
// Knex rejects `undefined` bindings outright; mysql2 treats them as NULL.
const bindings = params.map((param) => (param ?? null) as Knex.RawBinding);
const [rows] = await (txCtx?.trx ?? knex).raw(sql, bindings);
return Array.isArray(rows) ? rows : [];
},
};
};src/index.ts
const stateProvider = createKnexMysqlStateProvider({ knex });
await using stateAdapter = createMysqlStateAdapter({ stateProvider, dialect: "mysql" });
await stateAdapter.migrateToLatest();src/provider.ts
import {
type EntityManager,
IsolationLevel,
type MikroORM,
type Transaction,
} from "@mikro-orm/mysql";
import type { MysqlStateProvider } from "@queuert/mysql";
import { CompiledQuery, type Kysely } from "kysely";
import { type TxHooks, createTxHooks } from "queuert";
export type MikroOrmMysqlContext = { em: EntityManager };
export const createMikroOrmMysqlStateProvider = ({
orm,
}: {
orm: MikroORM;
}): MysqlStateProvider<MikroOrmMysqlContext> => {
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: MikroOrmMysqlContext): 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 { errno?: unknown; message?: unknown } | undefined;
if (driverError?.errno !== 1146) return undefined;
const tableName = /Table '([^']+)' doesn't 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 connection = orm.em.getConnection();
await connection.initClient();
const client = transaction ?? connection.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],
host: mysql.connectionOptions.host,
port: mysql.connectionOptions.port,
user: mysql.connectionOptions.user,
password: mysql.connectionOptions.password,
dbName: mysql.connectionOptions.database,
schemaGenerator: { skipTables: [/^queuert_/] },
});src/index.ts
const stateProvider = createMikroOrmMysqlStateProvider({ orm });
await using stateAdapter = createMysqlStateAdapter({ stateProvider, dialect: "mysql" });
await stateAdapter.migrateToLatest();Every connection the adapter uses must meet these:
connection requirements
- A default databaseConnect with a database selected. MySQL rejects the adapter's multi-table updates over
JSON_TABLEon a connection without one (error 1046), even when every table is qualified. - A strict sql_mode
STRICT_TRANS_TABLESorSTRICT_ALL_TABLES, so the server rejects a value instead of truncating it. Both servers enable it by default. - No NO_BACKSLASH_ESCAPESLeave
NO_BACKSLASH_ESCAPESout ofsql_mode: drivers escape the parameters of text-protocol statements with backslashes. Both servers leave it out by default.
Your provider must also meet these:
provider requirements
- READ COMMITTED transactions
withTransactionopens every transaction at READ COMMITTED; see Isolation level. - Single statementsNever enable
multipleStatementson the pool the provider uses.
Configuration
createMysqlStateAdapter
- stateProvider requiredThe
MysqlStateProviderfor your client; see State provider. - dialect required
"mysql"or"mariadb": the server the adapter talks to. It picks the collation and the SQL forms that differ between the two, so it must match the server. - schema the connection's databaseDatabase that holds the tables.
- tablePrefix "queuert_"Table name prefix for namespace isolation.
- jobIdType "uuid"Type of the job id columns:
"uuid"(CHAR(36),ascii_bin) or"varchar(n)"withnfrom 1 to 255, in the dialect's exact collation. - workerIdType "uuid"Type of the worker id columns, with the same presets as
jobIdType. - 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 = createMysqlStateAdapter({
stateProvider,
dialect: "mariadb",
maxBlockersPerJob: 500,
});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 MysqlStateProvider 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:
MysqlStateProvider
- 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 runsSET TRANSACTION ISOLATION LEVEL READ COMMITTEDand thenSTART TRANSACTIONon the same connection, with nothing between them; 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. With an
id, run the statement as a server-side prepared statement (mysql2'sexecute); without one, the text protocol (query) is fine. Placeholders are?. Never enablemultipleStatements. - Returns
{ kind: "undefined_table", tableName }for a missing-table error (errno1146), withtableNameset to the qualified<database>.<table>from theTable '<database>.<table>' doesn't 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.
mysql2 and the mariadb connector keep no transaction flag, so their examples' isTransaction tracks the connections its withTransaction opened. It keys on the connection rather than the context object, because Queuert passes copies of the context back to the provider.
Testing your provider
@queuert/mysql/conformance checks a provider you write. mysqlStateProviderConformanceCases are every case a state adapter must pass, the cases that hold two transactions open at once, and checks of the schema write fence. createMysqlStateProviderConformanceContext prepares the context they run against: it takes a createStateProvider factory, the dialect, and a withNonTransactionContext that runs a callback with a context outside any transaction, and migrates the schema once. An optional withSavepoint adds the savepoint cases, as on PostgreSQL. Your test file owns the database in its own hooks and registers one test per case:
src/provider.spec.ts
describe("mysql/state-mysql2 provider conformance", () => {
let pool: Pool;
let context: MysqlStateProviderConformanceContext;
beforeAll(async () => {
const { connectionOptions } = await acquireMysql("mysql:26", import.meta.url);
pool = createPool({ ...connectionOptions, connectionLimit: 20 });
context = await createMysqlStateProviderConformanceContext({
createStateProvider: async () => createMysql2PoolStateProvider({ pool }),
dialect: "mysql",
withNonTransactionContext: async (fn) => {
const connection = await pool.getConnection();
try {
return await fn({ connection });
} finally {
connection.release();
}
},
});
}, 120_000);
afterAll(async () => {
await pool.end();
});
for (const conformanceCase of mysqlStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("mysql/state-mariadb provider conformance", () => {
let pool: Pool;
let context: MysqlStateProviderConformanceContext;
beforeAll(async () => {
const { connectionOptions } = await acquireMysql("mariadb:13", import.meta.url);
pool = createPool({ ...connectionOptions, connectionLimit: 20 });
context = await createMysqlStateProviderConformanceContext({
createStateProvider: async () => createMariadbPoolStateProvider({ pool }),
dialect: "mariadb",
withNonTransactionContext: async (fn) => {
const connection = await pool.getConnection();
try {
return await fn({ connection });
} finally {
await connection.release();
}
},
});
}, 120_000);
afterAll(async () => {
await pool.end();
});
for (const conformanceCase of mysqlStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("mysql/state-bun-sql provider conformance", () => {
let sql: SQL;
let context: MysqlStateProviderConformanceContext;
beforeAll(async () => {
const { connectionOptions } = await acquireMysql("mysql:26", import.meta.url);
sql = createSql(connectionOptions);
context = await createMysqlStateProviderConformanceContext({
createStateProvider: async () => createBunSqlStateProvider({ sql }),
dialect: "mysql",
withNonTransactionContext: async (fn) => fn({ txSql: sql }),
});
}, 120_000);
afterAll(async () => {
await sql.close();
});
for (const conformanceCase of mysqlStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("mysql/state-drizzle provider conformance", () => {
let pool: Pool;
let context: MysqlStateProviderConformanceContext;
beforeAll(async () => {
const { connectionOptions } = await acquireMysql("mysql:26", import.meta.url);
pool = createPool({ ...connectionOptions, connectionLimit: 20 });
const db = drizzle(pool, { schema: {}, mode: "default" });
context = await createMysqlStateProviderConformanceContext({
createStateProvider: async () => createDrizzleMysqlStateProvider({ db }),
dialect: "mysql",
withNonTransactionContext: async (fn) => fn({ tx: db }),
});
}, 120_000);
afterAll(async () => {
await pool.end();
});
for (const conformanceCase of mysqlStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("mysql/state-kysely provider conformance", () => {
let db: Kysely<Database>;
let context: MysqlStateProviderConformanceContext;
beforeAll(async () => {
const { connectionOptions } = await acquireMysql("mysql:26", import.meta.url);
const kysely = new Kysely<Database>({
dialect: new MysqlDialect({
pool: createPool({ ...connectionOptions, connectionLimit: 20 }),
}),
});
db = kysely;
context = await createMysqlStateProviderConformanceContext({
createStateProvider: async () => createKyselyMysqlStateProvider({ db: kysely }),
dialect: "mysql",
withNonTransactionContext: async (fn) => fn({ db: kysely }),
});
}, 120_000);
afterAll(async () => {
await db.destroy();
});
for (const conformanceCase of mysqlStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("mysql/state-prisma provider conformance", () => {
let prisma: PrismaLike;
let context: MysqlStateProviderConformanceContext;
beforeAll(async () => {
const { connectionOptions } = await acquireMysql("mysql:26", import.meta.url);
prisma = await createPrisma(connectionOptions);
context = await createMysqlStateProviderConformanceContext({
createStateProvider: async () => createPrismaMysqlStateProvider<PrismaLike>({ prisma }),
dialect: "mysql",
withNonTransactionContext: async (fn) => fn({ prisma }),
});
}, 120_000);
afterAll(async () => {
await prisma?.$disconnect();
});
for (const conformanceCase of mysqlStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("mysql/state-knex provider conformance", () => {
let knex: Knex;
let context: MysqlStateProviderConformanceContext;
beforeAll(async () => {
const { connectionOptions } = await acquireMysql("mysql:26", import.meta.url);
knex = knexFactory({
client: "mysql2",
connection: connectionOptions,
pool: { min: 0, max: 20 },
});
context = await createMysqlStateProviderConformanceContext({
createStateProvider: async () => createKnexMysqlStateProvider({ knex }),
dialect: "mysql",
withNonTransactionContext: async (fn) => fn({ trx: knex }),
});
}, 120_000);
afterAll(async () => {
await knex.destroy();
});
for (const conformanceCase of mysqlStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});src/provider.spec.ts
describe("mysql/state-mikro-orm provider conformance", () => {
let context: MysqlStateProviderConformanceContext;
beforeAll(async () => {
context = await createMysqlStateProviderConformanceContext({
createStateProvider: async () => stateProvider,
dialect: "mysql",
withNonTransactionContext: async (fn) => fn({ em: orm.em.fork() }),
withSavepoint: async (txCtx, fn) =>
(txCtx as MikroOrmMysqlContext).em.transactional(async (em) => fn({ em })),
});
});
for (const conformanceCase of mysqlStateProviderConformanceCases) {
test(conformanceCase.title, async () => {
await conformanceCase.run(context);
});
}
});The full API is in the conformance reference.
Isolation level
Queuert supports only READ COMMITTED. This covers every transaction Queuert writes in, including the ones you pass to complete and to client methods. InnoDB's default is REPEATABLE READ, so open the transactions you pass to Queuert with SET TRANSACTION ISOLATION LEVEL READ COMMITTED, as the provider's withTransaction does. At REPEATABLE READ, a claim on a drained queue takes gap locks that stall every insert of a new due job until it commits.
Lock errors
A deadlock (1213) rolls back the whole transaction. A lock wait timeout (1205) rolls back only the statement and leaves the transaction open with the earlier writes in it: roll back a transaction that saw one rather than commit it, or set innodb_rollback_on_timeout=ON so the server rolls it back. Either error fails the Queuert call like any database error, so a failed attempt is rescheduled with backoff.
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. Reads still work.
Schema
The adapter creates its schema via migrateToLatest(), in the connection's database or the configured schema, with a table name prefix (default: queuert_, shown as {prefix}). DDL commits implicitly on both servers, so no migration is atomic: every schema statement is idempotent, and a run that stopped partway resumes where it stopped.
Type names, deduplication keys and text ids use an exact, NO PAD binary collation (utf8mb4_0900_bin on MySQL, utf8mb4_nopad_bin on MariaDB), so values that differ only in case, accents or trailing spaces stay distinct. Timestamps are DATETIME(6) in UTC, whatever the server's or the session's time zone.
The migrator behind migrateToLatest() is exported for your own tables too; see Application Migrations.
job — Stores all job state.
{prefix}job
- idpkjobIdType (default: CHAR(36))Job identifier
- type_nameVARCHAR(255)Job type identifier
- chain_idsame as idThe chain's head job id
- chain_indexINTPosition in chain (0 for the head)
- continued_to_idsame as idThe next job's id in the chain
- inputJSONJob input data
- outputJSONCompletion output (null until completed)
- statusVARCHAR(16)blocked, pending, running or completed (CHECK-constrained)
- created_atDATETIME(6)UTC — when the job was created
- scheduled_atDATETIME(6)UTC — earliest time the job can be acquired
- completed_atDATETIME(6)UTC — when the job completed
- completed_bysame as worker.idWorker ID
- attemptINTNumber of processing attempts
- last_attempt_atDATETIME(6)UTC — when the last failed attempt ended
- last_attempt_errorJSONError from the last failed or reclaimed attempt
- attempt_atDATETIME(6)UTC — when the current attempt started
- attempt_bysame as worker.idWorker ID holding the attempt
- attempt_tokenCHAR(36)Token of the current attempt
- trace_contextTEXTW3C traceparent for this job
- status_tsDATETIME(6), generatedattempt_at while running, completed_at once completed, scheduled_at otherwise; stands in for PostgreSQL's per-status partial indexes
- chain_statushead rows onlyVARCHAR(16)running or completed (CHECK-constrained)
- chain_completed_athead rows onlyDATETIME(6)UTC — when the chain completed
- chain_deduplication_keyhead rows onlyVARCHAR(255)Key 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
- indexpkINTPosition in the blockers array
- trace_contextTEXTW3C traceparent
worker — Holds one row per worker, each with a lease.
{prefix}worker
- started_atDATETIME(6)UTC — when the worker started
- lease_untilDATETIME(6)UTC — the worker is live while it is in the future
- tokenCHAR(36)Identifies this registration, new on every insert
- concurrencyINTMost attempts the worker runs at once
- idpkworkerIdType (default: CHAR(36))Worker ID, the worker runner's workerId
- type_namesJSONJob types the worker processes
migration — Tracks applied schema migrations.
{prefix}migration
- nameVARCHAR(255)Migration identifier (e.g., 001_schema_v1)
- applied_atDATETIME(6)UTC — when the migration was applied
migration_lock — Holds a single-row lease that gives migrateToLatest() cross-process mutual exclusion.
{prefix}migration_lock
- idINTAlways 1 (single-row constraint)
- locked_byVARCHAR(255)Owner id of the current migration run
- locked_untilDATETIME(6)UTC — lease 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>
- idpkINTNever written
Indexes
{prefix}job
- chain_index_idxChain position uniqueness
UNIQUE (chain_id, chain_index) - job_status_idxJob acquisition and job listing by status
(type_name, status, status_ts) - job_idxAll jobs by type and time
(type_name, created_at) - chain_idxChains by type and time
(type_name, chain_index, created_at) - chain_running_idxChain listing and counts by status
(type_name, chain_status, created_at) - chain_completed_idxCompleted chain listing
(type_name, chain_status, chain_completed_at) - chain_deduplication_idxDeduplication lookup
(chain_deduplication_key, type_name, chain_index, created_at) - job_attempt_by_idxWorker job listing and running job counts
(attempt_by, status, attempt_at)
{prefix}job_blocker
- job_blocker_chain_idxBlocker resolution
(blocked_by_chain_id)
{prefix}worker
- worker_idxWorker listing
(started_at)
Examples
@queuert/mysql
- GitHub MySQL state storage via @queuert/mysql with the mysql2 driver — job creation and completion inside application transactions, with every transaction at READ COMMITTED.
$ pnpm --filter example-mysql-state-mysql2 startsrc/provider.tssrc/index.tssrc/migrations.ts - GitHub MariaDB state storage via @queuert/mysql with the official mariadb connector — job creation and completion inside application transactions, with every transaction at READ COMMITTED.
$ pnpm --filter example-mysql-state-mariadb startsrc/provider.tssrc/index.tssrc/migrations.ts - GitHub MySQL state storage via @queuert/mysql using Bun's built-in SQL client in its MySQL mode — job creation and completion inside application transactions, no external MySQL dependencies.
$ pnpm --filter example-mysql-state-bun-sql startsrc/provider.tssrc/index.tssrc/migrations.ts - GitHub MySQL state storage via @queuert/mysql with Drizzle ORM — job creation and completion inside application transactions.
$ pnpm --filter example-mysql-state-drizzle startsrc/provider.tssrc/index.tssrc/migrations.tssrc/schema.ts - GitHub MySQL state storage via @queuert/mysql with Kysely — job creation and completion inside application transactions.
$ pnpm --filter example-mysql-state-kysely startsrc/provider.tssrc/index.tssrc/migrations.tssrc/database.ts - GitHub MySQL state storage via @queuert/mysql with Prisma ORM and its MariaDB driver adapter — job creation and completion inside application transactions.
$ pnpm --filter example-mysql-state-prisma startsrc/provider.tssrc/index.tssrc/migrations.tsprisma/schema.prisma - GitHub MySQL state storage via @queuert/mysql with Knex — job creation and completion inside application transactions.
$ pnpm --filter example-mysql-state-knex startsrc/provider.tssrc/index.tssrc/migrations.ts - GitHub MySQL state storage via @queuert/mysql with MikroORM — commit and rollback callbacks from MikroORM's transaction events, so calls take `{ em }` alone.
$ pnpm --filter example-mysql-state-mikro-orm startsrc/provider.tssrc/index.ts - GitHub Three worker processes (forked from the main process) share one MySQL database and compete for jobs, finding new work by polling because MySQL has no notify adapter — the main process creates orders and their chains in one transaction. Horizontal Scaling guide →
$ pnpm --filter example-mysql-multi-worker startsrc/index.tssrc/worker.ts
The API reference for every export is in @queuert/mysql.