Recap — Manual vs Autogenerate, Migration Patterns

# Manual vs Autogenerate, Migration Patterns Alembic is the standard tool for SQLAlchemy schema migrations. Generate scripts from model changes; apply them to upgrade/downgrade. ## Setup ```bash pip install alembic alembic init alembic ``` Creates `alembic/` directory with: - `env.py` — runtime config (connect to DB, import models). - `versions/` — migration scripts. - `alembic.ini` — config file. Configure `alembic/env.py` to import your models: ```python from my_app.models import Base target_metadata = Base.metadata ``` Set the DB URL in alembic.ini or env vars: ```ini sqlalchemy.url = postgresql://user:pass@localhost/db ``` ## Creating a migration ### Autogenerate ```bash alembic revision --autogenerate -m "Add users table" ``` Alembic compares models to DB; generates a migration script. ```python # alembic/versions/abc123_add_users_table.py def upgrade(): op.create_table( "users", sa.Column("id", sa.Integer, primary_key=True), sa.Column("email", sa.String(255), unique=True), sa.Column("name", sa.String(100)), ) def downgrade(): op.drop_table("users") ``` REVIEW the generated script. Autogenerate misses: - Renamed tables/columns (sees as drop + add → data loss). - Type changes that need careful handling. - Server-side defaults. - Custom constraints. For complex changes: write the migration manually. ### Manual migration ```bash alembic revision -m "Migrate user emails to lowercase" ``` Empty script: ```python def upgrade(): op.execute("UPDATE users SET email = LOWER(email)") def downgrade(): pass # can't reverse this safely ``` ## Running migrations ```bash alembic upgrade head # apply all unapplied migrations alembic upgrade +1 # one migration forward alembic upgrade abc123 # to specific revision alembic downgrade -1 # one back alembic downgrade base # all the way back ``` Check status: ```bash alembic current # current revision alembic history # all migrations ``` ## Migration patterns ### Pattern 1: Add a column (safe) ```python def upgrade(): op.add_column("users", sa.Column("phone", sa.String(20), nullable=True)) ``` Nullable = safe to add without breaking existing rows. ### Pattern 2: Add a NOT NULL column (careful) ```python def upgrade(): # Step 1: add as nullable op.add_column("users", sa.Column("country", sa.String(2), nullable=True)) # Step 2: backfill op.execute("UPDATE users SET country = 'IN' WHERE country IS NULL") # Step 3: enforce NOT NULL op.alter_column("users", "country", nullable=False) ``` Three-step pattern for adding NOT NULL columns to populated tables. Without it, the ALTER fails on existing rows with NULL. ### Pattern 3: Rename column (zero-downtime) ```python # Migration 1: add new column; backfill from old def upgrade(): op.add_column("users", sa.Column("full_name", sa.String(100))) op.execute("UPDATE users SET full_name = name") # Deploy code that writes to BOTH columns # Migration 2 (later): drop old column def upgrade(): op.drop_column("users", "name") ``` Don't rename in one shot if there's existing data; you'd break running code or lose data. ### Pattern 4: Drop a column (with data migration) ```python def upgrade(): # Move data first if needed op.execute("UPDATE users SET notes = COALESCE(notes, '') || ' ' || old_field") op.drop_column("users", "old_field") ``` Drops are destructive; back up first. ### Pattern 5: Add an index (concurrent) For Postgres, indexes on big tables lock by default: ```python def upgrade(): op.execute("CREATE INDEX CONCURRENTLY idx_users_email ON users(email)") ``` `CONCURRENTLY` doesn't lock (but takes longer). Important for production. ### Pattern 6: Backfill in batches For million-row backfills, doing it in one UPDATE locks the table: ```python def upgrade(): op.execute( "UPDATE users SET country = 'IN' " "WHERE id IN (SELECT id FROM users WHERE country IS NULL LIMIT 10000)" ) # Run this migration multiple times until done, OR write a separate script ``` For really large tables: write a separate Python script that batches updates. ## Common workflow ```bash # 1. Edit models vim my_app/models.py # 2. Generate migration alembic revision --autogenerate -m "Add user phone" # 3. REVIEW the generated script vim alembic/versions/xxx_add_user_phone.py # 4. Apply locally alembic upgrade head # 5. Test pytest # 6. Commit git add my_app/models.py alembic/versions/xxx.py git commit -m "Add user phone column" # 7. Deploy (CI runs `alembic upgrade head` on production DB) ``` ## Production deployment In CI/CD pipeline, run migrations before deploying code: ```yaml - run: alembic upgrade head - run: docker build -t myapp . - run: deploy ``` This ensures the schema is ready before new code starts. Reverse order = code expects new schema that isn't there yet. For very large tables / risky migra