api-database-postgresql

PostgreSQL 模式（node-postgres）

快速指南： 使用 pg 包（v8.x）直接访问 PostgreSQL。始终使用 Pool——不要在应用程序代码中创建单独的 Client 实例。所有用户输入都必须使用参数化查询（$1、$2）——切勿将字符串插值到 SQL 中。对于事务，使用 pool.connect() 获取专用客户端，并在 try/catch/finally 中使用 BEGIN/COMMIT/ROLLBACK，确保始终调用 client.release()。处理连接池的 error 事件，以避免进程因空闲客户端错误而崩溃。对于大型结果集，请使用 pg-query-stream，避免将所有数据加载到内存中。

---

<critical_requirements>

关键：使用此技能前

（所有值都必须使用参数化查询（$1、$2、……）——绝不能将用户输入拼接或插入 SQL 字符串）

（所有数据库访问都必须使用 Pool——绝不能在应用程序代码中创建独立的 Client 实例）

（调用 pool.connect() 后，必须在 finally 块中将客户端释放回连接池——客户端泄漏会耗尽连接池并导致应用程序挂起）

（必须处理 Pool 实例的 error 事件——未处理的空闲客户端错误会导致 Node.js 进程崩溃）

</critical_requirements>

---

示例

• 核心模式 —— 连接池设置、参数化查询、类型安全的结果、错误处理
• 事务 —— BEGIN/COMMIT/ROLLBACK、保存点、重试逻辑、咨询锁
• 流式处理 —— 游标、pg-query-stream、用于实时处理的 LISTEN/NOTIFY
• 进阶 —— SSL/TLS、预处理语句、迁移、测试模式

其他资源：

• reference.md —— 连接池选项、错误代码、QueryResult 属性、生产环境检查清单

---

自动检测： PostgreSQL、pg、node-postgres、Pool、Client、pool.query、pool.connect、client.query、$1、参数化查询、BEGIN、COMMIT、ROLLBACK、LISTEN、NOTIFY、pg_notify、pg-query-stream、pg-cursor、Cursor、QueryResult、QueryResultRow、connectionString、PGHOST、PGDATABASE、unique_violation、23505、死锁、40P01、咨询锁

适用场景：

• 直接对 PostgreSQL 执行 SQL 查询（不通过 ORM）
• 管理 Node.js/PostgreSQL 应用程序的连接池
• 需要保证原子性的多查询事务
• 在不将所有数据加载到内存的情况下流式处理大型结果集
• 通过 LISTEN/NOTIFY 实时接收变更通知
• 使用事务回滚隔离进行集成测试

涵盖的关键模式：

• 连接池配置与生命周期（创建、错误处理、优雅关闭）
• 使用 $1 格式占位符的参数化查询（防止 SQL 注入）
• 使用 TypeScript 泛型实现类型安全的查询结果
• 使用专用客户端管理事务
• 使用 pg-cursor 和 pg-query-stream 进行流式处理
• 使用 LISTEN/NOTIFY 实时处理 PostgreSQL 事件
• PostgreSQL 错误代码处理（约束冲突、死锁、序列化失败）
• SSL/TLS 连接配置
• 使用事务回滚隔离进行测试

不适用场景：

• 需要 ORM 或查询构建器时——请改用相应的 ORM/查询构建器技能
• 需要内存缓存时——请使用缓存解决方案
• 需要在没有关系约束的情况下存储文档时——请使用文档数据库
• 需要以亚毫秒级延迟进行简单键值查询时——请使用内存数据存储

---

## 理念

pg（node-postgres）是一个底层 PostgreSQL 客户端，让你完全掌控 SQL、连接和事务。核心原则：直接编写 SQL，让 PostgreSQL 承担繁重工作。

核心原则：

1. 使用连接池，绝不直接使用 Client——应用代码应始终使用 Pool。连接池负责管理连接、处理重新连接并防止连接耗尽。单条查询使用 pool.query()；需要专用客户端时（例如事务）使用 pool.connect()。
2. 所有查询都使用参数——绝不要通过字符串拼接构造 SQL。使用 $1、$2 占位符。这既能防止 SQL 注入，也能启用 PostgreSQL 查询计划缓存。
3. 在 finally 中释放连接——通过 pool.connect() 获取的任何客户端都必须在 finally 块中释放。泄漏的客户端会一直处于借出状态；一旦达到 max 个客户端泄漏，连接池就会死锁。
4. 明确报告错误——处理连接池的 error 事件。根据具体的 PostgreSQL 错误代码处理查询错误。绝不要悄无声息地忽略错误。
5. 流式处理大型结果集——不要把一百万行 SELECT * 的结果全部载入内存。对于大型结果集，请使用 pg-cursor 或 pg-query-stream。

