Azure PostgreSQL for TypeScript (node-postgres)

Connect to Azure Database for PostgreSQL Flexible Server using the pg (node-postgres) package with support for password and Microsoft Entra ID (passwordless) authentication.

Installation

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

Environment Variables

Required

AZURE_POSTGRESQL_HOST=<server>.postgres.database.azure.com AZURE_POSTGRESQL_DATABASE=<database> AZURE_POSTGRESQL_PORT=5432

For password authentication

AZURE_POSTGRESQL_USER=<username> AZURE_POSTGRESQL_PASSWORD=<password>

For Entra ID authentication

AZURE_POSTGRESQL_USER=<entra-user>@<server> # e.g., user@contoso.com AZURE_POSTGRESQL_CLIENTID=<managed-identity-client-id> # For user-assigned identity

Authentication

Option 1: Password Authentication

import { Client, Pool } from "pg";

const client = new Client({ host: process.env.AZURE_POSTGRESQL_HOST, database: process.env.AZURE_POSTGRESQL_DATABASE, user: process.env.AZURE_POSTGRESQL_USER, password: process.env.AZURE_POSTGRESQL_PASSWORD, port: Number(process.env.AZURE_POSTGRESQL_PORT) || 5432, ssl: { rejectUnauthorized: true } // Required for Azure });

await client.connect();

Option 2: Microsoft Entra ID (Passwordless) - Recommended

import { Client, Pool } from "pg"; import { DefaultAzureCredential } from "@azure/identity";

// For system-assigned managed identity const credential = new DefaultAzureCredential();

// For user-assigned managed identity // const credential = new DefaultAzureCredential({ // managedIdentityClientId: process.env.AZURE_POSTGRESQL_CLIENTID // });

// Acquire access token for Azure PostgreSQL const tokenResponse = await credential.getToken( "https://ossrdbms-aad.database.windows.net/.default" );

const client = new Client({ host: process.env.AZURE_POSTGRESQL_HOST, database: process.env.AZURE_POSTGRESQL_DATABASE, user: process.env.AZURE_POSTGRESQL_USER, // Entra ID user password: tokenResponse.token, // Token as password port: Number(process.env.AZURE_POSTGRESQL_PORT) || 5432, ssl: { rejectUnauthorized: true } });

await client.connect();

Core Workflows

Single Client Connection

import { Client } from "pg";

try { await client.connect();

const result = await client.query("SELECT NOW() as current_time"); console.log(result.rows[0].current_time); } finally { await client.end(); // Always close connection }

Connection Pool (Recommended for Production)

import { Pool } from "pg";

const pool = new Pool({ host: process.env.AZURE_POSTGRESQL_HOST, database: process.env.AZURE_POSTGRESQL_DATABASE, user: process.env.AZURE_POSTGRESQL_USER, password: process.env.AZURE_POSTGRESQL_PASSWORD, port: 5432, ssl: { rejectUnauthorized: true },

// Pool configuration max: 20, // Maximum connections in pool idleTimeoutMillis: 30000, // Close idle connections after 30s connectionTimeoutMillis: 10000 // Timeout for new connections });

// Query using pool (automatically acquires and releases connection) const result = await pool.query("SELECT * FROM users WHERE id = $1", [userId]);

// Explicit checkout for multiple queries const client = await pool.connect(); try { const res1 = await client.query("SELECT * FROM users"); const res2 = await client.query("SELECT * FROM orders"); } finally { client.release(); // Return connection to pool }

// Cleanup on shutdown await pool.end();

Parameterized Queries (Prevent SQL Injection)

// ALWAYS use parameterized queries - never concatenate user input const userId = 123; const email = "user@example.com";

// Single parameter const result = await pool.query( "SELECT * FROM users WHERE id = $1", [userId] );

// Multiple parameters const result = await pool.query( "INSERT INTO users (email, name, created_at) VALUES ($1, $2, NOW()) RETURNING *", [email, "John Doe"] );

// Array parameter const ids = [1, 2, 3, 4, 5]; const result = await pool.query( "SELECT * FROM users WHERE id = ANY($1::int[])", [ids] );

Transactions

const client = await pool.connect();

try { await client.query("BEGIN");

const userResult = await client.query( "INSERT INTO users (email) VALUES ($1) RETURNING id", ["user@example.com"] ); const userId = userResult.rows[0].id;

await client.query( "INSERT INTO orders (user_id, total) VALUES ($1, $2)", [userId, 99.99] );

await client.query("COMMIT"); } catch (error) { await client.query("ROLLBACK"); throw error; } finally { client.release(); }

Transaction Helper Function

async function withTransaction<T>( pool: Pool, fn: (client: PoolClient) => Promise<T> ): Promise<T> { const client = await pool.connect(); try { await client.query("BEGIN"); const result = await fn(client); await client.query("COMMIT"); return result; } catch (error) { await client.query("ROLLBACK"); throw error; } finally { client.release(); } }

// Usage const order = await withTransaction(pool, async (client) => { const user = await client.query( "INSERT INTO users (email) VALUES ($1) RETURNING *", ["user@example.com"] ); const order = await client.query( "INSERT INTO orders (user_id, total) VALUES ($1, $2) RETURNING *", [user.rows[0].id, 99.99] ); return order.rows[0]; });

Typed Queries with TypeScript

import { Pool, QueryResult } from "pg";

interface User { id: number; email: string; name: string; created_at: Date; }

// Type the query result const result: QueryResult<User> = await pool.query<User>( "SELECT * FROM users WHERE id = $1", [userId] );

const user: User | undefined = result.rows[0];

// Type-safe insert async function createUser( pool: Pool, email: string, name: string ): Promise<User> { const result = await pool.query<User>( "INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *", [email, name] ); return result.rows[0]; }

Pool with Entra ID Token Refresh

For long-running applications, tokens expire and need refresh:

import { Pool, PoolConfig } from "pg"; import { DefaultAzureCredential, AccessToken } from "@azure/identity";

class AzurePostgresPool { private pool: Pool | null = null; private credential: DefaultAzureCredential; private tokenExpiry: Date | null = null; private config: Omit<PoolConfig, "password">;

constructor(config: Omit<PoolConfig, "password">) { this.credential = new DefaultAzureCredential(); this.config = config; }

private async getToken(): Promise<string> { const tokenResponse = await this.credential.getToken( "https://ossrdbms-aad.database.windows.net/.default" ); this.tokenExpiry = new Date(tokenResponse.expiresOnTimestamp); return tokenResponse.token; }

private isTokenExpired(): boolean { if (!this.tokenExpiry) return true; // Refresh 5 minutes before expiry return new Date() >= new Date(this.tokenExpiry.getTime() - 5 * 60 * 1000); }

async getPool(): Promise<Pool> { if (this.pool && !this.isTokenExpired()) { return this.pool; }

// Close existing pool if token expired
if (this.pool) {
  await this.pool.end();
}

const token = await this.getToken();
this.pool = new Pool({
  ...this.config,
  password: token
});

return this.pool;

}

async query<T>(text: string, params?: any[]): Promise<QueryResult<T>> { const pool = await this.getPool(); return pool.query<T>(text, params); }

async end(): Promise<void> { if (this.pool) { await this.pool.end(); this.pool = null; } } }

// Usage const azurePool = new AzurePostgresPool({ host: process.env.AZURE_POSTGRESQL_HOST!, database: process.env.AZURE_POSTGRESQL_DATABASE!, user: process.env.AZURE_POSTGRESQL_USER!, port: 5432, ssl: { rejectUnauthorized: true }, max: 20 });

const result = await azurePool.query("SELECT NOW()");

Error Handling

import { DatabaseError } from "pg";

try { await pool.query("INSERT INTO users (email) VALUES ($1)", [email]); } catch (error) { if (error instanceof DatabaseError) { switch (error.code) { case "23505": // unique_violation console.error("Duplicate entry:", error.detail); break; case "23503": // foreign_key_violation console.error("Foreign key constraint failed:", error.detail); break; case "42P01": // undefined_table console.error("Table does not exist:", error.message); break; case "28P01": // invalid_password console.error("Authentication failed"); break; case "57P03": // cannot_connect_now (server starting) console.error("Server unavailable, retry later"); break; default: console.error(PostgreSQL error ${error.code}: ${error.message}); } } throw error; }

Connection String Format

// Alternative: Use connection string const pool = new Pool({ connectionString: postgres://${user}:${password}@${host}:${port}/${database}?sslmode=require });

// With SSL required (Azure) const connectionString = postgres://user:password@server.postgres.database.azure.com:5432/mydb?sslmode=require;

Pool Events

const pool = new Pool({ /* config */ });

pool.on("connect", (client) => { console.log("New client connected to pool"); });

pool.on("acquire", (client) => { console.log("Client checked out from pool"); });

pool.on("release", (err, client) => { console.log("Client returned to pool"); });

pool.on("remove", (client) => { console.log("Client removed from pool"); });

pool.on("error", (err, client) => { console.error("Unexpected pool error:", err); });

Azure-Specific Configuration

Setting Value Description

ssl.rejectUnauthorized

true

Always use SSL for Azure

Default port 5432

Standard PostgreSQL port

PgBouncer port 6432

Use when PgBouncer enabled

Token scope https://ossrdbms-aad.database.windows.net/.default

Entra ID token scope

Token lifetime ~1 hour Refresh before expiry

Pool Sizing Guidelines

Workload max

idleTimeoutMillis

Light (dev/test) 5-10 30000

Medium (production) 20-30 30000

Heavy (high concurrency) 50-100 10000

Note: Azure PostgreSQL has connection limits based on SKU. Check your tier's max connections.

Best Practices

Always use connection pools for production applications
Use parameterized queries - Never concatenate user input
Always close connections - Use try/finally or connection pools
Enable SSL - Required for Azure (ssl: { rejectUnauthorized: true } )
Handle token refresh - Entra ID tokens expire after ~1 hour
Set connection timeouts - Avoid hanging on network issues
Use transactions - For multi-statement operations
Monitor pool metrics - Track pool.totalCount , pool.idleCount , pool.waitingCount
Graceful shutdown - Call pool.end() on application termination
Use TypeScript generics - Type your query results for safety

Key Types

import { Client, Pool, PoolClient, PoolConfig, QueryResult, QueryResultRow, DatabaseError, QueryConfig } from "pg";

Reference Links

Resource URL

node-postgres Docs https://node-postgres.com

npm Package https://www.npmjs.com/package/pg

GitHub Repository https://github.com/brianc/node-postgres

Azure PostgreSQL Docs https://learn.microsoft.com/azure/postgresql/flexible-server/

Passwordless Connection https://learn.microsoft.com/azure/postgresql/flexible-server/how-to-connect-with-managed-identity

azure-postgres-ts

Safety Notice

Copy this and send it to your AI assistant to learn

Required

For password authentication

For Entra ID authentication

Source Transparency

Related Skills

github-issue-creator

azure-observability

azure-appconfiguration-java

azure-kusto