Created
March 10, 2023 12:37
-
-
Save predragnikolic/643b42a85aa11fa3eadda2f1c6f8072f to your computer and use it in GitHub Desktop.
Visualise Postgres with d2
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.