GenAI-created Mermaid ER diagram

As the first step of our AI roadmap, it is now possible, starting with v8.3.2, to import (reverse-engineer) a model in Mermaid ER diagram code.  This features allows to instantly generate models, polyglot or physical, then edit and enrich them, plus integrate suggestions from GenAI.  By integrating AI-generated diagrams with Hackolade Studio’s intuitive modeling, teams can efficiently develop and refine their data models, enhancing collaboration and accelerating the development cycle.

 

Mermaid 

Mermaid is a JavaScript-based diagramming and charting tool that uses Markdown-inspired text definitions, and a renderer to create and modify complex diagrams. Among many other types of diagrams, Mermaid supports ER diagrams.  Users have the possibility to use the tool for ER diagrams with basic functionality.  Its playground also allows to create simple diagrams, although far from the professional level and capabilities of Hackolade Studio.

 

Mermaid is a  popular tool, meaning that there are many existing ER diagrams that users would like to easily convert into Hackolade Studio models. Also, Mermaid has a very convenient AI interface, including their Mermaid Chart GPT plugin to ChatGPT.. 

 

Note that, among other things, Mermaid does NOT support: NOT NULL constraints, string length or numeric precision and scale, complex data types (except the root level of an array with no further details), generation of DDLs or physical schemas.  It nevertheless remains a handy way to prompt GenAI LLMs, then import the output into Hackolade Studio.

 

Prompt

ChatGPT and other generative AI models find it easier to generate ER diagrams in Mermaid code compared to SQL DDL or traditional modeling standards because Mermaid uses a simple, human-readable syntax that closely aligns with natural language. Its declarative and visual style allows AI to map conceptual relationships directly into diagram code without needing strict adherence to data types, constraints, or database-specific rules. Unlike DDLs, which requires precision and contextual knowledge of database engines, Mermaid is more forgiving and doesn’t require runtime validation. This makes it an ideal format for GenAI to produce quickly and accurately from natural prompts.

 

You may create prompts using entire business requirements, and fine-tune the prompt until the result suits your needs.  For the purpose of this documentation page, here is a super simple prompt example:

Generate an ERD in Mermaid code for an e-commerce application, full with tables, columns, data types, relationships, and comments at table and column levels.

 

With some LLMs, it might be required to specify 

"P*rovide syntactically correct code so it can be accepted by https://www.mermaidchart.com/*"  

 

You may also ask the LLM to 

"Only use the information provided" or to "Also suggest additional data that should be included."

 

Note: the results vary greatly depending on the LLM and the prompt.  

 

 

Here is a simple example response from ChatGPT for the above prompt:

 

erDiagram
    %% ----------------------------
    %% Users Table
    %% Stores registered customers and admin users
    %% ----------------------------
    USERS {
        int id PK "Primary key"
        string username "Unique username"
        string email "User email address"
        string password_hash "Encrypted password"
        string role "customer/admin"
        datetime created_at "Account creation date"
    }
    %% ----------------------------
    %% Products Table
    %% Stores items for sale
    %% ----------------------------
    PRODUCTS {
        int id PK "Primary key"
        string name "Product name"
        string description "Product description"
        decimal price "Price of the product"
        int stock_quantity "Available stock"
        datetime created_at "Date added"
        int category_id FK "FK to product category"
    }
    %% ----------------------------
    %% Categories Table
    %% Groups of similar products
    %% ----------------------------
    CATEGORIES {
        int id PK "Primary key"
        string name "Category name"
        string description "Optional details"
    }
    %% ----------------------------
    %% Orders Table
    %% Each customer order
    %% ----------------------------
    ORDERS {
        int id PK "Primary key"
        int user_id FK "FK to USERS"
        datetime order_date "Date of order"
        string status "pending/shipped/delivered"
        decimal total_amount "Calculated order total"
    }
    %% ----------------------------
    %% Order Items Table
    %% Line items per order
    %% ----------------------------
    ORDER_ITEMS {
        int id PK "Primary key"
        int order_id FK "FK to ORDERS"
        int product_id FK "FK to PRODUCTS"
        int quantity "Quantity ordered"
        decimal unit_price "Snapshot of product price"
    }
    %% ----------------------------
    %% Addresses Table
    %% Shipping or billing addresses
    %% ----------------------------
    ADDRESSES {
        int id PK "Primary key"
        int user_id FK "FK to USERS"
        string address_line1 "Street address"
        string address_line2 "Apartment/Suite/etc."
        string city "City"
        string state "State or region"
        string postal_code "Zip or postal code"
        string country "Country"
        string type "billing/shipping"
    }
    %% ----------------------------
    %% Relationships
    %% ----------------------------
    USERS ||--o{ ORDERS : "places"
    USERS ||--o{ ADDRESSES : "has"
    PRODUCTS ||--o{ ORDER_ITEMS : "included in"
    ORDERS ||--o{ ORDER_ITEMS : "contains"
    ORDERS }o--|| USERS : "belongs to"
    ORDER_ITEMS }o--|| PRODUCTS : "refers to"
    PRODUCTS }o--|| CATEGORIES : "belongs to"
 

 

Important: Mermaid supports many different kinds of diagrams.  Hackolade Studio currently only supports the import of ER diagram code of Mermaid.  The code must start with the first line erDiagram and follow the specification, and must be syntactically compliant for us to parse the code.

 

Reverse-engineer in Hackolade Studio

There are 2 ways to import Mermaid ER diagram code into Hackolade Studio: by reading Mermaid files with .mmd extensions, or by reading the clipboard into which you would have copied the Mermaid code from the GenAI provider's response to your prompt.

 

Depending on the target technology of your model, the import process will convert the data types into the closest corresponding type for the target.  Naming conventions, if enabled, are applied.

 

With either method, the above Mermaid code will result in this Hackolade Studio diagram:

 

From Mermaid files

Go to Tools > Reverse-Engineer > Mermaid ER Diagrams... and select one or more Mermaid files with the extension .mmd

 

 

From your Clipboard (TBA)

Our Open From dialog, which already allows to open models from either your local computer or from any of the Git repo providers you might have configured, is where we have allowed to read your clipboard (the temporary storage in your computer's memory.).  When you prompt your GPT and ask to create output in Mermaid ER diagram code, you may click the Copy button which populates your clipboard.  When you You access the Open From dialog and choose the Clipboard tab on the left, we automatically read the latest entry in your clipboard.  If you change the content of the clipboard, you can refresh the content of the dialog by clicking the Refresh button.

 

 

When you click on the Open button, the Mermaid ER diagram code is parsed and is added to he currently opened model.  Depending on the selected target, the data types are mapped according to the closest available data type for that target.  If no model is already opened, you are prompted to choose the target.