Building a Production-Grade REST API with Node.js, Express and PostgreSQL

"Build a REST API" tutorials end where the real work starts. The version below is the skeleton I use for client APIs: an order service that handles validation, pooling, transactions, pagination, idempotent creates, rate limiting, logging and shutdown correctly. It is around 350 lines; every one earned its place in an incident.

The layered structure

src/
├── server.ts            boot, graceful shutdown
├── app.ts               express app, global middleware, error handler
├── lib/
│   ├── db.ts            pg Pool + transaction helper
│   ├── logger.ts        pino, request-id aware
│   └── errors.ts        AppError + problem-details formatter
├── modules/orders/
│   ├── orders.routes.ts    HTTP: parse, validate, call service, respond
│   ├── orders.schema.ts    Zod schemas (request + response)
│   ├── orders.service.ts   business rules, transactions
│   └── orders.repo.ts      SQL only
└── middleware/
    ├── request-id.ts
    ├── rate-limit.ts
    └── idempotency.ts

HTTP ──▶ routes ──▶ service ──▶ repo ──▶ Postgres
         (Zod)     (rules)     (SQL)

The rule: service.ts never imports Express types. That single constraint makes it trivially unit-testable and reusable from a queue worker or a CLI, which matters once you add background jobs.

Database: pool and transactions

// src/lib/db.ts
import { Pool, type PoolClient } from 'pg';

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                        // per process; keep (processes × max) under Postgres max_connections
  idleTimeoutMillis: 30_000,
  connectionTimeoutMillis: 5_000,
  statement_timeout: 10_000,      // no query runs forever
});

pool.on('error', err => console.error('idle client error', err));

