Script to extract a json schema from a db

db-to-json-schema.py

import psycopg2
import json

# Connect to the PostgreSQL database
conn = psycopg2.connect(
    host="localhost",
    database="test",
    user="postgres",
    password="postgres"
)
cursor = conn.cursor()

# Query information about tables, columns, etc., excluding internal tables and PostGIS tables
cursor.execute("""
    SELECT table_name, column_name, data_type
    FROM information_schema.columns
    WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
    AND table_name NOT IN ('spatial_ref_sys', 'geometry_columns', 'geography_columns')
    ORDER BY table_name, ordinal_position
""")
data = cursor.fetchall()

# Convert the data to a JSON format
schema_json = {}
for table, column, data_type in data:
    if table not in schema_json:
        schema_json[table] = {}
    schema_json[table][column] = data_type

# Query relationships between tables
cursor.execute("""
    SELECT 
        kcu.table_name, 
        rel_kcu.table_name AS foreign_table_name,
        kcu.column_name, 
        rel_kcu.column_name AS foreign_column_name
    FROM 
        information_schema.table_constraints AS tc 
    JOIN 
        information_schema.key_column_usage AS kcu
          ON tc.constraint_name = kcu.constraint_name
          AND tc.table_schema = kcu.table_schema
    JOIN 
        information_schema.referential_constraints AS rc 
          ON tc.constraint_name = rc.constraint_name
          AND tc.table_schema = rc.constraint_schema
    JOIN 
        information_schema.key_column_usage AS rel_kcu 
          ON rc.unique_constraint_name = rel_kcu.constraint_name
          AND rc.unique_constraint_schema = rel_kcu.constraint_schema
    WHERE 
        tc.constraint_type = 'FOREIGN KEY' AND
        tc.table_name NOT IN ('spatial_ref_sys', 'geometry_columns', 'geography_columns')
""")
relationships = cursor.fetchall()

# Add relationships to the JSON schema
for table, foreign_table, column, foreign_column in relationships:
    if table in schema_json and column in schema_json[table]:
        schema_json[table][column] += f" (references {foreign_table}.{foreign_column})"

# Write the JSON representation to a file
with open("schema.json", "w") as f:
    json.dump(schema_json, f, indent=2)

# Close the database connection
cursor.close()
conn.close()