Mermaid + DuckDB for generating entity-relationship diagrams

er-diagram.mermaid

er-diagram.sql

/*
    Mermaid + DuckDB for generating entity-relationship diagrams

    DuckDB version: 0.10.2

    Bill Wallis, 2024-05-09
*/


select version();


create or replace table users (
    user_id         integer not null primary key,
    username        varchar not null unique,
    review_datetime timestamp,
    unique (user_id, review_datetime)
);

create or replace 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
);

create or replace table google_auth (
    user_id       integer not null primary key references users(user_id),
    last_accessed timestamp not null,
    google_token  varchar not null
);

create or replace table password_auth (
    user_id       integer not null primary key references users(user_id),
    last_accessed timestamp not null,
    email         varchar not null,
    password      varchar not null
);

create or replace table review_notes (
    user_id         integer not null,
    review_datetime timestamp not null,
    notes           varchar not null,
    primary key (user_id, review_datetime),
    foreign key (user_id, review_datetime) references users(user_id, review_datetime)
);


/* ER Generation */
copy (
        select 'erDiagram'
    union all
        select format(
            '    {:s} {{{:s}}}',
            table_name, string_agg(lower(data_type) || ' ' || column_name, ' ')
        )
        from information_schema.columns
        group by table_name
    union all
        select format(
            '    {:s} }}o--o{{ {:s} : "{:s}"',
            table_name, fk.table, fk.columns
        )
        from (
            select table_name, regexp_extract(
                constraint_text,
                'FOREIGN KEY \([A-Za-z_, ]+\) REFERENCES (\w+)\s?\(([A-Za-z_, ]+)\)',
                ['table', 'columns']
            ) as fk
            from duckdb_constraints()
            where constraint_type = 'FOREIGN KEY'
        )
) to 'er-diagram.mermaid' (header false, quote '', delimiter E'\n')
;

This is part of the following LinkedIn post:

https://www.linkedin.com/posts/bill-wallis_sql-mermaid-duckdb-activity-7193910294648131584-B5n4