evolvishub-db-migration 0.1.4
A professional database migration tool supporting multiple databases
License: MIT
Requires Python: >=3.8

# EvolvisHub DB Migration

<div align="center">
  <img src="assets/png/eviesales.png" alt="Evolvis AI Logo" width="200"/>
</div>

A robust database migration tool developed by [Evolvis AI](https://evolvis.ai) for managing database schema changes efficiently and reliably.

## About Evolvis AI

[Evolvis AI](https://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](mailto: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

```bash
pip install evolvishub-db-migration
```

## Usage

### Initialize a new migration project

```bash
db-migrate init config.yaml
```

### Create a new migration

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

### Apply migrations

```bash
db-migrate migrate config.yaml
```

### Check migration status

```bash
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

```python
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
```python
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
```python
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
```python
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
```python
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
```python
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:

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

migrations:
  directory: migrations
```

## Development

### Setup Development Environment

1. Clone the repository:
```bash
git clone https://github.com/evolvis-ai/evolvishub-db-migration.git
cd evolvishub-db-migration
```

2. Create and activate a virtual environment:
```bash
python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate
```

3. Install development dependencies:
```bash
pip install -r requirements-dev.txt
```

### Running Tests

```bash
pytest
```

## Contributing

We welcome contributions! Please see our [Contributing Guide](CONTRIBUTING.md) for details.

## License

This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.

## Support

For support, please contact [a.maxhuni@evolvis.ai](mailto:a.maxhuni@evolvis.ai) or visit [Evolvis AI](https://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