Connect TypeScript to Azure PostgreSQL

This skill covers connecting to Azure Database for PostgreSQL Flexible Server from TypeScript using the pg (node-postgres) package, supporting both password authentication and passwordless Microsoft Entra ID authentication. For Entra ID, DefaultAzureCredential acquires an access token scoped to https://ossrdbms-aad.database.windows.net/.default, which is then passed as the connection's password field - a token that lasts about an hour and must be refreshed before expiry. A full AzurePostgresPool wrapper class pattern is shown that checks token expiry (refreshing 5 minutes early), tears down and recreates the underlying Pool when the token has expired, and exposes a simple query() method that transparently gets a fresh pool as needed.

Core workflows covered include a single Client connection with try/finally cleanup via client.end(); a production-recommended connection Pool configured with max connections, idleTimeoutMillis, and connectionTimeoutMillis, including both auto-managed pool.query() calls and explicit pool.connect()/client.release() checkout for multiple queries in sequence; parameterized queries using $1/$2 placeholders (including array parameters via = ANY($1::int[])) to prevent SQL injection - the source is explicit that user input should never be concatenated into a query string; transactions wrapped in BEGIN/COMMIT/ROLLBACK with a reusable withTransaction() helper function that generalizes the try/catch/rollback/release pattern; and typed queries using pg's QueryResult<T> generic against a TypeScript interface describing the row shape.

Error handling maps specific PostgreSQL error codes to meaningful messages: 23505 (unique_violation), 23503 (foreign_key_violation), 42P01 (undefined_table), 28P01 (invalid_password), and 57P03 (cannot_connect_now, typically the server still starting). Pool event listeners (connect, acquire, release, remove, error) are shown for observability. An alternative connection-string format (postgres://user:password@host:port/database?sslmode=require) is also documented.

Use this when building a TypeScript/Node.js application that connects to Azure Database for PostgreSQL Flexible Server, especially when deciding between password and passwordless Entra ID authentication, or sizing a connection pool. Pool sizing guidance differentiates by workload: light dev/test workloads use max: 5-10 with a 30-second idle timeout, medium production workloads use max: 20-30, and heavy high-concurrency workloads use max: 50-100 with a shorter 10-second idle timeout - with a note that Azure PostgreSQL enforces connection limits based on the service tier, so pool sizing must respect that ceiling.

Install the required packages:

npm install pg @azure/identity
npm install -D @types/pg

Required environment variables are AZURE_POSTGRESQL_HOST, AZURE_POSTGRESQL_DATABASE, and AZURE_POSTGRESQL_PORT (5432 by default), plus either AZURE_POSTGRESQL_USER/AZURE_POSTGRESQL_PASSWORD for password auth or an Entra ID user (user@server) with an optional AZURE_POSTGRESQL_CLIENTID for a user-assigned managed identity. Output is a connected Client or Pool instance ready to run parameterized queries and transactions.

Built on pg (node-postgres) for the PostgreSQL wire protocol and @azure/identity's DefaultAzureCredential for Entra ID token acquisition, with SSL required (ssl: { rejectUnauthorized: true }) for all Azure connections, and optional PgBouncer support on port 6432.

TypeScript/Node.js developers connecting to Azure Database for PostgreSQL who need concrete patterns for connection pooling, Entra ID passwordless authentication with token refresh, parameterized queries, and transaction handling.