How to Keep Your Database Diagram Up to Date with CI

A database diagram stays accurate only if a machine regenerates it whenever the migrations change. Treat it as a build artifact: produce it in CI from the migrations directory, write it to a file in the repository, commit it through a pull request and review that diff like any other. This article shows the workflow, the choices behind it and the traps that make such pipelines noisy.

Why diagrams rot#

A schema diagram is usually created once, during design or an onboarding push, by a person with a diagramming tool. From that day on every migration makes it a little more wrong, and because nothing fails when it is wrong, nobody notices until a new engineer designs a feature against a table that was dropped a year ago. Documentation that is not produced by the build is documentation that is maintained by goodwill, and goodwill does not survive deadlines. The fix is not more discipline; it is removing the person from the loop.

The diagram as a build artifact#

Four properties make a generated diagram fit for a repository.

• Derived from the source of truth. The input is the migrations directory at the commit being built, not a shared database that may have drifted. How to generate an ER diagram from migration files compares the ways to do that; the reliable one runs the migrations against a real, throw-away database.
• Deterministic. The same schema must produce the same file, otherwise every run creates a diff. Renderers with a fixed layout seed, or with a stable ordering of tables and columns, satisfy this; check yours by running it twice.
• Text-based. An SVG is text, so version control stores it efficiently and a diff shows which table or column changed. A PNG is opaque.
• Stored next to what it documents. docs/schema-diagram.svg in the same repository as db/migration, linked from the README, so that a checkout of any commit carries its own picture.

A GitHub Actions workflow#

The workflow below runs when a push to main touches the migrations directory, and on demand. It uses the SchemaDrawer action, which uploads the directory, waits for the diagram and opens a pull request with the new file; if the file is identical to the committed one, no pull request is opened.

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 }}

Three details matter. The paths filter keeps the workflow from running on unrelated commits. The two permissions are what the action needs to push a branch and open the pull request with the default GITHUB_TOKEN; the GitHub Actions documentation describes how they scope that token. And database-version should be the version production runs on, because the same migration can produce different types or fail outright on another release; the action's documentation lists the engines and versions available.

Reviewing a diagram change#

The pull request the pipeline opens is where the diagram earns its keep. Open the migration that triggered it and the diagram side by side and ask three questions: does the new table or column appear where the author intended, did a foreign key materialize for every relationship the migration implied, and did anything disappear that should not have? A missing arrow is the most common finding; it usually means a column named like a foreign key without a constraint behind it, which the diagram makes visible and a code review of the SQL often misses.

Because the file is SVG, the raw diff of the pull request shows the changed table names and column names as text even before you open the rendered picture. Merge the pull request as you would a documentation change; there is no reason to require a second approval for a file the pipeline produced.

Running it in practice#

On every pull request, or only on main#

Generating on main keeps the repository's diagram matching what is deployed and produces one pull request per schema change. Generating on every pull request that touches the migrations is also possible and gives reviewers the picture while they review; the result should then be uploaded as a workflow artifact or posted as a comment, not committed, or every feature branch will conflict on the same file. Many teams do both: an artifact on pull requests, a commit on main.

Keeping the pipeline quiet#

• Commit only on change. A pipeline that commits an identical file every run creates noise and eventually gets ignored. The SchemaDrawer action skips the pull request when nothing changed; if you build your own, compare before committing.
• Use a recognizable branch prefix such as schemadrawer/update-, so that branch protection rules, auto-merge and clean-up jobs can target it.
• Do not trigger yourself. A workflow that commits to the repository can trigger another run of itself. The paths filter prevents that here, because the diagram lives outside the migrations directory; keep it that way.
• Fail loudly on broken migrations. If the migrations do not apply, the diagram job fails with the migration tool's output. That is a feature: it is the same failure production would have seen.

The self-hosted alternative#

The same workflow can be built from open-source parts: start a database container as a service, run the migration tool's CLI, then run SchemaSpy or tbls against the database and commit their output. It works well, and it is the right choice when the migrations must not leave your infrastructure. Budget for the upkeep, though: each engine needs an image and a driver, each migration tool its own installation step, and each repository a copy of the pipeline. The reasons a hosted service is simpler are the same reasons it is less flexible; choose by how many repositories and engines you have to cover.

Beyond the picture#

Once the diagram is generated, the rest of the database documentation can follow the same pattern. tbls generates a Markdown data dictionary from the same throw-away database; column comments written with COMMENT ON flow into it automatically. Link both from the README, and put the schema section of your documentation practices under the same rule: if a pipeline did not produce it, expect it to be stale. A generated diagram that is committed on every change also gives you a history, which is a cheap way to spot schema drift between what the repository says and what a live environment contains.

Where SchemaDrawer fits#

SchemaDrawer is the hosted piece in the workflow above. It takes the migration files of 22 tools, runs them in Docker against a throw-away PostgreSQL, MySQL, MariaDB, SQL Server, Oracle or SQLite of the version you choose, reads the schema back and returns an SVG of tables, columns, primary keys and foreign keys. Its layout is deterministic, so an unchanged schema produces an unchanged file, and the GitHub Action does the commit-and-pull-request part. It is free to use today; the getting started guide shows the raw API for pipelines other than GitHub Actions.

Key takeaways#

• Diagrams maintained by people rot; diagrams produced by the build do not.
• Generate from the migrations at the commit being built, deterministically, into a text format stored in the repository.
• Trigger on changes to the migrations directory, commit through a pull request, and only when the file changed.
• Review the diagram next to the migration; missing foreign keys are the usual catch.
• Use artifacts on pull requests and commits on the main branch to avoid conflicts.