Docs & Code Guide

A living diagram from SQL DDL: migrations are the change stream, the ERD regenerates when one lands

If your schema history is a folder of migration files, you already run the perfect trigger for a living ERD: schema changes arrive as merges, nothing else touches the model, and CI sees every one. The pattern is to treat migrations as the change stream and a schema dump as the truth: when a migration lands, dump the schema, regenerate the diagram from the dump. Here is why that split matters, and the exact wiring.

Try it free
8 min readFor teams whose schema history is a migrations folder

See it as a diagram

Everything below, as a diagram you can edit. Describe yours and see it in seconds.

179/2000• 3 credits left
Try:

No account needed · Editable canvas, not a picture

Two artifacts: the change stream and the truth

Migration tools differ in syntax but agree in shape. Flyway numbers SQL files like V7__add_payments.sql, Alembic keeps Python revisions under versions/ with upgrade and downgrade functions, node-pg-migrate timestamps JavaScript or TypeScript files. In each case the folder is an append-only stream of schema changes, and a merge into it is the exact moment the ERD goes stale.

The stream tells you when; it should not tell you what. The current schema is best read from the database itself, and pg_dump --schema-only is the canonical dump for Postgres: complete DDL, no data, reproducible. Regenerate the diagram from the dump, triggered by the stream.

pg_dump --schema-only --no-owner --no-privileges "$DATABASE_URL" > schema.sql

Paste DDL, get the ERD

The dump is also the generation input. Datadef's AI import accepts pasted SQL DDL as the prompt: CREATE TABLE statements with primary and foreign keys give it tables as nodes, FK constraints as edges, and schema names as natural zones. For the one-off version of this, the ERD from SQL generator owns the intent; this page is about the ERD that is still correct in six months.

Curate once after generating: group tables into domain zones, hide the framework bookkeeping tables (schema_migrations, flyway_schema_history, alembic_version), and annotate the relationships whose meaning is not obvious from column names. That curation is what regeneration should preserve, which is why updates edit the existing diagram rather than regenerating from zero.

CI: trigger on the migrations path

The wiring is a path filter plus two steps. Trigger on merges touching the migrations folder, run the migrations against the CI or staging database as the pipeline already does, dump the schema, and have an agent connected to Datadef's MCP server (registry io.datadef/mcp) update the ERD from the dump. The agent connection needs an API key, available on paid plans; setup is in the MCP diagram server guide.

Datadef never watches the repository or the database. The loop below is the entire mechanism, and removing any step removes the sync.

on:
  push:
    branches: [main]
    paths:
      - "db/migrations/**"

# after the job's migrate step:
- name: Dump the schema
  run: pg_dump --schema-only --no-owner "$DATABASE_URL" > schema.sql

- name: Update the ERD
  run: |
    claude -p "Read schema.sql and update the 'Core schema' diagram
    in Datadef via MCP. Keep the domain zones and annotations."

Why the dump beats replaying migrations

It is tempting to skip the database and update the diagram from the migration file alone, since it describes the change. Resist it. Migrations are imperative: they rename, alter, and drop against whatever state came before, and deriving the resulting schema means replaying the whole history correctly, which is precisely the error-prone bookkeeping the database already does. The dump is idempotent truth; the migration is one delta.

The dump also catches what the migration stream misses. The Friday-night ALTER applied straight to production in an incident never becomes a migration file, and a diagram regenerated from migrations alone would never learn of it. A dump from the real database does. Teams that want the diagram wired to the database rather than the repo should read the Postgres and MySQL variants of this page, which start from introspection instead of DDL files.

Where the DDL route runs out

A schema-only dump includes more than tables: views, functions, triggers, and sequences all appear, and an ERD should not draw them all. Decide the altitude, usually tables and FK edges, and let the agent filter consistently. Foreign keys that exist only in application code, common in older MySQL schemas and some ORM setups, produce no DDL constraint and therefore no edge; those relationships must be annotated by hand, honestly marked as unenforced.

The embed that keeps the wiki page current requires the Datadef project to be shared public, a real decision for a schema diagram. And the loop maintains the ERD, not the data dictionary: column-by-column prose descriptions are a documentation task, not a diagram task, and pretending otherwise would oversell the mechanism. The broader practice is covered in keep ER diagrams in sync with the database.

Stream triggers, dump informs

The migrations folder tells CI when the schema changed. The schema dump tells the agent what it now is. Keeping those roles separate is what makes the loop reliable.

FAQ

How do I generate an ER diagram from SQL?

Paste the DDL. A schema dump such as pg_dump --schema-only gives complete CREATE TABLE statements, and an AI diagram generator that accepts SQL DDL as a prompt turns tables into nodes, foreign keys into edges, and schemas into zones. From there, curate the layout once and set up regeneration so the diagram follows future migrations.

Should the ERD come from the migration files or from a schema dump?

The dump. Migrations are imperative deltas against prior state, so deriving the current schema from them means replaying history correctly, which the database already does for you. Use the migrations folder only as the CI trigger, and regenerate the diagram from a fresh schema-only dump.

How do I automate ERD regeneration when a migration merges?

Add a CI job with a path filter on the migrations folder (for example db/migrations/**). After the pipeline applies migrations, dump the schema with pg_dump --schema-only and have an MCP-connected agent update the existing diagram from the dump, preserving zones and annotations. Nothing watches the repo by itself; this job is the whole mechanism.

Does this catch schema changes made outside migrations?

Only if the dump comes from the real database. An emergency ALTER applied directly to production never appears in the migrations folder, so a migrations-only pipeline misses it, while a scheduled dump from production catches it. Many teams run both: the merge-triggered loop for normal changes and a periodic production dump as a drift check.

What about relationships without foreign key constraints?

They produce no DDL and therefore no automatic edge. Application-enforced relationships, common in older MySQL schemas and some ORM designs, have to be added to the diagram by hand and are worth marking as unenforced, so readers know the database will not protect them.