MySQL and MariaDB

Connecting

Supply a mysql:// connection string. The same string shape reaches MariaDB: mysql2 is the driver for both, and mysql2 is a dependency of the ORM.

import { DBType, type DBConfig } from "stabilize-orm";

const dbConfig: DBConfig = {
  type: DBType.MySQL,
  connectionString:
    process.env.DATABASE_URL || "mysql://user:pass@localhost:3306/mydb",
  retryAttempts: 3,
  retryDelay: 1000,
};

export default dbConfig;

The CLI scaffolds the same file with --type mysql:

The pool is created in the DBClient constructor, and connections are taken from it for the duration of a transaction and released afterwards. The mysql2 pool is not the only thing here that behaves differently from the other dialects — unlike Postgres, the driver does not encode values for you, which is what the Parameter Binding section below covers.

What the Dialect Changes

Every statement is built once and rendered for the target dialect. For MySQL and MariaDB that rendering is:

Identifiers Are Backticked

MySQL and MariaDB are the only supported servers where " does not quote an identifier — it starts a string literal instead, unless the server runs with ANSI_QUOTES in sql_mode, which is off by default. CREATE TABLE "users" (…) is therefore a syntax error there, and backticks are the spelling that always parses. Every identifier autoMigrate emits goes through that quoting:

CREATE TABLE IF NOT EXISTS `users` (`id` INT AUTO_INCREMENT PRIMARY KEY, `email` VARCHAR(255) NOT NULL)

ALTER TABLE `users` ADD COLUMN `nickname` VARCHAR(255)

CREATE UNIQUE INDEX `users_email_uniq` ON `users` (`email`)

Note the third statement: MySQL has no IF NOT EXISTS clause on CREATE INDEX and no inline substitute for one, so autoMigrate reads the table's indexes first — through SHOW INDEX FROM `users` — and skips any name it finds. That pre-check is what makes autoMigrate idempotent on this dialect, and it runs on every dialect. Statements you write by hand have to do the same check yourself.

Writes and LAST_INSERT_ID()

There is no RETURNING on this dialect, so create() inserts plainly, asks the same connection for the generated key, and fetches the row back by id — three statements where Postgres needs one:

-- await userRepo.create({ email: "ada@example.com", name: "Ada" })
INSERT INTO users (email, name) VALUES (?, ?)
SELECT LAST_INSERT_ID()
SELECT * FROM users WHERE users.id = ? LIMIT 1

The key probe only runs when the database assigned the key. A model whose id is STRING or UUID is caller-supplied, so the value you passed is used as-is and only the read-back follows. LAST_INSERT_ID() is read on the same pooled connection that ran the insert; if either half of that pair were missing, the id would come back undefined.

bulkCreate() inserts every row in one statement, and the key it reads back is the first of the batch — SQLite reports the last, so the arithmetic that recovers the rest of the ids differs between the two.

Upsert with ON DUPLICATE KEY UPDATE

upsert(entity, keys) emits the MySQL form, which names no conflict target. The update list is bound as parameters a second time, so the payload appears twice in the parameter array — the EXCLUDED trick Postgres uses to avoid that has no equivalent here.

-- await userRepo.upsert({ email: "ada@example.com", name: "Ada" }, ["email"])
INSERT INTO users (email, name) VALUES (?, ?)
ON DUPLICATE KEY UPDATE name = ?

Because nothing is returned by the statement, the row is read back afterwards by the conflict keys, and it is also read before the write so the ORM knows whether this is an insert or an update — that answer decides which hooks run.

Row Limiting

limit() and offset() are emitted as written. A bare OFFSET is not legal here, so an offset with no limit is padded with the largest signed 64-bit value rather than being rejected or silently dropped.

-- repo.find().limit(10).offset(20)
SELECT * FROM users
LIMIT 10 OFFSET 20

-- repo.find().offset(20) -- no limit set
SELECT * FROM users
LIMIT 9223372036854775807 OFFSET 20

-- repo.find().paginate(2, 25) composes the two: offset = (page - 1) * pageSize
SELECT * FROM users
LIMIT 25 OFFSET 25

Parameter Binding

MySQL is one of the two dialects that takes the library's ? placeholders exactly as written — nothing is renumbered, so the parameters bind in the order they appear.

Values, however, are not passed through untouched. mysql2 does not serialise an object to JSON the way pg does: it reads a plain object as a set of assignments, so { nested: 1 } binds as `nested` = 1. Meaningful in an UPDATE … SET list, a syntax error in a VALUES list — the server either finds the column count no longer matches (Column count doesn't match value count at row 1) or reads the object's own keys as column names (Unknown column 'nested' in 'field list'). The ORM therefore JSON-encodes every plain object and array before the driver sees it, which is what makes a DataTypes.JSON column writable with the object itself.

A Date is normalised for this dialect too, and the spelling is not cosmetic: a DATETIME column under the default STRICT_TRANS_TABLES refuses the T and the Z of an ISO string with Incorrect datetime value. Dates are therefore bound space-separated and at second resolution, which the whole MySQL family accepts and which is all a DATETIME column stores by default.

// repo.create({ createdAt: new Date("2024-01-15T10:30:00Z") })

// MySQL / MariaDB -- space separator, seconds, no zone:
//   "2024-01-15 10:30:00"

// PostgreSQL, SQLite and SQL Server bind the ISO form instead:
//   "2024-01-15T10:30:00.000Z"

Type Mapping

Every DataTypes member maps to a MySQL type. Note the unpinned precision on FLOAT and DOUBLE, the default DECIMAL(10,2), and that neither BOOLEAN nor UUID is a real type here. The widths shown are the defaults: length widens STRING, precision and scale size DECIMAL, and on FLOAT — the one dialect where a single-precision column takes a bit width — precision gives you FLOAT(n).

• BOOLEAN is TINYINT(1) — there is no real boolean in either MySQL or MariaDB. The driver hands the column back as the number 1 or 0, never as true/false; it is boolean by convention only. The generated toggle (SET col = NOT col) relies on that numeric truthiness.
• UUID is CHAR(36), so it round-trips as the plain string you supplied. STRING is VARCHAR(255) whatever length you declare; the mapper does not read it.
• A BLOB column comes back as a Buffer, not as a string.

MariaDB

MariaDB is served by the same dialect, and the dialect never branches on the server version — SELECT VERSION() answers with a string containing MariaDB, not a MySQL one, so a version check would not be a safe place to fork the behaviour anyway. Two consequences are worth knowing:

• A DataTypes.JSON column is an alias for LONGTEXT with a validity check attached, not a real type. The column is reported as longtext by information_schema, and the driver hands the value back as a string rather than a parsed object. See Data Types.
• MariaDB accepts INSERT … RETURNING, which MySQL does not. The dialect still emits neither: writes keep the ON DUPLICATE KEY UPDATE and LAST_INSERT_ID() path described above.

Transactions

A transaction takes a connection out of the pool, opens it with START TRANSACTION, and commits or rolls back on that same connection before releasing it back. The ORM does that wiring for you, so the callback looks the same as on every other dialect.

await orm.transaction(async (txClient) => {
  const userRepo = orm.getRepository(User);
  const profileRepo = orm.getRepository(Profile);

  const user = await userRepo.create({ email: "ada@example.com" }, {}, txClient);
  await profileRepo.create({ userId: user.id }, {}, txClient);
});

Limitations