菜单
Connect to Azure Database for PostgreSQL Flexible Server from Node.js/TypeScript using the pg (node-postgres) package.
默认使用会先检查来源的 Prompt；你也可以切换为直接命令，或下载本地副本。
决定是否安装前，请先阅读 SKILL.md，以及 SkillsMP 当前展示的配套文件。
用 Codex 或 Claude 帮你安装 复制这段 Prompt，粘贴到 Codex、Claude 或其他助手里，让它检查 Skill 页面并帮你完成安装。
直接命令不会经过审查 Prompt；运行前请先检查来源。
npx skills add https://github.com/BEKO2210/Firstbrain --skill azure-postgres-ts命令会保持在同一行。复制前请横向滚动并检查完整内容。
想先保存到本地？可下载 SkillsMP 当前能够提供的文件。
Arquitecto de Soluciones Principal y Consultor Tecnológico de Andru.ia. Diagnostica y traza la hoja de ruta óptima para proyectos de IA en español.
Security audit, hardening, threat modeling (STRIDE/PASTA), Red/Blue Team, OWASP checks, code review, incident response, and infrastructure security for any project.
Ingeniero de Sistemas de Andru.ia. Diseña, redacta y despliega nuevas habilidades (skills) dentro del repositorio siguiendo el Estándar de Diamante.
基于 SOC 职业分类
正在显示 SKILL.md
| name | azure-postgres-ts |
| description | Connect to Azure Database for PostgreSQL Flexible Server from Node.js/TypeScript using the pg (node-postgres) package. |
| type | skill |
| created | 2026-02-27T00:00:00.000Z |
| domain | cloud-infrastructure |
| category | azure |
| risk | unknown |
| source | community |
| tags | ["skill","cloud-infrastructure","azure","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.
npm install pg @azure/identity
npm install -D @types/pg
# 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
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.();
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();
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
}
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();
// 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]
);
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();
}
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];
});
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];
}
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();
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()");
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;
}
// 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`;
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);
});
| 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 |
| 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.
try/finally or connection poolsssl: { rejectUnauthorized: true })pool.totalCount, pool.idleCount, pool.waitingCountpool.end() on application terminationimport {
Client,
Pool,
PoolClient,
PoolConfig,
QueryResult,
QueryResultRow,
DatabaseError,
QueryConfig
} from "pg";
| 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 |
This skill is applicable to execute the workflow or actions described in the overview.