Quickly create Mermaid ER-Diagrams for DuckDB Tables

copy_to_mermaid.sql

-- 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')
;