Database Schema Differ

What This Does

A CLI tool to compare database schemas across different environments (development, staging, production), generate migration scripts, and track schema evolution over time. Support for PostgreSQL, MySQL, SQLite, and other databases via SQLAlchemy.

Key features:

• - Schema comparison: Compare schemas between databases, branches, or points in time
• Migration generation: Automatically generate SQL migration scripts (up/down) for schema changes
• Schema snapshots: Capture and store schema snapshots for historical comparison
• Drift detection: Identify schema drift between environments (dev vs prod, etc.)
• Multiple database support: PostgreSQL, MySQL, SQLite, SQL Server, Oracle via SQLAlchemy
• Export formats: Generate SQL, JSON, or visual diff outputs
• Integration ready: Works with Alembic, Django migrations, or standalone
• Change tracking: Track schema evolution over time with versioning
• CI/CD friendly: Output machine-readable formats for automation pipelines

When To Use

• - You need to compare database schemas between development and production
• You want to generate migration scripts for schema changes
• You're managing multiple database environments and need to ensure consistency
• You need to detect schema drift in production databases
• You're refactoring databases and need to track changes
• You want to automate schema validation in CI/CD pipelines
• You need to document schema changes for compliance or team coordination
• You're onboarding new team members and need to understand schema evolution
• You want to visualize schema differences between branches or versions

Usage

Basic commands:

CODEBLOCK0

Examples

Example 1: Compare development and production databases

CODEBLOCK1

Output:
CODEBLOCK2

Example 2: Generate migration script

CODEBLOCK3

Output (migration.sql):
CODEBLOCK4

Example 3: Check for schema drift in CI

CODEBLOCK5

Output (CI failure):
CODEBLOCK6

Example 4: Track schema evolution

CODEBLOCK7

Output:
CODEBLOCK8

Example 5: Visual schema comparison

CODEBLOCK9

Output:
CODEBLOCK10

Requirements

• - Python 3.x
• SQLAlchemy (for database connectivity)
• Alembic (optional, for migration generation)
• Database drivers: psycopg2 (PostgreSQL), pymysql (MySQL), etc.

Install dependencies:
CODEBLOCK11

Limitations

• - Requires database credentials and network access to compare live databases
• Complex schema changes may require manual review of generated migrations
• Limited support for database-specific features not covered by SQLAlchemy
• Performance may be impacted with very large schemas (1000+ tables)
• No built-in support for NoSQL databases (MongoDB, Redis, etc.)
• Cannot compare encrypted or compressed database dumps
• Limited error handling for connection issues or permission problems
• No support for comparing materialized views or database functions across all DB types
• Generated migrations may not handle data migration or complex transformation
• No built-in support for distributed database comparisons
• Limited to schema structure; does not compare data or indexes optimally
• May not detect all schema differences for databases with custom types or extensions
• No support for comparing database triggers or stored procedures across all database types
• Performance may degrade with very large tables or complex relationships
• No built-in support for schema version control systems (like Liquibase or Flyway)
• Limited error recovery for malformed SQL or corrupted schema files
• No support for real-time schema change monitoring
• Cannot compare schemas across different database types (e.g., PostgreSQL vs MySQL)
• Limited support for database-specific optimizations or extensions
• No built-in notification system for schema drift alerts
• May require manual adjustment of generated migration scripts for production use

Directory Structure

The tool works with database connection strings, SQL files, or schema snapshot files. No special configuration directories are required.

Error Handling

• - Invalid database connections show helpful error messages with connection details
• Permission errors suggest checking database credentials and access rights
• Schema parsing errors show line numbers and specific SQL issues
• Comparison errors suggest checking schema compatibility or database versions
• File not found errors suggest checking paths and file permissions
• Output generation errors suggest checking disk space and write permissions

Contributing

This is a skill built by the Skill Factory. Issues and improvements should be reported through the OpenClaw project.