mermaid.sql

readme.md

DuckDB ER Diagram Generator Macro

A DuckDB macro that generates Mermaid Entity-Relationship diagrams from your database schema.

Origina Source

• https://gist.github.com/Bilbottom/e1d3d677d2479e0602132327703ff15d
• https://gist.github.com/michael-simons/bd89eaae2bc8ecdcc911c1b08268894b

Installation

WebMacro

INSTALL webmacro FROM community; LOAD webmacro;
SELECT load_macro_from_url('https://gist.github.com/lmangani/dc9ea2ba0a0b2a54a1330e7db868e0bc/raw/297bbabdb588b4917cf7a357194cc0558bfcb5e9/mermaid.sql');

Manual

Install the macro in your DuckDB

Usage

Basic Example

-- Create sample tables
CREATE TABLE users (
    user_id INTEGER NOT NULL PRIMARY KEY,
    username VARCHAR NOT NULL UNIQUE,
    review_datetime TIMESTAMP,
    UNIQUE (user_id, review_datetime)
);

CREATE TABLE events (
    event_id INTEGER NOT NULL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(user_id),
    event_datetime TIMESTAMP NOT NULL,
    event_type VARCHAR NOT NULL
);

-- Generate and write ER diagram
COPY (
    SELECT line 
    FROM generate_er_diagram('er-diagram.mermaid')
    ORDER BY line_num
) 
TO 'er-diagram.mermaid' (header false, quote '', delimiter E'\n');

Filtering Tables

-- Generate diagram for user-related tables only
COPY (
    SELECT line 
    FROM generate_er_diagram('user-er.mermaid', 'user%')
    ORDER BY line_num
) 
TO 'user-er.mermaid' (header false, quote '', delimiter E'\n');

Custom Relationship Style

-- Use different relationship notation
COPY (
    SELECT line 
    FROM generate_er_diagram('er-custom.mermaid', '%', '}o--o{')
    ORDER BY line_num
) 
TO 'er-custom.mermaid' (header false, quote '', delimiter E'\n');

Parameters

• output_path: Name for the output file (used in generated content)
• table_pattern: SQL LIKE pattern for filtering tables (default: '%')
• rel_style: Mermaid relationship notation (default: '||--o{{')

Common Relationship Styles

• ||--o{{: One-to-many (default)
• }}o--o{{: Many-to-many
• ||--||: One-to-one
• }o--o{: Many-to-many (alternative)

Features

• Generates Mermaid-compatible ER diagrams
• Supports composite foreign keys
• Shows data types
• Table filtering using LIKE patterns
• Customizable relationship notation
• Includes column comments in relationships when available

Notes

• The macro returns a result set that must be written to a file using COPY
• Order by line_num to maintain correct diagram structure
• Table patterns use SQL LIKE syntax (e.g., 'user%' for all tables starting with "user")
• Relationship styles use Mermaid notation

Example Output

For the sample tables above, the generated Mermaid diagram will look like:

erDiagram
	    events {integer event_id integer user_id timestamp event_datetime varchar event_type}
	    users {integer user_id varchar username timestamp review_datetime}
	    users }o--o{ events : "user_id"

Loading

License

MIT License - feel free to use in your own projects!

mermaid.sql

CREATE OR REPLACE MACRO generate_er_diagram(output_path, table_pattern :='%', rel_style:='}o--o{') AS TABLE 
WITH hlp AS (
    SELECT 
        referenced_table,
        c.table_name,
        trim(string_agg(d.comment, ' ')) AS comment,
        list_reduce(referenced_column_names, (x,y) -> concat(x, ',', y)) AS columns,
        list_reduce(constraint_column_names, (x,y) -> concat(x, ',', y)) AS fk_columns
    FROM duckdb_constraints() c
    JOIN duckdb_columns d 
        ON d.table_name = c.table_name 
        AND list_contains(c.constraint_column_names, d.column_name)
    WHERE constraint_type = 'FOREIGN KEY'
        AND c.table_name LIKE table_pattern
    GROUP BY ALL
)
SELECT 
    output_path AS out_path,
    line,
    row_number() OVER () as line_num
FROM (
    SELECT 'erDiagram' AS line
    
    UNION ALL
    
    SELECT format(
        '    {:s} {{{:s}}}',
        table_name,
        string_agg(
            lower(
                if(data_type like '%(%',
                    substr(data_type, 1, strpos(data_type, '(') -1),
                    data_type
                )
            ) || ' ' || column_name,
            ' '
        )
    ) AS line
    FROM duckdb_tables() t
    JOIN duckdb_columns() c USING (table_name)
    WHERE table_name LIKE table_pattern
    GROUP BY TABLE_NAME
    
    UNION ALL
    
    SELECT format(
        '    {:s} {:s} {:s} : "{:s}"',
        referenced_table,
        rel_style,
        table_name,
        fk_columns
    ) AS line
    FROM hlp
) sub;

You're an absolute legend - thank you so much for this one!

Hi @lmangani - I really love your Macro - thanks so much for creating it.

I have modified a copy slightly to add Primary, Unique, and Foreign Key information, as well as replace spaces in data types (such as TIMESTAMP WITH TIME ZONE) to fix a bug I had when using columns with that type.

Here is the updated version in case it is helpful to you:

CREATE
OR REPLACE MACRO generate_er_diagram(output_path, table_pattern :='%', rel_style:='}o--o{') AS TABLE
WITH hlp AS (
    SELECT
        referenced_table,
        C.table_name,
        TRIM(string_agg(d.comment, ' ')) AS comment,
        list_reduce(referenced_column_names, (x,y) -> concat(x, ',', y)) AS columns,
        list_reduce(constraint_column_names, (x,y) -> concat(x, ',', y)) AS fk_columns
    FROM duckdb_constraints() C
    JOIN duckdb_columns d
        ON d.table_name = C.table_name
        AND list_contains(C.constraint_column_names, d.column_name)
    WHERE constraint_type = 'FOREIGN KEY'
        AND C.table_name LIKE table_pattern
    GROUP BY ALL
)
SELECT output_path AS out_path
     , line
     , row_number()   over () AS line_num
FROM (SELECT 'erDiagram' AS line

      UNION ALL

      SELECT format(
                     '    {:s} {{{:s}}}',
                     table_name,
                     string_agg(
                             LOWER(
                                     if(data_type LIKE '%(%',
                                        substr(data_type, 1, strpos(data_type, '(') - 1),
                                        replace(data_type, ' ', '_')
                                     )
                             ) || ' ' || column_name
                                 || COALESCE((SELECT listagg(
                                                             CASE constraint_type
                                                                 WHEN 'PRIMARY KEY'
                                                                     THEN ' PK'
                                                                 WHEN 'FOREIGN KEY'
                                                                     THEN ' FK'
                                                                 WHEN 'UNIQUE'
                                                                     THEN ' UK'
                                                                 ELSE ''
                                                                 END
                                                         , ',')
                                              FROM duckdb_constraints() cons
                                              WHERE cons.table_name = c.table_name
                                                AND list_contains(cons.constraint_column_names, c.column_name)
                                                AND cons.constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY', 'UNIQUE')),
                                             ''),
                             '' || chr(10) ORDER BY C.column_index ASC
                     )
             ) AS line
      FROM duckdb_tables() t
               JOIN duckdb_columns() c USING (table_name)
      WHERE table_name LIKE table_pattern
      GROUP BY table_name

      UNION ALL

      SELECT format(
                     '    {:s} {:s} {:s} : "{:s}"',
                     referenced_table,
                     rel_style,
                     table_name,
                     fk_columns
             ) AS line
      FROM hlp) sub;