Database Migration Tool

A powerful, flexible database migration tool built with Python that supports both YAML-based declarative migrations and Python-based programmatic migrations. This tool provides comprehensive schema management capabilities with automatic rollback support and metadata preservation.

• Dual Migration Support: YAML-based declarative migrations and Python-based programmatic migrations
• Migration Graph (DAG): Support for branching migrations with dependency management like Alembic
• Automatic Rollback: Intelligent rollback system with metadata preservation for safe reversions
• Schema Auto-Generation: Automatically detect and generate migrations from schema differences
• Enhanced Metadata: Captures column metadata for accurate rollback operations
• Table Rename Support: Handle table renames with mapping configuration
• Dry-Run Capability: Preview migration changes before applying them
• Comprehensive Logging: Track all applied migrations with detailed payload information
• Multiple Database Support: Works with any SQLAlchemy-supported database
• Parallel Development: Support for multiple developers working on migrations simultaneously
• Conflict Detection: Automatic detection and resolution of migration conflicts
• Schema Inspection: Comprehensive database and migration status inspection with visual indicators
• Sync Validation: Automatic detection of database schema sync issues

• Python 3.8+ (Python 3.9+ recommended)
• Core Dependencies: SQLAlchemy, PyYAML, Typer, python-dotenv, tabulate
• Database Driver: Choose based on your database:
  • SQLite: No additional driver needed (included with Python)
  • PostgreSQL: psycopg2-binary or psycopg2
  • MySQL: PyMySQL or mysqlclient
  • SQL Server: pyodbc or pymssql
  • Oracle: cx-Oracle

1. Clone the repository

   git clone <repository-url>
   cd migrateDB

2. Install dependencies

   Option A: Automated installation (recommended)

   # For SQLite (default)
   python install.py
   
   # For PostgreSQL
   python install.py --database postgresql
   
   # For MySQL
   python install.py --database mysql
   
   # For development
   python install.py --dev

   Option B: Manual installation

   # Full installation (recommended)
   pip install -r requirements.txt
   
   # Minimal installation (SQLite only)
   pip install -r requirements-minimal.txt
   
   # Development installation
   pip install -r requirements-dev.txt

   Option C: Quick installation

   pip install sqlalchemy pyyaml typer python-dotenv tabulate

3. Set up database configuration (optional) The tool auto-discovers database URLs from multiple sources:

   Option A: Environment Variables

   export DB_URL="sqlite:///your_database.db"
   # or for PostgreSQL: postgresql://user:password@localhost/dbname
   # or for MySQL: mysql://user:password@localhost/dbname

   Option B: .env file Create a .env file in the project root:

   DB_URL=sqlite:///your_database.db
   DATABASE_URL=postgresql://user:password@localhost/dbname

   Option C: Configuration files The tool also looks for database URLs in:

   • config.py (DATABASE_URL variable)
   • settings.py (DB_URL variable)
   • database.py (database_url variable)

   Option D: Default fallback If no database URL is found, defaults to sqlite:///migrate.db

migrateDB/
├── main.py                 # Entry point
├── commands.py             # CLI commands implementation
├── models.py              # SQLAlchemy metadata definitions
├── migrations/            # Migration files directory
│   ├── *.yml             # YAML migration files
│   └── *.py              # Python migration files
├── src/                  # Core migration logic
│   ├── applier.py        # Migration application logic
│   ├── db.py             # Database connection and metadata
│   ├── executors.py      # SQL operation executors
│   ├── migration_loader.py # Migration file parsing
│   └── planner.py        # Migration planning logic
└── utils/                # Utility functions
    └── utils.py          # Helper functions

1. Initialize the migration system

   python main.py init-db

2. Define your schema in a models file

   # models.py, schema.py, database.py, or any Python file
   from sqlalchemy import Table, Column, Integer, String, MetaData
   
   metadata = MetaData()
   
   users = Table(
       "users", metadata,
       Column("id", Integer, primary_key=True),
       Column("name", String(100), nullable=False),
       Column("email", String(255), unique=True)
   )

3. Auto-generate your first migration

   python main.py autogenerate -m "Initial schema"

4. Apply the migration

   python main.py apply

# Initialize migration metadata table
python main.py init-db [--db DATABASE_URL]

# Plan a migration (dry-run)
python main.py plan [MIGRATION_FILE] [--rename-map RENAME_MAP_FILE]

# Examples:
python main.py plan                                    # Use latest migration
python main.py plan migrations/20250101120000_add_users.yml
python main.py plan --rename-map custom_renames.yml

# Apply migrations
python main.py apply [MIGRATION_FILE] [OPTIONS]

# Options:
#   --db DATABASE_URL        Database connection string
#   --rename-map FILE        Table rename mapping file
#   --dry-run               Show what would be done without executing
#   --latest                Use the latest migration file

# Examples:
python main.py apply                                    # Apply latest migration
python main.py apply --latest                          # Explicitly use latest
python main.py apply --dry-run                         # Preview changes
python main.py apply migrations/20250101120000_add_users.yml

# Rollback the last applied migration
python main.py rollback [--db DATABASE_URL]

# Examples:
python main.py rollback
python main.py rollback --db "postgresql://user:pass@localhost/db"

# Auto-generate migration from schema differences
python main.py autogenerate [--db DATABASE_URL] [-m MESSAGE]

# Examples:
python main.py autogenerate
python main.py autogenerate -m "Add user profile table"
python main.py autogenerate --db "sqlite:///mydb.db"

# Register existing migration with timestamp
python main.py revision MIGRATION_FILE

# Example:
python main.py revision my_migration.yml
# Creates: migrations/20250101120000_my_migration.yml

# Discover and validate models files
python main.py discover-models [--models-file PATH]

# Examples:
python main.py discover-models                    # Auto-discover models file
python main.py discover-models --models-file "my_schema.py"

# Discover and validate database configuration
python main.py discover-db [--db URL]

# Examples:
python main.py discover-db                        # Auto-discover database URL
python main.py discover-db --db "postgresql://user:pass@localhost/db"

Configure once, use everywhere! Set up your database connection once during init-db, and all future commands will automatically use the same configuration.

# PostgreSQL (most common)
python main.py init-db --host localhost --port 5432 --user myuser --password mypass --database mydb --type postgresql

# MySQL
python main.py init-db --host localhost --port 3306 --user myuser --password mypass --database mydb --type mysql

# SQLite (no credentials needed)
python main.py init-db --database myapp.db --type sqlite

# Using complete URL
python main.py init-db --db "postgresql://user:pass@localhost/db"

# All these commands automatically use the saved configuration!
python main.py apply
python main.py rollback
python main.py autogenerate -m "Add new table"
python main.py plan

# Show current configuration
python main.py show-config

# Reset configuration (go back to auto-discovery)
python main.py reset-config

Benefits:

• ✅ Configure Once: Set database connection once during init-db
• ✅ Use Everywhere: All commands automatically use saved configuration
• ✅ Override When Needed: Can still override with command-line arguments
• ✅ Secure: Credentials stored in local config file (not in code)
• ✅ Flexible: Easy to change configuration anytime

Advanced dependency management for complex migration scenarios! The tool now supports Directed Acyclic Graphs (DAG) for managing migration dependencies, enabling parallel development and complex branching scenarios.

# Migration with dependencies
version: '20250113120000'
description: Add user authentication system
branch: feature-auth
dependencies: ['20250113100000']  # Depends on previous migration
revision_id: 'abc12345'
changes:
  - add_table:
      name: users
      columns:
        - name: id
          type: INTEGER
          primary_key: true
        - name: username
          type: VARCHAR(50)
          unique: true

# Create a new migration branch
python main.py create-branch feature-auth

# Create a branch from specific migration
python main.py create-branch feature-payments --base 20250113100000

# Developer A works on auth branch
python main.py autogenerate -m "Add user table" --branch feature-auth

# Developer B works on payments branch  
python main.py autogenerate -m "Add payment table" --branch feature-payments

