Skill v1.0.1
currentAutomated scan100/100+4 new
name: azure-postgres-ts description: Connect to Azure Database for PostgreSQL Flexible Server from Node.js/TypeScript using the pg (node-postgres) package. risk: critical source: community date_added: '2026-02-27' metadata: version: "1.0.1"
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/identitynpm install -D @types/pg
Environment Variables
# RequiredAZURE_POSTGRESQL_HOST=<server>.postgres.database.azure.comAZURE_POSTGRESQL_DATABASE=<database>AZURE_POSTGRESQL_PORT=5432# For password authenticationAZURE_POSTGRESQL_USER=<username>AZURE_POSTGRESQL_PASSWORD=<password># For Entra ID authenticationAZURE_POSTGRESQL_USER=<entra-user>@<server> # e.g., user@contoso.comAZURE_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 identityconst credential = new DefaultAzureCredential();// For user-assigned managed identity// const credential = new DefaultAzureCredential({// managedIdentityClientId: process.env.AZURE_POSTGRESQL_CLIENTID// });// Acquire access token for Azure PostgreSQLconst 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 userpassword: tokenResponse.token, // Token as passwordport: 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
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)
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 configurationmax: 20, // Maximum connections in poolidleTimeoutMillis: 30000, // Close idle connections after 30sconnectionTimeoutMillis: 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 queriesconst 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 shutdownawait pool.end();
3. Parameterized Queries (Prevent SQL Injection)
// ALWAYS use parameterized queries - never concatenate user inputconst userId = 123;const email = "user@example.com";// Single parameterconst result = await pool.query("SELECT * FROM users WHERE id = $1",[userId]);// Multiple parametersconst result = await pool.query("INSERT INTO users (email, name, created_at) VALUES ($1, $2, NOW()) RETURNING *",[email, "John Doe"]);// Array parameterconst ids = [1, 2, 3, 4, 5];const result = await pool.query("SELECT * FROM users WHERE id = ANY($1::int[])",[ids]);
4. 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();}
5. 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();}}// Usageconst 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
import { Pool, QueryResult } from "pg";interface User {id: number;email: string;name: string;created_at: Date;}// Type the query resultconst result: QueryResult<User> = await pool.query<User>("SELECT * FROM users WHERE id = $1",[userId]);const user: User | undefined = result.rows[0];// Type-safe insertasync 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.
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
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_violationconsole.error("Duplicate entry (details redacted)");break;case "23503": // foreign_key_violationconsole.error("Foreign key constraint failed (details redacted)");break;case "42P01": // undefined_tableconsole.error("Table does not exist:", error.message);break;case "28P01": // invalid_passwordconsole.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 stringconst 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
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/finallyor 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 |
When to Use
This skill is applicable to execute the workflow or actions described in the overview.
Limitations
- Use this skill only when the task clearly matches the scope described above.
- Do not treat the output as a substitute for environment-specific validation, testing, or expert review.
- Stop and ask for clarification if required inputs, permissions, safety boundaries, or success criteria are missing.