Amazon Aurora MySQL Dialect

Overview

Aurora MySQL-Compatible Edition
speaks MySQL through mysql2 on 3306 and authenticates like RDS for MySQL, so
AuroraMySQLDialect extends RdsMySQLDialect: IAM through
AWSAuthenticationPlugin, verified TLS, the SUPER refusals and the
mysql.rds_* builders are inherited.

Property	Value
Dialect name	`auroramysql`
Extends	`RdsMySQLDialect`
Driver	`mysql2`
Cloud SDK	`@aws-sdk/rds-signer` (IAM only, optional)
Default port	3306
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 rdsmysql dialect uses. Add @aws-sdk/rds-signer only for the token-based logins under Managed and cloud variants.

Connect

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

export const db = new Prorm({
  dialect: 'auroramysql',
  host: 'app.cluster-abc123.us-east-1.rds.amazonaws.com',
  database: 'app',
  username: 'app_user',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    caBundle: '/etc/ssl/rds/global-bundle.pem',
    routeReads: true,
  },
});

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

export const db = new Prorm({
  dialect: 'auroramysql',
  url: 'auroramysql://app_user:secret@app.cluster-abc123.us-east-1.rds.amazonaws.com:3306/app',
  dialectOptions: {
    caBundle: '/etc/ssl/rds/global-bundle.pem',
    routeReads: true,
  },
});

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: 'auroramysql',
  host: 'app.cluster-abc123.us-east-1.rds.amazonaws.com',
  database: 'app',
  username: 'app_user',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    caBundle: '/etc/ssl/rds/global-bundle.pem',
    routeReads: true,
  },
});

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

export const db = new Prorm({
  dialect: 'auroramysql',
  host: 'app.cluster-abc123.us-east-1.rds.amazonaws.com',
  database: 'app',
  username: 'app_user', // granted rds_iam; no password
  dialectOptions: {
    iamAuth: { region: 'us-east-1' }, // or true: the default credential chain
    caBundle: '/etc/ssl/rds/global-bundle.pem', // a token is only sent over verified TLS
    routeReads: true,
  },
});

Needs @aws-sdk/rds-signer installed next to the driver.

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. $co n t ain s f or a u ror am ys ql : t h eo p er a t o r^{'}$ 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: endpoints and read routing

Same model as Aurora PostgreSQL: connect to
the cluster endpoint; the reader endpoint (.cluster-ro-) is derived or given
as readerEndpoint. With routeReads: true a single plain SELECT outside a
transaction (no FOR UPDATE, LOCK IN SHARE MODE, INTO, GET_LOCK,
LAST_INSERT_ID(), …) goes to a second pool on the reader endpoint, with its
own IAM token signed for that host. { useWriter: true } keeps a read on the
writer; queryReader(sql) always uses the reader.

readerReplicaConfig(...) hands the reader endpoint to ReplicaManager
(password only). Disable ReplicaManager's lag detection for it: its MySQL lag
probe is SHOW SLAVE STATUS, which is empty on an Aurora Replica, so the
replica would be marked unhealthy. Use getReplicaStatus() instead.

Aurora functions and tables

Builder	SQL
`buildAuroraVersionQuery()` / `getAuroraVersion()`	`SELECT AURORA_VERSION()` (e.g. `3.05.2`; `8.4.7` from Aurora MySQL 8.4)
`buildAuroraVersionVariableQuery()`	`SELECT @@aurora_version`
`buildIsReaderQuery()` / `isReader()`	`SELECT @@innodb_read_only` — `ON` on a reader, `OFF` on the writer
`buildReplicaStatusQuery()` / `getReplicaStatus()`	`information_schema.replica_host_status`: role (`MASTER_SESSION_ID` = writer) and `REPLICA_LAG_IN_MILLISECONDS`. Read it on the writer.
`buildGlobalDbStatusQuery()`	`information_schema.aurora_global_db_status` (3.04+)
`buildGlobalDbInstanceStatusQuery()`	`information_schema.aurora_global_db_instance_status` (3.04+)
`buildReplicaReadConsistencySql(mode)`	`SET aurora_replica_read_consistency = 'EVENTUAL' \| 'SESSION' \| 'GLOBAL'`
`buildSetBinlogRetentionSql(hours)`	up to 2160 hours (Aurora MySQL 2.11+ and 3)
`buildSetReplicationDelaySql(...)`	refused: Aurora's `rds_set_configuration` takes only `binlog retention hours`

Aurora MySQL has no aurora_replica_status() function (that is Aurora
PostgreSQL); replica_host_status is its equivalent.

Aurora Serverless v2

An instance class behind the same endpoints; nothing changes here. The Data
API is a separate dialect.

Global databases

Secondary Regions are read-only. With write forwarding enabled on a secondary
cluster, a session forwards writes only after it sets
aurora_replica_read_consistency (buildReplicaReadConsistencySql); DDL,
LOAD DATA into permanent tables, XA and SAVEPOINT are not forwarded, and
only REPEATABLE READ is supported. The dialect does not refuse writes on a
secondary: that depends on cluster configuration it cannot see reliably.

Family and wiring

MySQL family, lineage auroramysql → rdsmysql → mysql.

URL schemes suggested: auroramysql://, aurora-mysql://.

What differs from RdsMySQL

AuroraMySQLDialect extends RdsMySQLDialect (rdsmysql) and
overrides 16 of its methods (Object.getOwnPropertyNames(AuroraMySQLDialect.prototype),
computed by the scratch test); everything else is inherited unchanged.

Area	Overridden
Connection and execution	`connect`, `disconnect`, `query`
DML builders	`buildSetReplicationDelaySql`
Introspection	`getAuroraVersion`, `getReplicaStatus`, `isReader`, `readerReplicaConfig`
Other	`buildAuroraVersionQuery`, `buildAuroraVersionVariableQuery`, `buildGlobalDbInstanceStatusQuery`, `buildGlobalDbStatusQuery`, `buildIsReaderQuery`, `buildReplicaReadConsistencySql`, `buildReplicaStatusQuery`, `queryReader`

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	`auroramysql`
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

Testing

tests/dialects/auroramysql.test.ts exercises the dialect with a mocked driver, so nothing touches a server.

Amazon Aurora 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: endpoints and read routing#

Aurora functions and tables#

Aurora Serverless v2#

Global databases#

Family and wiring#

What differs from RdsMySQL#

Capabilities#

Testing#

See also#