Amazon RDS for MariaDB Dialect

Overview

Amazon RDS for MariaDB
is MariaDB Community Server run by AWS on 3306. RdsMariaDBDialect extends
MariaDBDialect (driver mariadb) and adds verified TLS against the RDS CA
bundle, the SUPER refusals and the mysql.rds_* builders.

Property	Value
Dialect name	`rdsmariadb`
Extends	`MariaDBDialect`
Driver	`mariadb`
Cloud SDK	none
Default port	3306
Local runtime	none — hosted service only

Quick start

Install

$ npm install ts-prorm-orm mariadb

$ pnpm add ts-prorm-orm mariadb

$ yarn add ts-prorm-orm mariadb

$ bun add ts-prorm-orm mariadb

mariadb is the same driver the mariadb dialect uses.

Connect

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

export const db = new Prorm({
  dialect: 'rdsmariadb',
  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: 'rdsmariadb',
  url: 'rdsmariadb://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) },
  meta: { type: DataTypes.JSON() },
});

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

Password login only: the Quick start above is the whole story.

[!CAUTION] AWS IAM (iamAuth, tokenProvider) is refused
RDS for MariaDB: IAM database authentication is not available through this dialect. MariaDBDialect has no resolvePoolConfig hook and the mariadb connector has no per-connection password callback, so an IAM token (valid 15 minutes) would break every pooled connection opened after it expires. Use a password, or RdsMySQLDialect (mysql2), which supports IAM against RDS for MariaDB. The dialect throws at construction.

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

Containment on the meta column:

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

await User.findAll({ where: { meta: { [Op.contains]: { plan: 'pro' } } } });

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 → BOOLEAN, DATE → DATETIME, UUID → CHAR(36), JSON → JSON. The type comparison lines the major engines up side by side.

Connection

Password login. With caBundle (or PRORM_RDS_CA_BUNDLE) and no explicit
ssl, the connection verifies the server certificate against the RDS CA
bundle. An explicit ssl is kept.

MariaDBDialect has no resolvePoolConfig hook: it builds its pool from a
private settings object when connect() runs. RdsMariaDBDialect.connect()
writes the TLS value into that object before calling the base; a test guards
the hand-off.

IAM authentication is refused

RDS for MariaDB supports IAM database authentication, but this dialect refuses
iamAuth and tokenProvider at construction:

the base dialect has no pool hook, and

the mariadb connector takes password only as a string fixed when the pool
is created (no per-connection callback), so a 15-minute token would break
every pooled connection opened after it expires.

Use a password, or RdsMySQLDialect (mysql2 speaks to MariaDB and supports
IAM) at the cost of MariaDB-only SQL. buildCreateIamUserQuery(user) still
builds the IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS' user for other
clients.

What differs from MariaDB

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

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

No `SUPER`

As on RDS for MySQL, the master user lacks SUPER, FILE, SHUTDOWN and
CREATE TABLESPACE; buildGrantQuery refuses them and ALL on *.*.

Builder	SQL
`buildKillSql(id)` / `killConnection(id)`	`CALL mysql.rds_kill(id)`
`buildKillQuerySql(id)` / `killQuery(id)`	`CALL mysql.rds_kill_query(id)`
`buildKillQueryIdSql(queryId)`	`CALL mysql.rds_kill_query_id(queryId)` (MariaDB only; `QUERY_ID` from `information_schema.PROCESSLIST`)
`buildSetBinlogRetentionSql(hours \| null)`	`CALL mysql.rds_set_configuration('binlog retention hours', n)`; no upper bound is applied
`buildShowConfigurationSql()`	`CALL mysql.rds_show_configuration`
`buildStartReplicationSql()` / `buildStopReplicationSql()` / `buildSkipReplicationErrorSql()`	`mysql.rds_start_replication` / `rds_stop_replication` / `rds_skip_repl_error`

mysql.rds_start_replication_until is not available on MariaDB.

Family and wiring

MySQL family (as mariadb), lineage rdsmariadb → mariadb.

URL schemes suggested: rdsmariadb://, rds-mariadb://.

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	`rdsmariadb`
Transactions atomic	yes
Savepoints	yes
Isolation	up to `SERIALIZABLE`
Transaction modes	read-only no; deferrable no; constraint deferral no
RETURNING	`RETURNING` on INSERT
Upsert	`INSERT … ON DUPLICATE KEY UPDATE`
JSON column	`JSON` (`JSONB` → `JSON`)
Vector column	`VECTOR(3)`
Array column	`VARCHAR(255)`
WHERE builder	37 operator keys plus the `$json:` prefix; `$and`/`$or`/`$not`; `$exists`: yes; `$in` subquery: yes; expression operands: yes
SQL objects	family `mariadb`: triggers yes; procedures yes; functions `multiStatement`; materialized views emulated (`procedure`); sequences yes; enums `inline`; RLS no
Streaming	`queryStream()` present; `LISTEN` absent; `createExtension()` present

Testing

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

Amazon RDS for MariaDB 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 authentication is refused#

What differs from MariaDB#

No SUPER#

Family and wiring#

Capabilities#

Testing#

See also#