Database Schema Differ

Compare database schemas across environments, generate migration scripts, and track schema evolution.

Security Scan

The skill's documentation and metadata claim live database connectivity (SQLAlchemy/Alembic support) and broad functionality, but the included code only parses local SQL files and explicitly does not implement database connections — this mismatch is unexpected and could lead to misuse or data exposure if run against production systems.

Purpose & Capability

The name/description promise comparing live databases, migration generation, and support for multiple DB backends via SQLAlchemy/Alembic. The SKILL.md and README repeat that. However, the shipped implementation (scripts/main.py) only parses local .sql files and returns an explicit error for connection strings ("Database connections not implemented in this version"). The metadata lists SQLAlchemy and Alembic as Python requirements but the code does not import or use them. This is an incoherence between claimed capabilities and actual code.

Instruction Scope

Runtime instructions and examples show passing full DB connection strings (including user:pass@host) on the CLI. That is expected for a DB tool, but the code will treat non-.sql sources as unimplemented and print errors. The examples therefore mislead operators into believing live DB comparisons are supported. Also, passing credentials on the command line exposes them to local process-list inspection — the SKILL.md does not warn about that.

Install Mechanism

There is no install spec (instruction-only skill) and no downloads; this minimizes install-time risk. The README suggests installing Python packages (sqlalchemy, alembic, DB drivers) via pip, but nothing is automatically fetched or executed by the skill itself.

Credentials

The skill declares no required environment variables or credentials, which is proportionate for a tool that operates on local SQL files. However, SKILL.md/README encourage supplying DB connection strings including credentials on the CLI; that can expose secrets. The metadata lists third-party Python packages as requirements even though the included code does not use them — raising questions about why those packages are declared.

Persistence & Privilege

The skill does not request persistent privileges, does not set always:true, and has no install actions that modify system or other skills. It operates as a CLI script and will only read files explicitly provided to it.

What to consider before installing

The skill's README and SKILL.md promise live DB comparisons using SQLAlchemy/Alembic, but the bundled script only supports parsing local .sql files and explicitly returns "Database connections not implemented." That mismatch could be an unfinished/abandoned feature or misleading documentation. Before using: 1) Do not run this against production databases thinking it will produce safe migrations. 2) Inspect and test scripts/main.py on non-sensitive local SQL files to verify behavior. 3) Avoid passing connection strings containing passwords on command lines (use secure methods like environment variables or config files) — the examples show inline credentials which are visible to other local users. 4) If you need live DB support, ask the author for confirmation or a version that actually imports and uses SQLAlchemy/Alembic. 5) Consider running the tool in an isolated environment (container) and review generated migration SQL carefully before applying it.

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:

# Compare two database connections
python3 scripts/main.py compare postgresql://user:pass@host1/db postgresql://user:pass@host2/db

# Generate migration script from schema differences
python3 scripts/main.py diff dev_db.sql prod_db.sql --output migration.sql

# Create schema snapshot for future comparison
python3 scripts/main.py snapshot postgresql://user:pass@host/db --save snapshot.json

# Compare current schema with saved snapshot
python3 scripts/main.py compare-snapshot postgresql://user:pass@host/db snapshot.json

# Generate visual diff between schemas
python3 scripts/main.py visual-diff schema1.sql schema2.sql --html diff.html

# Check for schema drift in CI pipeline
python3 scripts/main.py check-drift --expected expected_schema.json --actual actual_schema.json

# Track schema evolution over time
python3 scripts/main.py history postgresql://user:pass@host/db --days 30

Examples

Example 1: Compare development and production databases

python3 scripts/main.py compare \
  postgresql://dev_user:dev_pass@localhost/dev_db \
  postgresql://prod_user:prod_pass@prod-host/prod_db \
  --output diff-report.json

Output:

🔍 Comparing schemas: dev_db (localhost) vs prod_db (prod-host)

📊 Summary:
- Tables: 42 vs 45 (3 missing in dev)
- Columns: 287 vs 295 (8 differences)
- Indexes: 67 vs 72 (5 differences)
- Constraints: 34 vs 38 (4 differences)

⚠️  Differences found (15):
1. Table `audit_logs` missing in dev
   → CREATE TABLE audit_logs (...)
   
2. Column `users.email_verified` missing in dev
   → ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE
   
3. Index `idx_users_email` missing in prod
   → CREATE INDEX idx_users_email ON users(email)
   
4. Constraint `fk_orders_customer_id` differs
   → ALTER TABLE orders DROP CONSTRAINT fk_orders_customer_id_old;
   → ALTER TABLE orders ADD CONSTRAINT fk_orders_customer_id FOREIGN KEY ...

✅ Generated migration: diff-report.json
✅ SQL migration script: migration_20240306_143022.sql

Example 2: Generate migration script

python3 scripts/main.py diff old_schema.sql new_schema.sql --format sql --output migration.sql

Output (migration.sql):

-- Generated: 2024-03-06 14:30:22
-- Database: PostgreSQL

-- UP Migration
CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    action VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT FALSE;

CREATE INDEX idx_users_email ON users(email);

ALTER TABLE orders 
    DROP CONSTRAINT fk_orders_customer_id_old,
    ADD CONSTRAINT fk_orders_customer_id 
    FOREIGN KEY (customer_id) REFERENCES customers(id) 
    ON DELETE CASCADE;

-- DOWN Migration (rollback)
DROP TABLE IF EXISTS audit_logs;

ALTER TABLE users DROP COLUMN IF EXISTS email_verified;

DROP INDEX IF EXISTS idx_users_email;

ALTER TABLE orders 
    DROP CONSTRAINT fk_orders_customer_id,
    ADD CONSTRAINT fk_orders_customer_id_old 
    FOREIGN KEY (customer_id) REFERENCES customers(id);

Example 3: Check for schema drift in CI

python3 scripts/main.py check-drift \
  --expected schemas/expected/prod.json \
  --actual schemas/actual/prod.json \
  --fail-on-drift

Output (CI failure):

❌ Schema drift detected!

Differences:
1. Unexpected table `temp_backup` in production
2. Missing index `idx_orders_status` in production
3. Column `users.last_login` has different type (TIMESTAMP vs TIMESTAMPTZ)

Exit code: 1 (failed due to --fail-on-drift)

Example 4: Track schema evolution

python3 scripts/main.py history postgresql://user:pass@host/db --days 90 --format timeline

Output:

📅 Schema Evolution Timeline (last 90 days)

2024-03-05: Added audit_logs table (v4.2.0 release)
2024-02-28: Added email_verified column to users table
2024-02-15: Created indexes for performance optimization  
2024-02-01: Added foreign key constraints for data integrity
2024-01-20: Initial schema snapshot (v4.0.0)

📈 Change Statistics:
- Tables: +3 (42 → 45)
- Columns: +23 (272 → 295)
- Indexes: +8 (64 → 72)
- Avg changes per week: 2.1

Example 5: Visual schema comparison

python3 scripts/main.py visual-diff schema_v1.sql schema_v2.sql --html schema_diff.html

Output:

✨ Generated visual diff: schema_diff.html

Open in browser to see:
- Side-by-side schema comparison
- Color-coded differences (added/removed/changed)
- Interactive expand/collapse for tables
- Export options for documentation

Differences highlighted:
✅ 5 tables added (green)
❌ 2 tables removed (red)  
🔄 12 columns modified (yellow)

Requirements

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

Install dependencies:

pip3 install sqlalchemy alembic psycopg2-binary pymysql

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.