MySQL Dialect

MySQL Dialect The MySQL dialect targets MySQL 5.7 and 8.0+ servers (and compatible forks). It is implemented in mysql on top of the mysql2 driver, using its promise API and a connection pool. Overview Dialect name: mysql Underlying library: mysql2 (mysql2/promise) Identifier quoting: backticks — table.column Placeholders: positional ? Pooling: always on (mysql.createPool), sized from pool.max (default 10) Upsert: INSERT ... ON DUPLICATE KEY UPDATE Auto-increment: AUTOINCREMENT columns + LASTINSERTID() Full-text search: MATCH(...) AGAINST(...) over FULLTEXT indexes MySQL organizes objects into databases, not schemas. Schema-oriented methods (createSchema, dropSchema, showAllSchemas) intentionally throw and point you at the CREATE DATABASE / DROP DATABASE / SHOW DATABASES equivalents; listSchemas() runs SHOW DATABASES. Connection Pass dialect: 'mysql' to the Prorm constructor along with standard connection fields. The default host is localhost and the default port is 3306. You can also construct the dialect directly when you only need the query builder / raw execution surface: Connection options Option | Default | Notes | --- | --- | --- | host | localhost | | port | 3306 | | username / password / database | — | Passed to mysql2 as user / password / database. | charset | utf8mb4 | Full Unicode incl. emoji. | timezone | Z | UTC. | ssl | — | Passed straight through to mysql2. | pool | { max: 10 } | pool.max sets the mysql2 connectionLimit. | retry | see below | Connection/query retry policy. | connectTimeout / idleTimeout | — | Forwarded to mysql2 when set. | Both connect() and query() retry on transient failures (ECONNREFUSED, ETIMEDOUT, Too many connections, Lock wait timeout, deadlocks, etc.). By default up to 3 attempts with a fixed 1s delay plus jitter; enable retry.backoff for exponential backoff. Placeholders MySQL uses positional ? placeholders, and the builder methods emit them. Values travel out-of-band in the values array — never string-interpolate user input. Raw SQL fragments can be embedded with a Literal, which is inlined verbatim (and therefore not escaped) instead of becoming a ?: Data types getDataTypeSql() maps the ORM's DataTypes to MySQL column types: ORM type | MySQL column | --- | --- | STRING(n) | VARCHAR(n) (default 255) | CHAR(n) | CHAR(n) | TEXT | TEXT / MEDIUMTEXT / LONGTEXT (by length) | INTEGER | INT (or TINYINT / SMALLINT / MEDIUMINT / BIGINT by length), UNSIGNED supported | BIGINT | BIGINT | FLOAT / DOUBLE / DECIMAL(p,s) | FLOAT / DOUBLE / DECIMAL(p,s) | BOOLEAN | TINYINT(1) | DATE | DATETIME (DATETIME(p) with precision) | DATEONLY / TIME | DATE / TIME | BLOB | BLOB (TINYBLOB / MEDIUMBLOB / LONGBLOB) | ENUM / SET | ENUM(...) / SET(...) | JSON / JSONB | JSON (MySQL has no separate JSONB) | UUID | CHAR(36) | BIT / YEAR | BIT(n) / YEAR | GEOMETRY, POINT, POLYGON, ... | native spatial types, optional SRID | BOOLEAN is stored as TINYINT(1); booleans are emitted as the TRUE / FALSE keywords (not 1 / 0) so comparisons against JSON booleans extracted with JSONEXTRACT() behave correctly. Creating tables and AUTOINCREMENT AUTOINCREMENT is the MySQL identity mechanism. Mark a column autoIncrement: true (typically with primaryKey: true); after an insert, LASTINSERTID() returns the generated value. Table options include engine, charset, collate, comment, initialAutoIncrement, and rowFormat. Column definitions support CHARACTER SET / COLLATE, INVISIBLE columns (MySQL 8.0+), inline REFERENCES, and comments. Upsert: ON DUPLICATE KEY UPDATE MySQL's upsert is INSERT ... ON DUPLICATE KEY UPDATE. It fires whenever a row would violate any PRIMARY KEY or UNIQUE constraint — you cannot scope it to a specific set of conflict columns the way PostgreSQL's ON CONFLICT (cols) does. The builder uses the row-alias form (AS newvals) rather than the deprecated VALUES(col) function form removed in newer MySQL: If updateOnDuplicate is omitted, every inserted column is updated on conflict. buildInsertQuery(..., { upsert: true }) produces the same clause inline. RETURNING is not supported. MySQL has no RETURNING clause, so passing returning to buildUpsertQuery throws rather than emitting invalid SQL. Re-select the row afterward (e.g. via LASTINSERTID() or the known unique key). Full-text search: MATCH ... AGAINST Create a FULLTEXT index, then query it with MATCH(...) AGAINST(...). Full-text indexes require InnoDB or MyISAM and only apply to CHAR / VARCHAR / TEXT columns. buildMatchAgainst supports mode: 'natural' (default, IN NATURAL LANGUAGE MODE) and mode: 'boolean' (IN BOOLEAN MODE), plus query-expansion modifiers. In WHERE objects the $match operator produces a parameterized MATCH(...) AGAINST(? ...) predicate: Querying, bulk insert and JSON query() returns { rows, rowCount, fields }. SELECT / SHOW / DESCRIBE / EXPLAIN populate rows; other statements report affectedRows as rowCount. For large result sets, queryStream() returns a Node Readable backed by mysql2's streaming API. bulkInsert() batches many rows into one multi-row INSERT: MySQL's JSON type is well supported via helpers such as buildJsonExtract (JSONUNQUOTE(JSONEXTRACT(...))), buildJsonContains, buildJsonSet, buildJsonTable (8.0+), and the JSONARRAYAGG / JSONOBJECTAGG aggregates. OFFSET without LIMIT is emitted as LIMIT 18446744073709551615 OFFSET n, since MySQL requires a LIMIT alongside OFFSET. Transactions and savepoints MySQLTransaction wraps a dedicated pooled connection and supports commit() / rollback() plus nested SAVEPOINT operations (createSavepoint, rollbackToSavepoint, releaseSavepoint). Partitioning MySQL supports RANGE, LIST, HASH, and KEY partitioning declared in CREATE TABLE. createPartitionedTable emits RANGE COLUMNS / LIST COLUMNS so non-integer partition columns (dates, strings) work, and addPartition / dropPartition manage partitions on an existing table. dropPartition will look up the parent table from INFORMATIONSCHEMA.PARTITIONS if you don't pass parentTable. Limitations No schemas. MySQL uses databases; the schema methods throw with guidance toward CREATE DATABASE / SHOW DATABASES. No RETURNING. Re-fetch after insert/upsert (LASTINSERTID()). No DETACH PARTITION. detachPartition throws; use DROP PARTITION. No Foreign Data Wrappers. createUserMapping / dropUserMapping and related FDW methods throw (PostgreSQL-only features). Upsert conflict scope. ON DUPLICATE KEY UPDATE reacts to any unique-key collision and cannot be limited to specific conflict columns. JSONB maps to JSON. MySQL has a single native JSON type. For a MySQL-compatible distributed database, see the TiDB dialect, which shares much of this SQL surface. Related reading Running mysql in Docker — get one going locally All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL