Generate a Mermaid diagram from a PostgreSQL query

psql-to-mmd-with-filter.sql

WITH target_tables AS (
  SELECT DISTINCT(constr.relname)
  FROM pg_class constr
    JOIN pg_namespace nmsp ON nmsp.oid = constr.relnamespace
    LEFT join pg_attribute attrs ON constr.oid = attrs.attrelid
    AND attrs.attnum > 0
    AND NOT attrs.attisdropped
    LEFT JOIN pg_type t ON attrs.atttypid = t.oid
  WHERE constr.relkind in ('r', 'p')
    AND NOT constr.relispartition
    AND nmsp.nspname !~ '^pg_'
    AND nmsp.nspname <> 'information_schema'
    AND CASE WHEN $1::text[] IS NULL
      THEN constr.relname <> ALL($2::text[])
      ELSE constr.relname = ANY($1::text[])
    END
),
entity_attrs_raw AS (
  SELECT
    constr.relname AS entity,
    attrs.attname AS attr_name,
    format_type(t.oid, attrs.atttypmod) AS attr_type
  FROM pg_class constr
    INNER JOIN pg_namespace nmsp ON nmsp.oid = constr.relnamespace
    LEFT JOIN pg_attribute attrs ON constr.oid = attrs.attrelid
      AND attrs.attnum > 0
      AND NOT attrs.attisdropped
    LEFT JOIN pg_type t ON attrs.atttypid = t.oid
  WHERE constr.relkind in ('r', 'p')
    AND NOT constr.relispartition
    AND nmsp.nspname !~ '^pg_'
    AND nmsp.nspname <> 'information_schema'
  ORDER BY constr.relname, attrs.attnum
),
entity_attrs_filtered AS (
  SELECT * FROM entity_attrs_raw WHERE entity IN (SELECT relname FROM target_tables)
  ORDER by entity
),
entity_attrs_sub1 AS ( -- replace long timezone with abbreviation
  SELECT entity, attr_name,
  REPLACE(attr_type, 'with time zone', 'tz') AS attr_type
  FROM entity_attrs_filtered
),
entity_attrs_sub2 AS ( -- shorten character varying to varchar
  SELECT entity, attr_name,
  REGEXP_REPLACE(attr_type, 'character varying(\(\d+\))?', 'varchar\1','g') AS attr_type
  FROM entity_attrs_sub1
),
entity_attrs_sub3 AS ( -- replace non-word characters with underscores
  SELECT entity, attr_name,
  REGEXP_REPLACE(attr_type, '[^\w()\[\]]+', '_','g') AS attr_type
  FROM entity_attrs_sub2
),
entity_attrs_sub4 AS ( -- replace array types with list(type)
  SELECT entity, attr_name,
  REGEXP_REPLACE(attr_type, '([\w_]+)\[\]', 'list(\1)','g') AS attr_type
  FROM entity_attrs_sub3
),
entity_attrs_formatted AS ( --- format attributes as "name type" and prefix with 4 spaces for mermaid formatting
  SELECT entity, 
  FORMAT(E'    %s %s', attr_type, attr_name) AS attr
  FROM entity_attrs_sub4
),
diagram_entities AS ( -- format each entity & attrs
  SELECT
  FORMAT(E'  %s {\n%s\n  }', entity, string_agg(attr, E'\n')) AS line
  FROM entity_attrs_formatted
  GROUP BY entity
),
relations_raw AS (
  SELECT
    left_type.relname AS left_name,
    right_type.relname AS right_name,
    constr.*
  FROM pg_constraint constr
    JOIN pg_class left_type ON constr.conrelid = left_type.oid AND  constr.contype = 'f'
    JOIN pg_class right_type ON constr.confrelid = right_type.oid
  WHERE NOT left_type.relispartition AND NOT right_type.relispartition
),
relations_filtered AS ( -- filter to find desired relations based on input
  SELECT * FROM relations_raw WHERE CASE WHEN $1::text[] IS NULL
    THEN
      left_name IN (SELECT relname FROM target_tables) OR
      right_name IN (SELECT relname FROM target_tables)

    ELSE
      left_name IN (SELECT relname FROM target_tables) AND
      right_name IN (SELECT relname FROM target_tables)
  END
),
relations_with_flags AS ( -- locate relations that are one-to-one
  SELECT relations_filtered.*, (
    SELECT COUNT(*) > 0
    FROM pg_constraint pks
    WHERE relations_filtered.conrelid = pks.conrelid
      AND relations_filtered.conkey && pks.conkey
      AND pks.contype = 'p'
  ) AS is_one_to_one -- one to one because left side is also a primary key
  FROM relations_filtered
),
diagram_relations AS (
  SELECT CASE WHEN is_one_to_one
    THEN format('%s ||..|| %s : %s',
      left_name,
      right_name,
      conname
    )
    ELSE format('%s }|..|| %s : %s',
      left_name,
      right_name,
      conname
    )
  END AS line
  FROM relations_with_flags
  ORDER BY line
),
diagram AS (
  SELECT 'erDiagram' AS line
  UNION ALL
  SELECT line FROM diagram_entities
  UNION ALL
  SELECT line FROM diagram_relations
)
SELECT string_agg(line, E'\n') AS diagram FROM diagram;

