Azure Database for MySQL Dialect

Reference for the azuremysql SQL dialect (Azure Database for MySQL flexible
server).

Source: azuremysql
Tests: azuremysql

Overview

Flexible server
is community MySQL run by Azure, reached with mysql2, so AzureMySQLDialect
extends MySQLDialect and models only authentication, TLS and the
managed-service limits.

Property	Value
Dialect name	`azuremysql`
Extends	`MySQLDialect`
Driver	`mysql2`
Optional SDK	`@azure/identity` (Entra tokens)
Default port	3306
Cloud SDK	`@azure/identity` (Entra only)
Local runtime	none — hosted service only

Quick start

Install

$ npm install ts-prorm-orm mysql2

$ pnpm add ts-prorm-orm mysql2

$ yarn add ts-prorm-orm mysql2

$ bun add ts-prorm-orm mysql2

mysql2 is the same driver the mysql dialect uses. Add @azure/identity only for the token-based logins under Managed and cloud variants.

Connect

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'azuremysql',
  host: 'mydb.mysql.database.azure.com',
  database: 'app',
  username: 'app',
  password: process.env.DB_PASSWORD,
});

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'azuremysql',
  url: 'azuremysql://app:secret@mydb.mysql.database.azure.com:3306/app',
});

A URL is folded into host, port, database, username and password before the dialect is built; any of those passed alongside it wins.

Define a model

import { DataTypes } from 'ts-prorm-orm';

const User = db.define('User', {
  id: { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  email: { type: DataTypes.STRING(255), allowNull: false, unique: true },
  name: { type: DataTypes.STRING(100) },
});

await db.sync(); // CREATE TABLE — see Generated SQL below

Query

import { Op } from 'ts-prorm-orm';

await User.create({ email: 'ada@example.com', name: 'Ada' });

const page = await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

Managed and cloud variants

One tab per way of logging in. The token-based ones fetch a short-lived token for every new pooled connection, so a pool that outlives a token keeps working; Cloud authentication covers the providers, caching and TLS rules behind them.

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'azuremysql',
  host: 'mydb.mysql.database.azure.com',
  database: 'app',
  username: 'ana@contoso.com', // the Entra principal
  dialectOptions: {
    entra: true, // DefaultAzureCredential: env, workload/managed identity, Azure CLI
  },
});

Needs @azure/identity installed next to the driver.

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'azuremysql',
  host: 'mydb.mysql.database.azure.com',
  database: 'app',
  username: 'ana@contoso.com',
  dialectOptions: {
    entra: { credential: { type: 'managed-identity', clientId: process.env.AZURE_CLIENT_ID } },
  },
});

Needs @azure/identity installed next to the driver.

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'azuremysql',
  host: 'mydb.mysql.database.azure.com',
  database: 'app',
  username: 'ana@contoso.com',
  dialectOptions: {
    entra: {
      credential: {
        type: 'client-secret',
        tenantId: process.env.AZURE_TENANT_ID!,
        clientId: process.env.AZURE_CLIENT_ID!,
        clientSecret: process.env.AZURE_CLIENT_SECRET!,
      },
    },
  },
});

Needs @azure/identity installed next to the driver.

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'azuremysql',
  host: 'mydb.mysql.database.azure.com',
  database: 'app',
  username: 'app',
  password: process.env.DB_PASSWORD,
});

Generated SQL

Each block is the statement the model call above it hands the dialect's query(), captured with the driver stubbed out. The model layer inlines the values.

Create table

db.sync() for the model in the Quick start:

CREATE TABLE

Insert, update and delete

await User.create({ email: 'ada@example.com', name: 'Ada' });
await User.update({ name: 'Grace' }, { where: { email: 'ada@example.com' } });
await User.destroy({ where: { id: 1 } });

Upsert

await User.upsert({ id: 1, email: 'ada@example.com', name: 'Ada Lovelace' });

upsert() also reads the row by primary key before and after the write, to return the instance.

Pagination

await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

JSON

[!NOTE] No JSON operator in the WHERE builder
DataTypes.JSON() maps to JSON, but this dialect's WHERE builder refuses Op.contains on it: Cannot render WHERE condition at meta.contains' is not implemented by its WHERE builder. The statement was not sent, because running it without the condition would match rows it should not.

Transaction with a savepoint

await db.transaction(async (t) => {
  await User.create({ email: 'grace@example.com', name: 'Grace' }, { transaction: t });
  // A nested transaction is a SAVEPOINT; its failure undoes only its own work.
  await db
    .transaction(async (sp) => {
      await User.destroy({ where: { id: 1 }, transaction: sp });
      throw new Error('keep the row');
    }, { transaction: t })
    .catch(() => {});
});

Skip locked rows (`SKIP LOCKED`)

A worker that claims rows from a queue table should pass over rows another transaction holds rather than wait for them. lock with skipLocked inside a transaction:

await db.transaction(async (t) => {
  await User.findAll({ where: { name: 'Ada' }, lock: { level: 'UPDATE', skipLocked: true }, transaction: t });
});

Type mapping for the portable descriptors: STRING → VARCHAR(255), auto-increment INTEGER → INT, BOOLEAN → TINYINT(1), DATE → DATETIME, UUID → CHAR(36), JSON → JSON. The type comparison lines the major engines up side by side.

Connection

import { Prorm } from 'ts-prorm-orm';

