azure-postgres-ts

面向 TypeScript 的 Azure PostgreSQL（node-postgres）

使用 pg（node-postgres）包连接到 Azure Database for PostgreSQL Flexible Server，支持密码和 Microsoft Entra ID（无密码）身份验证。

安装

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., [email protected]
AZURE_POSTGRESQL_CLIENTID=<managed-identity-client-id>  # For user-assigned identity

身份验证

选项 1：密码身份验证

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();

选项 2：Microsoft Entra ID（无密码）——推荐

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();

以下示例是彼此独立的示意代码，需要导入、架构以及有权访问的非生产数据库。参数绑定可保护值，但不能保护标识符或进行租户授权。TypeScript 查询泛型是编译时断言，不会在运行时验证结果。回滚失败时，应销毁客户端，而不要将其归还连接池；同时保留原始错误。

核心工作流

1. 单客户端连接

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. 连接池（生产环境推荐）

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. 参数化查询（防止 SQL 注入）

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

// 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. 事务

const client = await pool.connect();

try {
  await client.query("BEGIN");
  
  const userResult = await client.query(
    "INSERT INTO users (email) VALUES ($1) RETURNING id",
    ["[email protected]"]
  );
  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. 事务辅助函数

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 *",
    ["[email protected]"]
  );
  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. 使用 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];
}

使用 Entra ID 令牌刷新的连接池

使用已安装的 node-postgres 版本所支持的动态密码回调，以便新连接获取当前令牌。不要在每次令牌过期时拆除并重建共享连接池：并发调用方可能会与关闭操作发生竞争。

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

请先验证 Entra 主体映射和服务器身份验证设置。令牌属于机密信息；切勿记录令牌。令牌有效期由颁发方提供，并不保证为一小时。

错误处理

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

连接字符串格式

// 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:[email protected]: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);
});

Azure 专用配置

设置	值	说明
ssl.rejectUnauthorized	true	Azure 始终使用 SSL
默认端口	5432	PostgreSQL 标准端口
PgBouncer 端口	6432	启用 PgBouncer 时使用
令牌范围	https://ossrdbms-aad.database.windows.net/.default	Entra ID 令牌范围
令牌有效期	~1 小时	在过期前刷新

连接池大小指南

工作负载	max	idleTimeoutMillis
轻量（开发/测试）	5-10	30000
中等（生产）	20-30	30000
重型（高并发）	50-100	10000

注意：Azure PostgreSQL 的连接数限制取决于 SKU。请查看所用层级的最大连接数。

最佳实践

1. 生产应用始终使用连接池
2. 使用参数化查询——切勿拼接用户输入
3. 始终关闭连接——使用 try/finally 或连接池
4. 启用 SSL——Azure 要求使用（ssl: { rejectUnauthorized: true }）
5. 处理令牌刷新——Entra ID 令牌约 1 小时后过期
6. 设置连接超时——避免网络问题导致连接挂起
7. 使用事务——处理多语句操作
8. 监控连接池指标——跟踪 pool.totalCount、pool.idleCount、pool.waitingCount
9. 平稳关闭——在应用终止时调用 pool.end()
10. 使用 TypeScript 泛型——为查询结果指定类型以确保安全

关键类型

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

参考链接

资源	URL
node-postgres 文档	https://node-postgres.com
npm 包	https://www.npmjs.com/package/pg
GitHub 仓库	https://github.com/brianc/node-postgres
Azure PostgreSQL 文档	https://learn.microsoft.com/azure/postgresql/flexible-server/
无密码连接	https://learn.microsoft.com/azure/postgresql/flexible-server/how-to-connect-with-managed-identity

适用场景

此技能适用于执行概述中所述的工作流或操作。

限制

• 仅当任务明确符合上述范围时，才使用此技能。
• 不要将输出视为特定环境验证、测试或专家评审的替代品。
• 如果缺少必需的输入、权限、安全边界或成功标准，请停止并请求澄清。