AI Dev Tips 3: Create ER Diagrams from a DB Schema with Mermaid Markdown

Markdown-based ER (Entity Resource) diagram using Mermaid, a diagramming and charting tool

This is part of the AI Dev Tips series where we do creative things with dev ideas and ChatGPT.

Let’s develop a markdown-based ER (Entity Resource) diagram using Mermaid, a diagramming and charting tool.

🥰 If you like this kind of content…. subscribe/follow free, clap for it, share with friends/co-workers and maybe your company chat, and follow me as well for updates.

We’ll walk through the process of

1. Taking our schema we made in previous tutorials (or use one you have already or my example) and
2. Using ChatGPT to create the Mermaid Markdown
3. Generating the diagram code from a database schema.
4. Handling common issues. (there was one error we encountered)
5. Validating the visual design in MermaidLive
6. Visualizing the ERD
7. Integrating into your App
8. Other Mermaid Diagrams

AI Dev Tips series

Why Use an Entity Resource Diagram (ERD)?

An Entity Resource Diagram (ERD) is a visual representation of data models, showing entities, attributes, and relationships. ERDs help:

• Conceptualize database design.
• Communicate data structure with stakeholders and team members.
• Act as documentation for developers.
• Improve efficiency in validating, debugging, and troubleshooting data schemas.
• Could help to relate to API endpoints (although this is usually not recommended because as we’ll see in later tutorials it’s preferable to have APIs relate to functional requirements and UX/UI using Gherkins).

btw, short promo, I wrote this guide and you may like it! Check it out!

190+ cloud metrics detailed with formulas, calculation, scenarios, best practces — over 800+ pages detailed content.

Cloud Metrics Guide "Banyan Book" (full version)

When to start with an ERD in the dev process?

For a sketch of what you’ll be doing, I think it’’s fine to start early as soon as you know you need to create a database or a request is being developed by a product manager or stakeholder.

However, important — — IF you start early, realize your ERD work is just a “first draft” and you may have to scrap some if the request and functional design aspect (user flow, UI/UX) keeps changing.

Changes in the functional request could cause you to rethink your schema and, therefore, the ERD.

Remember -Your ERD is still evolving until AFTER the functional business/product request and the UX are complete.

Steps to Create an ERD with Mermaid

1. Prepare Your Database Schema

Start with a clearly defined schema that includes table names, field names, data types, and relationships. This schema will be the basis for generating the ERD.

We already made a schema in AI Dev Tips #1, read up on that if missed it.

Here is a schema — my example in the final image below may be slightly different than your, depending on ChatGPT output and your input (I varied mine for what I did in Mermaid)

We created this in AI Dev Tips #1 based on our business ideas.

-- Schema for an online bookstore

CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    genre VARCHAR(100),
    price NUMERIC(10, 2) NOT NULL,
    published_date DATE,
    stock_quantity INTEGER NOT NULL
);

