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
bash
npm install pg @azure/identity
npm install -D @types/pg
Environment Variables
bash
# 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
typescript
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
typescript
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();
Examples are separate sketches requiring imports/schema and an authorized non-production database. Parameter binding protects values, not identifiers or tenant authorization. TypeScript query generics are compile-time assertions, not runtime result validation. On rollback failure, destroy the client instead of returning it to the pool; preserve the original error.
Core Workflows
1. Single Client Connection
typescript
import { Client } 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: 5432,
ssl: { rejectUnauthorized: true }
});
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
}
2. Connection Pool (Recommended for Production)
typescript
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();
3. Parameterized Queries (Prevent SQL Injection)
typescript
// 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]
);
4. Transactions
typescript
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();
}
5. Transaction Helper Function
typescript
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];
});
6. Typed Queries with TypeScript
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
Use the installed node-postgres version's supported dynamic password callback so new connections obtain a current token. Do not tear down and recreate a shared pool on every token expiry: concurrent callers can race with shutdown.
typescript
import { Pool } from "pg";
import { DefaultAzureCredential } from "@azure/identity";
const credential = new DefaultAzureCredential();
const azurePool = new Pool({
host: process.env.AZURE_POSTGRESQL_HOST,
database: process.env.AZURE_POSTGRESQL_DATABASE,
user: process.env.AZURE_POSTGRESQL_USER,
ssl: { rejectUnauthorized: true },
password: async () => {
const token = await credential.getToken("https://ossrdbms-aad.database.windows.net/.default");
if (!token) throw new Error("Token acquisition failed");
return token.token;
},
});
Verify Entra principal mapping and server authentication settings first. Tokens are secrets; never log them. Token expiration is supplied by the issuer, not a guaranteed one-hour lifetime.
Error Handling
typescript
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 (details redacted)");
break;
case "23503": // foreign_key_violation
console.error("Foreign key constraint failed (details redacted)");
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
typescript
// Alternative: Use connection string
const pool = new Pool({
connectionString: `postgres://${encodeURIComponent(user)}:${encodeURIComponent(password)}@${host}:${port}/${encodeURIComponent(database)}`,
ssl: { rejectUnauthorized: true }
});
// With SSL required (Azure)
const connectionString =
`postgres://user:password@server.postgres.database.azure.com:5432/mydb?sslmode=require`;
Pool Events
typescript
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
typescript
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 | |
When to Use
This skill is applicable to execute the workflow or actions described in the overview.