Skip to content

Instantly share code, notes, and snippets.

@michael-simons

michael-simons/copy_to_mermaid.sql

Created November 21, 2024 11:21
Show Gist options
  • Save michael-simons/bd89eaae2bc8ecdcc911c1b08268894b to your computer and use it in GitHub Desktop.
Save michael-simons/bd89eaae2bc8ecdcc911c1b08268894b to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. michael-simons created this gist Nov 21, 2024.
    36 changes: 36 additions & 0 deletions copy_to_mermaid.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,36 @@
    -- Inspired by https://gist.github.com/Bilbottom/e1d3d677d2479e0602132327703ff15d
    -- Fixed datatypes that don't render in mermaid (structs, decimal etc.)
    -- Uses 1:n as base, cardinalities are hard to derive
    -- Looks for column comments
    -- Avoids regex.

    COPY (
    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
    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'
    GROUP BY ALL
    )
    SELECT 'erDiagram'
    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, ' ')
    )
    FROM duckdb_tables() t
    JOIN duckdb_columns() c USING (table_name)
    GROUP BY TABLE_NAME
    UNION ALL
    SELECT format(
    ' {:s} ||--o{{ {:s} : "{:s}"',
    referenced_table,
    table_name,
    ifnull(comment, columns)
    )
    FROM hlp
    ) TO 'er-diagram.mermaid' (header false, quote '', delimiter E'\n')
    ;