MySQLSupported

@zmdb/mysql is the complete MySQL vertical: its immutable dialect object owns compilation, DDL and migrations, catalog introspection, capabilities and the structural mysql2/promise adapter. Row-returning repository writes remain deliberately narrower: create, ordinary update, and ordinary upsert refuse because MySQL cannot satisfy their returned-entity contract in one statement.

Database-selection workflow#

The six official database packages use the same selection workflow. The package reference owns current install and peer ranges; the MySQL package README includes the standalone TypeScript setup and full capability table.

A hosted-service connection guide is a recipe using one of these owners or an explicitly supplied structural adapter. Protocol compatibility alone does not create another official package or transfer the recorded server qualification to that service.

Selecting it#

import { mysql, mysqlDriver } from '@zmdb/mysql';
import { createQueryCompiler } from '@zmdb/sql';

const driver = mysqlDriver(pool);
const compiler = createQueryCompiler(mysql);
const userRepo = defineRepository(users, driver);

Install mysql2 in the application and pass an existing pool or connection. It is an optional peer, so importing @zmdb/mysql does not load or install a client.

What it emits#

import { trustedTable } from '@zmdb/sql';

compiler.selectFrom(trustedTable('users')).where('email', '=', 'a@b.c').compile();
// { text: 'SELECT * FROM `users` WHERE `email` = ?', parameters: ['a@b.c'], effects: { operation: 'SELECT', requiresPrimary: false, returnsRows: true } }

No RETURNING#

The compiler refuses every MySQL returning() request before producing a CompiledQuery:

returning is not supported for INSERT on dialect "mysql"; omit returning() and perform an explicit read

The capability is declared separately for INSERT, upsert, UPDATE, and DELETE. That distinction can represent an INSERT-only MariaDB dialect later without making MySQL emit syntax it does not support.

BaseRepository.create, an ordinary value-bearing update, and an ordinary upsert propagate that refusal before driver execution because their public return types promise a row. They neither emit invalid SQL nor silently resolve to undefined. Use a lower-level statement without returning() and then read by a known primary or unique key:

import { trustedTable } from '@zmdb/sql';

const result = await driver.executeResult(compiler.insertInto(trustedTable('users')).values(dto).compile());
if (result.kind !== 'command') throw new Error('expected command metadata');
console.log(result.insertId, result.affectedRows);
const row = await userRepo.findOne({ email: { eq: dto.email } });

execute() returns [] for that command; it never disguises command metadata as an entity row. The explicit executeResult() path plus the follow-up read is two round trips and bypasses repository write validation, so validate the payload before compiling. Selecting on a supplied unique value is safe across a pool; LAST_INSERT_ID() is connection-local and needs both statements pinned to the same connection.

Expression-valued repository writes have a narrower explicit contract: update(id, { count: inc(1) }), increment, every updateMany, and an expression-valued upsert update object omit unsupported RETURNING, execute one statement, and resolve to undefined. They do not issue a hidden follow-up SELECT.

boolean is TINYINT(1)#

MySQL has no boolean type, so Sql<'boolean'> becomes TINYINT(1) and comes back as 0 or 1, not false or true. mysql2 does not convert it for you. Fix it in the driver, where you know the schema is a MySQL one:

// per-column, explicit — a generic 0/1 coercion will mangle real integers
return rows.map(r => ({ ...r, active: Boolean(r.active) }));

Or use mysql2's typeCast to handle TINY columns with length 1 globally. Either way, decide it once: a numeric 0 is falsy, but it still differs from the boolean false promised by an application schema.

Case sensitivity#

LIKE is case-insensitive by default, because the default collation is utf8mb4_0900_ai_ci. So like and ilike behave the same, and code written against MySQL will start matching differently the day it runs on Postgres. If you need case-sensitive matching, that is a collation choice:

ALTER TABLE users MODIFY email VARCHAR(255) COLLATE utf8mb4_0900_as_cs;

Table names are case-sensitive on Linux and not on macOS/Windows, per lower_case_table_names. Use lowercase table names and this never matters.

utf8mb4, not utf8#

MySQL's utf8 is three bytes and cannot store an emoji or many CJK characters. Always utf8mb4:

CREATE DATABASE app CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

VARCHAR(n) also counts _characters_, but the index key limit counts bytes — so a utf8mb4 VARCHAR(768) is the practical maximum for a single-column index on InnoDB.

DDL is not transactional#

MySQL auto-commits DDL, so a migration with two ALTER TABLEs can leave the first applied and the second failed. Wrapping it in BEGIN/COMMIT does not help. One statement per migration on MySQL. See migrate.

The required acceptance lane packs the published artifact, installs it outside the workspace, and runs compilation, migrations, CRUD, transaction rollback and catalog round-trip against MySQL 8.4.11 configured with utf8mb4, utf8mb4_0900_ai_ci and strict SQL mode. The consumer selects mysql2; generic packages do not install it.

Connecting#

PlanetScale, TiDB, and any MySQL-compatible server. PlanetScale may have foreign keys disabled; generated constraints from References<…> need that support turned on before migration.

---

See also: Query Compiler · Connect: PlanetScale · Dialect: Postgres