PostgreSQL from Node

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

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

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

// 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 })
})

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

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

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)

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/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);

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

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

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

// 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)
}