Amazon RDS for PostgreSQL Dialect

Overview

Amazon RDS for PostgreSQL
is community PostgreSQL run by AWS. The wire protocol, the pg driver, the port
and the SQL are PostgreSQL's, so RdsPostgresDialect extends
PostgresDialect and inherits everything. It changes only what the managed
service changes: IAM authentication, TLS, and what the master user may do.

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 postgres 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: 'rdspostgres',
  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', // verify the server certificate
  },
});

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

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

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: 'rdspostgres',
  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: 'rdspostgres',
  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 IF NOT EXISTS "users" ("id" SERIAL PRIMARY KEY, "email" VARCHAR(255) NOT NULL UNIQUE, "name" VARCHAR(100), "meta" JSONB, "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, "updatedAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);

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 } });

-- insert
INSERT INTO "users" ("email", "name") VALUES ('ada@example.com', 'Ada');

-- update
UPDATE "users" SET "name" = 'Grace', "updatedAt" = '2026-09-27T05:01:42.534Z' WHERE "email" = 'ada@example.com';

-- delete
DELETE FROM "users" WHERE "id" = 1;

Upsert

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

INSERT INTO "users" ("id", "email", "name") VALUES (1, 'ada@example.com', 'Ada Lovelace') ON CONFLICT ("id") DO UPDATE SET "email" = excluded."email", "name" = excluded."name";

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

SELECT * FROM "users" WHERE "name" LIKE 'A%' ORDER BY "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' } } } });

SELECT * FROM "users" WHERE "meta" @> '{"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(() => {});
});

BEGIN;
INSERT INTO "users" ("email", "name") VALUES ('grace@example.com', 'Grace');
SAVEPOINT "prorm_sp_1";
DELETE FROM "users" WHERE "id" = 1;
ROLLBACK TO SAVEPOINT "prorm_sp_1";
RELEASE SAVEPOINT "prorm_sp_1";
COMMIT;

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 });
});

SELECT * FROM "users" WHERE "name" = 'Ada' FOR UPDATE SKIP LOCKED;

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

Password

const db = new Prorm({
  dialect: 'rdspostgres',
  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' },
});

With a CA bundle (caBundle, or PRORM_RDS_CA_BUNDLE) and no explicit ssl,
the pool verifies the server certificate against it. Without either, the parent
default applies (no ssl). RDS for PostgreSQL 15 and later default to
rds.force_ssl = 1, which rejects unencrypted connections.

IAM database authentication

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

• The user needs GRANT rds_iam: buildCreateIamUserQueries('app_user')
  returns CREATE USER "app_user" and GRANT rds_iam TO "app_user". Once
  rds_iam is granted, IAM takes precedence over the password for that user.
• The dialect overrides the base resolvePoolConfig(base) hook and adds
  pgAuth()'s password function, so every new pooled connection and the
  LISTEN client get a current token. Tokens last 15 minutes and are cached
  and refreshed by rdsIamTokenProvider (see cloud-auth).
• The token is signed for the endpoint the pool connects to. Use the real RDS
  endpoint: a custom Route 53 name cannot be used to sign tokens.
• tokenProvider accepts any TokenProvider (a vault, a sidecar) instead of
  the built-in signer.

TLS whenever a token is sent

The bundle is never downloaded at runtime. Get it once from
https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem.

What the master user cannot do

The master user is a member of rds_superuser and is created NOSUPERUSER NOREPLICATION. RDS has no PostgreSQL superuser at all.

buildGrantPredefinedRoleQuery accepts rds_superuser, rds_replication,
rds_password and rds_iam.

Extensions

• listAvailableExtensions() — SHOW rds.extensions, the extensions this
  engine version offers. AWS notes it can be inaccurate for parameters added in
  a minor version, so it is informational, not enforced.
• getAllowedExtensions() — rds.allowed_extensions as a list, or null
  when it is *, unset, or unavailable (it exists from PostgreSQL 12.7/13.3).
• Since PostgreSQL 13, trusted extensions (pgcrypto, hstore, pg_trgm,
  uuid-ossp, plperl, …) can be installed by any user with CREATE on the
  database; others need rds_superuser. The server enforces that.

Not covered

• RDS Data API (separate batch), Kerberos authentication, Babelfish.
• pg_hba.conf is not editable on RDS; TLS enforcement is the rds.force_ssl
  parameter.

Family and wiring

• PostgreSQL family (grammar, ON CONFLICT, partial indexes, IS DISTINCT FROM). Name-keyed family tables (partial-index.ts,
  where-builder.ts) need rdspostgres added to behave like postgres.
• URL schemes suggested: rdspostgres://, rds-postgres://,
  rdspostgresql://.

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.

See also

• All dialects — the family tree and the capability matrix
• postgres — the parent dialect this one extends