Back to Details

azure-postgres-ts

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Azure PostgreSQL for TypeScript (node-postgres)

适用于TypeScript的Azure PostgreSQL（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.
使用
pg
（node-postgres）包连接至Azure Database for PostgreSQL Flexible Server，支持密码认证和Microsoft Entra ID（无密码）认证。

Installation

安装

bash
npm install pg @azure/identity
npm install -D @types/pg
bash
npm install pg @azure/identity
npm install -D @types/pg

Environment Variables

环境变量

bash
undefined
bash
undefined

Required

必填项

AZURE_POSTGRESQL_HOST=<server>.postgres.database.azure.com AZURE_POSTGRESQL_DATABASE=<database> AZURE_POSTGRESQL_PORT=5432
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>
AZURE_POSTGRESQL_USER=<username> AZURE_POSTGRESQL_PASSWORD=<password>

For Entra ID authentication

用于Entra ID认证

AZURE_POSTGRESQL_USER=<entra-user>@<server> # e.g., user@contoso.com AZURE_POSTGRESQL_CLIENTID=<managed-identity-client-id> # For user-assigned identity
undefined
AZURE_POSTGRESQL_USER=<entra-user>@<server> # 示例：user@contoso.com AZURE_POSTGRESQL_CLIENTID=<managed-identity-client-id> # 用于用户分配的标识
undefined

Authentication

认证方式

Option 1: Password Authentication

选项1：密码认证

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();
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 }  // Azure环境下必填
});

await client.connect();

Option 2: Microsoft Entra ID (Passwordless) - Recommended

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

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

Core Workflows

核心工作流

1. Single Client Connection

1. 单客户端连接

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
}
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();  // 务必关闭连接
}

2. Connection Pool (Recommended for Production)

2. 连接池（生产环境推荐）

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();
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 },
  
  // 连接池配置
  max: 20,                    // 连接池最大连接数
  idleTimeoutMillis: 30000,   // 闲置连接30秒后关闭
  connectionTimeoutMillis: 10000  // 新连接超时时间
});

// 使用连接池执行查询（自动获取和释放连接）
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. Parameterized Queries (Prevent SQL Injection)

3. 参数化查询（防止SQL注入）

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]
);
typescript
// 务必使用参数化查询 - 绝不要拼接用户输入
const userId = 123;
const email = "user@example.com";

// 单个参数
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. Transactions

4. 事务

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

5. 事务辅助函数

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

// 使用示例
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

6. 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];
}
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];
}

Pool with Entra ID Token Refresh

支持Entra ID令牌刷新的连接池

For long-running applications, tokens expire and need refresh:
typescript
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()");
对于长期运行的应用，令牌会过期，需要进行刷新：
typescript
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;
    // 提前5分钟刷新令牌
    return new Date() >= new Date(this.tokenExpiry.getTime() - 5 * 60 * 1000);
  }

  async getPool(): Promise<Pool> {
    if (this.pool && !this.isTokenExpired()) {
      return this.pool;
    }

    // 令牌过期则关闭现有连接池
    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()");

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:", 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;
}
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("重复条目:", 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;
}

Connection String Format

连接字符串格式

typescript
// 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`;
typescript
// 替代方案：使用连接字符串
const pool = new Pool({
  connectionString: `postgres://${user}:${password}@${host}:${port}/${database}?sslmode=require`
});

// 要求SSL（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);
});
typescript
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-Specific Configuration

Azure专属配置

SettingValueDescription
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 hourRefresh before expiry
设置值说明
ssl.rejectUnauthorized
true
Azure环境下始终使用SSL
默认端口
5432
PostgreSQL标准端口
PgBouncer端口
6432
启用PgBouncer时使用
令牌作用域
https://ossrdbms-aad.database.windows.net/.default
Entra ID令牌作用域
令牌有效期~1小时过期前需刷新

Pool Sizing Guidelines

连接池大小指南

Workload
max
idleTimeoutMillis
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.
工作负载
max
idleTimeoutMillis
轻量（开发/测试）5-1030000
中等（生产）20-3030000
高负载（高并发）50-10010000
注意：Azure PostgreSQL根据SKU有连接数限制，请查看你的层级的最大连接数。

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
  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泛型 - 为查询结果指定类型以确保安全

Key Types

关键类型

typescript
import {
  Client,
  Pool,
  PoolClient,
  PoolConfig,
  QueryResult,
  QueryResultRow,
  DatabaseError,
  QueryConfig
} from "pg";
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
资源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