Skip to main content

Programmatic ERD update with dbdiagram Public API

Introduction​

This guide shows you how to programmatically visualize and sync your ERDs to dbdiagram. By leveraging the DBML ecosystem and the dbdiagram Public API, you can transform your schema into a living document that evolves automatically alongside your codebase.

Use Case​

Imagine your team is implementing new features that require database changes, resulting in new migration files, and you want to ensure the ERD is instantly updated after merging into the main branch.

Manually updating the ERD is a common way, but it is tedious and easy to forget. This leads to:

  • Documentation Drift: The diagram no longer reflects the actual database schema.
  • Team Confusion: New and existing members are left with an unreliable source of truth.

So follow this guide, and you can find the solution to solve these problems.

High-Level Workflow​

The entire end-to-end workflow looks like this:

High-Level Workflow

Prerequisites​

Before you begin, make sure you have the following ready:

1. Project & Environment

  • A project hosted in a Git repository (this guide uses GitHub for its examples).
  • Your database schema must be managed by a migration-based tool (e.g., Sequelize, Knex, Flyway, etc.).
  • Node.js (version 22.0.0 or higher) installed in your local or CI environment.

2. Tools

  • @dbml/cli: The command-line tool for converting your database schema to DBML. You can install it by running:

    npm install -g @dbml/cli

3. dbdiagram Credentials

  • Pro Account: The dbdiagram Public API is a pro feature. You'll need to be subscribed to a Pro plan.
  • API Access Token: From the Workspace Modal in your dbdiagram account, go to the API Tokens tab and generate a new token.
  • Diagram ID: Create a new, empty diagram on dbdiagram.io and then get the ID from the URL. In the following example, the diagram ID would be 69608c68d6e030a02488f144.

Diagram URL Structure

Step-by-Step: Creating the Automated Workflow​

To fully automate this process, you can create a GitHub Actions workflow that runs on every push to your main branch.

Set up your GitHub credentials​

You will need to store these variables in your repository's Settings > Secrets and variables > Actions:

  • DBDIAGRAM_API_TOKEN: Your API Access Token.
  • DIAGRAM_ID: The Diagram ID.

Set up the workflow file​

Create a file at .github/workflows/update_erd.yml. The script below includes a final step that verifies if the API call was successful.

name: Update dbdiagram.io ERD

on:
  push:
    branches:
      - main

jobs:
  update-erd:
    runs-on: ubuntu-latest

    env:
      DATABASE_URL: postgresql://postgres:postgres@localhost:5432/test_db

    services:
      postgres:
        image: postgres:17
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: test_db
          POSTGRES_HOST_AUTH_METHOD: trust
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
          --health-start-period 20s
          --health-cmd="pg_isready -U postgres"

    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Set up Node.js
        uses: actions/setup-node@v4
        with:
          node-version: '22'

      - name: Install dependencies
        run: npm install

      - name: Install DBML CLI
        run: |
          npm install -g @dbml/cli

      - name: Apply database migrations
        run: |
          npx prisma migrate deploy

      - name: Generate DBML from database
        run: db2dbml postgres "postgresql://postgres:postgres@localhost:5432/test_db" -o schema.dbml

      - name: Sync and Verify DBML to dbdiagram.io
        run: |
          DBML_CONTENT=$(cat schema.dbml)
          JSON_PAYLOAD=$(printf '{ "name": "Automated ERD", "content": %s }' "$(echo "$DBML_CONTENT" | jq -Rs .)")

          # Make the API call, capturing both response body and status code
          RESPONSE=$(curl -s -w "\n%{http_code}" --location --request PUT "https://api.dbdiagram.io/v1/diagrams/${{ secrets.DIAGRAM_ID }}" \
          --header "dbdiagram-access-token: ${{ secrets.DBDIAGRAM_API_TOKEN }}" \
          --header "Content-Type: application/json" \
          --data "${JSON_PAYLOAD}")

          # Extract status code (last line) and response body (everything else)
          HTTP_STATUS=$(echo "$RESPONSE" | tail -n1)
          RESPONSE_BODY=$(echo "$RESPONSE" | sed '$d')

          # Check if the sync was successful (HTTP status 2xx)
          if [ $HTTP_STATUS -ge 200 ] && [ $HTTP_STATUS -lt 300 ]; then
            echo "Success! Diagram updated. Status: $HTTP_STATUS"
          else
            echo "Error! Failed to update diagram. Status: $HTTP_STATUS"
            echo "$RESPONSE_BODY"
            exit 1
          fi