Mapping Database Tables to API Resources, Step by Step

Turn database tables into API resources: map columns, HTTP methods to SQL, one-to-one, one-to-many and many-to-many relations, joins, embedding and…

Mapping database tables to an API: common questions

Should every database table have its own API endpoint?

No. Tables are normalised for storage; resources are shaped for consumers. One-to-one tables merge into one resource, join tables disappear into fields on the relationship, and some tables never appear at all.

How do you show a one-to-many relationship in a REST API?

Embed a small copy of the parent in each child when screens always need it, link by ID otherwise, offer ?expand= on request, and use a paged sub-collection such as /venues/{id}/events for the parent’s page.

Related chapters

Keep learning

Overview

The internal number leaks how many events exist and invites people to guess /events/1048577. Use the public ID instead. There is nothing to convert: the public_id column already holds the safe outward ID. Right: the bigint stays inside for joins and indexes. Callers get the public ID. "id": "evt_8Kq2mZ" It is the right value, but the field should simply be called “id” on the outside. Right: renamed to “id”. The caller never sees two kinds of ID. Then callers have no ID at all to send back in the next request. 'Arijit Live' "name": "Arijit Live" Right: a clear name and a plain string. Nothing to change. The name is the main thing the screen shows. "starts_at": "2026-11-12T19:00:00+05:30" The database print format differs per driver. Send an RFC 3339 string with an offset. Right: same instant, written as RFC 3339 in the venue’s time zone. The start time is essential for the event page. “price_paise” leaks the storage unit into the name, and the currency is missing. Right: a money object with integer minor units and the currency, as in the payload chapter. Buyers need the price. "status": "on_sale" Nobody outside knows what 3 means. Map the number to a readable word. Right: 3 becomes "on_sale". The lookup table stays inside. The app needs to know whether tickets can be bought. 'VIP list in drive' Staff notes would go to every buyer. Columns like this are why you never serialise rows directly. There is no safe form of an internal note. It stays in the database. Right: internal data never leaves the building. Usually noise for a buyer. Expose it only if a real consumer needs it (admin tools often do). Fine for an admin API, but the buyer screen has no use for it. Right for the public API: only send what the consumer needs. Soft delete is a storage detail. Deleted rows are simply not returned (or answered with 404 or 410). Deletion shows up as “this event does not exist any more”, not as a field. Lab: map each column of one events row to the API send as is rename / convert keep inside columns decided decisions that match a good API Pick what happens to each column. The response below updates as you go. GET /venues/ven_31\nGET /events?venue_id=ven_31 (each event repeats the same venue) GET /events?city=mumbai&expand=venue\n→ each event carries a small venue object, only because this page asked 21 round trips: the classic N+1. The page waits for the slowest venue call. A sub-collection answers “events of ONE venue”. A search page mixes many venues, so it does not fit. One request. On the server one JOIN (events ⋈ venues) fills every venue. Embed it always, or only when the page asks with expand. Every event repeats the same venue the page already has. Works, but wastes bytes. Fine for a few events. For hundreds, a separate paged sub-collection is easier to cache and page. A filter on the flat collection works too: GET /events?venue_id=… is the same query, just a different URL style. Two round trips for one page. Acceptable, but the event page almost always shows the venue: embed a small copy. Lab: one venue has many events. Which API shape fits which screen? Event page (1 event) Venue page (its 12 events) Search results (20 events) API shape Only venue_id Always embed the venue HTTP calls from the app database queries (rough) Lab: an event has many artists, an artist plays many events Arijit Live Monsoon Fest Arijit Singh Shreya Ghoshal Prateek Kuhad opening act Fold the join table into the event Expose the join table as is The join table disappears. Its own columns (role, order) become fields on each lineup item, next to the artist they describe. One request, one query: events ⋈ event_artists ⋈ artists, sorted by billing_order. Callers now need three requests, learn internal numeric IDs, and depend on a table that exists only to connect two others. Rename it in the database and every client breaks.

Two shapes for two jobs

Tables are built for storing Split into many small tables so every fact lives in exactly one place (normalised). Numbers for keys, codes for statuses, columns for every internal need. APIs are built for using Shaped around what a screen or partner needs: readable names, related data nearby, nothing internal, stable when the tables change. rows, keys, joins SELECT … JOIN … WHERE Mapping layer rename, convert, hide, nest API resource JSON the caller reads The mapping layer is a set of small response types between the database model and the wire. It is what lets each side change without breaking the other. A resource is rarely one table. Sometimes it is one row of one table (an event). Often it is several tables put together (a booking with its seats and payment). And some tables never become resources at all. Start from the consumer

Very basic: one table, one resource

