azure-postgres-ts

# 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(); ``` ## 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 For long-running applications, tokens expire and need refresh: ```typescript 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 ```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:", 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 ```typescript // 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 ```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