Plan database schema evolution with Mermaid ER diagrams

Database schemas don't ship once and stay frozen. They grow: new columns, new tables, constraints added, old relationships deprecated. When the schema changes, developers need to understand not just the current structure, but how it got there and what the migration path looked like.

Mermaid ER diagrams let you document schema evolution right in your codebase — alongside your migrations, versioned in Git, and reviewed in PRs just like the code.

Why diagram schema versions

A migration script (ALTER TABLE users ADD COLUMN plan_id INT) tells you what changed. An ER diagram tells you why and what it means for the rest of the system.

When a new engineer joins and asks "why does the invoices table have a workspace_id?", you can show them a diagram that illustrates the data model at that point in the schema's life. When you're planning a breaking schema change, you can diagram the before and after to get feedback in a PR before the migration runs.

Schema diagrams are especially powerful for:

• On-call debugging — "why is this query slow?" — see table size and index relationships
• Feature planning — visualize where new data fits before you write the migration
• Documentation — new team members learn the data model faster than reading schema.sql
• Retrospectives — "we regret the denormalization in v2; here's what v3 should look like"

Basic ER diagram syntax

Every ER diagram starts with erDiagram. Entities are tables; attributes are columns; relationships are cardinality lines:

erDiagram
    USERS {
        int id
        string email
        string password_hash
        timestamp created_at
    }
    WORKSPACES {
        int id
        string name
        string plan
        timestamp created_at
    }
    USERS ||--o{ WORKSPACES : "member of"

Cardinality symbols show the relationship strength:

Symbol	Meaning
`|\	`
o|	Zero or one
|\{	One or more
o{	Zero or more

So USERS \|\|--o{ WORKSPACES reads as: "each user belongs to one workspace, each workspace has zero or more users."

Versioning: documenting schema migration

Create a timeline of schema versions as your product evolves. Keep old versions in version-specific folders:

v1 (initial launch):

erDiagram
    USERS {
        int id
        string email
        string password
        timestamp created_at
    }
    DIAGRAMS {
        int id
        int user_id
        string title
        text content
        timestamp created_at
    }
    USERS ||--o{ DIAGRAMS : "creates"

v2 (add workspaces — 3 months later):

erDiagram
    USERS {
        int id
        string email
        string password
        timestamp created_at
    }
    WORKSPACES {
        int id
        string name
        int owner_id
        timestamp created_at
    }
    DIAGRAMS {
        int id
        int workspace_id
        string title
        text content
        timestamp created_at
    }
    USERS ||--o{ WORKSPACES : "owns"
    WORKSPACES ||--o{ DIAGRAMS : "contains"

v3 (add collaboration — 6 months later):

erDiagram
    USERS {
        int id
        string email
        string password
        timestamp created_at
    }
    WORKSPACES {
        int id
        string name
        int owner_id
        timestamp created_at
    }
    WORKSPACE_MEMBERS {
        int id
        int workspace_id
        int user_id
        string role
        timestamp joined_at
    }
    DIAGRAMS {
        int id
        int workspace_id
        string title
        text content
        timestamp created_at
        int updated_by
    }
    USERS ||--o{ WORKSPACE_MEMBERS : "joins"
    WORKSPACES ||--o{ WORKSPACE_MEMBERS : "has"
    WORKSPACES ||--o{ DIAGRAMS : "contains"
    DIAGRAMS }o--|| USERS : "last_edited_by"

Each version is a commit in the repo. When a reviewer asks "what changed?", run git diff on the migration file, then show the before/after diagrams side by side. The visual change is often clearer than reading SQL.

Capturing important constraints visually

Mermaid doesn't have a perfect syntax for all SQL constraints, but you can add them as comments in the diagram or as attribute annotations:

erDiagram
    USERS {
        int id "primary key"
        string email "unique, not null"
        string password_hash "not null"
        timestamp created_at "default current_timestamp"
    }
    WORKSPACES {
        int id "primary key"
        string name "not null"
        int owner_id "foreign key → users.id"
        timestamp created_at
    }
    WORKSPACE_MEMBERS {
        int workspace_id "foreign key → workspaces.id"
        int user_id "foreign key → users.id"
        string role "enum: owner, admin, editor, viewer"
    }
    USERS ||--o{ WORKSPACES : "owns"
    WORKSPACES ||--o{ WORKSPACE_MEMBERS : "has members"
    USERS ||--o{ WORKSPACE_MEMBERS : "is member of"

The strings in quotes document the constraints. They don't render visually, but they're searchable and appear in tooltips. Use these for:

• Unique constraints (unique, not null)
• Defaults (default false, default now())
• Enum values (enum: pending, active, archived)
• Check constraints (> 0, length ≥ 3)

Migration planning: before and after in one PR

When you're planning a breaking schema change, create two diagrams in the same PR:

Before (current schema):

erDiagram
    INVOICES {
        int id
        int user_id
        int amount
        timestamp created_at
    }
    USERS ||--o{ INVOICES : "generates"

After (proposed schema):

erDiagram
    INVOICES {
        int id
        int workspace_id
        int amount
        timestamp created_at
    }
    WORKSPACES ||--o{ INVOICES : "generates"

The diagram clearly shows: invoices now tie to workspaces, not individual users. Reviewers see the data model change instantly. Comments like "won't this break downstream analytics?" get surfaced before the migration runs.

Keeping diagrams in sync with migrations

Add a diagram file for each major schema version:

db/
  migrations/
    0001_initial.sql
    0002_add_workspaces.sql
    0003_add_collaboration.sql
  schemas/
    schema-v1.md    ← contains ER diagram
    schema-v2.md    ← contains ER diagram
    schema-v3.md    ← contains ER diagram
    CURRENT.md      ← always the latest

When you create a migration, update the corresponding schema diagram in the same PR. Over time, this creates a living timeline of how your data model evolved. New engineers can trace the schema's history and understand why certain decisions were made.

Using schema diagrams for on-call debugging

When you're investigating a production issue — a slow query, missing data, or an integrity error — pull up the current schema diagram:

• Slow query? See the table relationships and understand which joins might be inefficient.
• Cascading deletes? The diagram shows which tables depend on each other; cascade relationships stand out.
• Data integrity error? The diagram documents your constraints; if data violates them, the issue becomes clearer.

Much faster than opening schema.sql and mentally parsing SQL.

Example: complete evolution of a multi-tenant SaaS

Here's a realistic progression from single-user to team collaboration:

v1.0 (MVP):

Users create diagrams. One user, many diagrams.

v2.0 (Teams):

Users join workspaces. Workspaces own diagrams.
Introduces multi-tenancy.

v3.0 (Collaboration):

Multiple users per workspace with roles.
Adds diagram comments and edit tracking.

v4.0 (API & Automation):

API tokens for programmatic access.
GitHub integration for architecture diagrams.
Audit logging for compliance.

Each version builds on the previous one. The diagrams form a portfolio of design decisions.

Pitfall: over-normalization then denormalization

A common pattern: v1 is over-normalized (5+ join tables), v2 adds denormalized columns for performance, v3 cleans it up. Diagram evolution shows exactly when the tradeoff happened and why. Future engineers reading the commit history can see the reasoning.

FAQ

Should I diagram views, or only base tables? Diagram base tables and critical views. Views are usually performance optimizations; they clutter the conceptual schema diagram. Keep a separate diagram for your read model if it's significantly different.

How detailed should attributes be? Detailed enough for a new engineer to understand the table without reading the migration. Include type, key constraints, and if a column has a special meaning (e.g., "denormalized from user.plan for query performance").

Do I need to diagram indexes? No. Indexes are implementation details. If an index is critical to understanding the schema (e.g., a partial index on a filter column), add a comment, but keep it simple.

Can I auto-generate the ER diagram from the actual database? Yes, tools exist (mermerd, DBeaver, some database IDEs), but they often over-include columns you don't care about. Hand-crafted diagrams for documentation are clearer. Reserve auto-generated diagrams for exploratory work.

Create a schemas/ folder in your repo, add the initial ER diagram in the first commit, and update it every time a migration lands. Six months in, you'll have a visual archive of every data model decision your team made. Try diagramming your schema in MermaidCreator — paste your table definitions and experiment with relationships until the model clicks.