# PostgreSQL from Node

> Use PostgreSQL from TypeScript with node-postgres: pools, parameterised queries, typed rows, transactions, migrations and a repository layer.

Backend with Node.js roadmap · Data · 18 min read · https://levelcode.org/roadmaps/node-backend/postgres-node

In-memory arrays vanish on restart and can't be shared between server processes. Real data lives in a database, and for most backends that database is **PostgreSQL**. This lesson connects a Node API to PostgreSQL with `pg` (node-postgres), the most widely used driver: connection pools, safe parameterised queries, typing rows, transactions, schema migrations, and a repository layer that keeps SQL out of your route handlers. It assumes you can write basic SQL; the [SQL roadmap](https://levelcode.org/roadmaps/sql) teaches it from scratch.

## Running PostgreSQL locally

The quickest way is a container:

```bash
docker run -d --name orders-db -p 5432:5432 \
  -e POSTGRES_USER=orders -e POSTGRES_PASSWORD=orders -e POSTGRES_DB=orders postgres:17
```

```ini
# .env
DATABASE_URL=postgres://orders:orders@localhost:5432/orders
```

```bash
npm install pg
npm install --save-dev @types/pg
```

## Connection pools

Opening a PostgreSQL connection is expensive (a TCP and TLS handshake, authentication, a new server process), so you don't open one per request. A **pool** keeps a set of open connections and lends them out:

```ts
// src/db.ts
import pg from 'pg'
import { config } from './config.ts'

export const pool = new pg.Pool({
  connectionString: config.DATABASE_URL,
  max: 10,                        // at most 10 connections from this process
  idleTimeoutMillis: 30_000,      // close connections idle for 30 s
  connectionTimeoutMillis: 5_000, // fail if no connection is free within 5 s
})

pool.on('error', (err) => {
  // An idle connection broke (database restarted, network blip). The pool replaces it.
  console.error({ msg: 'idle database connection error', err })
})
```

`pool.query(...)` borrows a connection, runs the query, and returns the connection automatically.

How big should the pool be? Smaller than you'd think. PostgreSQL handles each connection with a separate process, and a few dozen active ones saturate a typical server. Five application instances with `max: 10` already means 50 connections. Requests beyond the pool size simply wait briefly for a free connection, which is better than overloading the database.

## Parameterised queries

Pass values **separately** from the SQL text, as `$1`, `$2` placeholders:

```ts
const { rows } = await pool.query(
  'SELECT id, customer_email, status FROM orders WHERE customer_email = $1 AND status = $2',
  [email, 'paid'],
)
```

The driver sends the SQL and the values to PostgreSQL separately, so a value can never be interpreted as SQL. Never build SQL with template strings:

```ts
// SQL injection: email = "x' OR '1'='1" returns every order in the table
await pool.query(`SELECT * FROM orders WHERE customer_email = '${email}'`)
```

Placeholders work for **values** only. Table names, column names and sort directions can't be parameters; if they come from user input, map them through a fixed allowlist (`const column = { newest: 'created_at', total: 'total_cents' }[sort]`).

## Typing rows

`pool.query` accepts a row type, but it's a claim, not a check (the database returns whatever the SQL selects):

```ts
type OrderRow = {
  id: number
  customer_email: string
  status: 'pending' | 'paid' | 'shipped' | 'cancelled'
  total_cents: number
  created_at: Date
}

const { rows } = await pool.query<OrderRow>('SELECT * FROM orders WHERE id = $1', [id])
const order = rows[0] // OrderRow | undefined (with noUncheckedIndexedAccess)
```

Know how `pg` converts PostgreSQL types to JavaScript:

| PostgreSQL | JavaScript from `pg` | Why |
|---|---|---|
| `integer`, `real`, `double precision` | `number` | Fits safely |
| `bigint`, `numeric` | **`string`** | Can exceed JavaScript's safe integer range or lose decimal precision |
| `timestamptz`, `date` | `Date` | |
| `boolean` | `boolean` | |
| `json`, `jsonb` | parsed object | |
| `text[]` and other arrays | arrays | |

The `bigint` surprise catches everyone: `SELECT count(*)` returns `bigint`, so `rows[0].count` is `"42"`, a string. Cast in SQL (`count(*)::int`) or convert explicitly. For money, store integer cents in an `integer` or `bigint` column, never floats.

Map rows to your own domain types at the edge of the data layer, so `snake_case` columns and driver quirks don't leak into the rest of the app:

```ts
export type Order = { id: number; customerEmail: string; status: OrderRow['status']; totalCents: number; createdAt: Date }

const toOrder = (r: OrderRow): Order => ({
  id: r.id,
  customerEmail: r.customer_email,
  status: r.status,
  totalCents: Number(r.total_cents),
  createdAt: r.created_at,
})
```

