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

There is a version of building a REST API that lives in tutorials: a routes file, a list endpoint that selects everything from a table, a congratulations screen. It is not wrong, exactly. It is incomplete in a way that only becomes visible at 2 a.m., when the API that worked in the demo is returning 500s on a slightly malformed input, the logs say nothing useful, and the database is quietly missing the one index that would have kept the dashboard under a second.

I have built and rescued enough APIs to know the gap between tutorial-API and production-API is not about talent. It is about a checklist that nobody hands you. This is that checklist, ordered the way the problems actually arrive. Examples use Node.js, Express, and PostgreSQL, but the principles port to almost any stack — including the Next.js route handlers I use for smaller services.

Start with the schema, not the routes

Every post-mvp crisis I have been handed traced back to a schema designed after the code. Someone built routes first, tables second, and by the tenth feature the data model had contradicting the application's actual nouns. Schema is not plumbing. It is the durable, load-bearing description of your business — orders, customers, subscriptions, entitlements — and every five-star feature request lands on it eventually.

My working rules, gathered from scar tissue:

• Name tables by nouns, singular or plural consistently. Yes, it is pedantic. No, it is not negotiable. Half of API confusion is naming hesitation communicated as rendering bugs.
• Timestamps on everything, from day one. created_at and updated_at with defaults. You cannot retrofit time; you can only reconstruct it expensively from logs.
• UUIDs for public identifiers. Sequential IDs leak volume and — in some products — user counts. A UUID primary key plus an internal index makes reports safe to expose.
• Constraints are requirements. Every rule you know in English — an order belongs to exactly one customer; an invoice cannot be negative — becomes a constraint, a check, or a foreign key. Application code is a suggestion; the database is the law.
• Indexes for the queries you will actually run, not the ones you imagine. Foreign keys, filter columns, sort columns. Then verify with EXPLAIN ANALYZE on realistic data volume, because query plans change completely at 50 rows versus 5 million.

One concrete example from my own work: in the Tillqorin project, a sale touches orders, order items, inventory movements, and the expense ledger. The schema encodes the invariant that every sale decrements inventory exactly once through database constraints alone — the application layer can be buggy-messy and the books still reconcile. That is the level at which schema design pays rent: it makes whole categories of bug structurally impossible rather than merely rare.

Layer the Express app like you will actually maintain it

The single-file API is a learning exercise. The layered API survives contact with a second developer — which, in my experience, is the realistic horizon for most client work. The shape that has held up across my projects:

src/
  routes/        // HTTP in, service out — thin
  services/      // business rules, zero request objects
  repositories/  // SQL only, no business decisions
  middleware/    // auth, validation, rate limiting
  errors/        // typed ApiError classes
  db/            // pool, migrations, seeds

The rule that makes the layers real: SQL never appears in route handlers. Ever. Not "rarely." The moment route handlers talk to the database directly, testing means booting a server, documentation means reading code, and swapping the data layer means rewriting routes. With the layers, a route reads like a table of contents: validate, authorize, call service, shape response. Reviewers can find the change that matters in seconds.

Services stay free of Express types. A service function takes plain data and returns plain data. That one discipline is what makes your business logic testable without HTTP at all — and eventually it is what makes it portable to whatever platform your product outgrows Express for.

Validate every input at the boundary — literally every input

Half of production 500s are validation gaps wearing a security hat. The request that blew up was always slightly different from the request you imagined: a string where a number was expected, a query parameter that is a comma when it should be a dash, a body field that is an array when the schema wanted an object.

Boundary validation with Zod or Joi, on body, params, and query string alike, converts these from 500s with stack traces into clean 400s with human messages:

const createOrderSchema = z.object({
  customer_id: z.string().uuid(),
  items: z.array(z.object({
    sku: z.string().min(1),
    qty: z.number().int().positive(),
  })).min(1),
  notes: z.string().max(2000).optional(),
}).strict()  // reject unknown fields — silently ignored payload keys are a bug factory

Cast types explicitly even where Postgres would coerce. ?page=abc should return a 400 from your validator, not a 500 from the database engine discovering it on page 47 of your logs. And validate command semantics, not just shape — a quantity of 999999999 passes shape checks and still ruins your inventory math. Validation is where you encode "what a plausible request looks like," and vague imagination there becomes incident reports later.

Errors: envelopes, statuses, and the honest status code

An API's error story is its user interface for machines. If clients have to parse error strings to know what went wrong, you have outsourced your debugging to strangers. The envelope I standardize on:

{
  "error": {
    "code": "ORDER_NOT_FOUND",
    "message": "No order with id 8f3c… for this account.",
    "request_id": "req_9f2a1b"
  }
}

Codes are stable enumerations clients can branch on. Messages are human, non-leaky (no SQL fragments, no stack traces, ever). Request IDs flow from your logs into the response so support can grep an incident from one side to the other.

