Azure Postgres Ts

1---
2name: azure-postgres-ts
3description: |
4  Connect to Azure Database for PostgreSQL Flexible Server from Node.js/TypeScript using the pg (node-postgres) package. Use for PostgreSQL queries, connection pooling, transactions, and Microsoft Entra ID (passwordless) authentication. Triggers: "PostgreSQL", "postgres", "pg client", "node-postgres", "Azure PostgreSQL connection", "PostgreSQL TypeScript", "pg Pool", "passwordless postgres".
5package: pg
6---
7 
8# Azure PostgreSQL for TypeScript (node-postgres)
9 
10Connect to Azure Database for PostgreSQL Flexible Server using the `pg` (node-postgres) package with support for password and Microsoft Entra ID (passwordless) authentication.
11 
12## Installation
13 
14```bash
15npm install pg @azure/identity
16npm install -D @types/pg
17```
18 
19## Environment Variables
20 
21```bash
22# Required
23AZURE_POSTGRESQL_HOST=<server>.postgres.database.azure.com
24AZURE_POSTGRESQL_DATABASE=<database>
25AZURE_POSTGRESQL_PORT=5432
26 
27# For password authentication
28AZURE_POSTGRESQL_USER=<username>
29AZURE_POSTGRESQL_PASSWORD=<password>
30 
31# For Entra ID authentication
32AZURE_POSTGRESQL_USER=<entra-user>@<server>   # e.g., user@contoso.com
33AZURE_POSTGRESQL_CLIENTID=<managed-identity-client-id>  # For user-assigned identity
34```
35 
36## Authentication
37 
38### Option 1: Password Authentication
39 
40```typescript
41import { Client, Pool } from "pg";
42 
43const client = new Client({
44  host: process.env.AZURE_POSTGRESQL_HOST,
45  database: process.env.AZURE_POSTGRESQL_DATABASE,
46  user: process.env.AZURE_POSTGRESQL_USER,
47  password: process.env.AZURE_POSTGRESQL_PASSWORD,
48  port: Number(process.env.AZURE_POSTGRESQL_PORT) || 5432,
49  ssl: { rejectUnauthorized: true }  // Required for Azure
50});
51 
52await client.connect();
53```
54 
55### Option 2: Microsoft Entra ID (Passwordless) - Recommended
56 
57```typescript
58import { Client, Pool } from "pg";
59import { DefaultAzureCredential } from "@azure/identity";
60 
61// For system-assigned managed identity
62const credential = new DefaultAzureCredential();
63 
64// For user-assigned managed identity
65// const credential = new DefaultAzureCredential({
66//   managedIdentityClientId: process.env.AZURE_POSTGRESQL_CLIENTID
67// });
68 
69// Acquire access token for Azure PostgreSQL
70const tokenResponse = await credential.getToken(
71  "https://ossrdbms-aad.database.windows.net/.default"
72);
73 
74const client = new Client({
75  host: process.env.AZURE_POSTGRESQL_HOST,
76  database: process.env.AZURE_POSTGRESQL_DATABASE,
77  user: process.env.AZURE_POSTGRESQL_USER,  // Entra ID user
78  password: tokenResponse.token,             // Token as password
79  port: Number(process.env.AZURE_POSTGRESQL_PORT) || 5432,
80  ssl: { rejectUnauthorized: true }
81});
82 
83await client.connect();
84```
85 
86## Core Workflows
87 
88### 1. Single Client Connection
89 
90```typescript
91import { Client } from "pg";
92 
93const client = new Client({
94  host: process.env.AZURE_POSTGRESQL_HOST,
95  database: process.env.AZURE_POSTGRESQL_DATABASE,
96  user: process.env.AZURE_POSTGRESQL_USER,
97  password: process.env.AZURE_POSTGRESQL_PASSWORD,
98  port: 5432,
99  ssl: { rejectUnauthorized: true }
100});
101 
102try {
103  await client.connect();
104  
105  const result = await client.query("SELECT NOW() as current_time");
106  console.log(result.rows[0].current_time);
107} finally {
108  await client.end();  // Always close connection
109}
110```
111 
112### 2. Connection Pool (Recommended for Production)
113 
114```typescript
115import { Pool } from "pg";
116 
117const pool = new Pool({
118  host: process.env.AZURE_POSTGRESQL_HOST,
119  database: process.env.AZURE_POSTGRESQL_DATABASE,
120  user: process.env.AZURE_POSTGRESQL_USER,
121  password: process.env.AZURE_POSTGRESQL_PASSWORD,
122  port: 5432,
123  ssl: { rejectUnauthorized: true },
124  
125  // Pool configuration
126  max: 20,                    // Maximum connections in pool
127  idleTimeoutMillis: 30000,   // Close idle connections after 30s
128  connectionTimeoutMillis: 10000  // Timeout for new connections
129});
130 
131// Query using pool (automatically acquires and releases connection)
132const result = await pool.query("SELECT * FROM users WHERE id = $1", [userId]);
133 
134// Explicit checkout for multiple queries
135const client = await pool.connect();
136try {
137  const res1 = await client.query("SELECT * FROM users");
138  const res2 = await client.query("SELECT * FROM orders");
139} finally {
140  client.release();  // Return connection to pool
141}
142 
143// Cleanup on shutdown
144await pool.end();
145```
146 
147### 3. Parameterized Queries (Prevent SQL Injection)
148 
149```typescript
150// ALWAYS use parameterized queries - never concatenate user input
151const userId = 123;
152const email = "user@example.com";
153 
154// Single parameter
155const result = await pool.query(
156  "SELECT * FROM users WHERE id = $1",
157  [userId]
158);
159 
160// Multiple parameters
161const result = await pool.query(
162  "INSERT INTO users (email, name, created_at) VALUES ($1, $2, NOW()) RETURNING *",
163  [email, "John Doe"]
164);
165 
166// Array parameter
167const ids = [1, 2, 3, 4, 5];
168const result = await pool.query(
169  "SELECT * FROM users WHERE id = ANY($1::int[])",
170  [ids]
171);
172```
173 
174### 4. Transactions
175 
176```typescript
177const client = await pool.connect();
178 
179try {
180  await client.query("BEGIN");
181  
182  const userResult = await client.query(
183    "INSERT INTO users (email) VALUES ($1) RETURNING id",
184    ["user@example.com"]
185  );
186  const userId = userResult.rows[0].id;
187  
188  await client.query(
189    "INSERT INTO orders (user_id, total) VALUES ($1, $2)",
190    [userId, 99.99]
191  );
192  
193  await client.query("COMMIT");
194} catch (error) {
195  await client.query("ROLLBACK");
196  throw error;
197} finally {
198  client.release();
199}
200```
201 
202### 5. Transaction Helper Function
203 
204```typescript
205async function withTransaction<T>(
206  pool: Pool,
207  fn: (client: PoolClient) => Promise<T>
208): Promise<T> {
209  const client = await pool.connect();
210  try {
211    await client.query("BEGIN");
212    const result = await fn(client);
213    await client.query("COMMIT");
214    return result;
215  } catch (error) {
216    await client.query("ROLLBACK");
217    throw error;
218  } finally {
219    client.release();
220  }
221}
222 
223// Usage
224const order = await withTransaction(pool, async (client) => {
225  const user = await client.query(
226    "INSERT INTO users (email) VALUES ($1) RETURNING *",
227    ["user@example.com"]
228  );
229  const order = await client.query(
230    "INSERT INTO orders (user_id, total) VALUES ($1, $2) RETURNING *",
231    [user.rows[0].id, 99.99]
232  );
233  return order.rows[0];
234});
235```
236 
237### 6. Typed Queries with TypeScript
238 
239```typescript
240import { Pool, QueryResult } from "pg";
241 
242interface User {
243  id: number;
244  email: string;
245  name: string;
246  created_at: Date;
247}
248 
249// Type the query result
250const result: QueryResult<User> = await pool.query<User>(
251  "SELECT * FROM users WHERE id = $1",
252  [userId]
253);
254 
255const user: User | undefined = result.rows[0];
256 
257// Type-safe insert
258async function createUser(
259  pool: Pool,
260  email: string,
261  name: string
262): Promise<User> {
263  const result = await pool.query<User>(
264    "INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *",
265    [email, name]
266  );
267  return result.rows[0];
268}
269```
270 
271## Pool with Entra ID Token Refresh
272 
273For long-running applications, tokens expire and need refresh:
274 
275```typescript
276import { Pool, PoolConfig } from "pg";
277import { DefaultAzureCredential, AccessToken } from "@azure/identity";
278 
279class AzurePostgresPool {
280  private pool: Pool | null = null;
281  private credential: DefaultAzureCredential;
282  private tokenExpiry: Date | null = null;
283  private config: Omit<PoolConfig, "password">;
284 
285  constructor(config: Omit<PoolConfig, "password">) {
286    this.credential = new DefaultAzureCredential();
287    this.config = config;
288  }
289 
290  private async getToken(): Promise<string> {
291    const tokenResponse = await this.credential.getToken(
292      "https://ossrdbms-aad.database.windows.net/.default"
293    );
294    this.tokenExpiry = new Date(tokenResponse.expiresOnTimestamp);
295    return tokenResponse.token;
296  }
297 
298  private isTokenExpired(): boolean {
299    if (!this.tokenExpiry) return true;
300    // Refresh 5 minutes before expiry
301    return new Date() >= new Date(this.tokenExpiry.getTime() - 5 * 60 * 1000);
302  }
303 
304  async getPool(): Promise<Pool> {
305    if (this.pool && !this.isTokenExpired()) {
306      return this.pool;
307    }
308 
309    // Close existing pool if token expired
310    if (this.pool) {
311      await this.pool.end();
312    }
313 
314    const token = await this.getToken();
315    this.pool = new Pool({
316      ...this.config,
317      password: token
318    });
319 
320    return this.pool;
321  }
322 
323  async query<T>(text: string, params?: any[]): Promise<QueryResult<T>> {
324    const pool = await this.getPool();
325    return pool.query<T>(text, params);
326  }
327 
328  async end(): Promise<void> {
329    if (this.pool) {
330      await this.pool.end();
331      this.pool = null;
332    }
333  }
334}
335 
336// Usage
337const azurePool = new AzurePostgresPool({
338  host: process.env.AZURE_POSTGRESQL_HOST!,
339  database: process.env.AZURE_POSTGRESQL_DATABASE!,
340  user: process.env.AZURE_POSTGRESQL_USER!,
341  port: 5432,
342  ssl: { rejectUnauthorized: true },
343  max: 20
344});
345 
346const result = await azurePool.query("SELECT NOW()");
347```
348 
349## Error Handling
350 
351```typescript
352import { DatabaseError } from "pg";
353 
354try {
355  await pool.query("INSERT INTO users (email) VALUES ($1)", [email]);
356} catch (error) {
357  if (error instanceof DatabaseError) {
358    switch (error.code) {
359      case "23505":  // unique_violation
360        console.error("Duplicate entry:", error.detail);
361        break;
362      case "23503":  // foreign_key_violation
363        console.error("Foreign key constraint failed:", error.detail);
364        break;
365      case "42P01":  // undefined_table
366        console.error("Table does not exist:", error.message);
367        break;
368      case "28P01":  // invalid_password
369        console.error("Authentication failed");
370        break;
371      case "57P03":  // cannot_connect_now (server starting)
372        console.error("Server unavailable, retry later");
373        break;
374      default:
375        console.error(`PostgreSQL error ${error.code}: ${error.message}`);
376    }
377  }
378  throw error;
379}
380```
381 
382## Connection String Format
383 
384```typescript
385// Alternative: Use connection string
386const pool = new Pool({
387  connectionString: `postgres://${user}:${password}@${host}:${port}/${database}?sslmode=require`
388});
389 
390// With SSL required (Azure)
391const connectionString = 
392  `postgres://user:password@server.postgres.database.azure.com:5432/mydb?sslmode=require`;
393```
394 
395## Pool Events
396 
397```typescript
398const pool = new Pool({ /* config */ });
399 
400pool.on("connect", (client) => {
401  console.log("New client connected to pool");
402});
403 
404pool.on("acquire", (client) => {
405  console.log("Client checked out from pool");
406});
407 
408pool.on("release", (err, client) => {
409  console.log("Client returned to pool");
410});
411 
412pool.on("remove", (client) => {
413  console.log("Client removed from pool");
414});
415 
416pool.on("error", (err, client) => {
417  console.error("Unexpected pool error:", err);
418});
419```
420 
421## Azure-Specific Configuration
422 
423| Setting | Value | Description |
424|---------|-------|-------------|
425| `ssl.rejectUnauthorized` | `true` | Always use SSL for Azure |
426| Default port | `5432` | Standard PostgreSQL port |
427| PgBouncer port | `6432` | Use when PgBouncer enabled |
428| Token scope | `https://ossrdbms-aad.database.windows.net/.default` | Entra ID token scope |
429| Token lifetime | ~1 hour | Refresh before expiry |
430 
431## Pool Sizing Guidelines
432 
433| Workload | `max` | `idleTimeoutMillis` |
434|----------|-------|---------------------|
435| Light (dev/test) | 5-10 | 30000 |
436| Medium (production) | 20-30 | 30000 |
437| Heavy (high concurrency) | 50-100 | 10000 |
438 
439> **Note**: Azure PostgreSQL has connection limits based on SKU. Check your tier's max connections.
440 
441## Best Practices
442 
4431. **Always use connection pools** for production applications
4442. **Use parameterized queries** - Never concatenate user input
4453. **Always close connections** - Use `try/finally` or connection pools
4464. **Enable SSL** - Required for Azure (`ssl: { rejectUnauthorized: true }`)
4475. **Handle token refresh** - Entra ID tokens expire after ~1 hour
4486. **Set connection timeouts** - Avoid hanging on network issues
4497. **Use transactions** - For multi-statement operations
4508. **Monitor pool metrics** - Track `pool.totalCount`, `pool.idleCount`, `pool.waitingCount`
4519. **Graceful shutdown** - Call `pool.end()` on application termination
45210. **Use TypeScript generics** - Type your query results for safety
453 
454## Key Types
455 
456```typescript
457import {
458  Client,
459  Pool,
460  PoolClient,
461  PoolConfig,
462  QueryResult,
463  QueryResultRow,
464  DatabaseError,
465  QueryConfig
466} from "pg";
467```
468 
469## Reference Links
470 
471| Resource | URL |
472|----------|-----|
473| node-postgres Docs | https://node-postgres.com |
474| npm Package | https://www.npmjs.com/package/pg |
475| GitHub Repository | https://github.com/brianc/node-postgres |
476| Azure PostgreSQL Docs | https://learn.microsoft.com/azure/postgresql/flexible-server/ |
477| Passwordless Connection | https://learn.microsoft.com/azure/postgresql/flexible-server/how-to-connect-with-managed-identity |
478