DatabaseArchitectureWorkflows

Database migration diagrams with Mermaid

July 1, 20266 min readThe MermaidCreator team

Database migrations are a frequent source of outages and confusion. Documenting what changed, why it changed, and how to rollback requires clarity that Slack messages and code comments can't provide. Mermaid diagrams let you visualize schema transformations, data movement, and rollback procedures—keeping your team aligned before you touch production.

Why diagram database migrations

Schema changes often involve multiple tables, cross-service impacts, and multi-step rollback procedures. A diagram makes these explicit:

Before-and-after schemas show exactly which columns and relationships changed
Data flow diagrams clarify how records move during a backfill or rename
Rollback sequences document the exact steps to undo a migration if something breaks
Team alignment — engineers, DBAs, and product all see the same model

flowchart TD
    A["Migration Started:<br/>Add users_v2 table"] --> B["Step 1:<br/>Backfill data from users"]
    B --> C["Step 2:<br/>Verify counts match"]
    C --> D{Data valid?}
    D -->|Yes| E["Step 3:<br/>Switch queries to v2"]
    D -->|No| Rollback["Rollback:<br/>Drop users_v2<br/>Resume queries on users"]
    E --> F["Step 4:<br/>Drop old users table<br/>(after 1 week)"]
    Rollback --> G["Complete (reverted)"]
    F --> H["Complete (migrated)"]

Example 1: Column rename with zero downtime

Renaming a frequently-used column (e.g., user_id → customer_id) requires dual-write durability:

graph LR
    subgraph "Before Migration"
        OldSchema["Table: users<br/>Columns: user_id (PK)"]
    end
    
    subgraph "During Migration"
        NewSchema["Table: users<br/>Columns: user_id, customer_id<br/>Trigger: user_id ↔ customer_id"]
    end
    
    subgraph "After Migration"
        FinalSchema["Table: users<br/>Columns: customer_id (PK)<br/>user_id dropped"]
    end
    
    OldSchema -->|Add customer_id column| NewSchema
    NewSchema -->|Backfill data<br/>Dual-write triggers| FinalSchema

The key steps:

Expand: Add the new column; backfill existing rows
Dual-write: Application writes to both columns (via trigger or app logic)
Read-switch: Application reads from the new column
Write-switch: Remove old column; disable trigger

Example 2: Data migration with verification

When moving data between schemas (e.g., user profiles moving from SQL to a document store), verification is critical:

flowchart TD
    A["Start: PostgreSQL<br/>users table<br/>10 million rows"] -->|Export| B["Stage 1: Extract to JSON<br/>(streaming)"]
    B --> C["Stage 2: Transform schema<br/>Flatten nested fields<br/>Compute derived fields"]
    C -->|Write| D["MongoDB<br/>users collection<br/>10 million docs"]
    D --> E["Stage 3: Verification<br/>Count match?<br/>Sample checksums?"]
    E -->|✓ Pass| F["Stage 4: Flag read traffic<br/>10% to MongoDB"]
    E -->|✗ Fail| Rollback["Rollback: Keep PostgreSQL<br/>Drop MongoDB data"]
    F -->|Monitor| G["Ramp to 100% MongoDB reads"]
    G --> H["Complete migration<br/>PostgreSQL → Archive"]
    Rollback --> I["Resume PostgreSQL reads"]

Example 3: ER diagram before and after

Use Mermaid's entity relationship (ER) syntax to document schema structure before and after:

Before:

erDiagram
    USERS ||--o{ ORDERS : places
    USERS {
        int user_id PK
        string email
        string phone
    }
    ORDERS {
        int order_id PK
        int user_id FK
        date created_at
        string status
    }

After (normalized):

erDiagram
    USERS ||--o{ PROFILES : has
    USERS ||--o{ ORDERS : places
    USERS {
        int user_id PK
        string email
    }
    PROFILES {
        int profile_id PK
        int user_id FK "unique"
        string phone
        text bio
    }
    ORDERS {
        int order_id PK
        int user_id FK
        date created_at
        string status
    }

The change: move the phone column out of USERS into a separate PROFILES table to reduce null values and improve query performance.

Example 4: Rollback sequence diagram

When things go wrong, you need a clear sequence of operations:

sequenceDiagram
    actor DBA
    participant PrimaryDB
    participant ReplicaDB
    participant AppServers
    
    DBA->>AppServers: Stop writes (read-only mode)
    AppServers->>PrimaryDB: Drain in-flight writes
    PrimaryDB->>ReplicaDB: Sync final transactions
    DBA->>ReplicaDB: Verify row counts
    Note over DBA: Issue detected!
    DBA->>PrimaryDB: Restore from backup (T-1 hour)
    PrimaryDB->>ReplicaDB: Re-initialize replica
    DBA->>AppServers: Resume writes
    AppServers->>PrimaryDB: Traffic returns to normal

Documenting migration assumptions and constraints

Add a summary table or notes to your diagram to capture constraints:

Assumption	Impact	Risk Mitigation
Downtime window: 30 seconds	Users see read-only briefly	Execute during low-traffic window (2 AM UTC)
Backfill: 10 million rows	Takes ~8 minutes	Test on staging; backfill concurrently
Rollback duration: 15 min	Production unavailable	Keep backups online for 1 week post-migration
Query performance: +5% after normalization	Worth it at scale	Benchmark on replica; verify indexes

Best practices for migration diagrams

One diagram per migration: Keep scope tight; one diagram = one schema change
Timeline horizontal: Left-to-right flows read like a timeline (before → after)
Color-code steps: Use shape+color to distinguish safe operations (green), risky ones (red), and rollbacks
Link to SQL: Reference the actual migration file (e.g., migrations/0042_rename_user_id.sql) in the diagram caption
Test on staging: Verify the diagram matches your actual test run before production
Include team roles: Sequence diagrams help clarify who (DBA, app team, on-call) does what
Document monitoring: Add a final step showing health checks, alerts, and log tailing post-migration

Common migration gotchas visualized

Forgot to add an index after normalization:

flowchart TD
    A["Normalize: Move phone<br/>from users → profiles"] --> B["Backfill complete"]
    B --> C["Queries now JOIN<br/>users ↔ profiles"]
    C --> D{Index on<br/>profiles.user_id?}
    D -->|No| E["⚠️ Queries slow<br/>Full table scan"]
    D -->|Yes| F["✓ Queries fast<br/>Index seek"]

Dual-write without verification:

flowchart TD
    A["App writes to column A"] --> B["Trigger copies to column B"]
    B --> C["Delete column A"]
    C --> D["But trigger still fires!"]
    D --> E["⚠️ Orphaned data<br/>Inconsistency"]

FAQ

How do I diagram a multi-region migration? Use subgraphs to show each region's state and timing: subgraph "US East" and subgraph "EU West", with arrows showing sync order.

Should I include rollback as a separate diagram or part of the main flow? For complex migrations, create a separate rollback flowchart that mirrors the forward steps in reverse. Reference it from the main diagram.

What if the migration depends on deployment order? Use a sequence diagram to show service A deploys first, then service B switches to read from the new schema, then schema cleanup.

Can I show time estimates on the diagram? Yes—add labels to edges: B -->|8 min| C shows the backfill takes 8 minutes.

Database migrations are risky precisely because they combine schema changes, data movement, and strict timing. Visualizing the before, after, and rollback plan—with MermaidCreator's visual editor—gives your team the clarity needed to execute safely.

Microservices