The events table Send as is Clear name, plain type, the consumer needs it. Rename or convert Better name, codes to words, paise to a money object, timestamps to RFC 3339, internal ID to public ID. Keep inside Internal IDs, staff notes, soft-delete markers, audit columns, anything secret.

Basic: HTTP methods map to SQL statements

HTTP request What runs in the database Watch out for GET /events?city=mumbai , 'Always page (a LIMIT), always hide deleted rows, and index what you filter and sort on.'], [ GET /events/evt_8Kq2mZ POST /events INSERT INTO events (…) VALUES (…) RETURNING id, public_id PATCH /events/evt_8Kq2mZ , 'Update only an allow-list of fields, or callers can set status or price (mass assignment).'], [ DELETE /events/evt_8Kq2mZ This one-to-one mapping between a table and a resource is where most APIs start, and for simple things it is fine. The trouble begins when an API copies every table this way: callers then have to join the tables themselves, over the network.

Beginner: one-to-one, two tables, one resource

users and user_profiles, one row each per person users 1 ── 1 user_profiles The team split the user into two tables: login data that is read on every request, and profile data that is wider and changes on its own. That is a storage decision. The app does not care, so the API joins them back into one resource:

Medium: one-to-many, venues and their events

venues 1 ──< N events The “many” side holds the foreign key: each event row stores its venue_id. In the API there are four ways to show that link, and each fits a different screen. Pick a screen and try them: Just the ID Expand on request Link by default, embed when the caller asks. Keeps one endpoint useful for many screens. The foreign key never appears as a bare number: venue_id in the table is a bigint, but the API sends the venue’s public ID ("ven_31"), or a small object. And beware the server-side version of N+1: loading 20 events, then running one venue query per event. Use a JOIN, or one WHERE id IN (…) query for all venues (the DataLoader idea from the GraphQL chapter

Advanced: many-to-many, events and artists

events and artists joined by event_artists, which also carries role and billing order A join table: its key is the pair (event_id, artist_id). events N >──< N artists via event_artists A relational database cannot store “many on both sides” directly, so it adds a join table. That table is a storage trick. Most of the time it should vanish from the API, and its extra columns become fields on the relationship: What the caller wants See an event’s lineup GET /events/evt_8Kq2mZ (lineup embedded) , 'events ⋈ event_artists ⋈ artists ORDER BY billing_order'], ['See an artist’s shows', GET /artists/art_pr/events , 'INSERT INTO event_artists … ON CONFLICT DO UPDATE (safe to repeat)'], ['Remove an artist', DELETE /events/evt_8Kq2mZ/lineup/art_sh

More courses

Jobs, linked to lessons — Browse jobs where every requirement links to the chapter that teaches it. See how ready you are, how long the gap takes to close, and which listings look stale.
Match a job post to courses — Paste a job post. Each skill is matched to a track here and put in learning order, foundations first.
React from open source with patterns — Patterns extracted from Material UI, Radix, React Aria, React Hook Form, Redux Toolkit, TanStack, and Next.js — not slides about hooks.
Database in depth — How a query becomes work. Indexes, isolation, locks, and the plans that explain why a page is slow — not an ORM tour.
Build a real API with NestJS — One ticket-booking API, built in order: a plain Node server first, then NestJS modules, validation, a real database, and the features real clients lean on.
Microservices — Microservice patterns one diagram at a time: how to split a monolith, how services talk, and how data stays consistent without distributed transactions.
Kafka — A beginner-to-senior path through event logs, reliable delivery, production operations, and disaster recovery.
AI engineering for software engineers — Eight free reads, in order, for software engineers moving into AI: when to build an agent, how to build one, how to test it, and how the model works underneath.
Frontend system design — How a UI is sliced when it is a product, not a page: ownership of state, cache, routes, and what happens when the network lies.
TypeScript in depth — The type system as a design tool. Inference, narrowing, generics, and the errors that mean the model is wrong — not a catalog of utility types.
CSS in depth — See the box, then the page. Visual chapters from the box model through Flexbox surprises, Grid, selectors, and units, then backgrounds, shapes, and visual effects, each with a playground.
Accessibility — Foundations first (WCAG in plain words, semantic HTML, lists, alt text), then hands-on HTML labs, a full pass of WAI-ARIA 1.3, and a final fix-the-page challenge.
Docker — What an image is, why layers cache, and how containers find each other on a network.
Redis — Fifteen Redis patterns, one picture each: cache, session, lock, counter, rate limiter, queue, and when Redis is the wrong tool.
Kubernetes — Kubernetes from zero to operators: why it exists, Pods, Deployments and Services, then configuration, networking, storage, scheduling, scaling and security.