Pular para conteúdo

Database Migrations

Processo de migrations com Alembic.

Workflow

flowchart TD
    Start[Mudança no Model] --> Create[Criar Migration]
    Create --> Review[Code Review da Migration]
    Review --> Backup[Backup Staging DB]
    Backup --> ApplyStaging[Aplicar em Staging]
    ApplyStaging --> Test[Testar em Staging]
    Test --> QA{QA Passou?}
    QA -->|Não| Fix[Corrigir Migration]
    Fix --> Review
    QA -->|Sim| PRMain[PR para Main]
    PRMain --> BackupProd[Backup Production DB]
    BackupProd --> ApplyProd[Aplicar em Production]
    ApplyProd --> Verify[Verificar Production]
    Verify --> Done[✅ Concluído]

Criar Migration

Autogenerate (Recomendado)

# Atualizar model
# Edit app/models/user.py

# Gerar migration
alembic revision --autogenerate -m "add user email column"

# Arquivo gerado em alembic/versions/abc123_add_user_email.py

Manual

alembic revision -m "create custom index"

# Editar arquivo gerado manualmente

Revisar Migration

SEMPRE revisar migration gerada:

def upgrade():
    # ✅ Safe: Adicionar coluna nullable
    op.add_column('users', sa.Column('email', sa.String(255), nullable=True))

    # ✅ Safe: Popular com defaults
    op.execute("UPDATE users SET email = username || '@temp.com' WHERE email IS NULL")

    # ✅ Safe: Tornar NOT NULL depois de popular
    op.alter_column('users', 'email', nullable=False)

    # ✅ Safe: Índice com CONCURRENTLY
    op.create_index('idx_users_email', 'users', ['email'], postgresql_concurrently=True)

def downgrade():
    # ✅ Implementar downgrade sempre!
    op.drop_index('idx_users_email')
    op.drop_column('users', 'email')

Aplicar Migration

Staging

# Backup primeiro (automático no CI/CD)
# Aplicar
alembic upgrade head

# Verificar
alembic current
python scripts/verify_schema.py

Production

# Backup (CRÍTICO!)
aws rds create-db-snapshot \
  --db-instance-identifier prod-db \
  --db-snapshot-identifier "pre-migration-$(date +%Y%m%d-%H%M%S)"

# Aplicar com config de production
alembic -c alembic.prod.ini upgrade head

# Verificar
alembic -c alembic.prod.ini current

Regras de Ouro

✅ SEMPRE

  • Testar em staging primeiro
  • Fazer backup antes de aplicar em production
  • Revisar migration gerada pelo autogenerate
  • Testar downgrade
  • Migrations são backward compatible
  • Adicionar índices com CONCURRENTLY

❌ NUNCA

  • Editar migration já aplicada em production
  • Deletar dados sem confirmação explícita
  • Migration sem downgrade
  • Breaking changes sem plano de migração
  • Aplicar direto em production sem staging

Safe Migrations

Adicionar Coluna

def upgrade():
    # Step 1: Add nullable
    op.add_column('users', sa.Column('phone', sa.String(20), nullable=True))

    # Step 2: Populate (if needed)
    # op.execute("UPDATE ...")

    # Step 3: Make NOT NULL (in future migration if needed)

Renomear Coluna

# Migration 1: Add new column
def upgrade():
    op.add_column('users', sa.Column('full_name', sa.String(255)))
    op.execute("UPDATE users SET full_name = name")

# Migration 2 (deploy app that uses full_name)

# Migration 3: Remove old column
def upgrade():
    op.drop_column('users', 'name')

Adicionar Índice

def upgrade():
    # CONCURRENTLY não bloqueia table
    op.create_index(
        'idx_users_email',
        'users',
        ['email'],
        unique=True,
        postgresql_concurrently=True
    )

# Note: CONCURRENTLY requer conexão autocommit

Migrations Complexas

Data Migration

def upgrade():
    # Schema change
    op.add_column('orders', sa.Column('status_v2', sa.String(50)))

    # Data migration
    connection = op.get_bind()

    # Batch update (para evitar timeout)
    connection.execute("""
        UPDATE orders
        SET status_v2 = CASE
            WHEN status = 0 THEN 'pending'
            WHEN status = 1 THEN 'completed'
            WHEN status = 2 THEN 'cancelled'
        END
        WHERE status_v2 IS NULL
    """)

    # Fazer NOT NULL
    op.alter_column('orders', 'status_v2', nullable=False)

    # Em migration futura: drop status antiga

Verificação

# Ver versão atual
alembic current

# Ver histórico
alembic history

# Ver pending migrations
alembic history -i

# Verificar schema
python scripts/verify_schema.py

CI/CD Integration

# deploy-staging.yml
- name: Run Migrations
  env:
    DATABASE_URL: ${{ secrets.STAGING_DATABASE_URL }}
  run: |
    pip install alembic psycopg2-binary
    alembic upgrade head

- name: Verify Schema
  run: |
    alembic current
    python scripts/verify_schema.py

Referências