Skip to content

Instantly share code, notes, and snippets.

@lukaszhanusik

lukaszhanusik/er-diagram.mermaid

Forked from billwallis/er-diagram.mermaid
Created May 10, 2024 21:57
Show Gist options
  • Save lukaszhanusik/1af75cd53733c909da2cf0219b225512 to your computer and use it in GitHub Desktop.
Save lukaszhanusik/1af75cd53733c909da2cf0219b225512 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @billwallis billwallis revised this gist May 9, 2024. 1 changed file with 10 additions and 0 deletions.
    10 changes: 10 additions & 0 deletions er-diagram.mermaid
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,10 @@
    erDiagram
    users {integer user_id varchar username timestamp review_datetime}
    events {integer event_id integer user_id timestamp event_datetime varchar event_type}
    google_auth {integer user_id timestamp last_accessed varchar google_token}
    password_auth {integer user_id timestamp last_accessed varchar email varchar password}
    review_notes {integer user_id timestamp review_datetime varchar notes}
    events }o--o{ users : "user_id"
    google_auth }o--o{ users : "user_id"
    password_auth }o--o{ users : "user_id"
    review_notes }o--o{ users : "user_id, review_datetime"
  2. @billwallis billwallis created this gist May 9, 2024.
    74 changes: 74 additions & 0 deletions er-diagram.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,74 @@
    /*
    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')
    ;