---

核心模式

模式 1：设置连接池

在应用启动时为每个数据库创建一个连接池。完整配置示例请参阅 examples/core.md。

// ✅ Good Example - Pool with error handling
import pg from "pg";

const POOL_MAX_CLIENTS = 20;
const IDLE_TIMEOUT_MS = 30_000;
const CONNECTION_TIMEOUT_MS = 5_000;

function createPool(): pg.Pool {
  const pool = new pg.Pool({
    connectionString: process.env.DATABASE_URL,
    max: POOL_MAX_CLIENTS,
    idleTimeoutMillis: IDLE_TIMEOUT_MS,
    connectionTimeoutMillis: CONNECTION_TIMEOUT_MS,
  });

  pool.on("error", (err) => {
    console.error("Unexpected idle client error:", err.message);
  });

  return pool;
}

export { createPool };

优点： 使用命名常量配置连接池，使用环境变量设置连接字符串，通过错误处理器避免空闲客户端错误导致进程崩溃

// ❌ Bad Example - No pool, standalone client
import pg from "pg";

const client = new pg.Client("postgres://localhost/mydb");
await client.connect();
// One connection for entire app -- no pooling, no reconnection,
// no concurrency. If client disconnects, app crashes.

缺点： 独立的 Client 没有连接池、不会自动重新连接，也不支持并发——每条查询都会占用唯一连接并阻塞后续查询

---

模式 2：参数化查询

始终使用 $1 风格的占位符。带类型的查询辅助函数请参阅 examples/core.md。

// ✅ Good Example - Parameterized query with typed result
interface UserRow {
  id: number;
  name: string;
  email: string;
}

const result = await pool.query<UserRow>(
  "SELECT id, name, email FROM users WHERE id = $1",
  [userId],
);

const user = result.rows[0]; // UserRow | undefined

优点： $1 占位符可防止 SQL 注入，泛型 <UserRow> 为 rows 数组提供类型，结果类型正确

// ❌ Bad Example - String interpolation (SQL INJECTION!)
const result = await pool.query(
  `SELECT * FROM users WHERE name = '${userName}'`,
);
// userName = "'; DROP TABLE users; --" -> catastrophic

缺点： 字符串插值会导致 SQL 注入，结果行没有类型安全保障，SELECT * 返回的列没有类型

---

模式 3：事务

使用 pool.connect() 获取事务专用客户端。有关保存点、重试和咨询锁，请参阅 examples/transactions.md。

// ✅ Good Example - Transaction with proper cleanup
async function transferFunds(
  pool: pg.Pool,
  fromId: number,
  toId: number,
  amount: number,
): Promise<void> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query(
      "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
      [amount, fromId],
    );
    await client.query(
      "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
      [amount, toId],
    );
    await client.query("COMMIT");
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

优点： 通过 pool.connect() 获取专用客户端，发生错误时执行 ROLLBACK，在 finally 中调用 client.release()，确保客户端返回连接池