## Migrations

The schema changes over time, and every environment (your laptop, CI, production) must get the same changes in the same order. **Migrations** are numbered SQL files applied once each, recorded in a table:

```sql
-- migrations/001_create_orders.sql
CREATE TABLE orders (
  id             integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_email text        NOT NULL,
  status         text        NOT NULL DEFAULT 'pending'
                             CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  total_cents    integer     NOT NULL CHECK (total_cents >= 0),
  created_at     timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id   integer NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  sku        text    NOT NULL,
  quantity   integer NOT NULL CHECK (quantity > 0),
  unit_cents integer NOT NULL CHECK (unit_cents >= 0),
  PRIMARY KEY (order_id, sku)
);

CREATE INDEX orders_customer_email_idx ON orders (customer_email);
```

A small runner applies pending files at startup, each in its own transaction:

```ts
// src/migrate.ts
import { readdir, readFile } from 'node:fs/promises'
import path from 'node:path'
import type pg from 'pg'

export async function migrate(pool: pg.Pool, dir = path.join(import.meta.dirname, '..', 'migrations')) {
  const client = await pool.connect()
  try {
    await client.query('SELECT pg_advisory_lock(727272)') // one migrator at a time across instances
    await client.query(
      'CREATE TABLE IF NOT EXISTS schema_migrations (name text PRIMARY KEY, applied_at timestamptz NOT NULL DEFAULT now())',
    )
    const done = new Set((await client.query<{ name: string }>('SELECT name FROM schema_migrations')).rows.map((r) => r.name))
    const files = (await readdir(dir)).filter((f) => f.endsWith('.sql')).sort()

    for (const file of files) {
      if (done.has(file)) continue
      const sql = await readFile(path.join(dir, file), 'utf8')
      await client.query('BEGIN')
      try {
        await client.query(sql)
        await client.query('INSERT INTO schema_migrations (name) VALUES ($1)', [file])
        await client.query('COMMIT')
        console.log(`applied ${file}`)
      } catch (err) {
        await client.query('ROLLBACK')
        throw new Error(`migration ${file} failed`, { cause: err })
      }
    }
  } finally {
    await client.query('SELECT pg_advisory_unlock(727272)')
    client.release()
  }
}
```

Rules that keep migrations safe: never edit a migration that has run anywhere (add a new one instead), and make changes in backwards-compatible steps when old and new code run side by side during a deploy (add a column as nullable, backfill, then add the constraint). Tools such as `node-pg-migrate`, Drizzle Kit or Prisma Migrate do the same job with more features.

## Transactions

A transaction makes several statements succeed or fail together. With a pool, every statement in a transaction must run on the **same connection**, so check one out with `pool.connect()`:

```ts
// src/db.ts (continued)
export async function withTransaction<T>(fn: (client: pg.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 (err) {
    await client.query('ROLLBACK')
    throw err
  } finally {
    client.release() // always return the connection, or the pool slowly runs dry
  }
}
```

```ts
const order = await withTransaction(async (tx) => {
  const { rows } = await tx.query<{ id: number }>(
    'INSERT INTO orders (customer_email, total_cents) VALUES ($1, $2) RETURNING id',
    [input.customerEmail, totalCents],
  )
  const orderId = rows[0]!.id
  for (const item of input.items) {
    await tx.query('INSERT INTO order_items (order_id, sku, quantity, unit_cents) VALUES ($1, $2, $3, $4)', [
      orderId, item.sku, item.quantity, prices[item.sku],
    ])
  }
  return orderId
})
```

If any insert fails, `ROLLBACK` removes the order too: no half-written orders. The most common pool bug is a code path that checks out a client and never releases it; after `max` such requests, every query waits forever. Always release in `finally`, or use a helper like this one.

> [!WARNING]
> Don't call `pool.query` inside a transaction callback: it runs on a *different* connection, outside the transaction. Use the `tx` client you were given for every statement that belongs to the transaction.

## A repository layer

Keep SQL in one module per feature, exposing plain functions with domain types. Routes and services then never see SQL or rows:

```ts
// src/orders/repository.ts
import { pool } from '../db.ts'

export async function findOrder(id: number): Promise<Order | undefined> {
  const { rows } = await pool.query<OrderRow>('SELECT * FROM orders WHERE id = $1', [id])
  return rows[0] && toOrder(rows[0])
}

export async function listOrders(opts: { status?: Order['status']; limit: number; afterId?: number }): Promise<Order[]> {
  const { rows } = await pool.query<OrderRow>(
    `SELECT * FROM orders
     WHERE ($1::text IS NULL OR status = $1)
       AND ($2::int IS NULL OR id > $2)
     ORDER BY id
     LIMIT $3`,
    [opts.status ?? null, opts.afterId ?? null, opts.limit],
  )
  return rows.map(toOrder)
}
```