new Prorm({
  dialect: 'azuremysql',
  host: 'mydb.mysql.database.azure.com',
  database: 'app',
  username: 'ana@contoso.com', // or the Entra group name
  dialectOptions: {
    entra: true, // or { credential: {...} }, or tokenProvider
  },
});

new Prorm({
  dialect: 'azuremysql',
  host,
  database,
  username: 'admin',
  password: '…',
});

An Entra login sends an access token (scope
https://ossrdbms-aad.database.windows.net/.default) as a clear-text password
over TLS — the server switches to mysql_clear_password. With entra, the
dialect builds entraTokenProvider({ target: 'mysql-flexible' }) and its
resolvePoolConfig override spreads mysqlAuth() into
the pool config: an async mysql_clear_password auth plugin that fetches a
current token for each new connection. password is removed and
enableCleartextPlugin is not set. Microsoft will accept only
ossrdbms-audience tokens from January 2027.

TLS. require_secure_transport is ON by default. When ssl is not given
the dialect verifies the certificate with Node's trust store
({ rejectUnauthorized: true }). With Entra, construction throws if ssl is
not verified, if username is missing, or if password is also set; the
plugin itself refuses a connection that ends up without TLS.

What differs from MySQL

AzureMySQLDialect extends MySQLDialect (mysql) and
overrides 8 of its methods (Object.getOwnPropertyNames(AzureMySQLDialect.prototype),
computed by the scratch test); everything else is inherited unchanged.

Area	Overridden
Connection and execution	`query`, `resolvePoolConfig`
DDL and schema	`buildCreateAadUserSql`, `buildGrantQuery`, `createPartitionedTable`, `createTable`
Introspection	`assertEngine`, `assertSupportedStatement`

Limits, refused by name

Call	Why
`createTable` / `createPartitionedTable` with engine `MyISAM`, `BLACKHOLE`, `ARCHIVE`, `FEDERATED`	Only InnoDB and MEMORY are supported
`buildGrantQuery` with `SUPER`, `FILE`, `CREATE TABLESPACE`, `SHUTDOWN`	Restricted static privileges
`SELECT … INTO OUTFILE` through `query()`	No file-system access
`LOAD DATA INFILE` without `LOCAL` through `query()`	Only `LOAD DATA LOCAL INFILE`

The query() checks ignore literals and comments and can be turned off with
statementGuard: false.

Entra users

d.buildCreateAadUserSql('user1@yourtenant.onmicrosoft.com');
// CREATE AADUSER 'user1@yourtenant.onmicrosoft.com'
d.buildCreateAadUserSql('userWithLongName@yourtenant.onmicrosoft.com', 'shortName');
// CREATE AADUSER '…' AS 'shortName'   — required over 32 characters
d.buildCreateAadUserSql('Prod_DB_Readonly'); // a group

Run it as the Entra administrator, then GRANT privileges separately.

Caveats / not-yet-verified

Tests mock mysql2; nothing has run against a live server.

DEFINER clauses (which need SUPER) and writes to the read-only mysql
system schema are left to the server's errors.

Whether the server offers mysql_clear_password for an Entra user
immediately in the handshake, or via an auth switch, is as described in
cloud-auth; it has not been observed live here.

Capabilities

Computed from the code — capabilitiesOf() (src/transaction-support.ts),
whereSupportOf() (src/query-builders/expression-render.ts),
sqlObjectCapabilitiesOf() (src/sql-objects/capabilities.ts) and the
builders — by the scratch test described in the
capability matrix.

Capability	`azuremysql`
Transactions atomic	yes
Savepoints	yes
Isolation	up to `SERIALIZABLE`
Transaction modes	read-only yes; deferrable no; constraint deferral no
RETURNING	not emitted; `returning` is accepted and ignored
Upsert	`INSERT … ON DUPLICATE KEY UPDATE`
JSON column	`JSON` (`JSONB` → `JSON`)
Vector column	`VECTOR(3)`
Array column	`VARCHAR(255)`
WHERE builder	28 operator keys plus the `$json:` prefix; `$and`/`$or`/`$not`; `$exists`: no; `$in` subquery: no; expression operands: no
SQL objects	family `mysql`: triggers yes; procedures yes; functions `multiStatement`; materialized views emulated (`procedure`); sequences no; enums `inline`; RLS no
Streaming	`queryStream()` present; `LISTEN` absent; `createExtension()` present

Set up Microsoft Entra authentication

Limitations

Cloud authentication, MySQL dialect

Azure Database for MySQL Dialect

Overview#

Quick start#

Install#

Connect#

Define a model#

Query#

Managed and cloud variants#

Generated SQL#

Create table#

Insert, update and delete#

Upsert#

Pagination#

JSON#

Transaction with a savepoint#

Skip locked rows (SKIP LOCKED)#

Connection#

What differs from MySQL#

Limits, refused by name#

Entra users#

Caveats / not-yet-verified#

Capabilities#

Related reading#

Overview

Quick start

Install

Connect

Define a model

Query

Managed and cloud variants

Generated SQL

Create table

Insert, update and delete

Upsert

Pagination

JSON

Transaction with a savepoint

Skip locked rows (`SKIP LOCKED`)

Connection

What differs from MySQL

Limits, refused by name

Entra users

Caveats / not-yet-verified

Capabilities

Related reading