How to Generate an ER Diagram from Migration Files

The dependable way to generate an ER diagram from migration files is to run the migrations against a real database of the right engine and version, read the schema back from the database's own metadata, and render that. Parsing the files is faster but sees only what the parser understands; drawing by hand is out of date by the next pull request. This guide compares the three approaches and then shows the executed-migration approach step by step, first with Docker and open-source tools, then with SchemaDrawer's API and GitHub Action.

Why migrations are the right source#

Migrations are the one description of the schema that is versioned, reviewed and applied in order. A live database can carry manual hot fixes, and a hand-drawn diagram carries whatever the author remembered, but the migrations directory is, by construction, what every environment was built from. A diagram derived from it documents the schema at a given commit, can be regenerated for any commit, and can be checked in a pull request next to the migration that changed it.

Three ways to get from files to a picture#

1. Draw it by hand#

Tools such as dbdiagram.io or draw.io are quick for a first design, and a good choice while the schema is still being discussed. Once migrations exist, though, the drawing has to be updated by a person every time, and it will not be. Hand-drawn diagrams are for designing, not for documenting.

2. Parse the migration files#

A parser reads the SQL, or the ORM's model definitions, and builds a diagram without touching a database. It is fast and needs no infrastructure, but it can only draw what it understands. Every engine has its own dialect, Django and Alembic express migrations as Python operations, Prisma and Drizzle generate SQL from a schema file, Liquibase migrations may be XML, YAML, JSON or SQL, and any tool allows procedural statements whose effect depends on the data. A parser that covers all of that is a database engine in disguise, and none of them keeps up. Why parsing SQL is not enough goes through the concrete cases.

3. Run the migrations and introspect#

Start a throw-away database of the engine and version you deploy on, let the migration tool apply the files exactly as it would in production, then ask the database what exists. Every engine exposes its catalog, through information_schema, engine-specific views or JDBC metadata, and the answer is authoritative: tables, columns with their real types, primary keys, foreign keys. The cost is that you need a container runtime and the migration tool itself. That cost is paid once, in a pipeline.

Doing it yourself with Docker#

The self-hosted version of approach three takes three containers. The example uses Flyway SQL migrations in db/migration, PostgreSQL 18 and tbls for the rendering; SchemaSpy works the same way.

docker network create erd
docker run -d --name pg --network erd -e POSTGRES_PASSWORD=secret postgres:18

# apply the migrations with the real Flyway
docker run --rm --network erd -v "$PWD/db/migration:/flyway/sql" flyway/flyway \
  -url=jdbc:postgresql://pg:5432/postgres -user=postgres -password=secret migrate

# read the schema back and render documentation with diagrams
docker run --rm --network erd -v "$PWD:/work" -w /work ghcr.io/k1low/tbls \
  doc "postgres://postgres:secret@pg:5432/postgres?sslmode=disable" docs/schema

docker rm -f pg && docker network rm erd

Wait for the database to accept connections before running Flyway; in a pipeline a short retry loop or a health check does it. The output is a directory of Markdown files with an ER diagram per table and for the whole schema. Swap postgres:18 for mysql:9 or mariadb:11.8 and the JDBC URL accordingly, and the same pipeline documents those engines.

What you maintain in return: the container versions, the migration tool's image, drivers for each engine, the rendering tool's configuration, and a pipeline definition for every repository that needs a diagram. For one project that is an afternoon; for twenty projects using six different migration tools it becomes a small platform.

The hosted way: SchemaDrawer's API#

SchemaDrawer runs approach three as a service. It accepts migration files for 22 tools, runs them in Docker against a throw-away database of the engine and version you choose, and returns an SVG. Two HTTP calls are involved because a drawing takes seconds to minutes, so it is queued rather than answered inline.