`listOrders` uses **keyset pagination** (`id > last seen id`) rather than `OFFSET`, which stays fast on large tables, and optional filters written as `$1 IS NULL OR ...` so one query handles every combination.

Writing SQL by hand gives you full control and no surprises. Query builders and ORMs trade some of that for convenience and type inference:

| Tool | Style | Notes |
|---|---|---|
| `pg` with SQL | Raw SQL | Full control; you write row types yourself |
| Kysely | Type-safe query builder | SQL-shaped API, types inferred from your schema |
| Drizzle | Query builder plus schema in TypeScript | Migrations generated from the schema |
| Prisma | ORM with its own schema language | Most abstraction; generated client |

All of them sit on the same ideas: pools, parameters, transactions and migrations.

## Practice

Back the orders API with PostgreSQL:

1. Write the migration above plus the runner, and run migrations at startup.
2. Implement `createOrder(input)` in a transaction (order plus items, with prices from a fixed `PRICES` map; an unknown SKU throws a validation error before anything is written), `findOrder(id)` returning the order with its items, and `listOrders` with keyset pagination.
3. Add `GET /orders/:id`, `GET /orders?status=&after=&limit=`, `POST /orders` routes using the schemas from the previous lesson.
4. Prove the transaction works: make inserting the second item fail (for example, the same SKU twice violates the primary key) and check no order row remains.
5. Prove the pool releases connections: create a pool with `max: 2`, run 20 `createOrder` calls concurrently, and check they all succeed.

**Solution**

```ts
// src/orders/repository.ts
import { pool, withTransaction } from '../db.ts'

export const PRICES: Record<string, number> = { 'PEN-01': 120, 'BAG-01': 3500, 'NOTE-01': 350 }

export type OrderStatus = 'pending' | 'paid' | 'shipped' | 'cancelled'
type OrderRow = { id: number; customer_email: string; status: OrderStatus; total_cents: number; created_at: Date }
type ItemRow = { sku: string; quantity: number; unit_cents: number }

export type Order = {
  id: number
  customerEmail: string
  status: OrderStatus
  totalCents: number
  createdAt: Date
  items?: { sku: string; quantity: number; unitCents: number }[]
}

const toOrder = (r: OrderRow): Order => ({
  id: r.id, customerEmail: r.customer_email, status: r.status, totalCents: r.total_cents, createdAt: r.created_at,
})

export class UnknownSkuError extends Error {}

export async function createOrder(input: { customerEmail: string; items: { sku: string; quantity: number }[] }) {
  for (const item of input.items) if (!(item.sku in PRICES)) throw new UnknownSkuError(`unknown SKU ${item.sku}`)
  const total = input.items.reduce((sum, i) => sum + PRICES[i.sku]! * i.quantity, 0)

  return withTransaction(async (tx) => {
    const { rows } = await tx.query<{ id: number }>(
      'INSERT INTO orders (customer_email, total_cents) VALUES ($1, $2) RETURNING id',
      [input.customerEmail, total],
    )
    const orderId = rows[0]!.id
    for (const item of input.items) {
      await tx.query('INSERT INTO order_items (order_id, sku, quantity, unit_cents) VALUES ($1, $2, $3, $4)', [
        orderId, item.sku, item.quantity, PRICES[item.sku],
      ])
    }
    return orderId
  })
}

export async function findOrder(id: number): Promise<Order | undefined> {
  const { rows } = await pool.query<OrderRow>('SELECT * FROM orders WHERE id = $1', [id])
  if (!rows[0]) return undefined
  const items = await pool.query<ItemRow>('SELECT sku, quantity, unit_cents FROM order_items WHERE order_id = $1 ORDER BY sku', [id])
  return { ...toOrder(rows[0]), items: items.rows.map((i) => ({ sku: i.sku, quantity: i.quantity, unitCents: i.unit_cents })) }
}

export async function listOrders(opts: { status?: OrderStatus; limit: number; afterId?: number }) {
  const { rows } = await pool.query<OrderRow>(
    `SELECT * FROM orders WHERE ($1::text IS NULL OR status = $1) AND ($2::int IS NULL OR id > $2) ORDER BY id LIMIT $3`,
    [opts.status ?? null, opts.afterId ?? null, opts.limit],
  )
  const items = rows.map(toOrder)
  return { items, nextAfter: items.length === opts.limit ? items.at(-1)!.id : null }
}
```