CREATE TABLE Orders (
    order_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES Users(user_id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(50) DEFAULT 'Pending',
    shipping_address VARCHAR(255) NOT NULL,
    billing_address VARCHAR(255) NOT NULL
);

CREATE TABLE OrderItems (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES Orders(order_id),
    book_id INTEGER REFERENCES Books(book_id),
    quantity INTEGER NOT NULL,
    price_per_unit NUMERIC(10, 2) NOT NULL,
    total_price NUMERIC(10, 2) GENERATED ALWAYS AS (quantity * price_per_unit) STORED
);

CREATE TABLE Reviews (
    review_id SERIAL PRIMARY KEY,
    book_id INTEGER REFERENCES Books(book_id),
    user_id INTEGER REFERENCES Users(user_id),
    rating INTEGER CHECK (rating >= 1 AND rating <= 5),
    review_text TEXT,
    review_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Authors (
    author_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    biography TEXT,
    date_of_birth DATE,
    nationality VARCHAR(100)
);

CREATE TABLE Genres (
    genre_id SERIAL PRIMARY KEY,
    genre_name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

2. Generate Mermaid Markdown ERD with ChatGPT

Input your schema into ChatGPT to get an ERD output.

ChatGPT 4o mini prompt:
I need Mermaid markdown for the following schema: (insert your schema here)

This will output the code

Crow feet notation is used to describe the cardinality of relationships between entities in an ER diagram. Here’s what each part in the code means:

• |: A single vertical bar represents “one.”
• o: A circle represents “zero or one.”
• | — o{: This combination represents a “one-to-many” relationship. The vertical bar (|) indicates “one,” and the crow foot ({) indicates “many.”
• o — o{: This combination represents a “zero or many” relationship.

3. Input the ChatGPT output into Mermaid Playground

Ask ChatGPT to convert the schema into Mermaid markdown format.

Mermaid Playground: https://mermaid.live/ (aka Mermaid Live)

note: there was one error that had to be corrected, see further below for more info on that one.

4. Insert into Editor, Visualize the ERD

Copy the Mermaid markdown and paste it into your editor or a code playground like Mermaid Live.

5. Error handling

I have gotten several errors in the past with ChatGPT’s output on Mermaid, but 95% of the time it was fixed by telling ChatGPT to fix the error and giving it the error. One time after some research, I discovered it was just a library version difference.

In this tutorial, I did get this one error initially when i inserted the code into the Mermaid Playground (Mermaid Live) Editor:

I did not see the issue initially, so I simply put that error into ChatGPT:

ChatGPT 4o mini prompt:
There was was an error, please fix:
(insert error text here here)

6. Visualization

Put that back into the code area of Mermaid Playground.

Then on the right you should see a visualization (note: this image may be slightly different from your schema/ERD depending on what you input and ChatGPT output):

Schema in Mermaid

I wrote this guide and you may like it! Check it out!

190+ cloud metrics detailed with formulas, calculation, scenarios, best practces — over 800+ pages detailed content.

Cloud Metrics Guide “Banyan Book” (full version)

7. Integrating into your App

There are about 10+ different ways to integrate this!

Integration code and plugins really depends on your environment and needs. So I’m not going through all that here. See Getting Started. But I do hope some tips help you get started.

A few key points:

• Mermaid is for markdown, not native React JSX, for example.
• There are integration plugins for Confluence, ChatGPT, Visual Studio Code, Vitepress, Microsoft Powerpoint and Microsoft Word and others. These make it very easy to integrate to documentation and static site apps.
• A React app requires some more work. There are several routes. You can try to use the mermaid API natively, or as plain html/js (calling the cloud packageAPI) or include it with MDX. If I get a chance, may do this as a tutorial soon.
• Plain html can be used with the mermaid API calling mermaid from undelivr. See Getting Started.
• Some frameworks may include a way to use mermaid natively, for example Docusaurus allows this I believe if turned on.
• It may take some time to get what you want. Docusaurus was easy. But in plain React Typescript with Vite, I initially got a lot of type errors that were difficult to resolve. I have used in Next.js with MDX, but not the recent versions so not sure if there have been compatibility changes.
• I use Docusaurus and Confluence for my documentation and those have built-in plugins that required no major coding.

This is generated in in one of my Docusaurus instances:

8. Other Mermaid Diagrams

See this area of the playground or the documentation for more ideas of types of diagrams you could prgromatically create:

Flowcharts Syntax | Mermaid

Other types of diagrams are also available, like flowcharts:

Also mindmaps, and many more — the main advantage is you can create these programmatically.

If you are into Cloud Engineering… I wrote a guide to cloud metrics.

Store: Buy the full Cloud Metrics book / more info: CloudMetricsGuide.com

Cloud Metrics Guide / 190+ cloud metrics / 800+ pages

Cloud metrics covered:

• 190+ metrics in the categories of: User, Network, Reliability, Compute, Compute Scaling, API, Database, Storage, Events and Queues, Security and Cost metrics.
• 190 + cloud metrics in detail with insights!
• 800+ pages of valuable content, the ULTIMATE reference guide!
• PDF, Searchable, Linked Table of Contents (TOC) for fast navigation.
• Scenario/s giving example calculations, formulas and realistic situations.
• List of “Gotchas” for each metric usage, for better understanding.
• Best Practices and implementation suggestions for each metric!!!

Store: Buy the full ebook / more info: CloudMetricsGuide.com

About me

I’m a cloud architect, senior developer and tech lead who enjoys solving high-value challenges with innovative solutions.

I’m always open to discussing projects. If you need help, have an opportunity or simply want to chat, you can reach me on X/Twitter @csjcode and same username gmail. Also, can schedule a Zoom here (lower on page): https://systemsarchitect.io/docs/consulting

I’ve worked 20+ years in software development, both in an enterprise setting such as NIKE and the original MP3.com, as well as startups like FreshPatents, SystemsArchitect.io, API.cc, and Instantiate.io.

My experience ranges from cloud ecommerce, API design/implementation, serverless, AI integration for development, content management, frontend UI/UX architecture and login/authentication. I give tech talks, tutorials and share documentation of architecting software. Also previously held AWS Solutions Architect certification.

Recently I’m working on Instantiate.io, a value creation experiment tool to help startup planning with AI. I’m making progress writing a reference manual on cloud metrics.

Also, an enthusiast of blockchain, I’m active working on applications in the innovative Solana blockchain ecosystem.

Thanks for reading and hope to see you around!