microsoft-azure-postgres-ts

name	microsoft-azure-postgres-ts
description	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".
license	MIT
metadata	{"author":"Microsoft","version":"1.0.0","package":"pg"}

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., [e-mail]
AZURE_POSTGRESQL_CLIENTID=<managed-identity-client-id>  # For user-assigned identity
AZURE_TOKEN_CREDENTIALS=prod # Required only if DefaultAzureCredential is used in production

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, ManagedIdentityCredential } from "@azure/identity";

// Local dev: DefaultAzureCredential. Production: set AZURE_TOKEN_CREDENTIALS=prod or AZURE_TOKEN_CREDENTIALS=<specific_credential>
const credential = new DefaultAzureCredential({requiredEnvVars: ["AZURE_TOKEN_CREDENTIALS"]});
// Or use a specific credential directly in production:
// See https://learn.microsoft.com/javascript/api/overview/azure/identity-readme?view=azure-node-latest#credential-classes
// const credential = new ManagedIdentityCredential();

// 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.,             
  : (process..) || ,
  : { :  }
});

 client.();

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 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 =  client.();
}  {
  client.();  
}


 pool.();

3. Parameterized Queries (Prevent SQL Injection)

// ALWAYS use parameterized queries - never concatenate user input
const userId = 123;
const email = "[e-mail]";

// 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

const client = await pool.connect();

try {
  await client.query("BEGIN");
  
  const userResult = await client.query(
    "INSERT INTO users (email) VALUES ($1) RETURNING id",
    ["[e-mail]"]
  );
  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();
  }
}

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

6. 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({requiredEnvVars: ["AZURE_TOKEN_CREDENTIALS"]});
    this.config = config;
  }

  private async getToken(): Promise<string> {
    const tokenResponse = await this..(
      
    );
    . =  (tokenResponse.);
     tokenResponse.;
  }

   ():  {
     (!.)  ;
    
      () >=  (..() -  *  * );
  }

   (): <> {
     (. && !.()) {
       .;
    }

    
     (.) {
       ..();
    }

     token =  .();
    . =  ({
      ....,
      : token
    });

     .;
  }

   query<T>(: , ?: []): <<T>> {
     pool =  .();
     pool.<T>(text, params);
  }

   (): <> {
     (.) {
       ..();
      . = ;
    }
  }
}


 azurePool =  ({
  : process..!,
  : process..!,
  : process..!,
  : ,
  : { :  },
  : 
});

 result =  azurePool.();

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;
}

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:[e-mail]: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

1. Always use connection pools for production applications
2. Use parameterized queries - Never concatenate user input
3. Always close connections - Use try/finally or connection pools
4. Enable SSL - Required for Azure (ssl: { rejectUnauthorized: true })
5. Handle token refresh - Entra ID tokens expire after ~1 hour
6. Set connection timeouts - Avoid hanging on network issues
7. Use transactions - For multi-statement operations
8. Monitor pool metrics - Track pool.totalCount, pool.idleCount, pool.waitingCount
9. Graceful shutdown - Call pool.end() on application termination
10. 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