/api-database-postgresql

name: api-database-postgresql
description: Direct PostgreSQL access with node-postgres (pg) -- connection pools, parameterized queries, transactions, streaming, LISTEN/NOTIFY, error handling

PostgreSQL Patterns (node-postgres)

> **Quick Guide:** Use the `pg` package (v8.x) for direct PostgreSQL access. **Always use `Pool`** -- never create individual `Client` instances in application code. Use **parameterized queries** (`$1`, `$2`) for ALL user input -- never interpolate strings into SQL. For transactions, check out a dedicated client with `pool.connect()` and use `BEGIN`/`COMMIT`/`ROLLBACK` in a `try`/`catch`/`finally` that always calls `client.release()`. Handle the pool `error` event to prevent process crashes from idle client errors. Use `pg-query-stream` for large result sets to avoid loading everything into memory.

---

<critical_requirements>

CRITICAL: Before Using This Skill

**(You MUST use parameterized queries (`$1`, `$2`, ...) for ALL values -- NEVER concatenate or interpolate user input into SQL strings)**

**(You MUST use `Pool` for all database access -- NEVER create standalone `Client` instances in application code)**

**(You MUST release clients back to the pool in a `finally` block after `pool.connect()` -- leaked clients exhaust the pool and hang the application)**

**(You MUST handle the `error` event on Pool instances -- unhandled idle client errors crash the Node.js process)**

</critical_requirements>

---

Examples

• [Core Patterns](examples/core.md) -- Pool setup, parameterized queries, type-safe results, error handling
• [Transactions](examples/transactions.md) -- BEGIN/COMMIT/ROLLBACK, savepoints, retry logic, advisory locks
• [Streaming](examples/streaming.md) -- Cursors, pg-query-stream, LISTEN/NOTIFY for real-time
• [Advanced](examples/advanced.md) -- SSL/TLS, prepared statements, migrations, testing patterns

**Additional resources:**

• [reference.md](reference.md) -- Pool options, error codes, QueryResult properties, production checklist

---

**Auto-detection:** PostgreSQL, pg, node-postgres, Pool, Client, pool.query, pool.connect, client.query, $1, parameterized query, BEGIN, COMMIT, ROLLBACK, LISTEN, NOTIFY, pg_notify, pg-query-stream, pg-cursor, Cursor, QueryResult, QueryResultRow, connectionString, PGHOST, PGDATABASE, unique_violation, 23505, deadlock, 40P01, advisory lock

**When to use:**

• Direct SQL queries against PostgreSQL (not behind an ORM)
• Connection pool management for Node.js/PostgreSQL applications
• Transactions spanning multiple queries that must be atomic
• Streaming large result sets without loading everything into memory
• Real-time change notifications via LISTEN/NOTIFY
• Integration testing with transaction rollback isolation

**Key patterns covered:**

• Pool configuration and lifecycle (creation, error handling, graceful shutdown)
• Parameterized queries with `$1`-style placeholders (SQL injection prevention)
• Type-safe query results using TypeScript generics
• Transaction management with dedicated clients
• Streaming with pg-cursor and pg-query-stream
• LISTEN/NOTIFY for real-time PostgreSQL event handling
• PostgreSQL error code handling (constraint violations, deadlocks, serialization failures)
• SSL/TLS connection configuration
• Testing with transaction rollback isolation

**When NOT to use:**

• You need an ORM or query builder -- use your ORM/query builder skill instead
• You need in-memory caching -- use a caching solution
• You need document storage without relational constraints -- use a document database
• Simple key-value lookups at sub-millisecond latency -- use an in-memory data store

---

<philosophy>

Philosophy

`pg` (node-postgres) is a **low-level PostgreSQL client** that gives you full control over SQL, connections, and transactions. The core principle: **write SQL directly, let PostgreSQL do the heavy lifting.**

**Core principles:**

1. **Pool, never Client** -- Application code should always use `Pool`. The pool manages connections, handles reconnection, and prevents connection exhaustion. Use `pool.query()` for single queries, `pool.connect()` when you need a dedicated client (transactions). 2. **Parameterized everything** -- Never build SQL by string concatenation. Use `$1`, `$2` placeholders. This prevents SQL injection AND enables PostgreSQL query plan caching. 3. **Release in finally** -- Any client obtained via `pool.connect()` must be released in a `finally` block. A leaked client sits checked out forever, and once `max` clients leak, the pool deadlocks. 4. **Fail loudly** -- Handle the pool's `error` event. Handle query errors with specific PostgreSQL error codes. Never swallow errors silently. 5. **Stream large results** -- Don't `SELECT *` a million rows into memory. Use `pg-cursor` or `pg-query-stream` for large result sets.

</philosophy>

---

<patterns>

Core Patterns

Pattern 1: Pool Setup

Create a single pool per database at application startup. See [examples/core.md](examples/core.md) for full configuration examples.

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

**Why good:** Named constants for pool config, environment variable for connection string, error handler prevents process crash from idle client errors

// ❌ 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