Azure Database for PostgreSQL Dialect

Reference for the azurepostgres SQL dialect (Azure Database for PostgreSQL
flexible server).

Source: azurepostgres
Tests: azurepostgres

Overview

Flexible server is
community PostgreSQL run by Azure, reached with pg, so
AzurePostgresDialect extends PostgresDialect and models only
authentication, TLS and the managed-service limits.

Property	Value
Dialect name	`azurepostgres`
Extends	`PostgresDialect`
Driver	`pg`
Optional SDK	`@azure/identity` (Entra tokens)
Default port	5432 (6432 for the built-in PgBouncer)
Cloud SDK	`@azure/identity` (Entra only)
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 postgres dialect uses. Add @azure/identity 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: 'azurepostgres',
  host: 'mypg.postgres.database.azure.com',
  database: 'app',
  username: 'app',
  password: process.env.DB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'azurepostgres',
  url: 'azurepostgres://app:secret@mypg.postgres.database.azure.com:5432/app',
});

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: 'azurepostgres',
  host: 'mypg.postgres.database.azure.com',
  database: 'app',
  username: 'ana@contoso.com', // the Entra principal
  dialectOptions: {
    entra: true, // DefaultAzureCredential: env, workload/managed identity, Azure CLI
  },
});

Needs @azure/identity installed next to the driver.

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

export const db = new Prorm({
  dialect: 'azurepostgres',
  host: 'mypg.postgres.database.azure.com',
  database: 'app',
  username: 'ana@contoso.com',
  dialectOptions: {
    entra: { credential: { type: 'managed-identity', clientId: process.env.AZURE_CLIENT_ID } },
  },
});

Needs @azure/identity installed next to the driver.

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

export const db = new Prorm({
  dialect: 'azurepostgres',
  host: 'mypg.postgres.database.azure.com',
  database: 'app',
  username: 'ana@contoso.com',
  dialectOptions: {
    entra: {
      credential: {
        type: 'client-secret',
        tenantId: process.env.AZURE_TENANT_ID!,
        clientId: process.env.AZURE_CLIENT_ID!,
        clientSecret: process.env.AZURE_CLIENT_SECRET!,
      },
    },
  },
});

Needs @azure/identity installed next to the driver.

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

export const db = new Prorm({
  dialect: 'azurepostgres',
  host: 'mypg.postgres.database.azure.com',
  database: 'app',
  username: 'app',
  password: process.env.DB_PASSWORD,
});

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

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

// Microsoft Entra ID: the user is the principal's name, the password a token
new Prorm({
  dialect: 'azurepostgres',
  host: 'mypg.postgres.database.azure.com',
  database: 'app',
  username: 'ana@contoso.com', // or a group / service principal / managed identity display name
  dialectOptions: {
    entra: true, // or { credential: {...} }, or tokenProvider
  },
});

// Password login
new Prorm({
  dialect: 'azurepostgres',
  host,
  database,
  username: 'admin',
  password: '…',
});

With entra the dialect builds entraTokenProvider({ target: 'postgres-flexible' })
(scope https://ossrdbms-aad.database.windows.net/.default), and its
resolvePoolConfig override spreads pgAuth() into the
pool config: password becomes an async function, so every new connection —
pooled or the dedicated LISTEN client — gets a current token.

TLS. The server requires TLS by default (require_secure_transport = ON)
and its certificate chains to public roots. When ssl is not given the
dialect uses { rejectUnauthorized: true } with Node's trust store. With
Entra, construction throws if ssl is not verified, if username is
missing, or if password is also set.

What differs from Postgres

AzurePostgresDialect extends PostgresDialect (postgres) and
overrides 15 of its methods (Object.getOwnPropertyNames(AzurePostgresDialect.prototype),
computed by the scratch test); everything else is inherited unchanged.

Area	Overridden
Connection and execution	`resolvePoolConfig`
DDL and schema	`buildCreateEntraPrincipalSql`, `buildCreateEntraPrincipalWithOidSql`, `buildCreateRoleQuery`, `buildCreateUserQuery`, `createDatabaseSQL`, `createExtension`, `createRole`, `createTable`, `createUser`
Introspection	`assertNoSuperuser`, `assertNoTablespace`, `getAllowedExtensions`
Other	`buildAllowedExtensionsQuery`, `buildListEntraPrincipalsSql`

Extensions: the `azure.extensions` allow-list

Flexible server creates only extensions listed in the azure.extensions
server parameter. createExtension(name) checks first:

the list is allowedExtensions when given, otherwise
SELECT current_setting('azure.extensions', true) AS allowed;

if the extension is not on it, the call throws with the current list and
the az postgres flexible-server parameter set --name azure.extensions …
command that adds it, and nothing is sent.

getAllowedExtensions() and buildAllowedExtensionsQuery() are public.
Extensions that also need shared_preload_libraries are left to the server.

`azure_pg_admin` limits, refused by name

There is no superuser: the server admin is a member of azure_pg_admin, a
restricted role (azuresu is Microsoft's).

Call	Refused because
`createUser` / `createRole` / `buildCreateUserQuery` / `buildCreateRoleQuery` with `superuser: true`	Superuser is reserved; use `azure_pg_admin` membership
`createTable(…, { tablespace })`, `createDatabaseSQL({ tablespace })`	Tablespaces cannot be created or used

Entra role builders

All return { sql, bindings } with $n parameters.

Builder	Function
`buildCreateEntraPrincipalSql(name, { isAdmin, isMfa })`	`pg_catalog.pgaadauth_create_principal(roleName, isAdmin, isMfa)`
`buildCreateEntraPrincipalWithOidSql(name, oid, type, { isAdmin, isMfa })`	`pg_catalog.pgaadauth_create_principal_with_oid(…)`, type `user`/`group`/`service`
`buildListEntraPrincipalsSql(adminsOnly)`	`pg_catalog.pgaadauth_list_principals(isAdminValue)`

Run them connected as an Entra administrator.

Caveats / not-yet-verified

Tests mock pg; nothing has run against a live server.

CREATE EXTENSION sent through raw query() is not checked; only
createExtension() is.

Other limits (no manual backups, CREATE CAST only for azure_pg_admin
members, azure_pg_admin cannot be altered) are left to the server.

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

Microsoft Entra authentication,
Manage Entra roles

Access management (azure_pg_admin)

Allow extensions

Limits

Cloud authentication, PostgreSQL dialect

Azure Database for 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#

What differs from Postgres#

Extensions: the azure.extensions allow-list#

azure_pg_admin limits, refused by name#

Entra role builders#

Caveats / not-yet-verified#

Capabilities#

Related reading#

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

What differs from Postgres

Extensions: the `azure.extensions` allow-list

`azure_pg_admin` limits, refused by name

Entra role builders

Caveats / not-yet-verified

Capabilities

Related reading