Visualise Postgres with d2

index.js

const { Client } = require('pg')
const fs = require('fs');

const relations= []
async function main() {
    const client = new Client({
        connectionString: '', // e.g. postgres://user:password@host:5432/database
    })
    await client.connect()
    const res = await client.query(`
SELECT c.table_schema, c.table_name, c.column_default, c.column_name, c.is_nullable, c.data_type, ccu.constraint_name
    FROM information_schema.columns AS c
    LEFT JOIN information_schema.constraint_column_usage AS ccu
        ON ccu.table_name = c.table_name
        AND ccu.column_name = c.column_name
WHERE c.table_schema = 'public'
ORDER BY c.table_name ASC, c.ordinal_position;
`)
    const table = {
        // tableName: {
        //   fields: {name, }
        // }
    }
    for (let r of res.rows) {
        if (!table[r.table_name]) table[r.table_name] = {}
        if  (!table[r.table_name].fields) table[r.table_name].fields = []


        const constraint_name = r.constraint_name ??""
        let constraint = null
        if (constraint_name.includes('pkey')) constraint = 'primary_key'
        if (constraint_name.includes('fkey')) constraint = 'foreign_key'
        if (constraint_name.includes('_key')) constraint = 'unique'

        if (constraint === 'foreign_key') {
            let x = constraint_name.replace('_fkey', '')
            const [toName, ...columns] = x.split('_')
            relations.push({from: r.table_name, to: toName, column: columns.join('_')})
        }
        const field = table[r.table_name].fields.find(x => r.column_name === x.name)
        if (field?.constraint === 'primary_key') {
            continue
        }

        table[r.table_name].fields.push({
            name: r.column_name,
            type: r.data_type,
            default: r.column_default ?? null,
            nullable: r.is_nullable === 'YES',
            constraint
        })

    }

    content = ''
    for (const rel of relations) {
        const field = table[rel.to].fields.find(x => x.name == rel.column)
        if (field) {
            field.constraint = 'foreign_key'
        }
    }

    for (let tableName in table) {
        let fields = ''
        table[tableName].fields.forEach((field, i) => {fields += `${field.name}${field.nullable ? '?': ''}: ${field.type} ${field.default ? `- Default: ${field.default}` : ''} ${field.constraint ? `{constraint: ${field.constraint}}`: ''}\n`})
        content += `
${tableName}: {
  shape: sql_table
  ${fields}
}
`
    }

    console.log(relations)
    for (const rel of relations) {
        content += `\n${rel.from} <- ${rel.to}`
    }

    try {
      fs.writeFileSync(__dirname + '/hello.d2', content);
      // file written successfully
    } catch (err) {
      console.error(err);
    }

    await client.end()
}

main()

Requirements:

• make sure to install the pg npm dependecy.

SELECT
  conname AS constraint_name,
  conrelid::regclass AS table_name,
  a.attname AS column_name,
  confrelid::regclass AS referenced_table_name,
  af.attname AS referenced_column_name
FROM
  pg_constraint c
  JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
  JOIN pg_attribute af ON af.attnum = ANY(c.confkey) AND af.attrelid = c.confrelid
ORDER BY
  conrelid::regclass::text,
  conname;

This query retrieves information from the pg_constraint table, which stores details about constraints, including foreign key constraints. It joins this table with pg_attribute twice to get information about the columns involved in the foreign key relationship.

The columns returned by the query are:

constraint_name: The name of the foreign key constraint.
table_name: The name of the table containing the foreign key.
column_name: The name of the column in the table containing the foreign key.
referenced_table_name: The name of the referenced table.
referenced_column_name: The name of the referenced column.
The results are ordered by table name and constraint name.