# Both can work simultaneously without conflicts

# Merge two branches when ready
python main.py merge-branches feature-auth feature-payments -m "Merge auth and payments"

# 1. Initial setup
python main.py init-db --host localhost --user myuser --password mypass --database mydb --type postgresql

# 2. Developer A creates auth branch
python main.py create-branch feature-auth
python main.py autogenerate -m "Add users table" --branch feature-auth
python main.py apply migrations/20250113120000_add_users_table.yml

# 3. Developer B creates payments branch (from same base)
python main.py create-branch feature-payments
python main.py autogenerate -m "Add payments table" --branch feature-payments
python main.py apply migrations/20250113130000_add_payments_table.yml

# 4. Check migration graph
python main.py graph
# Output:
# Migration Graph:
# ==================================================
# 
# Branch: main
# --------------------
#   20250113100000: Initial schema
#     Dependencies: none
#     Revision ID: def45678
# 
# Branch: feature-auth
# --------------------
#   20250113120000: Add users table
#     Dependencies: 20250113100000
#     Revision ID: abc12345
# 
# Branch: feature-payments
# --------------------
#   20250113130000: Add payments table
#     Dependencies: 20250113100000
#     Revision ID: ghi78901

# 5. Merge branches when ready
python main.py merge-branches feature-auth feature-payments -m "Merge auth and payments"
python main.py apply migrations/20250113140000_merge_feature_auth_feature_payments.yml

• ✅ Parallel Development: Multiple developers can work on migrations simultaneously
• ✅ Dependency Tracking: Clear dependency relationships between migrations
• ✅ Conflict Detection: Automatic detection of migration conflicts
• ✅ Branch Management: Easy creation and merging of migration branches
• ✅ Graph Visualization: Visual representation of migration dependencies
• ✅ Cycle Detection: Prevents circular dependencies
• ✅ Backward Compatible: Existing linear migrations continue to work

Comprehensive database and migration status inspection! The tool provides powerful commands to inspect your database schema and migration status with clear visual indicators.

# Full status report with schema validation
python main.py status

# Verbose mode with detailed information
python main.py status --verbose

# Check against specific models file
python main.py status --models-file custom_models.py

# Skip sync checking for faster execution
python main.py status --no-check-sync

Output Example:

🔍 Migration & Schema Status Report
============================================================

📊 MIGRATION STATUS
------------------------------
✅ Applied migrations: 6
⏳ Pending migrations: 0

🔄 DATABASE SYNC STATUS
------------------------------
Status: ⚠️ Out of Sync
  • Extra tables: migration_log

🔗 DEPENDENCY HEALTH
------------------------------
✅ DAG is valid - no cycles detected
✅ No dependency conflicts

🎯 CURRENT STATE
------------------------------
Current heads: 20250913122220, 20250913122712

📋 SUMMARY
------------------------------
✅ All migrations applied - database is up to date
⚠️ Database schema may be out of sync - consider running 'python main.py autogenerate'

# Quick overview for CI/CD pipelines
python main.py status-quick

Output Example:

✅ All migrations applied
📊 Database has 5 tables
🎯 Current heads: 20250913122220, 20250913122712

• Table Comparison: Compares current database tables with target models
• Missing Tables: Identifies tables that should exist but don't
• Extra Tables: Identifies tables that exist but aren't in models
• Dependency Health: Validates migration dependency graph
• Conflict Detection: Identifies migration conflicts and circular dependencies
• Branch Summary: Shows migration status by branch

# Check status before starting work
python main.py status

# Quick check during development
python main.py status-quick

# Detailed inspection when debugging
python main.py status --verbose

# Quick status check in CI
python main.py status-quick

# Full validation in staging
python main.py status --check-sync

# Monitor migration status
python main.py status --no-check-sync

# Validate against production models
python main.py status --models-file production_models.py

YAML migrations use a declarative format for schema changes:

version: '20250101120000'
description: Add users table with indexes
changes:
- create_table:
    table: users
    columns:
    - name: id
      type: INTEGER
      nullable: false
      primary_key: true
    - name: name
      type: VARCHAR(100)
      nullable: false
    - name: email
      type: VARCHAR(255)
      nullable: true
      unique: true

