API Design with ERD: How Your Database Schema Shapes Your API

The Connection Between Schema and API

Your API is a public contract with clients — mobile apps, third-party integrations, and frontend teams. Your database schema is a private implementation detail. The most common mistake in API design is exposing the database schema directly as the API surface, coupling internal implementation to external contracts.

However, a well-designed ERD is still the best starting point for API design. The entities in your ERD map naturally to API resources, and relationships in your schema translate into nested resources or query parameters. This guide shows how to make that translation thoughtfully.

Mapping ERD Entities to REST Resources

Each primary entity in your ERD typically becomes a top-level REST resource. Relationships determine the nesting strategy.

Consider this schema:

CREATE TABLE projects (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        VARCHAR(255) NOT NULL,
  owner_id    UUID NOT NULL REFERENCES users(id),
  visibility  VARCHAR(20) DEFAULT 'private',
  created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE project_members (
  project_id  UUID REFERENCES projects(id) ON DELETE CASCADE,
  user_id     UUID REFERENCES users(id) ON DELETE CASCADE,
  role        VARCHAR(20) NOT NULL DEFAULT 'viewer',
  joined_at   TIMESTAMPTZ DEFAULT now(),
  PRIMARY KEY (project_id, user_id)
);

CREATE TABLE diagrams (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id  UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  name        VARCHAR(255) NOT NULL,
  data        JSONB NOT NULL DEFAULT '{}'
);

This maps to:

GET    /projects                    → List projects
POST   /projects                    → Create project
GET    /projects/:id                → Get project
PATCH  /projects/:id                → Update project
DELETE /projects/:id                → Delete project

GET    /projects/:id/members        → List project members
POST   /projects/:id/members        → Add member
DELETE /projects/:id/members/:userId → Remove member

GET    /projects/:id/diagrams       → List diagrams
POST   /projects/:id/diagrams       → Create diagram
GET    /projects/:id/diagrams/:dId  → Get diagram

The nesting depth mirrors the dependency in your ERD: diagrams belong to projects, so they live under /projects/:id/diagrams. Avoid nesting more than two levels deep — beyond that, use query parameters instead.

Efficient Queries for Nested Resources

A naive implementation of GET /projects/:id/diagrams might issue a separate query per project attribute. Design the query to return everything needed in a single round-trip.

-- Single query for GET /projects/:id (with member count and diagram count)
SELECT
  p.id,
  p.name,
  p.visibility,
  p.created_at,
  u.display_name AS owner_name,
  u.avatar_url   AS owner_avatar,
  COUNT(DISTINCT m.user_id) AS member_count,
  COUNT(DISTINCT d.id)      AS diagram_count
FROM projects p
JOIN users u          ON u.id = p.owner_id
LEFT JOIN project_members m ON m.project_id = p.id
LEFT JOIN diagrams d        ON d.project_id = p.id
WHERE p.id = $1
GROUP BY p.id, u.display_name, u.avatar_url;

-- Paginated list: GET /projects?cursor=<id>&limit=20
SELECT
  p.id, p.name, p.visibility, p.created_at,
  pm.role AS my_role
FROM projects p
JOIN project_members pm ON pm.project_id = p.id AND pm.user_id = $current_user
WHERE p.id > $cursor_id   -- Keyset pagination
ORDER BY p.id
LIMIT 21;  -- Fetch one extra to determine if there is a next page

GraphQL Schema from ERD

GraphQL's type system maps almost one-to-one with a well-designed ERD. Each entity becomes a type, and each foreign key relationship becomes a field resolver.

-- ERD relationship:
-- projects.owner_id → users.id  (many-to-one)
-- diagrams.project_id → projects.id  (many-to-one)

# GraphQL schema derived from the ERD
type Project {
  id:          ID!
  name:        String!
  visibility:  String!
  createdAt:   String!
  owner:       User!       # Resolved via JOIN on users.id = projects.owner_id
  members:     [Member!]!  # Resolved via JOIN on project_members
  diagrams:    [Diagram!]! # Resolved via JOIN on diagrams.project_id
  memberCount: Int!
}

type Member {
  user:     User!
  role:     String!
  joinedAt: String!
}

type Query {
  project(id: ID!): Project
  projects(limit: Int, cursor: ID): ProjectConnection!
}

N+1 warning: Without DataLoader batching, resolving owner for a list of 20 projects fires 20 separate user queries. Use DataLoader to batch:

-- DataLoader batches: SELECT * FROM users WHERE id = ANY($ids)
-- Instead of 20 individual: SELECT * FROM users WHERE id = $1
SELECT id, email, display_name, avatar_url
FROM users
WHERE id = ANY($1::uuid[]);

Designing the API Contract from ERD

The clearest API contracts come from clean, well-normalized ERDs. When your schema is ambiguous, your API will be too. Erdmates lets you design your ERD collaboratively with frontend and backend engineers simultaneously, so the API contract and the database schema evolve together — reducing mismatches and rework during integration.

Conclusion

Your ERD is the foundation of your API design. Map primary entities to top-level resources, use nesting to express strong ownership relationships, and write efficient queries that return all necessary data in one round-trip. In GraphQL, leverage the ERD type system directly but add DataLoader batching to prevent N+1 queries. A clean schema leads to a clean API — invest in the ERD first.