MySQL 8+

MySQL 8+ is the second-most-shipped dialect because of procurement: enterprises that standardized on MySQL want to stay there. The framework provides full feature parity with postgres — including binlog CDC for cross-instance reactivity (mysql and mariadb share one binlog reader) — plus a handful of driver quirks worked around transparently.

#Target version

MySQL 8.0.18 or later. Two reasons:

1. LIMIT ? as a prepared-statement parameter was rejected with ER_WRONG_ARGUMENTS in 8.0.0–8.0.17. The framework inlines integer literals (validated as non-negative) via sql.unsafe(String(n)) to work around this, but other tooling you connect (BI tools, ORMs) may break.
2. JSON_OBJECT + JSON_ARRAYAGG semantics stabilized in 8.0.14. The framework's JSON-agg eager-load compiler depends on these.

MySQL 5.7 is not supported — the JSON eager loads would fail.

#Configuration

DB_DIALECT=mysql
DB_URL=mysql://app:app@db.internal:3306/app
# …or discrete fields — the same DB_* vars every dialect uses
# (there are no MYSQL_* vars):
DB_HOST=…  DB_PORT=…  DB_USER=…  DB_PASSWORD=…  DB_DATABASE=…

# Strict mode is REQUIRED. Most modern MySQL deploys have it on by
# default; verify with `SELECT @@sql_mode;` — should contain
# `STRICT_TRANS_TABLES,STRICT_ALL_TABLES`. The framework's DDL
# emitter relies on strict-mode validation to surface schema bugs
# at migration time.

#Driver: @effect/sql-mysql2

Wraps the mysql2 driver (Node.js mysql client). The framework's MysqlStore opens a connection pool sized via DB_MAX_CONNECTIONS (default 10).

