Amazon RDS for MySQL Dialect

Overview

Amazon RDS for MySQL
is MySQL Community Edition run by AWS, reached with mysql2 on 3306.
RdsMySQLDialect extends MySQLDialect and changes only IAM
authentication, TLS, and the missing SUPER privilege.

Property	Value
Dialect name	`rdsmysql`
Extends	`MySQLDialect`
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 mysql 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: 'rdsmysql',
  host: 'mydb.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',
  },
});

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

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

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: 'rdsmysql',
  host: 'mydb.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',
  },
});

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

export const db = new Prorm({
  dialect: 'rdsmysql',
  host: 'mydb.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
  },
});

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.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

Options are MySQL's plus iamAuth, tokenProvider, caBundle and
rdsProxy, with the same TLS rules as RDS for PostgreSQL.

IAM database authentication

const db = new Prorm({
  dialect: 'rdsmysql',
  host: 'mydb.abc123.us-east-1.rds.amazonaws.com',
  database: 'app',
  username: 'app_user',
  dialectOptions: { iamAuth: { region: 'us-east-1' }, caBundle: '/etc/ssl/rds/global-bundle.pem' },
});

Create the user with buildCreateIamUserQuery('app_user', { requireSSL: true }):

AWSAuthenticationPlugin makes the server switch the login to
mysql_clear_password. mysql2 has no password callback, so the dialect's
resolvePoolConfig override installs mysqlAuth()'s mysql_clear_password
auth plugin, which sends a current token for every new connection, and
removes the static password (otherwise mysql2 would hash and send it
first). The plugin refuses to send a token on a connection without TLS.

mysql2's built-in ssl: 'Amazon RDS' profile counts as verified TLS and is
kept as given.

What differs from MySQL

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

Area	Overridden
Connection and execution	`resolvePoolConfig`
DML builders	`buildSetBinlogRetentionSql`, `buildSetReplicationDelaySql`
DDL and schema	`buildCreateIamUserQuery`, `buildGrantQuery`
Other	`buildKillQuerySql`, `buildKillSql`, `buildShowConfigurationSql`, `buildSkipReplicationErrorSql`, `buildStartReplicationSql`, `buildStopReplicationSql`, `killConnection`, `killQuery`

No `SUPER`

The master user holds neither SUPER, FILE, SHUTDOWN nor

CREATE
TABLESPACE

, and a user can only grant what it holds.

Operation	Result
`buildGrantQuery` with `SUPER`, `FILE`, `SHUTDOWN`, `CREATE TABLESPACE`	refused; for `SUPER` the message points to the `mysql.rds_*` procedures
`buildGrantQuery` with `ALL` / `ALL PRIVILEGES` on `.`	refused (it includes them); `ALL` on a database is fine

The mysql.rds_* procedures replace the statements that need SUPER:

Builder	SQL
`buildKillSql(id)` / `killConnection(id)`	`CALL mysql.rds_kill(id)` (instead of `KILL id`)
`buildKillQuerySql(id)` / `killQuery(id)`	`CALL mysql.rds_kill_query(id)` (instead of `KILL QUERY id`)
`buildSetBinlogRetentionSql(hours \| null)`	`CALL mysql.rds_set_configuration('binlog retention hours', n)` — 1 to 168; `null` restores the default
`buildSetReplicationDelaySql('source delay' \| 'target delay', s)`	`CALL mysql.rds_set_configuration(…, s)` — 0 to 86400
`buildShowConfigurationSql()`	`CALL mysql.rds_show_configuration`
`buildStartReplicationSql()` / `buildStopReplicationSql()`	`CALL mysql.rds_start_replication` / `rds_stop_replication`
`buildSkipReplicationErrorSql()`	`CALL mysql.rds_skip_repl_error`

Ids are validated as positive integers and inlined; no builder takes raw SQL.
Global variables are changed in the DB parameter group, not with SET GLOBAL.

On RDS for MySQL 8.0.32 and lower (5.7.41 and lower), rds_kill and
rds_kill_query cannot end sessions of users whose names are longer than 16
characters.

Not covered

The Data API, the mysql.rds_set_external_* / GTID procedures for replication
from an external source, and the global status history procedures.

Family and wiring

MySQL family (triggers, fulltext, <=>, ON DUPLICATE KEY UPDATE). Name-keyed
family tables need rdsmysql added to behave like mysql.

URL schemes suggested: rdsmysql://, rds-mysql://.

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

Amazon RDS 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#

IAM database authentication#

What differs from MySQL#

No SUPER#

Not covered#

Family and wiring#

Capabilities#

See also#