EvolvisHub DB Migration

A robust database migration tool developed by Evolvis AI for managing database schema changes efficiently and reliably.

About Evolvis AI

Evolvis AI is a leading provider of AI-powered solutions, helping businesses transform their operations through innovative technology. Our mission is to make artificial intelligence accessible to companies of all sizes, enabling them to compete effectively in today's data-driven environment.

Author

Alban Maxhuni, PhD
Email: a.maxhuni@evolvis.ai
Senior Data Scientist at Evolvis AI

Features

• Support for multiple database types (SQLite, PostgreSQL, MySQL)
• Version-controlled migrations
• Rollback capability
• Migration status tracking
• Command-line interface
• Configuration management
• Logging and error handling

Installation

pip install evolvishub-db-migration

Usage

Initialize a new migration project

db-migrate init config.yaml

Create a new migration

db-migrate create config.yaml my_migration "CREATE TABLE my_table (id INTEGER PRIMARY KEY)"

Apply migrations

db-migrate migrate config.yaml

Check migration status

db-migrate status config.yaml

Example output:

Total migrations: 1
Applied migrations: 1
Pending migrations: 0
✓ 001_my_migration (version: 001)
All migrations applied

Python Examples

Basic Usage

from evolvishub_db_migration import MigrationManager
from evolvishub_db_migration.config import ConfigManager

# Initialize configuration
config = ConfigManager("config.yaml")

# Create migration manager
migration_manager = MigrationManager(config)

# Create a new migration
migration_manager.create_migration(
    name="create_users_table",
    sql_up="CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100))",
    sql_down="DROP TABLE users"
)

# Apply all pending migrations
migration_manager.migrate()

# Check migration status
status = migration_manager.get_status()
print(f"Applied migrations: {status.applied_count}")
print(f"Pending migrations: {status.pending_count}")

Working with Different Databases

PostgreSQL Example

from evolvishub_db_migration import MigrationManager
from evolvishub_db_migration.config import ConfigManager

# PostgreSQL configuration
config = ConfigManager({
    "database": {
        "type": "postgresql",
        "connection_string": "postgresql://user:password@localhost:5432/mydb"
    },
    "migrations": {
        "directory": "migrations"
    }
})

migration_manager = MigrationManager(config)

# Create a complex migration
migration_manager.create_migration(
    name="add_user_roles",
    sql_up="""
    CREATE TYPE user_role AS ENUM ('admin', 'user', 'guest');
    ALTER TABLE users ADD COLUMN role user_role DEFAULT 'user';
    CREATE INDEX idx_users_role ON users(role);
    """,
    sql_down="""
    DROP INDEX idx_users_role;
    ALTER TABLE users DROP COLUMN role;
    DROP TYPE user_role;
    """
)

MySQL Example

from evolvishub_db_migration import MigrationManager
from evolvishub_db_migration.config import ConfigManager

# MySQL configuration
config = ConfigManager({
    "database": {
        "type": "mysql",
        "connection_string": "mysql://user:password@localhost:3306/mydb"
    },
    "migrations": {
        "directory": "migrations"
    }
})

migration_manager = MigrationManager(config)

# Create a migration with foreign keys
migration_manager.create_migration(
    name="create_orders_table",
    sql_up="""
    CREATE TABLE orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id)
    );
    """,
    sql_down="DROP TABLE orders;"
)

SQLite Example

from evolvishub_db_migration import MigrationManager
from evolvishub_db_migration.config import ConfigManager

# SQLite configuration
config = ConfigManager({
    "database": {
        "type": "sqlite",
        "connection_string": "sqlite:///database.db"
    },
    "migrations": {
        "directory": "migrations"
    }
})

migration_manager = MigrationManager(config)

# Create a simple migration
migration_manager.create_migration(
    name="create_products_table",
    sql_up="""
    CREATE TABLE products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        price REAL NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    """,
    sql_down="DROP TABLE products;"
)

Advanced Usage

Custom Migration Script

from evolvishub_db_migration import MigrationManager
from evolvishub_db_migration.config import ConfigManager
from evolvishub_db_migration.models import Migration

# Initialize with custom configuration
config = ConfigManager({
    "database": {
        "type": "postgresql",
        "connection_string": "postgresql://user:password@localhost:5432/mydb"
    },
    "migrations": {
        "directory": "migrations",
        "table_name": "custom_migrations_table"
    }
})

migration_manager = MigrationManager(config)

# Create a migration with custom metadata
migration = Migration(
    name="complex_schema_update",
    version="2024_03_15_001",
    sql_up="""
    -- Complex schema changes
    BEGIN;
    
    -- Add new columns
    ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
    ALTER TABLE users ADD COLUMN login_count INTEGER DEFAULT 0;
    
    -- Create new table
    CREATE TABLE user_sessions (
        id SERIAL PRIMARY KEY,
        user_id INTEGER REFERENCES users(id),
        session_start TIMESTAMP,
        session_end TIMESTAMP
    );
    
    -- Create indexes
    CREATE INDEX idx_user_sessions_user_id ON user_sessions(user_id);
    CREATE INDEX idx_users_last_login ON users(last_login);
    
    COMMIT;
    """,
    sql_down="""
    BEGIN;
    
    DROP INDEX idx_users_last_login;
    DROP INDEX idx_user_sessions_user_id;
    DROP TABLE user_sessions;
    ALTER TABLE users DROP COLUMN login_count;
    ALTER TABLE users DROP COLUMN last_login;
    
    COMMIT;
    """
)

# Apply the migration
migration_manager.apply_migration(migration)

# Rollback the last migration
migration_manager.rollback()

Error Handling

from evolvishub_db_migration import MigrationManager
from evolvishub_db_migration.config import ConfigManager
from evolvishub_db_migration.exceptions import MigrationError

try:
    config = ConfigManager("config.yaml")
    migration_manager = MigrationManager(config)
    
    # Attempt to apply migrations
    migration_manager.migrate()
    
except MigrationError as e:
    print(f"Migration failed: {str(e)}")
    # Handle the error appropriately
    
except Exception as e:
    print(f"Unexpected error: {str(e)}")
    # Handle unexpected errors

Configuration

The configuration file (config.yaml) should have the following structure:

database:
  type: sqlite
  connection_string: sqlite:///database.db

migrations:
  directory: migrations

Development

Setup Development Environment

1. Clone the repository:

git clone https://github.com/evolvis-ai/evolvishub-db-migration.git
cd evolvishub-db-migration

2. Create and activate a virtual environment:

python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

3. Install development dependencies:

pip install -r requirements-dev.txt

Running Tests

pytest

Contributing

We welcome contributions! Please see our Contributing Guide for details.

License

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

Support

For support, please contact a.maxhuni@evolvis.ai or visit Evolvis AI.

Acknowledgments

• Thanks to all contributors who have helped shape this project
• Special thanks to the Evolvis AI team for their support and guidance