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/mysql

Requires 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();

Every connection the adapter uses must meet these:

connection requirements

  • A default database
    Connect with a database selected. MySQL rejects the adapter's multi-table updates over JSON_TABLE on a connection without one (error 1046), even when every table is qualified.
  • A strict sql_mode
    STRICT_TRANS_TABLES or STRICT_ALL_TABLES, so the server rejects a value instead of truncating it. Both servers enable it by default.
  • No NO_BACKSLASH_ESCAPES
    Leave NO_BACKSLASH_ESCAPES out of sql_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
    withTransaction opens every transaction at READ COMMITTED; see Isolation level.
  • Single statements
    Never enable multipleStatements on the pool the provider uses.

Configuration

createMysqlStateAdapter

  • stateProvider required
    The MysqlStateProvider for 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 database
    Database 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)" with n from 1 to 255, in the dialect's exact collation.
  • 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_000
    The per-status count at which countChainsByTypeName and countJobsByTypeName (and the dashboard's type counts) stop counting and report hasMore. A higher cap means a longer index scan per count.
  • generateJobId crypto.randomUUID
    Called for every job created without a caller-supplied id.
  • validateJobId accept all
    Runs on every caller-supplied and generated ID before writing, throwing InvalidJobIdError on failure.
  • generateWorkerId crypto.randomUUID
    Called once for each worker runner created without a workerId; must return a new id on every call.
  • validateWorkerId accept all
    Runs on the configured or generated worker id; createWorkerRunner throws InvalidWorkerIdError on failure.

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 txHooks in its context creates them here with withTxHooks, around the transaction, so they flush after COMMIT. It runs SET TRANSACTION ISOLATION LEVEL READ COMMITTED and then START TRANSACTION on 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 txHooks in 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's execute); without one, the text protocol (query) is fine. Placeholders are ?. Never enable multipleStatements.
  • Returns { kind: "undefined_table", tableName } for a missing-table error (errno 1146), with tableName set to the qualified <database>.<table> from the Table '<database>.<table>' doesn't exist message, and undefined for any other error, or when it cannot read the table name. The adapter uses it to turn a write that hit a dropped write fence into SchemaFencedError.
  • close optional
    Only needed when the provider owns resources beyond the client or pool you pass in. Must be idempotent.

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);
    });
  }
});

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 uniquenessUNIQUE (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

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

esc

Search guides, integrations and every exported symbol.

↑ ↓ to navigate↵ to open