Amazon Aurora PostgreSQL Dialect

Overview

Aurora PostgreSQL-Compatible Edition
speaks PostgreSQL through pg on 5432 and authenticates exactly like RDS for
PostgreSQL, so AuroraPostgresDialect extends
RdsPostgresDialect: IAM tokens, verified TLS against the
RDS CA bundle, the rds_superuser refusals and the extension allow-list are
inherited. What Aurora adds is the cluster: writer and reader endpoints, and
the Aurora function set.

Property	Value
Dialect name	`aurorapostgres`
Extends	`RdsPostgresDialect`
Driver	`pg`
Cloud SDK	`@aws-sdk/rds-signer` (IAM only, optional)
Default port	5432
Local runtime	none — hosted service only

Quick start

Install

$ npm install ts-prorm-orm pg

$ pnpm add ts-prorm-orm pg

$ yarn add ts-prorm-orm pg

$ bun add ts-prorm-orm pg

pg is the same driver the rdspostgres dialect uses. Add @aws-sdk/rds-signer only for the token-based logins under Managed and cloud variants. pg-copy-streams is optional: only the features that need it load it.

Connect

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

export const db = new Prorm({
  dialect: 'aurorapostgres',
  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, // plain SELECTs go to the reader endpoint
  },
});

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

export const db = new Prorm({
  dialect: 'aurorapostgres',
  url: 'aurorapostgres://app_user:secret@app.cluster-abc123.us-east-1.rds.amazonaws.com:5432/app',
  dialectOptions: {
    caBundle: '/etc/ssl/rds/global-bundle.pem',
    routeReads: true, // plain SELECTs go to the reader endpoint
  },
});

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.JSONB() },
});

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

meta is JSONB, which the containment query below needs.

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: 'aurorapostgres',
  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, // the reader pool signs its own token
  },
});

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

export const db = new Prorm({
  dialect: 'aurorapostgres',
  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, // the reader pool signs its own token
  },
});

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

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 → SERIAL, BOOLEAN → BOOLEAN, DATE → TIMESTAMP, UUID → UUID, JSON → JSON. The type comparison lines the major engines up side by side.

Connection: endpoints

Endpoint	Name	Reaches
Cluster	`<cluster>.cluster-<id>.<region>.rds.amazonaws.com`	the writer, and the new writer after a failover
Reader	`<cluster>.cluster-ro-<id>.<region>.rds.amazonaws.com`	Aurora Replicas, load-balanced per connection
Instance / custom	anything else	one instance / a chosen set

Connect host to the cluster endpoint. The reader endpoint is derived from it
(.cluster- → .cluster-ro-), or pass readerEndpoint.

Routing reads to the reader endpoint

dialectOptions: {
  routeReads: true;
} // readerEndpoint derived from host

With routeReads, a second pool (a second AuroraPostgresDialect, with its own
IAM token provider signed for the reader host) is opened on connect, and
query() sends a statement there only when it is a single plain SELECT,
outside a transaction, with no FOR UPDATE/SHARE, no INTO, and no function
that writes or holds session state (nextval, advisory locks, set_config, …).
WITH is never routed. Everything else — writes, DDL, transactions, and any
query with { useWriter: true } — stays on the writer.

Aurora Replicas are asynchronous: a read that must see this session's latest
commit should pass useWriter: true. queryReader(sql) sends one query to the
reader regardless of routeReads.

ReplicaManager

readerReplicaConfig({ username, password, database }) returns a
ReplicaConnectionConfig for the ORM's ReplicaManager. It refuses IAM
(ReplicaManager opens its pool with a static password and no TLS options). Its
generic PostgreSQL lag probe does not measure Aurora lag; use
getReplicaStatus().

Aurora functions

