Adapters / MySQL

MySQL

MySQL 8 or MariaDB through any driver. The same query-function contract as Postgres, plus the row count MySQL needs because it has no RETURNING.

Setup

notify.ts

import {
  createMysqlTables,
  mysql2Query,
  mysqlAdapter,
  mysqlTransaction,
} from "easy-ping/adapters/mysql";
import mysql from "mysql2/promise";

// timezone "Z" is not optional: the adapter writes DATETIME as UTC and must
// read it back unshifted. Without it mysql2 assumes local time.
const pool = mysql.createPool({ uri: process.env.DATABASE_URL, timezone: "Z" });
const query = mysql2Query(pool);

await createMysqlTables(query);

export const notify = easyPing({
  database: mysqlAdapter(query, { transaction: mysqlTransaction(pool) }),
  // ...
});

Everything above the adapter is identical to every other database.

The contract

type MysqlQuery = (
  text: string,
  params: readonly unknown[],
) => Promise<{ rows: readonly Record<string, unknown>[]; affectedRows: number }>;

One field more than the Postgres contract: MySQL has no RETURNING, so the number of rows an UPDATE touched has to come from the driver. mysql2Query adapts a mysql2 pool or connection; any other driver is a few lines to the same shape.

How a claim works here

The claim primitive is the one operation with no portable spelling (RFC 0003). MySQL gets two implementations, chosen by whether you passed transaction:

you passed	the claim is	skips a row another sweep holds?
`transaction`	`SELECT … FOR UPDATE SKIP LOCKED`, then `UPDATE`, then a re-select — one transaction	yes (MySQL 8.0+)
nothing	one `UPDATE d JOIN (SELECT id … ORDER BY … LIMIT ?) s … WHERE d.status = 'pending' OR lease expired`	no, but it never double-claims

The lock-free form works because InnoDB evaluates the outer WHERE against the row after locking it, so a row another sweep won in the meantime fails the predicate and is left alone. Under real contention two such statements can deadlock; InnoDB rolls one back and asks for a retry, and the adapter retries it, bounded and jittered. Pass transaction in production: it is what the rowLock conformance case exercises, and SKIP LOCKED never waits.

Creating the tables

createMysqlTables(query, { plugins? }) renders and runs CREATE TABLE IF NOT EXISTS for the core tables and any plugin schemas you pass, safe on every boot. Or take the statements yourself with renderMysqlDdl from easy-ping/schema.

Two MySQL-specific choices in that DDL:

Indexes are declared inline in CREATE TABLE, because MySQL has no CREATE INDEX IF NOT EXISTS and the bootstrap has to stay re-runnable.
String columns are sized to InnoDB's 3072-byte key limit. A string in a single-column key or index is VARCHAR(768) (push endpoints can be long); one in a composite key is VARCHAR(255); one with a default is VARCHAR(255) because TEXT cannot carry one; everything else is TEXT. Booleans are TINYINT(1), dates DATETIME(3), JSON is JSON.

Bootstrap, not migration

There is no planMysqlMigration yet. A later version that adds a column will need that column added by hand on MySQL until there is.

What to know

affectedRows counts rows changed, not rows matched, unless the pool sets the FOUND_ROWS flag. The library counts with a SELECT where it needs a matched count (markRead), so its own behaviour is right either way; PluginStore.update returns what the driver says.
MariaDB works. The upsert uses VALUES() rather than MySQL 8.0.19's AS new alias for exactly that reason. SKIP LOCKED needs MariaDB 10.6+.
Dates come back as Date with timezone: "Z", or as strings with dateStrings: true, which the adapter parses as UTC. Any other timezone setting shifts every timestamp.