- add_index:
    table: users
    name: idx_users_email
    columns: [email]

- add_column:
    table: users
    column: created_at
    type: DATETIME
    nullable: true

- drop_column:
    table: users
    column: old_field

Python migrations provide full programmatic control:

def upgrade(engine):
    """Apply the migration."""
    with engine.connect() as conn:
        conn.execute(text("""
            CREATE TABLE users (
                id INTEGER PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                email VARCHAR(255) UNIQUE
            )
        """))

def downgrade(engine):
    """Rollback the migration."""
    with engine.connect() as conn:
        conn.execute(text("DROP TABLE users"))

• create_table / drop_table
• rename_table

• add_column / drop_column
• alter_column (type changes, nullable modifications)

• add_index / drop_index

• Metadata Preservation: All operations store metadata for accurate rollback
• Table Rename Mapping: Handle table renames with rename_map.yml
• Enhanced Rollback: Automatic reversal of all supported operations
• Schema Validation: Compare current schema with target schema

For organizations that prefer not to store database credentials in files, you can set the database URL programmatically:

# In your application code
from migrateDB import set_database_url

# Set database URL directly in code
set_database_url("postgresql://user:password@localhost:5432/mydatabase")

# Now all migration commands will use this URL automatically
# python main.py status
# python main.py apply migrations/20250101_add_users.yml

Benefits:

• ✅ No credential files: Database URL not stored on disk
• ✅ Environment variables: Can use os.getenv("DATABASE_URL")
• ✅ Application integration: Works with existing app configuration
• ✅ CI/CD friendly: Perfect for automated deployments
• ✅ Multi-tenant ready: Dynamic database URLs per tenant

Example with environment variables:

import os
from migrateDB import set_database_url

# From environment variable
db_url = os.getenv("DATABASE_URL")
if db_url:
    set_database_url(db_url)
else:
    raise ValueError("DATABASE_URL environment variable not set")

The tool uses the following priority order for database configuration:

1. Command-line arguments (highest priority)

   python main.py status --db "postgresql://user:pass@host:port/db"

2. Programmatic configuration (security-conscious)

   from migrateDB import set_database_url
   set_database_url("postgresql://user:pass@host:port/db")

3. Saved configuration (traditional)

   python main.py init-db --host localhost --user myuser --password mypass

4. Environment variables (fallback)

   export DB_URL="postgresql://user:pass@host:port/db"
   python main.py status

5. Auto-discovery (lowest priority)

   • Automatically detects database from common patterns

Create a .env file in your project root:

# Database connection
DB_URL=sqlite:///your_database.db

# For PostgreSQL
# DB_URL=postgresql://username:password@localhost:5432/database_name

# For MySQL
# DB_URL=mysql://username:password@localhost:3306/database_name

Create rename_map.yml to handle table renames:

table_renames:
  old_table_name: new_table_name
  legacy_users: users

• Transaction Support: All migrations run in database transactions
• Rollback Capability: Every migration can be safely rolled back
• Metadata Preservation: Column types, constraints, and indexes are preserved
• Dry-Run Mode: Preview changes before applying
• Migration Logging: Complete audit trail of all applied migrations

1. Migration not found

   # Ensure migration file exists and is properly formatted
   python main.py plan migrations/your_migration.yml

2. Database connection failed

   # Check your DB_URL in .env file
   python main.py init-db --db "sqlite:///test.db"

3. Rollback failed

   # Check migration log for the last applied migration
   # Ensure database is in a consistent state

Enable verbose logging by modifying the commands to include debug output:

# Add logging configuration in your migration files
import logging
logging.basicConfig(level=logging.DEBUG)

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add some amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

This project is licensed under the MIT License - see the LICENSE file for details.

• Built with SQLAlchemy for database abstraction
• CLI powered by Typer
• YAML support via PyYAML

Need help? Check the command help with python main.py --help or python main.py [command] --help