psql-to-mmd.sql

WITH entity_attrs_raw AS (
  SELECT
    constr.relname AS entity,
    attrs.attname AS attr_name,
    format_type(t.oid, attrs.atttypmod) AS attr_type
  FROM pg_class constr
    INNER JOIN pg_namespace nmsp ON nmsp.oid = constr.relnamespace
    LEFT JOIN pg_attribute attrs ON constr.oid = attrs.attrelid
      AND attrs.attnum > 0
      AND NOT attrs.attisdropped
    LEFT JOIN pg_type t ON attrs.atttypid = t.oid
  WHERE constr.relkind in ('r', 'p')
    AND NOT constr.relispartition
    AND nmsp.nspname !~ '^pg_'
    AND nmsp.nspname <> 'information_schema'
  ORDER BY constr.relname, attrs.attnum
),
entity_attrs_sub1 AS ( -- replace long timezone with abbreviation
  SELECT entity, attr_name,
  REPLACE(attr_type, 'with time zone', 'tz') AS attr_type
  FROM entity_attrs_raw
),
entity_attrs_sub2 AS ( -- shorten character varying to varchar
  SELECT entity, attr_name,
  REGEXP_REPLACE(attr_type, 'character varying(\(\d+\))?', 'varchar\1','g') AS attr_type
  FROM entity_attrs_sub1
),
entity_attrs_sub3 AS ( -- replace non-word characters with underscores
  SELECT entity, attr_name,
  REGEXP_REPLACE(attr_type, '[^\w()\[\]]+', '_','g') AS attr_type
  FROM entity_attrs_sub2
),
entity_attrs_sub4 AS ( -- replace array types with list(type)
  SELECT entity, attr_name,
  REGEXP_REPLACE(attr_type, '([\w_]+)\[\]', 'list(\1)','g') AS attr_type
  FROM entity_attrs_sub3
),
entity_attrs_formatted AS ( --- format attributes as "name type" and prefix with 4 spaces for mermaid formatting
  SELECT entity, 
  FORMAT(E'    %s %s', attr_type, attr_name) AS attr
  FROM entity_attrs_sub4
),
diagram_entities AS ( -- format each entity & attrs
  SELECT
  FORMAT(E'  %s {\n%s\n  }', entity, string_agg(attr, E'\n')) AS line
  FROM entity_attrs_formatted
  GROUP BY entity
),
relations_raw AS (
  SELECT
    left_type.relname AS left_name,
    right_type.relname AS right_name,
    constr.*
  FROM pg_constraint constr
    JOIN pg_class left_type ON constr.conrelid = left_type.oid AND  constr.contype = 'f'
    JOIN pg_class right_type ON constr.confrelid = right_type.oid
  WHERE NOT left_type.relispartition AND NOT right_type.relispartition
),
relations_with_flags AS ( -- locate relations that are one-to-one
  SELECT relations_raw.*, (
    SELECT COUNT(*) > 0
    FROM pg_constraint pks
    WHERE relations_raw.conrelid = pks.conrelid
      AND relations_raw.conkey && pks.conkey
      AND pks.contype = 'p'
  ) AS is_one_to_one -- one to one because left side is also a primary key
  FROM relations_raw
),
diagram_relations AS (
  SELECT CASE WHEN is_one_to_one
    THEN format('%s ||..|| %s : %s',
      left_name,
      right_name,
      conname
    )
    ELSE format('%s }|..|| %s : %s',
      left_name,
      right_name,
      conname
    )
  END AS line
  FROM relations_with_flags
  ORDER BY line
),
diagram AS (
  SELECT 'erDiagram' AS line
  UNION ALL
  SELECT line FROM diagram_entities
  UNION ALL
  SELECT line FROM diagram_relations
)
SELECT string_agg(line, E'\n') AS diagram FROM diagram;