api-database-postgresql

PostgreSQL パターン（node-postgres）

クイックガイド： PostgreSQL に直接アクセスするには pg パッケージ（v8.x）を使用します。必ず Pool を使用してください。アプリケーションコードで個別の Client インスタンスを作成してはいけません。すべてのユーザー入力にはパラメーター化クエリ（$1、$2）を使用してください。SQL に文字列を補間してはいけません。トランザクションでは、pool.connect() で専用クライアントを取得し、必ず client.release() を呼び出す try／catch／finally の中で BEGIN／COMMIT／ROLLBACK を使用します。アイドルクライアントのエラーによるプロセスのクラッシュを防ぐため、プールの error イベントを処理してください。大きな結果セットをメモリにすべて読み込まずに処理するには、pg-query-stream を使用します。

---

<critical_requirements>

重要：このスキルを使用する前に

（すべての値にパラメーター化クエリ（$1、$2、...）を必ず使用してください。ユーザー入力を SQL 文字列に連結または補間してはいけません）

（すべてのデータベースアクセスに必ず Pool を使用してください。アプリケーションコードで独立した Client インスタンスを作成してはいけません）

（pool.connect() の後は、必ず finally ブロックでクライアントをプールに返却してください。クライアントが解放されないとプールが枯渇し、アプリケーションがハングします）

（Pool インスタンスの error イベントを必ず処理してください。処理されないアイドルクライアントのエラーは Node.js プロセスをクラッシュさせます）

</critical_requirements>

---

例

• 基本パターン -- Pool のセットアップ、パラメーター化クエリ、型安全な結果、エラー処理
• トランザクション -- BEGIN／COMMIT／ROLLBACK、セーブポイント、再試行ロジック、アドバイザリロック
• ストリーミング -- カーソル、pg-query-stream、リアルタイム処理のための LISTEN／NOTIFY
• 高度なパターン -- SSL／TLS、プリペアードステートメント、マイグレーション、テストパターン

追加リソース：

• reference.md -- Pool のオプション、エラーコード、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 によるリアルタイムの変更通知
• トランザクションのロールバック分離を利用した統合テスト

対象となる主なパターン：

• Pool の構成とライフサイクル（作成、エラー処理、グレースフルシャットダウン）
• $1 形式のプレースホルダーを使ったパラメーター化クエリ（SQL インジェクションの防止）
• TypeScript のジェネリクスを使用した型安全なクエリ結果
• 専用クライアントによるトランザクション管理
• pg-cursor と pg-query-stream によるストリーミング
• PostgreSQL イベントのリアルタイム処理のための LISTEN／NOTIFY
• PostgreSQL のエラーコード処理（制約違反、デッドロック、シリアライゼーション失敗）
• SSL／TLS 接続の構成
• トランザクションのロールバック分離を利用したテスト

使用しない場面：

• ORM またはクエリビルダーが必要な場合 -- 代わりに ORM／クエリビルダーのスキルを使用してください
• インメモリキャッシュが必要な場合 -- キャッシュソリューションを使用してください
• リレーショナル制約のないドキュメントストレージが必要な場合 -- ドキュメントデータベースを使用してください
• サブミリ秒のレイテンシでシンプルなキーと値の検索が必要な場合 -- インメモリデータストアを使用してください

---

## 基本方針

pg（node-postgres）は、SQL、接続、トランザクションを完全に制御できる低レベルの PostgreSQL クライアントです。基本原則は、SQL を直接記述し、重い処理は PostgreSQL に任せることです。

基本原則：

1. Client ではなく、常に Pool を使う -- アプリケーションコードでは、常に Pool を使ってください。プールは接続を管理し、再接続に対応し、接続の枯渇を防ぎます。単一のクエリには pool.query() を、専用クライアントが必要な場合（トランザクション）には pool.connect() を使ってください。
2. すべてパラメーター化する -- 文字列連結で SQL を組み立てないでください。$1、$2 のプレースホルダーを使ってください。これにより、SQL インジェクションを防ぐだけでなく、PostgreSQL のクエリプランのキャッシュも有効になります。
3. finally で解放する -- pool.connect() で取得したクライアントは、必ず finally ブロックで解放してください。解放し忘れたクライアントはチェックアウトされたままになり、max 個のクライアントが解放されずに残ると、プールがデッドロックします。
4. エラーを明確に扱う -- プールの error イベントを処理してください。クエリのエラーは PostgreSQL の具体的なエラーコードに基づいて処理してください。エラーを黙って無視しないでください。
5. 大きな結果セットはストリーミングする -- 100 万件の行を 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 ペイロードを使います。

使用する場面： 1 秒未満のレイテンシーが重要で、通知量が少ないか中程度（毎秒数百件）のリアルタイム通知に適しています。高スループットのストリーミングには、専用のメッセージブローカーを使ってください。

---

<decision_framework>

判断の指針

pool.query() vs 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() で読み込むこと。メモリ不足や GC の負荷を引き起こします。
• 接続文字列をハードコードすること。環境ごとの設定ができず、バージョン管理システムで認証情報が漏えいするリスクがあります。
• PostgreSQL の個別のエラーコードを処理しないこと。汎用的なエラー処理では、どの制約や列が原因なのかという有用な情報が失われます。
• pool.query() で LISTEN を使用すること。通知は特定の接続に紐付けられますが、pool.query は接続をすぐに解放します。

よくある間違い：

• 該当する行がない場合、result.rows[0] が undefined になる可能性を忘れること。アクセスする前に必ず確認してください。
• SELECT の結果が空かどうかの確認に result.rowCount を使うこと。代わりに result.rows.length を使用してください。rowCount は一部のコマンド（例：LOCK）では null になりますが、rows.length は常に信頼できます。
• SQL の文字列リテラル内で $1 を使うこと。'$1' は文字列リテラルであり、パラメーターではありません。引用符の外で $1 を使用してください。
• パラメーター内の PostgreSQL 配列は自動的に変換されることを忘れること。[1, 2, 3] は {1,2,3} に変換され、= ANY($1) では機能しますが、IN ($1) では機能しません（IN の代わりに = ANY($1::int[]) を使用してください）。
• 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>