// ❌ Bad Example - Transaction with pool.query()
await pool.query("BEGIN");
await pool.query("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
await pool.query("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
await pool.query("COMMIT");
// Each pool.query() may use a DIFFERENT client -- the BEGIN/COMMIT
// execute on different connections, so there is no transaction at all

缺点： pool.query() 每次都会借出一个随机客户端——BEGIN、UPDATE 和 COMMIT 可能在不同连接上执行，因此实际上并没有事务

---

模式 4：使用 PostgreSQL 错误代码处理错误

PostgreSQL 错误包含一个带 SQLSTATE 错误代码的 code 字段。完整错误代码表请参阅 reference.md。

// ✅ Good Example - Handling specific PostgreSQL errors
const PG_UNIQUE_VIOLATION = "23505";
const PG_FOREIGN_KEY_VIOLATION = "23503";
const PG_DEADLOCK_DETECTED = "40P01";
const PG_SERIALIZATION_FAILURE = "40001";

interface PgError extends Error {
  code: string;
  constraint?: string;
  detail?: string;
  table?: string;
  column?: string;
}

function isPgError(err: unknown): err is PgError {
  return err instanceof Error && "code" in err;
}

try {
  await pool.query("INSERT INTO users (email) VALUES ($1)", [email]);
} catch (err) {
  if (isPgError(err) && err.code === PG_UNIQUE_VIOLATION) {
    throw new ConflictError(`Email already exists: ${err.constraint}`);
  }
  if (isPgError(err) && err.code === PG_DEADLOCK_DETECTED) {
    // Retry the operation
  }
  throw err;
}

优点： 使用命名常量表示错误代码（无需魔法字符串），使用类型守卫安全访问属性，针对不同错误类型进行特定处理，并重新抛出未知错误

---

模式 5：流式处理大型结果集

对于返回大量行的查询，请使用 pg-cursor 或 pg-query-stream。完整的流式处理模式请参阅 examples/streaming.md。

// ✅ Good Example - Cursor for batch processing
import Cursor from "pg-cursor";

const BATCH_SIZE = 100;

async function processAllOrders(pool: pg.Pool): Promise<void> {
  const client = await pool.connect();
  try {
    const cursor = client.query(
      new Cursor("SELECT * FROM orders WHERE status = $1", ["pending"]),
    );

    let rows = await cursor.read(BATCH_SIZE);
    while (rows.length > 0) {
      await processBatch(rows);
      rows = await cursor.read(BATCH_SIZE);
    }

    await cursor.close();
  } finally {
    client.release();
  }
}

优点： 以固定大小的批次处理行，而无需将整个结果集载入内存，并在 finally 中正确释放客户端

---

模式 6：LISTEN/NOTIFY

PostgreSQL 可以向已连接的客户端推送实时通知。完整示例请参阅 examples/streaming.md。

// ✅ Good Example - LISTEN/NOTIFY with dedicated client
const CHANNEL = "order_updates";

async function listenForUpdates(pool: pg.Pool): Promise<pg.PoolClient> {
  const client = await pool.connect();

  client.on("notification", (msg) => {
    if (msg.channel === CHANNEL && msg.payload) {
      const data = JSON.parse(msg.payload);
      handleOrderUpdate(data);
    }
  });

  await client.query(`LISTEN ${CHANNEL}`);
  return client; // Caller is responsible for release on shutdown
}

// Publishing from another connection
await pool.query("SELECT pg_notify($1, $2)", [CHANNEL, JSON.stringify(data)]);

优点： 专用客户端在监听器的整个生命周期内保持借出状态，使用带参数的通道和载荷调用 pg_notify()，避免注入；使用 JSON 载荷承载结构化数据

适用场景： 对亚秒级延迟有要求且流量较低至中等（每秒数百条）的实时通知。对于高吞吐量流式处理，请使用专用消息代理。

---

<decision_framework>

决策框架

pool.query() 与 pool.connect() 的比较

Do I need a dedicated client?
├─ Single query, no transaction? -> pool.query() (auto-releases)
├─ Multiple queries in a transaction? -> pool.connect() + BEGIN/COMMIT/ROLLBACK
├─ LISTEN for notifications? -> pool.connect() (keep client for lifetime of listener)
├─ Cursor/streaming? -> pool.connect() (cursor binds to a connection)
└─ Prepared statements across queries? -> pool.connect() (plan caches per connection)

错误处理策略

What kind of PostgreSQL error?
├─ 23505 (unique_violation)? -> Map to 409 Conflict, include constraint name
├─ 23503 (foreign_key_violation)? -> Map to 400 Bad Request, entity not found
├─ 23502 (not_null_violation)? -> Map to 400 Bad Request, missing required field
├─ 23514 (check_violation)? -> Map to 400 Bad Request, validation failed
├─ 40P01 (deadlock_detected)? -> Retry with backoff (safe to retry)
├─ 40001 (serialization_failure)? -> Retry with backoff (safe to retry)
├─ 57014 (query_canceled)? -> Timeout, consider increasing statement_timeout
├─ 08xxx (connection_exception)? -> Pool handles reconnection, log and retry
└─ Other? -> Log full error, return 500

流式处理决策

How many rows will the query return?
├─ < 1,000 rows? -> pool.query() is fine (result fits in memory)
├─ 1,000 - 100,000 rows? -> pg-cursor with batch processing
├─ 100,000+ rows? -> pg-query-stream piped to a writable stream
└─ Need to export to file? -> pg-query-stream piped to file write stream

</decision_framework>

---

<red_flags>

高风险问题

高优先级问题：

• 使用字符串插值／拼接传递 SQL 值——这是 SQL 注入，也是数据库代码中最危险的漏洞
• 在事务中使用 pool.query()——每次调用都可能使用不同的连接，因此 BEGIN/COMMIT 不会生效
• 在调用 pool.connect() 后未释放客户端——泄漏的客户端会耗尽连接池；一旦泄漏的客户端数量达到 max，应用在调用 pool.connect() 时就会发生死锁
• 缺少 pool.on("error") 处理程序——空闲客户端错误会在连接池上发出；若未处理，就会导致 Node.js 进程崩溃
• 在应用代码中使用独立的 Client——没有连接池、没有重连机制，也不支持并发

中优先级问题：

• 在生产查询中使用 SELECT *——会返回不必要的列、在架构变更时造成问题，并阻止仅索引扫描
• 使用 pool.query() 而不是流式处理来加载数百万行数据——会导致内存耗尽和垃圾回收压力
• 硬编码连接字符串——无法按环境配置，并有凭据泄露到版本控制中的风险
• 未处理特定的 PostgreSQL 错误代码——通用错误处理会丢失有价值的信息（涉及哪个约束、哪一列）
• 使用 pool.query() 执行 LISTEN——通知会绑定到特定连接；pool.query 会立即释放该连接

常见错误：

• 忘记没有匹配行时 result.rows[0] 可能是 undefined——访问前务必先检查
• 依赖 result.rowCount 检查 SELECT 是否为空——应改用 result.rows.length；某些命令（例如 LOCK）的 rowCount 为 null，而 rows.length 始终可靠
• 在 SQL 字符串字面量中使用 $1——'$1' 是字面字符串，而不是参数；应在引号外使用 $1
• 忘记 PostgreSQL 参数中的数组会自动转换——[1, 2, 3] 会变成 {1,2,3}，可用于 = ANY($1)，但不能用于 IN ($1)（应使用 = ANY($1::int[])，而不是 IN）
• 常规调用 client.release(true)——传入 true 会销毁客户端，而不是将其归还到连接池；仅在发生不可恢复的错误后使用

注意事项与边缘情况：

• 连接池 error 事件与查询错误：连接池 error 事件会在空闲客户端与后端断开连接时触发（例如服务器重启）。查询错误则会从查询调用本身抛出或被拒绝。两种情况都需要处理程序。
• connectionTimeoutMillis: 0（默认值）表示不设超时——连接池耗尽时，连接会无限期等待。生产环境中务必设置超时时间。
• idleTimeoutMillis 只影响已归还到连接池的客户端——已借出的客户端若始终未释放，就永远不会被清理。
• PostgreSQL 的 numeric／decimal 类型默认以字符串形式返回（以避免 JavaScript 浮点精度损失）。若需要数值，请显式解析。
• LISTEN 在事务结束后仍然有效——如果执行 BEGIN、LISTEN channel、ROLLBACK，监听器仍处于活动状态。LISTEN 不属于事务操作。
• pool.end() 会等待所有已借出的客户端释放。如果客户端发生泄漏（始终未释放），pool.end() 会一直挂起。
• SSL 连接：如果连接字符串中包含任何 SSL 参数（sslmode、sslcert、sslkey、sslrootcert），整个 ssl 配置对象都会被替换——二者择一使用，不要同时使用。

</red_flags>

---

<critical_reminders>

关键提醒

（所有值都必须使用参数化查询（$1、$2 等）——绝不将用户输入拼接或插值到 SQL 字符串中）

（所有数据库访问都必须使用 Pool——绝不在应用代码中创建独立的 Client 实例）

（调用 pool.connect() 后，必须在 finally 块中将客户端释放回连接池——未释放的客户端会耗尽连接池并导致应用程序挂起）

（必须处理 Pool 实例上的 error 事件——未处理的空闲客户端错误会导致 Node.js 进程崩溃）

不遵守这些规则会导致 SQL 注入漏洞、连接池耗尽、应用程序挂起和进程崩溃。

</critical_reminders>