Create a database ERD in your CI/CD pipeline

This post shows how to make your (Gitlab) CI pipeline create an ERD image as auto/self documentation.

Lets say you have a project that migrates your database. For example a Rails project or some Flyway migrations scripts.

You don’t want to constantly share outdated ERD’s with everybody. This article shows how to automatically create an ERD after database migrations have run using SchemaCrawler.

After your app is deployed / database has been migrated add the following step in your pipeline. This example is using your Gitlab CI pipeline which has the awesome posibility to use any Docker image as starting point.

create_erd:
  stage: create_erd
  image: sualeh/schemacrawler
  variables:
    DB_HOST: somehost.com
    DB_PORT: 5432
    DB_DATABASE: your_database
    DB_USERNAME: admin
    DB_PASSWORD: $DEV_ADMIN_DB_PASSWORD # this is a Gitlab CI secret
  script:
    - INITIAL_DIR=$(pwd)
    - cd /schemacrawler
    - ./schemacrawler.sh -server=postgresql -host=$DB_HOST -port=$DB_PORT -user=$DB_USERNAME -password=$DB_PASSWORD -database=$DB_DATABASE -loglevel=CONFIG -command=schema -outputformat=pdf -o schema.pdf
    - cp schema.pdf $INITIAL_DIR/schema.pdf
  artifacts:
    paths:
    - schema.pdf
    expire_in: 4 weeks

This will create a fresh ERD for every change in your database. Replace pdf with png to get nice images (used in link below).

Now to show your latest ERD in documentation add the following to your README.md or create an ERD.md.

# ERD

Following image is linking to job artifact in the `master` branch. Download it [here][erd].

![ERD schema][erd]

[erd]: https://your.gitlab.com/some-group/your-project/-/jobs/artifacts/master/raw/schema.pdf?job=new_erd_develop

Menu