The driver returns BOOLEAN as 0|1 (TINYINT(1) is MySQL's underlying type). The framework's decodeRowsFromSchema post-processor converts back to true|false for any column the schema registry declares as boolean(). Without this, every reactive subscription opening on a BOOLEAN column would see 1 instead of true and fail framework Schema validators.

#No RETURNING — INSERT then SELECT

MySQL 8.x does not support RETURNING * on any DML statement. The framework's mysql store handles this with deterministic IDs + a follow-up SELECT:

// What you write:
await ctx.store.insert('todos', { title: 'hello', done: false })

// What the framework does on mysql (internally — `generateId` takes the
// table's RESOLVED scheme, not a bare table name):
const id = generateId(resolveIdScheme(undefined, 'todos'), 'todos')   // TypeID auto-injection
await sql`INSERT INTO todos (id, title, done) VALUES (${id}, ${title}, ${done})`
const [row] = await sql`SELECT * FROM todos WHERE id = ${id} LIMIT 1`
return row

This works because the framework's mutation middleware generates IDs client-side BEFORE the insert via the schema registry's TypeID / ULID / Snowflake generator. The follow-up SELECT is O(1) against the PK. Performance impact: one extra round-trip per write — meaningful for very high-throughput workloads but invisible for typical app traffic.

For UPDATE: UPDATE then SELECT by the known PK. For DELETE: SELECT before delete (to capture the row for the ChangeEvent) then DELETE.

#AUTO_INCREMENT ids — LAST_INSERT_ID() recovery

The write-then-SELECT above relies on knowing the PK before the insert — which the framework's client-side TypeID / ULID / Snowflake auto-injection guarantees on every framework path. The escape hatch is a hand-rolled numeric AUTO_INCREMENT primary key: a table you created yourself with id INT PRIMARY KEY AUTO_INCREMENT, inserted WITHOUT a client-side id. The store recovers the DB-generated id via LAST_INSERT_ID():

// A table with a numeric AUTO_INCREMENT PK (no framework id() column):
//   CREATE TABLE counters (id INT PRIMARY KEY AUTO_INCREMENT, label VARCHAR(64))

const row = await ctx.store.insert('counters', { label: 'hits' })
row.id            // ← the DB-generated id, recovered and returned

const rows = await ctx.store.insertMany('counters', [
  { label: 'a' }, { label: 'b' }, { label: 'c' },
])
rows.map((r) => r.id)   // ← the full generated range, in input order

LAST_INSERT_ID() is connection-scoped, so the INSERT, the id read, and the post-image SELECT are pinned to one connection (a short transaction when you're not already inside one). For insertMany, LAST_INSERT_ID() returns the FIRST generated id and the engine allocates the rest consecutively, so the store re-selects the [first, first+n-1] range. This works identically on mysql and mariadb. (If the PK is not actually AUTO_INCREMENT, the insert throws a clear error rather than returning a bogus id.)

#CDC — binlog (ROW format)

MySQL has no LISTEN/NOTIFY, but MySQL 8's ROW-format binary log is a real out-of-band CDC source — the SAME binlog the framework already tails for mariadb. The framework tails the primary's binlog via the @vlasky/zongji replication client, so every replica tails the binlog itself and a write on any instance surfaces on every instance's onChange (true cross-instance reactivity in a multi-replica deploy). This is at parity with mariadb: binlog CDC is one path with per-variant detection, not two implementations.

#How it works

1. With CDC=1 (the default for any SQL dialect) on mysql, the store starts a binlog reader on a separate replication connection — distinct from the SQL pool.
2. The reader subscribes to WriteRows / UpdateRows / DeleteRows events and turns each into a ChangeEvent { table, op, old, new }. UPDATE events carry both the BEFORE and AFTER row images (needs binlog_row_image=FULL), so old is richer than inline mode can produce.
3. In CDC mode the binlog reader is the sole emitter — the write path stays silent, so each write surfaces exactly once per instance, delivered by that instance's own reader.
4. Framework-internal _voltro_-prefixed tables (and any table not in the reactive set) are skipped by the reader.

#Requirements

The reader fails fast at boot if these aren't met:

• binlog_format=ROW — statement/mixed formats don't carry per-row images.
• binlog_row_image=FULL — needed for complete UPDATE/DELETE before-images (a non-FULL image logs a warning; before-images may be partial).
• log_bin=ON. MySQL 8.0+ enables this by default (unlike mariadb, which needs an explicit --log-bin), so most MySQL 8 deploys already have a binlog.
• A DB user with REPLICATION SLAVE, REPLICATION CLIENT.
• A UNIQUE server_id per reader. Duplicate server_ids silently break binlog streams. The framework derives one per pod from POD_NAME / HOSTNAME (falling back to the PID in dev).
• The @vlasky/zongji package. It ships as an optionalDependency of @voltro/sql-mysql; if it's absent, a cdc request throws a clear "install @vlasky/zongji" error rather than silently degrading.

#mysql-vs-mariadb difference: binlog-end query

The one engine divergence the store handles for you: MySQL 8.4 removed SHOW MASTER STATUS in favour of SHOW BINARY LOG STATUS. The framework resolves the current binlog end with the right statement per variant (SHOW BINARY LOG STATUS on mysql, SHOW MASTER STATUS on mariadb), falling back to the other spelling for mysql < 8.4. You don't wire anything for this — it's picked from the variant flag.

#Resume offsets

Each replica persists its progress in the _voltro_cdc_offsets table — one row per replica (PK = replicaId), holding the last binlog (file, position) it fully processed. On boot the reader resumes from that point; events between crash and resume replay and self-heal via the dispatcher's per-subscribe re-query. (Resume is by binlog file + position, not by GTID.)

If the persisted offset has been purged (err 1236) or rejected after a failover, the reader jumps to the current binlog end and signals a resync so dependent subscriptions re-query rather than missing the gap.

#Boot summary

[voltro:dev] sql dialect resolved: mysql — CDC: binlog CDC (ROW), RETURNING: INSERT/UPDATE/DELETE then SELECT (no RETURNING)
[voltro:dev] mysql binlog CDC enabled { replicaId: 'pod-0', serverId: 1234567, reactiveTables: 42 }
[voltro:dev] cdc: binlog reader attached { serverId: 1234567, from: 'current-end' }

Set CDC=0 to fall back to inline-emit (single-process only, no binlog dependency) — useful for tests and single-binary deploys.

#Parameterized LIMIT — auto-inlined

mysql2's prepared-statement layer rejects LIMIT ? with the cryptic error ER_WRONG_ARGUMENTS. The framework's compileSelect inlines integer literals across every dialect to dodge this:

-- What the framework emits:
SELECT * FROM users WHERE tenant_id = ? ORDER BY created_at DESC LIMIT 100

-- Not what would fail:
SELECT * FROM users WHERE tenant_id = ? ORDER BY created_at DESC LIMIT ?

The 100 is a literal sql.unsafe(String(100)). The framework validates non-negative integer before emitting → zero injection surface.

User-written unsafe() SQL on mysql should follow the same pattern: inline integer literals via sql.literal(String(n)) rather than ${n} parameter binding.

#DEFAULT on TEXT columns — VARCHAR(255) fallback

MySQL (strict mode) rejects DEFAULT values on TEXT/BLOB columns with BLOB, TEXT, GEOMETRY or JSON column 'col' can't have a default value. MariaDB allows this; MySQL does not.

The framework's DDL emitter detects the case and switches to VARCHAR(255):

text().default('json')                     // → VARCHAR(255) DEFAULT 'json'
text().oneOf(['a', 'b', 'c']).default('a') // → VARCHAR(255) DEFAULT 'a' CHECK (col IN ('a','b','c'))
text().nullable()                          // → TEXT (unchanged — no default to trip up)

VARCHAR(255) is the framework's heuristic — enough for typical enum-like values, short status strings, format identifiers. If you need longer defaulted text, declare the column as text().nullable() + handle the missing-default case in application code, OR drop down to unsafe().

#JSON columns

json() columns emit JSON (mysql's native binary JSON type since 5.7+). The driver auto-parses on read; same shape as postgres. No coercion overhead.

JSON_VALID is enforced by mysql — you can't insert non-JSON strings into a JSON column. Test fixtures that bypass the framework's validation will hit this.

#Identifier quoting

`users` — backticks. Standard MySQL. The framework's compiler emits these for every identifier; user-written unsafe() SQL should match.

#Workflow cluster

@effect/cluster's SqlRunnerStorage mysql branch uses GET_LOCK(name, timeout) for shard claims and ON DUPLICATE KEY UPDATE for runner upserts. Same end-to-end behaviour as postgres; takeover on runner death takes ~5–15s.

#Read replicas

The framework's mysql replication adapter reads GTID positions on both sides (@@global.gtid_executed on mysql) — but the catch-up comparison is a stub today: compare() always answers 'behind'. Under the default RYW_POLICY=fallback that's fine (fallback pins on RYW-position presence and never calls compare()); under RYW_POLICY=wait, a session with a pending RYW position always falls back to the primary instead of ever seeing a caught-up replica. A real GTID_SUBSET() round-trip is a tracked follow-up.

DB_REPLICA_URLS=mysql://app:app@replica-1:3306/app
RYW_POLICY=fallback

Set gtid_mode=ON + enforce_gtid_consistency=ON on every node in the topology — without GTIDs, the replication adapter can't track positions and the RYW guarantee silently degrades.

#Known caveats

• Mysql 8.4.x raised the default auth_plugin to caching_sha2_password. The mysql2 driver supports it but some older Node.js builds need the RSA-OAEP opt-in. If you hit ERR_OSSL_UNSUPPORTED, create the app's database user with mysql_native_password on the server (there is no framework env var for the auth plugin).
• DATETIME(6) for sub-second precision. The framework emits this for timestamp() columns; lower-precision DATETIME drops microseconds and your audit logs lose ordering on fast inserts.
• utf8mb4_unicode_ci collation is required for the framework's text columns to compare correctly. utf8 (legacy 3-byte) breaks emoji + non-BMP chars; charset checks fire at first insert.

#Where it lives

• voltro/packages/sql-mysql/src/store.ts — MysqlStore with variant: 'mysql'
• voltro/packages/sql-mysql/src/retry.ts — isRetryableMysqlFailure (1213 / 1205 deadlock + lock wait timeout)
• voltro/packages/sql-mysql/src/replicationAdapter.ts — GTID capture/probe for RYW (catch-up compare() is a stub)
• voltro/packages/database/src/migrate.ts — mysql DDL branch (line 59)
• voltro/packages/database/src/sqlCompiler.ts — integer-literal LIMIT inlining