alembic

# Alembic

## Overview

Alembic is the migration tool for SQLAlchemy. It tracks database schema changes as versioned Python scripts — like Git for your database. Supports autogeneration from model changes, branching, and data migrations.

## Instructions

### Step 1: Setup

```bash
pip install alembic
alembic init alembic
```

```python
# alembic/env.py — Configure with async SQLAlchemy
from alembic import context
from sqlalchemy.ext.asyncio import create_async_engine
from models import Base
import asyncio

config = context.config
target_metadata = Base.metadata

def run_migrations_online():
    connectable = create_async_engine(config.get_main_option("sqlalchemy.url"))

    async def do_run():
        async with connectable.connect() as connection:
            await connection.run_sync(do_migrations)

    def do_migrations(connection):
        context.configure(connection=connection, target_metadata=target_metadata)
        with context.begin_transaction():
            context.run_migrations()

    asyncio.run(do_run())

run_migrations_online()
```

### Step 2: Create Migrations

```bash
# Auto-generate from model changes
alembic revision --autogenerate -m "add projects table"

# Create empty migration (for data migrations)
alembic revision -m "backfill user roles"
```

```python
# alembic/versions/001_add_projects.py — Generated migration
def upgrade():
    op.create_table('projects',
        sa.Column('id', sa.String(36), primary_key=True),
        sa.Column('name', sa.String(100), nullable=False),
        sa.Column('owner_id', sa.String(36), sa.ForeignKey('users.id')),
        sa.Column('created_at', sa.DateTime, server_default=sa.func.now()),
    )
    op.create_index('ix_projects_owner_id', 'projects', ['owner_id'])

def downgrade():
    op.drop_index('ix_projects_owner_id')
    op.drop_table('projects')
```

### Step 3: Data Migrations

```python
# alembic/versions/002_backfill_roles.py — Data migration
from alembic import op
import sqlalchemy as sa

def upgrade():
    # Add column
    op.add_column('users', sa.Column('role', sa.String(20), server_default='member'))

    # Backfill existing rows
    conn = op.get_bind()
    conn.execute(sa.text("UPDATE users SET role = 'admin' WHERE email LIKE '%@mycompany.com'"))

def downgrade():
    op.drop_column('users', 'role')
```

### Step 4: Commands

```bash
alembic upgrade head          # apply all pending migrations
alembic downgrade -1          # rollback one migration
alembic history               # show migration history
alembic current               # show current revision
alembic upgrade +1            # apply next migration only
```

## Guidelines

- Always review autogenerated migrations — they may miss renames (detected as drop+create).
- Run migrations in CI before deploying — catch schema issues early.
- Data migrations should be idempotent — safe to run multiple times.
- Use `op.batch_alter_table()` for SQLite (which doesn't support ALTER TABLE well).
- Never edit applied migrations — create new ones instead.