Docs & Code Guide

Keep ER diagrams in sync with the database: the migration is the trigger

An ER diagram cannot drift secretly, because every change that would invalidate it already passes through a narrow gate: the migration. Flyway versions, Alembic revisions, Prisma migrate folders, every one is a declared, reviewed, merged event that says "the schema is different now". Teams with stale ER diagrams are not missing information, they are missing a hook on an event they already emit. This page wires that hook.

Try it free
7 min readFor teams whose schema diagram shows tables dropped two quarters ago

See it as a diagram

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

176/2000• 3 credits left
Try:

No account needed · Editable canvas, not a picture

The migration is the change event

Whatever your stack, schema changes arrive as files in one directory: V42__add_invoices.sql for Flyway, a timestamped revision under alembic/versions, a folder under prisma/migrations. That gives CI a perfect filter. A workflow that runs only when the migrations path changes fires on exactly the merges that can invalidate the ER diagram, and never otherwise.

This is the difference between "keep the diagram up to date", a chore with no deadline, and "the diagram regenerates when a migration merges", a mechanism. Chores lose to sprint pressure every time; mechanisms do not ask.

Diff tools prove when the diagram lies

Before automating regeneration, it helps to measure the current drift. migra diffs two Postgres schemas and emits the ALTER statements separating them, so running it between production and the schema your diagram was drawn from quantifies the lie precisely. atlas schema diff does the same across more engines, comparing live databases, migration directories, or HCL definitions in any combination.

For docs specifically, tbls has the check built in: tbls diff compares the generated schema docs against the live database and exits non-zero when they disagree, which turns "is our ER documentation current" into a CI assertion rather than an opinion.

Generate from the live connection or from DDL

Two mature open-source generators draw the diagram from the database itself. SchemaSpy connects over JDBC and produces browsable HTML with relationship diagrams rendered via Graphviz. tbls connects with a DSN in a small .tbls.yml, writes Markdown docs per table plus an ER diagram, and is small enough to run in any CI image. Both inherit foreign keys automatically; both also inherit the layout limitations of automatic rendering on large schemas.

Datadef takes the text route: paste your DDL, a schema dump, or migration files as the prompt and it generates an editable ER diagram you can rearrange, annotate, and zone by domain. The ERD from SQL generator is the first-run entry point; the ongoing loop is what the next section wires up. The DDL-specific details live in living diagram from SQL DDL.

# .tbls.yml: docs and drift check from the live connection
dsn: postgres://readonly:[email protected]:5432/app
docPath: docs/schema

# In CI, after any merge touching db/migrations/**:
tbls diff          # exits non-zero when docs/schema lags the database
tbls doc --force   # regenerate the per-table docs and ER diagram

Regenerate on merge, embed everywhere

The full loop has three parts. Trigger: a CI workflow filtered on the migrations path. Regeneration: an AI agent connected to the Datadef MCP server (registry name io.datadef/mcp) reads the merged migration and updates the ER diagram to match; it runs as a CI step or on request during review, and needs an API key, available on paid plans. Propagation: the diagram, shared public, is embedded by URL in Confluence, Notion, or the README, so every copy shows the new version within minutes of the update without anyone re-exporting an image.

The judgment call of when regeneration is worth running at all, every migration versus meaningful ones, is discussed in when to regenerate an architecture diagram; for ER diagrams the honest answer is nearly always, because a single dropped column makes the picture wrong.

The caveats on regeneration

Nothing here is automatic in the marketing sense. Datadef does not connect to your database and does not watch the migrations directory; the trigger is your CI workflow and the update is one agent call inside it. The live embed exists only while the project is shared public, which can be wrong for schemas that are themselves sensitive; a regenerated export committed to the repo is the private-schema fallback.

Automatic generators have their own honest limit worth naming: past a few dozen tables, machine layout degrades into spaghetti. The practical pattern is a generated-then-curated diagram, zoned by domain, that an agent updates incrementally rather than relaying out from scratch each time.

FAQ

How do I keep an ER diagram up to date automatically?

Hook regeneration to migrations. Every schema change already merges as a migration file, so a CI workflow filtered on the migrations directory fires exactly when the diagram can become wrong. In that job, either regenerate docs with a tool like tbls or SchemaSpy from a live connection, or have an AI agent connected to the Datadef MCP server update the diagram from the merged DDL. Embedded by URL, the updated diagram then propagates to wikis and READMEs within minutes.

How can I check whether my ER diagram matches the database?

Diff the schemas rather than eyeballing the picture. migra compares two Postgres schemas and prints the ALTER statements between them; atlas schema diff does the same across more engines. If your docs are generated with tbls, run tbls diff: it compares the docs against the live database and exits non-zero on drift, which makes the check a CI gate.

What tools generate an ER diagram from an existing database?

SchemaSpy connects over JDBC and generates browsable HTML documentation with relationship diagrams. tbls connects via a DSN and writes Markdown docs plus an ER diagram, and fits CI well. Datadef generates an editable diagram from pasted DDL or a schema dump, which suits teams that want to rearrange, annotate, and zone the result rather than accept automatic layout.

Should the ER diagram live in the repo or in a diagram tool?

The trigger should live in the repo; the diagram can live wherever it stays editable and embeddable. A committed export is versioned but frozen pixels between regenerations. A diagram served by URL from a tool updates every embed at once when it changes. Many teams do both: the tool is the source, and CI commits a rendered snapshot for offline use.

Why do auto-generated ER diagrams become unreadable on large schemas?

Automatic layout optimizes edge crossings, not comprehension, and past a few dozen tables the result is a hairball that treats a critical foreign key and a housekeeping table alike. The workable pattern is zoning by domain, showing column detail only on core entities, and updating the curated diagram incrementally instead of regenerating the layout from scratch on every change.