<< All versions

Skill v1.0.1

currentAutomated scan100/100
sickn33/agentic-awesome-skills/azure-postgres-ts

+4 new

──Details
PublishedNovember 14, 2026 at 07:34 PM
Content Hashsha256:4a343bab1a29f6d4...
Git SHA560b762c309d
Bump Typepatch
Compare with v1.0.0
──Files
Files (1 file, 12.2 KB)
SKILL.md12.2 KBactive
SKILL.md · 438 lines · 12.2 KB

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

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

SettingValueDescription
ssl.rejectUnauthorizedtrueAlways use SSL for Azure
Default port5432Standard PostgreSQL port
PgBouncer port6432Use when PgBouncer enabled
Token scopehttps://ossrdbms-aad.database.windows.net/.defaultEntra ID token scope
Token lifetime~1 hourRefresh before expiry

Pool Sizing Guidelines

WorkloadmaxidleTimeoutMillis
Light (dev/test)5-1030000
Medium (production)20-3030000
Heavy (high concurrency)50-10010000
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

typescript
import {
Client,
Pool,
PoolClient,
PoolConfig,
QueryResult,
QueryResultRow,
DatabaseError,
QueryConfig
} from "pg";

Reference Links

ResourceURL
node-postgres Docshttps://node-postgres.com
npm Packagehttps://www.npmjs.com/package/pg
GitHub Repositoryhttps://github.com/brianc/node-postgres
Azure PostgreSQL Docshttps://learn.microsoft.com/azure/postgresql/flexible-server/
Passwordless Connectionhttps://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.
← v1.0.0All versions