Status codes deserve a honesty note: 400 for the client's input is wrong, 401 for missing credentials, 403 for lacking permission, 404 for missing resources (and do not be afraid to use it defensively — 404 on resources the caller should not know exist leaks less than a 403 proves existence), 409 for conflicts like unique violations, 422 for well-formed-but-meaningless requests, 429 for rate limits, and 500 only when it is genuinely your fault. Mapping Postgres error codes at the repository boundary keeps this honest automatically: 23505 becomes 409, serialization failures become 503 with retry hints, and so on down the list.

Auth that survives real requirements

Auth is where tutorials and production diverge most, because real requirements hide inside "just add login": session vs token, refresh and rotation, role granularity, account lockout, and the ten ways a frontend stores a token badly.

• Short-lived JWTs or server sessions — my default is server sessions in httpOnly cookies for web apps, tokens only when a non-browser client needs them. httpOnly + Secure + SameSite blocks the whole class of XSS token theft that localStorage invites.
• Hash passwords with bcrypt or argon2. Nobody needs to hear this twice, yet someone every year does not.
• Authorization at the service layer, not per-route hooks. "Can this user act on this object?" is business logic; middleware can only answer "is this request authenticated?"” Confusing the two is how endpoints end up trusting user IDs from the request body.
• Rate limit public endpoints — particularly anything that emails, SMSes, or computes. The compromise endpoint is always the one that costs money per call.
• Verify webhook signatures (Stripe, email providers, etc.) and process webhook payloads asynchronously from a queue, not inline. Webhooks will retry; your handler should expect that and stay idempotent.

Performance: the three moves that matter

API performance conversations drown in optimization tactics, but three dominate everything:

• Pagination everywhere, cursor-based for anything beyond small, bounded lists. "Limit and offset" degrades linearly and then catastrophically; cursors stay flat.
• Kill N+1 queries. Every ORM temptingly lazy-loads relations in a loop. Watch your dev logs for query counts per request; a view that issues 1 + N queries is the single most common "why is this slow" I meet.
• Cache the expensive, bounded read. Dashboard aggregates, reference data, rate-card lookups. Short TTL caches at the HTTP layer (with careful invalidation) buy back more latency than any micro-tuning, and they fail gracefully — a stale report is a stale report, not an outage.

Then measure honesty: p95 not averages, real production data shapes, and — this one saves careers — a dashboard alerting on error rate and latency drift. You will not tune what you cannot see, and "users say it feels slow" is a signal that arrives after the damage is done.

Deployment and the boring end of the lifecycle

Migrations run on deploy, in order, backward-compatible (add columns, backfill, then switch reads). NODE_ENV strict, secrets in the platform's secret store, never in the repo, never in client bundles. Health and readiness endpoints that actually check the database, not just the process — a green tick from an HTTP server with a dead database pool is worse than no healthcheck, because it routes traffic to a zombie. Backups: automated, and test a restore quarterly, because the restore is the thing you actually need; a backup nobody has ever restored is a hope, not a plan.

Observability round out with structured logs (JSON with request IDs), an error tracker, and a simple uptime check from outside your own platform. All three together get you to a Wednesday where a complaint becomes a five-minute lookup instead of a seance.

FAQ

Express or Fastify or NestJS in 2026? Express for maximal familiarity and hiring; Fastify when you want better performance without ceremony; NestJS when the team wants strong structure and can afford its opinions. All three build the same REST API; the layers above matter more than the router below.

REST or GraphQL? RESTUnless your clients need to assemble wildly varying shapes of the same data, REST with good resource design is simpler to cache, secure, hire for, and document. GraphQL earns its complexity in specific products; most products are not those products.

Raw SQL, Prisma, or Drizzle? Any of them, with the repository discipline. My personal split: raw SQL or Drizzle for query-heavy domains where I want the plan in my head, Prisma when velocity on CRUD matters more than query control.

How do I version an API? Path prefixes (/v1) and additive change discipline. Break changes mint a new prefix; deprecations carry sunset headers and real notice. Most small products over-think versioning because they have not yet felt the pain that versioning prevents.

Idempotency, concurrency, and the honest distributed reality

Two concepts turn API engineering from "works on my machine" into "works when the network disagrees with you," and they deserve a section each because interview advice covers neither adequately.

Idempotency means the same request applied twice leaves the same state as applied once. Clients retry — mobile networks drop, browsers retry idempotently on their own, users double-click — and the fijación is not "tell users to stop." Design write endpoints so retries are safe:

• Client-supplied idempotency keys on anything that moves money or creates resources. Store them with a TTL; replay returns the original result instead of creating a duplicate.
• Unique constraints as the backstop. A UNIQUE(order_id, email) makes the double-send a 409 rather than two orders — the database refusing is the most reliable idempotency layer you have.
• Webhooks are retries. Providers redeliver; your handler must deduplicate on event IDs, not assume delivery once. I have watched duplicate- payment bugs live entire quarters because "it should only fire once" was treated as a contract.

