Azure PostgreSQL 时间序列 (Time Series)
Azure PostgreSQL for TypeScript (node-postgres)
使用 pg (node-postgres) 包连接到 Azure Database for PostgreSQL 灵活服务器，支持密码验证和 Microsoft Entra ID（无密码）验证。
安装
npm install pg @azure/identity
npm install -D @types/pg环境变量
# 必填
AZURE_POSTGRESQL_HOST=<server>.postgres.database.azure.com
AZURE_POSTGRESQL_DATABASE=<database>
AZURE_POSTGRESQL_PORT=5432
密码验证
AZURE_POSTGRESQL_USER=<username>
AZURE_POSTGRESQL_PASSWORD=<password>
Entra ID 验证
AZURE_POSTGRESQL_USER=<entra-user>@<server> # 例如 [email protected]
AZURE_POSTGRESQL_CLIENTID=<managed-identity-client-id> # 用于用户分配的标识身份验证
选项 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 } // Azure 必填
});
await client.connect();
选项 2：Microsoft Entra ID (无密码) - 推荐
import { Client, Pool } from "pg";
import { DefaultAzureCredential } from "@azure/identity";
// 用于系统分配的托管标识
const credential = new DefaultAzureCredential();
// 用于用户分配的托管标识
// const credential = new DefaultAzureCredential({
// managedIdentityClientId: process.env.AZURE_POSTGRESQL_CLIENTID
// });
// 获取 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 用户
password: tokenResponse.token, // 将令牌作为密码
port: Number(process.env.AZURE_POSTGRESQL_PORT) || 5432,
ssl: { rejectUnauthorized: true }
});
await client.connect();
核心工作流
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(); // 务必关闭连接
}
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 },
// 连接池配置
max: 20, // 连接池最大连接数
idleTimeoutMillis: 30000, // 关闭
30秒后释放空闲连接
connectionTimeoutMillis: 10000 // 新连接的超时时间
});
// 使用 pool 进行查询（自动获取并释放连接）
const result = await pool.query("SELECT * FROM users WHERE id = $1", [userId]);
// 显式检出连接以执行多个查询
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(); // 将连接返回至连接池
}
// 关闭时清理
await pool.end();
### 3. 参数化查询（防止 SQL 注入）// 始终使用参数化查询 - 切勿直接拼接用户输入
const userId = 123;
const email = "[email protected]";
// 单个参数
const result = await pool.query(
"SELECT * FROM users WHERE id = $1",
[userId]
);
// 多个参数
const result = await pool.query(
"INSERT INTO users (email, name, created_at) VALUES ($1, $2, NOW()) RETURNING *",
[email, "John Doe"]
);
// 数组参数
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();
}
}
// 使用示例
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;
}
// 为查询结果指定类型
const result: QueryResult<User> = await pool.query<User>(
"SELECT * FROM users WHERE id = $1",
[userId]
);
const user: User | undefined = result.rows[0];
// 类型安全的插入操作
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 令牌刷新的连接池
对于长期运行的应用程序，令牌会过期并需要刷新：
import { Pool, PoolConfig } from "pg";
import { DefaultAzureCredential, AccessToken } from "@azure/identity";
class AzurePostgresPool {
private pool: Pool | null = null;
private credential: DefaultAzureCredential;
private toke
nExpiry: 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;
// 在过期前 5 分钟刷新
return new Date() >= new Date(this.tokenExpiry.getTime() - 5 * 60 * 1000);
}
async getPool(): Promise<Pool> {
if (this.pool && !this.isTokenExpired()) {
return this.pool;
}
// 如果 token 已过期，关闭现有连接池
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;
}
}
}
// 使用示例
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("重复条目:", error.detail);
break;
case "23503": // foreign_key_violation (外键约束冲突)
console.error("外键约束失败:", error.detail);
break;
case "42P01": // undefined_table (表不存在)
console.error("表不存在:", error.message);
break;
case "28P01": // invalid_password (密码错误)
console.error("身份验证失败");
break;
case "57P03": // cannot_connect_now (服务器启动中)
console.error("服务器不可用，请稍后重试");
break;
default:
console.error(PostgreSQL 错误 ${error.code}: ${error.message});
}
}
throw error;
}
连接字符串格式
// 另一种方式：使用连接字符串
const pool = new Pool({
connectionString: postgres://${user}:${password}@${host}:${port}/${database}?sslmode=require
});
// 强制要求 SSL (Azure)
const connectionString =
postgres://user:[email protected]:5432/mydb?sslmode=require;
连接池事件
const pool = new Pool({ /* 配置 */ });
pool.on("connect", (client) => {
console.log("新客户端已连接到连接池");
});
pool.on("acquire", (client) => {
console.log("客户端已从连接池中取出");
});
pool.on("release", (err, client) => {
console.log("客户端已返回连接池");
});
pool.on("remove", (client) => {
console.log("客户端已从连接池中移除");
});
pool.on("error", (err, client) => {
console.error("连接池出现意外错误:", err);
});
Azure 特定配置
| 设置 |
| 数值 | 描述 |
|---------|-------|-------------|
| ssl.rejectUnauthorized | true | Azure 始终使用 SSL |
| 默认端口 | 5432 | 标准 PostgreSQL 端口 |
| PgBouncer 端口 | 6432 | 启用 PgBouncer 时使用 |
| Token 范围 | https://ossrdbms-aad.database.windows.net/.default | Entra ID token 范围 |
| Token 有效期 | ~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. 处理 Token 刷新 - Entra ID token 在约 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 |
适用场景
此技能适用于执行概览中所描述的工作流或操作。局限性
- 仅在任务与上述范围明确匹配时使用此技能。
- 不要将输出视为针对特定环境的验证、测试或专家评审的替代方案。
- 如果缺少必要的输入、权限、安全边界或成功标准，请停止并请求澄清。
