ERD: Postgres SQL to MermaidJs

sql

-- This will query your Postgres DB, and generate MermaidJs formatted ERD

SELECT 'erDiagram'
UNION ALL
  SELECT 
      format(E'%s { %s\n}', 
          c.relname, 
          string_agg(
            format(E'\n  %s %s',
              REPLACE(
                REPLACE(format_type(t.oid, a.atttypmod),
                   ' ', '_'),
                   '.', '-'),
              a.attname
            ), E''
          ))
  FROM pg_class c 
  JOIN pg_namespace n on n.oid = c.relnamespace
  LEFT JOIN pg_attribute a ON c.oid = a.attrelid and a.attnum > 0 and not a.attisdropped
  LEFT JOIN pg_type t ON a.atttypid = t.oid
  WHERE c.relkind in ('r', 'p') 
    AND NOT c.relispartition
    AND n.nspname !~ '^pg_' 
    AND n.nspname <> 'information_schema'
    -- NAMESPACE
    AND n.nspname = '<<NAMESPACE>>'
  GROUP BY c.relname

UNION ALL
SELECT
    format('%s }|..|| %s : %s', c1.relname, c2.relname, c.conname)
FROM pg_constraint c
JOIN pg_namespace ON c.connamespace = pg_namespace.oid
JOIN pg_class c1 on c.conrelid = c1.oid and c.contype = 'f'
JOIN pg_class c2 on c.confrelid = c2.oid
-- NAMESPACE
WHERE nspname = '<<NAMESPACE>>'
  AND NOT c1.relispartition 
  AND NOT c2.relispartition
;