chartdb: database diagrams editor, visualize and design your DB

[nelsonic]

https://github.com/chartdb/chartdb

Currently top of HN: https://news.ycombinator.com/item?id=41339308

https://app.chartdb.io/

Totally unnecessary use of a Modal 🙄

WITH fk_info AS (
  select array_to_string(array_agg(CONCAT('{"schema":"', schema_name, '"',
                                          ',"table":"', table_name, '"',
                                          ',"column":"', fk_column, '"',
                                          ',"foreign_key_name":"', foreign_key_name, '"',
                                          ',"reference_table":"', reference_table, '"',
                                          ',"reference_column":"', reference_column, '"',
                                          ',"fk_def":"', fk_def,
                                          '"}')), ',') as fk_metadata
  from (
      SELECT
          'public' as schema_name,
          conname AS foreign_key_name,
          conrelid::regclass AS table_name,
          (regexp_matches(pg_get_constraintdef(oid), 'FOREIGN KEY \((\w+)\) REFERENCES (\w+)\((\w+)\)', 'g'))[1] AS fk_column,
          (regexp_matches(pg_get_constraintdef(oid), 'FOREIGN KEY \((\w+)\) REFERENCES (\w+)\((\w+)\)', 'g'))[2] AS reference_table,
          (regexp_matches(pg_get_constraintdef(oid), 'FOREIGN KEY \((\w+)\) REFERENCES (\w+)\((\w+)\)', 'g'))[3] AS reference_column,
          pg_get_constraintdef(oid) as fk_def
      FROM
          pg_constraint
      WHERE
          contype = 'f'
          AND connamespace = 'public'::regnamespace) as x
), pk_info AS (
  SELECT array_to_string(array_agg(CONCAT('{"schema":"', schema_name, '"',
                                          ',"table":"', pk_table, '"',
                                          ',"column":"', pk_column, '"',
                                          ',"pk_def":"', pk_def,
                                          '"}')), ',') as pk_metadata
  FROM (
      SELECT
          'public' as schema_name,
          conrelid::regclass::text AS pk_table,
          unnest(string_to_array(substring(pg_get_constraintdef(oid) FROM '\((.*?)\)'), ',')) AS pk_column,
          pg_get_constraintdef(oid) as pk_def
      FROM
          pg_constraint
      WHERE
          contype = 'p'
          AND connamespace = 'public'::regnamespace
  ) as y
),
indexes_cols as (
  select tnsp.nspname                                                                        as schema_name,
                         trel.relname                                                        as table_name,
                         pg_relation_size(tnsp.nspname || '.' || '"' || irel.relname || '"') as index_size,
                         irel.relname                                                        as index_name,
                         am.amname                                                           as index_type,
                         a.attname                                                           as col_name,
                         (case when i.indisunique = true then 'true' else 'false' end)       as is_unique,
                         irel.reltuples                                                      as cardinality,
                         1 + Array_position(i.indkey, a.attnum)                              as column_position,
                         case o.OPTION & 1 when 1 then 'DESC' else 'ASC' end                 as direction,
       CASE WHEN indpred IS NOT NULL THEN 'true' ELSE 'false' END as is_partial_index
                  from pg_index as i
                           join pg_class as trel on trel.oid = i.indrelid
                           join pg_namespace as tnsp on trel.relnamespace = tnsp.oid
                           join pg_class as irel on irel.oid = i.indexrelid
                           join pg_am as am on irel.relam = am.oid
                           cross join lateral unnest (i.indkey)
                  with ordinality as c (colnum, ordinality) left join lateral unnest (i.indoption)
                  with ordinality as o (option, ordinality)
                  on c.ordinality = o.ordinality join pg_attribute as a on trel.oid = a.attrelid and a.attnum = c.colnum
                  where tnsp.nspname not like 'pg_%'
                  group by tnsp.nspname, trel.relname, irel.relname, am.amname, i.indisunique, i.indexrelid, irel.reltuples, a.attname, array_position(i.indkey, a.attnum), o.OPTION, i.indpred
),
cols as (
  select array_to_string(array_agg(CONCAT('{"schema":"', cols.table_schema,
                                              '","table":"', cols.table_name,
                                              '","name":"', cols.column_name,
                                              '","ordinal_position":"', cols.ordinal_position,
                                              '","type":"', LOWER(replace(cols.data_type, '"', '')),
                                              '","character_maximum_length":"', COALESCE(cols.character_maximum_length::text, 'null'),
                                              '","precision":',
                                                  CASE
                                                      WHEN cols.data_type = 'numeric' OR cols.data_type = 'decimal'
                                                      THEN CONCAT('{"precision":', COALESCE(cols.numeric_precision::text, 'null'),
                                                                  ',"scale":', COALESCE(cols.numeric_scale::text, 'null'), '}')
                                                      ELSE 'null'
                                                  END,
                                              ',"nullable":', case when (cols.IS_NULLABLE = 'YES') then 'true' else 'false' end,
                                              ',"default":"', COALESCE(cols.column_default, ''),
                                              '","collation":"', coalesce(cols.COLLATION_NAME, ''), '"}')), ',') as cols_metadata
    from information_schema.columns cols
    where cols.table_schema not in ('information_schema', 'pg_catalog')
), indexes_metadata as (
  select array_to_string(array_agg(CONCAT('{"schema":"', schema_name,
                                          '","table":"', table_name,
                                          '","name":"', index_name,
                                          '","column":"', replace(col_name :: text, '"', E'"'),
                                          '","index_type":"', index_type,
                                          '","cardinality":', cardinality,
                                          ',"size":', index_size,
                                          ',"unique":', is_unique,
                                          ',"is_partial_index":', is_partial_index,
                                          ',"direction":"', lower(direction),
                                          '"}')), ',') as indexes_metadata
        from indexes_cols x
), tbls as (
  select array_to_string(array_agg(CONCAT('{', '"schema":"', TABLE_SCHEMA, '",', '"table":"', TABLE_NAME, '",', '"rows":',
                                    coalesce((select s.n_live_tup
                                              from pg_stat_user_tables s
                                              where tbls.TABLE_SCHEMA = s.schemaname and tbls.TABLE_NAME = s.relname),
                                             0), ', "type":"', TABLE_TYPE, '",', '"engine":"",', '"collation":""}')),
                   ',') as tbls_metadata
    from information_schema.tables tbls
    where tbls.TABLE_SCHEMA not in ('information_schema', 'pg_catalog')
), config as (
  select array_to_string(
                   array_agg(CONCAT('{"name":"', conf.name, '","value":"', replace(conf.setting, '"', E'"'), '"}')),
                   ',') as config_metadata
    from pg_settings conf
), views as
(
  select array_to_string(array_agg(CONCAT('{"schema":"', views.schemaname, '","view_name":"', viewname, '"}')),
                   ',') as views_metadata
    from pg_views views
  where views.schemaname not in ('information_schema', 'pg_catalog')
)
select CONCAT('{    "fk_info": [', coalesce(fk_metadata, ''),
                  '], "pk_info": [', COALESCE(pk_metadata, ''),
                  '], "columns": [', coalesce(cols_metadata, ''),
                  '], "indexes": [', coalesce(indexes_metadata, ''),
                  '], "tables":[', coalesce(tbls_metadata, ''),
                  '], "views":[', coalesce(views_metadata, ''),
                  '], "database_name": "', current_database(), '', '", "version": "', '',
            '"}') as " "
from fk_info, pk_info, cols, indexes_metadata, tbls, config, views;