export async function withTransaction<T>(fn: (client: 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 (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

Pool sizing is the setting people get wrong most. Postgres defaults to 100 connections and each one costs real memory. Four API processes with max: 10 is 40; add workers and you are near the limit. If you need more, put PgBouncer in front rather than raising max_connections.

Errors as data: problem details

// src/lib/errors.ts
export class AppError extends Error {
  constructor(public status: number, public title: string, public detail?: string, public extra: Record<string, unknown> = {}) {
    super(detail ?? title);
  }
}
export const NotFound     = (what: string) => new AppError(404, 'Not found', `${what} does not exist`);
export const Conflict     = (detail: string) => new AppError(409, 'Conflict', detail);
export const Unprocessable= (issues: unknown) => new AppError(422, 'Validation failed', undefined, { issues });

// src/app.ts (error handler, registered last)
app.use((err: unknown, req: Request, res: Response, _next: NextFunction) => {
  const e = err instanceof AppError ? err : new AppError(500, 'Internal server error');
  if (e.status >= 500) req.log.error({ err }, 'unhandled error');
  res.status(e.status).type('application/problem+json').json({
    type: `https://api.example.com/problems/${e.status}`,
    title: e.title,
    status: e.status,
    detail: e.status >= 500 ? undefined : e.detail,     // never leak internals
    instance: req.originalUrl,
    requestId: req.id,
    ...e.extra,
  });
});

Every error, expected or not, comes back in the same shape with a request ID the client can quote back to you. That request ID is the single most useful thing in a support ticket.

Validation and typed routes

// src/modules/orders/orders.schema.ts
import { z } from 'zod';

export const CreateOrder = z.object({
  customerId: z.string().uuid(),
  items: z.array(z.object({ sku: z.string().min(1), qty: z.number().int().min(1).max(99) })).min(1).max(50),
  couponCode: z.string().max(32).optional(),
});
export type CreateOrder = z.infer<typeof CreateOrder>;

export const ListOrders = z.object({
  cursor: z.string().optional(),
  limit: z.coerce.number().int().min(1).max(100).default(25),
  status: z.enum(['pending', 'paid', 'shipped', 'refunded']).optional(),
});

// src/middleware/validate.ts
export const validate = (schema: z.ZodTypeAny, source: 'body' | 'query' | 'params') =>
  (req: Request, _res: Response, next: NextFunction) => {
    const r = schema.safeParse(req[source]);
    if (!r.success) return next(Unprocessable(r.error.flatten()));
    req[source] = r.data;                     // replaced with the parsed, coerced, typed version
    next();
  };

// src/modules/orders/orders.routes.ts
import { Router } from 'express';
import { validate } from '@/middleware/validate';
import { idempotent } from '@/middleware/idempotency';
import { CreateOrder, ListOrders } from './orders.schema';
import * as service from './orders.service';

export const orders = Router();

orders.get('/', validate(ListOrders, 'query'), async (req, res, next) => {
  try { res.json(await service.list(req.query as any)); } catch (e) { next(e); }
});

orders.get('/:id', async (req, res, next) => {
  try { res.json(await service.get(req.params.id)); } catch (e) { next(e); }
});

orders.post('/', idempotent(), validate(CreateOrder, 'body'), async (req, res, next) => {
  try {
    const order = await service.create(req.body as CreateOrder, req.user.id);
    res.status(201).location(`/orders/${order.id}`).json(order);
  } catch (e) { next(e); }
});

The service: rules and transactions

// src/modules/orders/orders.service.ts
import { withTransaction } from '@/lib/db';
import * as repo from './orders.repo';
import { NotFound, Conflict } from '@/lib/errors';
import type { CreateOrder } from './orders.schema';

export async function create(input: CreateOrder, actorId: string) {
  return withTransaction(async (tx) => {
    const prices = await repo.lockPrices(tx, input.items.map(i => i.sku));   // SELECT ... FOR UPDATE on stock rows
    for (const item of input.items) {
      const p = prices.get(item.sku);
      if (!p) throw NotFound(`SKU ${item.sku}`);
      if (p.stock < item.qty) throw Conflict(`Insufficient stock for ${item.sku}: ${p.stock} left`);
    }
    const total = input.items.reduce((s, i) => s + prices.get(i.sku)!.priceCents * i.qty, 0);
    const order = await repo.insertOrder(tx, { customerId: input.customerId, totalCents: total, createdBy: actorId });
    await repo.insertItems(tx, order.id, input.items, prices);
    await repo.decrementStock(tx, input.items);
    await repo.outbox(tx, 'order.created', { orderId: order.id });     // event for workers, same transaction
    return order;
  });
}

export async function list(q: { cursor?: string; limit: number; status?: string }) {
  const rows = await repo.listAfter(q.cursor, q.limit + 1, q.status);   // fetch one extra to know if there's more
  const hasMore = rows.length > q.limit;
  const items = hasMore ? rows.slice(0, -1) : rows;
  return { items, nextCursor: hasMore ? encodeCursor(items[items.length - 1]) : null };
}

Stock check, order insert, item insert and stock decrement all succeed or all roll back. The outbox row is the transactional-outbox pattern: the "order created" event is written in the same transaction, and a worker publishes it afterwards, so you never emit an event for an order that did not commit.

Cursor pagination in SQL

-- keyset pagination: stable under inserts, O(log n) with the right index
SELECT id, status, total_cents, created_at
FROM orders
WHERE ($1::text IS NULL OR status = $1)
  AND (created_at, id) < ($2::timestamptz, $3::uuid)     -- decoded from the cursor
ORDER BY created_at DESC, id DESC
LIMIT $4;

CREATE INDEX orders_created_id_idx ON orders (created_at DESC, id DESC);

OFFSET 50000 scans and discards 50,000 rows; keyset pagination seeks straight to the row. Composite index column order is what makes it fast.

Idempotency keys for POST

Mobile clients retry. Payment webhooks retry. Without idempotency, a retried POST creates two orders. The client sends an Idempotency-Key header; the server stores the response for that key and replays it on retry.

// src/middleware/idempotency.ts
import { redis } from '@/lib/redis';

export const idempotent = (ttlSeconds = 86_400) => async (req: Request, res: Response, next: NextFunction) => {
  const key = req.get('Idempotency-Key');
  if (!key) return next();                                          // optional for now; make required per client
  const scoped = `idem:${req.user.id}:${key}`;

  const cached = await redis.get(scoped);
  if (cached) { const { status, body } = JSON.parse(cached); return res.status(status).set('Idempotent-Replayed', 'true').json(body); }

  const lock = await redis.set(`${scoped}:lock`, '1', 'NX', 'EX', 30);   // stop concurrent duplicates
  if (!lock) return next(new AppError(409, 'Conflict', 'Request with this Idempotency-Key is in progress'));

  const json = res.json.bind(res);
  res.json = (body: unknown) => {
    if (res.statusCode < 500) redis.set(scoped, JSON.stringify({ status: res.statusCode, body }), 'EX', ttlSeconds);
    return json(body);
  };
  next();
};

The operational layer

// src/app.ts (excerpt)
import pinoHttp from 'pino-http';
import { rateLimit } from 'express-rate-limit';
import { randomUUID } from 'node:crypto';

app.use((req, _res, next) => { req.id = req.get('x-request-id') ?? randomUUID(); next(); });
app.use(pinoHttp({ genReqId: req => req.id, redact: ['req.headers.authorization'] }));
app.use(rateLimit({ windowMs: 60_000, limit: 120, standardHeaders: 'draft-7', keyGenerator: req => req.user?.id ?? req.ip }));

app.get('/health', async (_req, res) => {
  try { await pool.query('SELECT 1'); res.json({ ok: true, db: 'up' }); }
  catch { res.status(503).json({ ok: false, db: 'down' }); }
});

// src/server.ts -- graceful shutdown so in-flight requests finish during deploys
const server = app.listen(3000);
for (const sig of ['SIGTERM', 'SIGINT']) {
  process.on(sig, () => {
    server.close(async () => { await pool.end(); process.exit(0); });
    setTimeout(() => process.exit(1), 10_000).unref();     // hard stop if something hangs
  });
}

Graceful shutdown is what makes zero-downtime deploys actually zero-downtime: the orchestrator sends SIGTERM, the server stops accepting connections, finishes what it has, closes the pool and exits. Without it, every deploy drops whatever requests were mid-flight.

The production checklist

This is the same skeleton that runs several client APIs I maintain, some on Node, some on Bun (see the comparison). If your API is growing past what a single service should hold, read Monolith vs Microservices before splitting anything; the module folders above are already the boundaries you would extract along.