Builder	SQL
`buildAuroraVersionQuery()` / `getAuroraVersion()`	`SELECT aurora_version()` (e.g. `16.4.1`)
`buildInstanceIdentifierQuery()`	`SELECT aurora_db_instance_identifier()`
`buildReplicaStatusQuery()` / `getReplicaStatus()`	`aurora_replica_status()` with role (`MASTER_SESSION_ID` = writer) and `replica_lag_in_msec`
`buildIsWriterQuery()`	joins `aurora_replica_status()` to `aurora_db_instance_identifier()`
`buildGlobalDbStatusQuery()`	`SELECT * FROM aurora_global_db_status()`
`buildGlobalDbInstanceStatusQuery()`	`SELECT * FROM aurora_global_db_instance_status()`
`buildStatQuery(fn)`	`aurora_stat_system_waits`, `aurora_stat_wait_event`, `aurora_stat_wait_type`, `aurora_stat_memctx_usage`
`buildSystemWaitsQuery()`	system waits joined to wait type/event names
`buildBackendWaitsQuery(pid)`	`aurora_stat_backend_waits($1)` with names
`buildWriteForwardConsistencySql(mode)`	`SET apg_write_forward.consistency_mode = 'SESSION' \| 'EVENTUAL' \| 'GLOBAL' \| 'OFF'`

Aurora Serverless v2

Serverless v2 is an instance class: its instances sit behind the same cluster
and reader endpoints and take the same connections. Nothing changes in this
dialect. The Data API is a separate dialect.

Global databases

A global database has one primary Region and up to 10 read-only secondary
Regions. Connect writers to the primary cluster (or the global writer
endpoint); a secondary cluster's endpoints serve reads. With write forwarding
enabled on a secondary cluster, DML is forwarded to the primary; set the
session's consistency with buildWriteForwardConsistencySql. DDL, COPY,
LOCK, TRUNCATE, SAVEPOINT and SERIALIZABLE are not forwarded.

The dialect does not refuse writes on a secondary cluster: whether a write
succeeds depends on write forwarding, which is cluster configuration and not
visible reliably from SQL. The server rejects them.

Not covered

Babelfish, the Data API, aurora_stat_* functions not listed above.

Family and wiring

PostgreSQL family, lineage aurorapostgres → rdspostgres → postgres.

URL schemes suggested: aurorapostgres://, aurora-postgres://,
aurorapostgresql://.

What differs from RdsPostgres

AuroraPostgresDialect extends RdsPostgresDialect (rdspostgres) and
overrides 17 of its methods (Object.getOwnPropertyNames(AuroraPostgresDialect.prototype),
computed by the scratch test); everything else is inherited unchanged.

Area	Overridden
Connection and execution	`connect`, `disconnect`, `query`
DML builders	`buildInstanceIdentifierQuery`
Introspection	`getAuroraVersion`, `getReplicaStatus`, `readerReplicaConfig`
Other	`buildAuroraVersionQuery`, `buildBackendWaitsQuery`, `buildGlobalDbInstanceStatusQuery`, `buildGlobalDbStatusQuery`, `buildIsWriterQuery`, `buildReplicaStatusQuery`, `buildStatQuery`, `buildSystemWaitsQuery`, `buildWriteForwardConsistencySql`, `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	`aurorapostgres`
Transactions atomic	yes
Savepoints	yes
Isolation	up to `SERIALIZABLE`
Transaction modes	read-only yes; deferrable yes; constraint deferral yes
RETURNING	`RETURNING` on INSERT
Upsert	`INSERT … ON CONFLICT (…) DO UPDATE`
JSON column	`JSON` (`JSONB` → `JSONB`)
Vector column	`VECTOR(3)`
Array column	`INTEGER[]`
WHERE builder	62 operator keys; `$and`/`$or`/`$not`; `$exists`: yes; `$in` subquery: yes; expression operands: yes
SQL objects	family `postgres`: triggers yes; procedures yes; functions `multiStatement`; materialized views native; sequences yes; enums `named`; RLS `policy`
Streaming	`queryStream()` present; `LISTEN` present; `createExtension()` present

Amazon Aurora PostgreSQL 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#

Routing reads to the reader endpoint#

ReplicaManager#

Aurora functions#

Aurora Serverless v2#

Global databases#

Not covered#

Family and wiring#

What differs from RdsPostgres#

Capabilities#

See also#