Concurrency is the conversation between two requests that think they are alone. The inventory race I described in database terms earlier has an API-layer cousin: optimistic locking (version columns and 409-on-mismatch) versus pessimistic locking (row locks for hot resources). The pragmatic default is optimistic — conflicts are rare per resource — with pessimistic or queue-serialized access reserved for genuinely hot paths like flash-sale stock. Both approaches share one rule that saves pain: make conflict handling a designed response, visible in your API documentation, not an accidental 500 that support discovers live.

Testing and documenting the API

Two practices separate maintainable APIs from the ones that rot:

• Tests at three depths. Repository tests against a real Postgres (Docker or a disposable instance) for SQL correctness; service tests for business rules in isolation; a thin integration suite that drives representative flows through HTTP and validates the envelopes end to end. The first two run in seconds; the last one runs on merge, because its job is catching the wiring, not the logic.
• Documentation that cannot lie. OpenAPI generated from the same schemas your validation uses, so a field added to the Zod validator is in the docs without ceremony. Hand-maintained docs rot invisibly; generated ones rot loudly — when generation breaks, CI notices.

One habit I picked up from operating my own APIs: a contract test consumer — a tiny script that exercises the documented happy paths repeatedly in staging. It has caught more auth regressions than any unit test, because it tests what integrations actually do: the exact documented sequence, every day.

Observability: instrumenting for the Tuesday incident

You will debug an API incident someday, and the quality of that debugging session was decided months earlier by what you logged. The minimum viable observability set:

• Structured logs with request IDs flowing through middleware into every log line and into the response envelope — so a user-reported issue converts to a grep in seconds.
• Query timing for the slow paths, logged with parameters when over budget — this is how N+1s and missing indexes announce themselves before customers do.
• An error tracker (Sentry-class) with source maps and release tagging, so "what changed and when" is one click rather than archaeology.
• Metrics over averages: p50/p95/p99 on latency, error rate by route, saturation signals (pool size, queue depth). Averages hide the tails where users live; percentiles design for the user having the worst experience, which is the one you will hear about.

And one cultural note: incidents are schema-change rehearsals in disguise. Every production incident I have handled ended by improving either a constraint, a validation rule, or an alert threshold. Write those down as the incident closes; otherwise you pay tuition twice.

Cost of an API, thought through honestly

Small teams underestimate API work because the deliverable copy looks small — "a few endpoints." The honest accounting I give when scoping: schema and migrations (the biggest single item), endpoints including their validation and auth edges, observability, deployment automation, and documentation. Roughly a third of the total effort is everything except the endpoints themselves. When that third is cut, the endpoints ship fine and the API costs grow for years — in incident time, in onboarding time, and in the eventual rebuild. Budget the boring third; it is where the lifetime value is.

Profiling queries, auditing indexes — the actual method

Every performance chapter eventually says "measure first," and almost nothing tells you the actual workflow. Mine, in the order I apply it to a slow endpoint:

1. Time the request in isolation (curl with a stopwatch or a test harness) and split total latency between app time and database time. If the database dominates, the fix lives in SQL; if the app dominates, the loop sits in JS. Skipping this step is how N+1 hunts begin on the wrong continent.
2. Run the hot query with EXPLAIN ANALYZE and read it at the real scale — synthetic data seeded at production volumes. The plan's sequential-scan flags, mis-ordering, and estimated-versus-actual row misreads each name a different medicine: an index, a better query, or fresher planner statistics.
3. Fix the shape or add the index — never both at once. One change, re-measure, keep or revert. Composite index decisions (column order in a composite follows the equality-then-range rule) come from reading these plans, not from guessing.
4. Re-check the N+1 family after every fix — removing one lazy-load loop often unmasks the next one behind it, and measuring once never catches that.

What this method produces across a quarter is quieter than a dramatic rewrite: predictable latency you can promise customers, alerts you believe, and — my favorite side effect — a review culture where query changes arrive with their own plan outputs pasted in the PR description. Habits form from what reviewers see; show them the plan and they start expecting it.

Schema change review — the process behind the promise

Since this article opened by putting schema first, the working review checklist deserves its own short placement, because database migrations are the one artifact teams still review with vibes:

• Does it invalidate data instead of transforming it? Any migration that would lose information under rollback needs a written restore path, attached to the PR, before review.
• Backfill strategy declared: new non-null columns get a nullable phase, a backfill in batches, then the constraint — never an instant non-null on a live table.
• Index additions measured on staging row counts, because a missing index is slow and a uselessly wide index silently taxes every write.
• Lock windows considered: long table rewrites on big tables need a maintenance plan; the review question is not "does it work" but "what does the API see while it runs."

Want an API designed this way for your product? Read a case study or talk to Tayyab.