dbcatalog 0.1.1

Export DB schema as JSON (types & relations) generating DDL and ER Diagrams.

DB Schema Extractor

A robust, modular Python tool to extract database schema metadata (tables, columns, relationships, indexes) into a structured JSON format. It supports multiple dialects and standard connection strings.

Features

• Multi-Dialect Support: SQLite, PostgreSQL, MySQL, MSSQL.
• Comprehensive Metadata: Extracts tables, columns, primary keys, foreign keys (including ON DELETE/ON UPDATE rules), indexes, and unique constraints.
• Modular Architecture: Easy to extend with new providers.
• Dual Usage: Use as a Command Line Interface (CLI) or import as a Python library.

Installation

Ensure you have Python installed. Install the required dependencies using the provided requirements file:

pip install -r requirements.txt

This installs sqlalchemy and drivers for PostgreSQL, MySQL, and MSSQL. SQLite support is built-in.

Usage

1. Command Line Interface (CLI)

Run the tool locally providing the database URL.

# Basic usage
python -m dbcatalog.main --url "postgresql://user:pass@localhost:5432/mydb"

# Pretty print output to a file
python -m dbcatalog.main --url "sqlite:///chinook.db" --out schema.json --pretty

# Include indexes in the export
python -m dbcatalog.main --url "mysql://user:pass@localhost/db" --include-indexes

2. Python Library

You can integrate the extractor into your own Python scripts.

from dbcatalog import DBDriver

# Initialize driver
driver = DBDriver("postgresql://user:pass@localhost:5432/mydb")

# Option 1: Full Schema Export
schema_json = driver.get_db_schema(include_indexes=True)
print(schema_json)

# Option 2: Granular Access
schemas = driver.get_schemas()
for schema in schemas:
    tables = driver.get_tables(schema)
    for table in tables:
        columns = driver.get_columns(schema, table)
        relations = driver.get_relations(schema, table)
        print(f"Table: {table}, Columns: {len(columns)}, Relations: {len(relations)}")

Supported Output

The tool exports a JSON object containing:

• Database name & engine
• List of Schemas
• For each Table:
  • Columns (name, type, nullable, default)
  • Primary Keys
  • Foreign Keys (referenced table/cols, on_delete/on_update actions)
  • Indexes (unique/non-unique)
  • Unique Constraints