Build a REST API From a Database Schema

Published 24 October 2026. A database schema already holds most of what an API needs: entities, fields, types and relations. Turning it into a good REST API is still a series of decisions. This guide walks through them in the order you meet them.

CREATE TABLE orders (
  id          uuid PRIMARY KEY,
  customer_id uuid REFERENCES customers,
  status      text NOT NULL,
  total       numeric(10,2),
  placed_at   timestamptz
);

GET/v1/orders

GET/v1/customers/{id}/orders

Step 1. Decide which tables become resources

Not every table deserves an endpoint. Join tables that only link two entities usually become relations, not resources. Audit tables, queues and internal settings usually stay private. Start by listing the tables a client would think in: customers, orders, products, appointments. Those become your resources, named in plural nouns, such as /customers and /orders.

Step 2. Choose fields and hide what is internal

Columns such as password_hash, internal_notes or soft delete flags must never appear in responses. Decide per resource which fields are readable, which are writable and which are set only by the server, such as id, created_at and updated_at. Rename columns whose names leak implementation details, so the API stays stable when the database changes.

Step 3. Map types to validation rules

The schema tells you a lot about valid input. NOT NULL columns become required fields on create. VARCHAR lengths become maximum lengths. Unique constraints need a clear error when a client sends a duplicate. Foreign keys need a check that the referenced record exists. Enums and check constraints become allowed values in the API contract. Validating in the API, not only in the database, gives clients readable errors instead of a generic failure.

Step 4. Turn foreign keys into relations

A foreign key from orders to customers is a belongs to relation on orders and a has many relation on customers. Expose it in two ways: a nested route such as /customers/42/orders for the common question, and an expand or include parameter so a client can fetch an order with its customer in one request. Keep nesting shallow. One level is usually enough.

Step 5. Design list endpoints with care

List endpoints carry most of the traffic and most of the risk.

Pagination on every list, with a maximum page size, so no client reads a million rows at once.
Cursor pagination for large or fast changing tables, page numbers for small ones.
Filters on the columns clients actually search by, with indexes behind them.
Sorting on a fixed list of fields, never on arbitrary input.

Step 6. Add actions that are not plain updates

Real systems have operations such as approve, cancel, refund or book. Modelling them as a PATCH on a status field hides business rules. Give them their own endpoints, such as POST /appointments/42/cancel, with their own validation and their own permission.

Step 7. Secure it

Pick an authentication method for each kind of client: API keys for server to server calls, JWT for your own apps, OAuth2 client credentials for partners. Add a rate limit per key. Use parameterised queries everywhere. Return the same error shape for every failure.

Step 8. Document and test

Describe the API in an OpenAPI 3.1 document, publish interactive docs with request examples, and write contract tests that check each endpoint against the document. A mock server from the same document lets frontend work start in parallel.

Doing it in minutes instead of weeks

Each step is straightforward, but together they take weeks per project, and they must be redone every time the schema changes. APIGenerator reads SQL DDL or a Prisma schema and makes these decisions with you: it proposes resources, hides internal columns you mark, turns constraints into validation and foreign keys into relations, adds pagination, auth, docs and contract tests, and lets you review it all before you deploy or export. Start on the api database page, or describe the system in plain words on create rest api. If AI agents need the same data, the schema also produces a postgres mcp server.

APIGenerator