```ts
// check-db.ts
import { pool } from './src/db.ts'
import { migrate } from './src/migrate.ts'
import { createOrder, findOrder, listOrders } from './src/orders/repository.ts'

await migrate(pool)

const id = await createOrder({ customerEmail: 'ana@example.com', items: [{ sku: 'PEN-01', quantity: 3 }, { sku: 'BAG-01', quantity: 1 }] })
console.log(await findOrder(id))

const before = (await pool.query('SELECT count(*)::int AS n FROM orders')).rows[0].n
try {
  await createOrder({ customerEmail: 'ben@example.com', items: [{ sku: 'PEN-01', quantity: 1 }, { sku: 'PEN-01', quantity: 2 }] })
} catch (err) {
  console.log('failed as expected:', (err as Error).message)
}
const after = (await pool.query('SELECT count(*)::int AS n FROM orders')).rows[0].n
console.log({ before, after }) // equal: the order insert was rolled back

const results = await Promise.all(
  Array.from({ length: 20 }, (_, i) => createOrder({ customerEmail: `c${i}@example.com`, items: [{ sku: 'NOTE-01', quantity: 1 }] })),
)
console.log(`${results.length} concurrent orders created`)
console.log((await listOrders({ limit: 5 })).nextAfter)
await pool.end()
```

```text
applied 001_create_orders.sql
{ id: 1, customerEmail: 'ana@example.com', status: 'pending', totalCents: 3860, createdAt: 2026-11-01T..., items: [ { sku: 'BAG-01', quantity: 1, unitCents: 3500 }, { sku: 'PEN-01', quantity: 3, unitCents: 120 } ] }
failed as expected: duplicate key value violates unique constraint "order_items_pkey"
{ before: 1, after: 1 }
20 concurrent orders created
6
```

Why is the fifth order's ID 6 and not 5? The failed order's `INSERT` took ID 2 before the transaction rolled back, and identity (sequence) values are never handed back, even on rollback. That's deliberate: it keeps ID generation fast under concurrency. So IDs are unique and increasing, but not gapless: never use them to count rows or as invoice numbers that must be consecutive.

With `max: 2`, the 20 concurrent `createOrder` calls queue for the two connections and all complete, because `withTransaction` releases each connection in `finally`. Remove that `release()` and the third call waits forever (until `connectionTimeoutMillis` fails it), which is exactly the leak to look for when an API mysteriously stops answering. `findOrder` makes two queries; fetching items for a *list* of orders that way would be an N+1 problem, solved by one query with `WHERE order_id = ANY($1)`.

## Key takeaways

- Share one `pg.Pool` per process, keep it small, and handle the pool's `error` event.
- Always pass values as `$1`, `$2` parameters; allowlist identifiers such as column names that can't be parameters.
- Row type arguments are claims; know that `bigint` and `numeric` arrive as strings, and map rows to domain types in the data layer.
- Apply numbered SQL migrations in order, once each, under a lock, and never edit one that has run.
- Run transactions on one checked-out client, roll back on error, and always release the client in `finally`.

## Quiz

1. Why does SELECT count(*) FROM orders return "42" (a string) through pg?

   A. pg returns every column as a string
   B. count(*) is bigint, which pg returns as a string to avoid losing precision beyond JavaScript's safe integer range
   C. The query is missing a type argument
   D. PostgreSQL stores counts as text

   **Answer: B.** bigint and numeric can exceed what a JavaScript number represents exactly, so pg returns them as strings. Cast with ::int when the value is small, or convert explicitly.

2. Inside withTransaction(async (tx) => ...), you call pool.query('INSERT ...'). What's the problem?

   A. Nothing; pool.query joins the current transaction
   B. It deadlocks immediately
   C. It runs on a different connection, outside the transaction, so it won't roll back with the rest
   D. pool.query can't run INSERT statements

   **Answer: C.** A transaction belongs to one connection. Every statement that should be part of it must use the checked-out tx client.

3. After a deploy, the API gradually stops responding, and the logs show queries waiting for a connection. What's the most likely cause?

   A. A code path checks out a client with pool.connect() and doesn't release it
   B. The pool's max is too large
   C. Too many parameterised queries
   D. The migration table is missing

   **Answer: A.** Each unreleased client permanently removes a connection from the pool. Once all are leaked, every query waits. Release in finally.

4. A migration has already run in production and contains a mistake. What should you do?

   A. Edit the migration file and redeploy
   B. Delete its row from schema_migrations and run it again
   C. Drop the database and re-run all migrations
   D. Write a new migration that corrects it

   **Answer: D.** Environments that already applied the old version would never see an edit. A new migration moves every environment forward consistently.