curl -X POST https://schemadrawer.com/api/v1/drawing \
  -H "Request-Id: $(uuidgen)" \
  -F "userId=5f0c1d2e-3a4b-4c5d-8e6f-7a8b9c0d1e2f" \
  -F "migrationType=FLYWAY" \
  -F "databaseType=POSTGRESQL" \
  -F "version=18" \
  -F "files=@db/migration/V1__create_owners_and_pets.sql" \
  -F "files=@db/migration/V2__create_vets.sql" \
  -F "files=@db/migration/V3__create_visits.sql"
# {"jobId":"0b6e2f4c-8d1a-4c3e-9f7a-2a5b6c7d8e9f"}

curl https://schemadrawer.com/api/v1/drawing/0b6e2f4c-8d1a-4c3e-9f7a-2a5b6c7d8e9f
# {"jobId":"...","status":"SUCCESS","createdAt":"...","errorMessage":null,"diagramUrl":"https://...svg"}

The parameters are few. migrationType names the tool (FLYWAY, LIQUIBASE, PRISMA, DJANGO and so on), databaseType and version pick the engine, and files carries the migration files. For tools with nested layouts, such as Prisma's one directory per migration, send each file's path relative to the migrations directory as its file name (with curl, -F "files=@path;filename=relative/path") so that the tool sees the structure it expects. Request-Id is a UUID you make up per drawing; a retry with the same id returns the same job instead of drawing twice. userId is a UUID that identifies you. Poll the second URL until status is SUCCESS or ERROR; on success diagramUrl serves the SVG for one hour, and on error errorMessage contains the migration tool's own output, which is usually the fastest way to find a broken migration. The API reference lists every field and status code, and the getting started guide is the short version.

In CI: the GitHub Action#

The same two calls are wrapped in a GitHub Action that uploads a directory, waits for the diagram and opens a pull request with the result when it changed.

name: Update schema diagram

on:
  push:
    branches: [main]
    paths: ['db/migration/**']
  workflow_dispatch:

permissions:
  contents: write
  pull-requests: write

jobs:
  draw:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: schemadrawercom/action@v1
        with:
          sql-directory: db/migration
          output-path: docs/schema-diagram.svg
          migration-type: FLYWAY
          database-type: POSTGRESQL
          database-version: '18'
          github-token: ${{ secrets.GITHUB_TOKEN }}

The paths filter keeps the workflow quiet until a migration changes; the pull request makes the diagram change visible next to the migration that caused it. Keeping the diagram up to date with CI covers branch strategy, review and the alternatives in detail, and the action's documentation lists every input.

Pick the engine version you deploy on#

A migration is not the same schema on every version of an engine. Defaults, type mappings and even accepted syntax change between releases, and a migration tool may refuse an old server altogether. Draw against the version production runs on; when you are about to upgrade, draw against both and compare. SchemaDrawer offers PostgreSQL 10 to 18, MySQL 8.0 to 9, MariaDB 10.6 to 12, SQL Server 2017 to 2025, Oracle 21 and 23, and SQLite; the database pages list which tools reach which engine.

What the diagram contains, and what it does not#

An executed-migration diagram shows the physical schema: tables, columns with the types the engine reports, primary keys and foreign keys. Bookkeeping tables that migration tools create for themselves, such as flyway_schema_history or databasechangelog, are filtered out. Views, indexes, triggers, sequences and data are not drawn; they belong in a data dictionary. Limits worth knowing for the hosted service: an upload of up to 100 MB and 2000 files, 120 seconds per drawing, and a diagram link that is valid for an hour, so store the file in your repository rather than linking to it.

Key takeaways#

• Migrations are the versioned truth about the schema; derive the diagram from them, not from memory or from a possibly drifted database.
• Parsing is convenient but partial; running the migrations against a real engine is the only method that is exact by construction.
• Self-hosting takes Docker, the migration tool and a renderer such as tbls or SchemaSpy; a hosted API removes the maintenance.
• Always draw against the engine version you deploy on, and commit the diagram so that every commit has its picture.