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;