Azure Cosmos DB for PostgreSQL Dialect

Retirement notice. Microsoft Learn states (checked September 2026):
"Azure Cosmos DB for PostgreSQL is on a retirement path and no longer
recommended for new projects." Microsoft's recommended replacements are the
Elastic Clusters
feature of Azure Database for PostgreSQL (also Citus-based) for PostgreSQL
workloads, and Azure Cosmos DB for NoSQL for NoSQL workloads. Use this
dialect for clusters that already exist; do not start new projects on it.

Overview

Azure Cosmos DB for PostgreSQL is managed Citus: a coordinator
and workers, each a PostgreSQL server running the citus extension. Despite
the name, it shares nothing with the Cosmos DB NoSQL API. Applications talk to
the coordinator with pg over ordinary PostgreSQL, so
CosmosPostgresDialect extends CitusDialect and inherits every
distribution, co-location, rebalancer and columnar builder.

Property	Value
Dialect name	`cosmospostgres`
Extends	`CitusDialect`
Driver	`pg`
Default port	5432
Default database	`citus`
Default user	`citus` (unset when `entra` is on)
TLS	verified TLS by default; `ssl: false` refused
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 citus 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: 'cosmospostgres',
  host: 'c-mycluster.abcdef123456.postgres.cosmos.azure.com',
  password: process.env.CITUS_PASSWORD, // user and database default to 'citus'
});

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: 'cosmospostgres',
  host: 'c-mycluster.abcdef123456.postgres.cosmos.azure.com',
  username: 'app@contoso.onmicrosoft.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: 'cosmospostgres',
  host: 'c-mycluster.abcdef123456.postgres.cosmos.azure.com',
  username: 'app@contoso.onmicrosoft.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: 'cosmospostgres',
  host: 'c-mycluster.abcdef123456.postgres.cosmos.azure.com',
  username: 'app@contoso.onmicrosoft.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: 'cosmospostgres',
  host: 'c-mycluster.abcdef123456.postgres.cosmos.azure.com',
  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

The coordinator's host is c-<cluster>.<uniqueID>.postgres.cosmos.azure.com
— the c- prefix marks the coordinator.

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

const d = new Prorm({
  dialect: 'cosmospostgres',
  host: cosmosPostgresCoordinatorHost('mycluster', '12345678901234'),
  password: process.env.CITUS_PASSWORD, // user 'citus', database 'citus' by default
});

A *.postgres.cosmos.azure.com host that does not start with c- is refused
at construction (applications connect to the coordinator). Other hosts — a
private endpoint name, your own PgBouncer — are accepted. For the managed
PgBouncer, use the host and port from the portal's "PgBouncer connection
strings".

TLS is always enforced by the service (TLS 1.2 or higher) and cannot be
disabled. The dialect defaults ssl to { rejectUnauthorized: true } (Node's
default roots) and refuses ssl: false.

What differs from Citus

CosmosPostgresDialect extends CitusDialect (citus) and
overrides 12 of its methods (Object.getOwnPropertyNames(CosmosPostgresDialect.prototype),
computed by the scratch test); everything else is inherited unchanged.

Area	Overridden
Connection and execution	`resolvePoolConfig`
DDL and schema	`buildCreateRoleQuery`, `buildCreateUserQuery`, `createDatabaseSQL`, `createRole`, `createUser`, `dropDatabaseSQL`
Other	`citusAddNode`, `citusDrainNode`, `citusRemoveNode`, `citusSetCoordinatorHost`, `nodesManaged`

Microsoft Entra ID

const d = new Prorm({
  dialect: 'cosmospostgres',
  host: 'c-mycluster.12345678901234.postgres.cosmos.azure.com',
  username: 'app@contoso.onmicrosoft.com', // the Entra principal
  dialectOptions: {
    entra: true, // DefaultAzureCredential
    // entra: { credential: { type: 'managed-identity', clientId } }
    // entra: { tokenProvider }  // any TokenProvider; @azure/identity not loaded
  },
});

The token is requested for the service's own resource,
https://token.postgres.cosmos.azure.com/.default
(COSMOS_POSTGRES_ENTRA_SCOPE) — not the ossrdbms-aad audience used by
Azure Database for PostgreSQL flexible server. The dialect overrides the
Postgres resolvePoolConfig hook to spread pgAuth(entraTokenProvider(...))
into the pool config, so every new pooled connection (and the LISTEN client)
gets a current token; tokens last 5–90 minutes. password is ignored.

Service limits: Entra authentication is not available on PostgreSQL 11, 12 and
13 clusters, and Entra groups are not supported.

Managed limits, refused by name

The built-in citus role is not a superuser (only Microsoft signs in as
postgres). It can create extensions, but:

Call	Why
`createDatabaseSQL`, `dropDatabaseSQL`	One database per cluster; `CREATE DATABASE` fails. The name is chosen at provisioning.
`createUser`, `buildCreateUserQuery`, `createRole`, `buildCreateRoleQuery`	The `citus` role cannot create roles; other roles are created in the portal / API so the service replicates them to every node.
`citusAddNode`, `citusRemoveNode`, `citusDrainNode`, `citusSetCoordinatorHost`	Nodes are managed by Azure: scale the cluster in the portal / API, then `rebalanceTableShards()`.
`new Prorm({
dialect: 'cosmospostgres',
dialectOptions: {
ssl: false,
},
})`	TLS cannot be disabled.
A non-`c-` `*.postgres.cosmos.azure.com` host	Not the coordinator.
`entra` with no `username` (at connect)	The token is issued for a principal.

Everything else — create_distributed_table, reference tables, schema-based
sharding, alter_distributed_table, rebalance_table_shards, columnar tables
— is inherited from CitusDialect.

Caveats / not verified

citus_rebalance_start / _status / _stop / _wait are inherited but not
listed in the service's function reference (which documents
rebalance_table_shards); they may or may not be available on a given
cluster version.

The four node-management UDFs are refused because they are absent from the
service's documented function surface and node scaling is a portal/API
operation; Microsoft does not say "refused" in those words.

Behaviour against a live cluster is not exercised; tests mock pg and the
Entra credential.

References

Introduction (retirement notice)

Authentication

Configure authentication / Entra tokens

Security overview (TLS)

Limits

Function reference

Connection pooling

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

Azure Cosmos DB 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 Citus#

Microsoft Entra ID#

Managed limits, refused by name#

Caveats / not verified#

References#

Capabilities#

See also#

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 Citus

Microsoft Entra ID

Managed limits, refused by name

Caveats / not verified

References

Capabilities

See also