Turn Your Liquibase Schema into a Visual Database Diagram with AI & Mermaid.js

Managing database changes is challenging enough without having to also maintain up-to-date documentation. What if you could automatically generate a visual entity relationship diagram directly from your Liquibase schema snapshot? In this post, I share an innovative approach that leverages Liquibase, Maven, and the power of large language models (LLMs) to convert your database schema (in YAML format) into a clean, visual Mermaid.js diagram.

Step 1: Generate Your Schema Snapshot

Liquibase makes it simple to capture a snapshot of your current database schema. Using Maven, you can output the snapshot in YAML format with a single command. For example:

mvn liquibase:snapshot -Dliquibase.snapshotFormat=yaml -DoutputFile=schema.yml

This command creates a file named schema.yml containing the structured schema of your database.

Step 2: A Minimal Relational Database Example

Let’s assume we have a more comprehensive database for an e-commerce application with five tables: users, orders, products, order_items, and categories. A simplified version of the schema.yml generate above might look like this:

database:
  name: shopdb
  tables:
    - name: users
      columns:
        - name: id
          type: int
          primaryKey: true
        - name: username
          type: varchar(255)
        - name: email
          type: varchar(255)
    - name: orders
      columns:
        - name: id
          type: int
          primaryKey: true
        - name: user_id
          type: int
          foreignKey:
            referencedTable: users
            referencedColumn: id
        - name: order_date
          type: datetime
    - name: products
      columns:
        - name: id
          type: int
          primaryKey: true
        - name: name
          type: varchar(255)
        - name: price
          type: decimal(10,2)
        - name: category_id
          type: int
          foreignKey:
            referencedTable: categories
            referencedColumn: id
    - name: order_items
      columns:
        - name: id
          type: int
          primaryKey: true
        - name: order_id
          type: int
          foreignKey:
            referencedTable: orders
            referencedColumn: id
        - name: product_id
          type: int
          foreignKey:
            referencedTable: products
            referencedColumn: id
        - name: quantity
          type: int
    - name: categories
      columns:
        - name: id
          type: int
          primaryKey: true
        - name: name
          type: varchar(255)

Step 3: Generate a Mermaid.js Diagram Using an LLM

Large language models excel at parsing structured text. You can feed the above YAML schema into an LLM and ask it to generate a Mermaid.js diagram. Here’s an example prompt:

Generate a Mermaid.js entity relationship diagram based on the following Liquibase YAML schema. The diagram should clearly display each table, its columns (marking primary keys and foreign keys), and the relationships between the tables. Output the result as a Mermaid.js code block.

For our example, you might receive a diagram similar to:

erDiagram
    USERS {
      INT id PK
      VARCHAR username
      VARCHAR email
    }
    ORDERS {
      INT id PK
      INT user_id FK
      DATETIME order_date
    }
    PRODUCTS {
      INT id PK
      VARCHAR name
      DECIMAL price
      INT category_id FK
    }
    ORDER_ITEMS {
      INT id PK
      INT order_id FK
      INT product_id FK
      INT quantity
    }
    CATEGORIES {
      INT id PK
      VARCHAR name
    }

    USERS ||--o{ ORDERS : "places"
    ORDERS ||--o{ ORDER_ITEMS : "contains"
    PRODUCTS ||--o{ ORDER_ITEMS : "included in"
    CATEGORIES ||--o{ PRODUCTS : "categorizes"

If you’ve read my previous blog post, Improving Your Merge Requests using AI and Mermaid Diagrams, you’ll know that one of the biggest benefits of Mermaid is its versatility. You can render these diagrams in Merge Request descriptions, within IntelliJ, or even in Confluence pages.

Here is the rendered Mermaid diagram:

erDiagram
    USERS {
      INT id PK
      VARCHAR username
      VARCHAR email
    }
    ORDERS {
      INT id PK
      INT user_id FK
      DATETIME order_date
    }
    PRODUCTS {
      INT id PK
      VARCHAR name
      DECIMAL price
      INT category_id FK
    }
    ORDER_ITEMS {
      INT id PK
      INT order_id FK
      INT product_id FK
      INT quantity
    }
    CATEGORIES {
      INT id PK
      VARCHAR name
    }

    USERS ||--o{ ORDERS : "places"
    ORDERS ||--o{ ORDER_ITEMS : "contains"
    PRODUCTS ||--o{ ORDER_ITEMS : "included in"
    CATEGORIES ||--o{ PRODUCTS : "categorizes"

Benefits of This Approach

• Up-to-date Documentation: Generating diagrams directly from your live schema ensures that your documentation always reflects the current state of your database.
• Improved Collaboration: Visual representations make it easier for both technical and non-technical team members to understand the database structure.
• Repository Integration: Including the schema snapshot and diagram in your repository means that anyone cloning the project gets immediate insight into the database design.

Using these techniques, you can streamline your database documentation process and maintain an accurate, visual reference of your schema—all directly within your repository.

Conclusions

By combining Liquibase, Maven, and the power of large language models, you can effortlessly generate accurate, visual entity relationship diagrams that keep your documentation current and your team aligned. This approach not only saves time but also improves collaboration by providing a clear and dynamic view of your database architecture directly within your repository. Give it a try and transform how you document your database changes!

I hope this tip helps someone out there :)