Alembic Migrations — Evolving the Schema Safely

pip install alembic
alembic init alembic                     # creates alembic/ + alembic.ini

# ── alembic/env.py — the two lines that matter ──
from app.core.db import Base
from app.models import student, offer    # import ALL model modules (registers tables)
target_metadata = Base.metadata
# and set the URL from settings, not alembic.ini:
# config.set_main_option("sqlalchemy.url", get_settings().database_url)

# ── the daily loop ──
# 1. Edit the model: add  backlogs: Mapped[int] = mapped_column(default=0)
# 2. Generate:
alembic revision --autogenerate -m "add backlogs to students"
# 3. REVIEW alembic/versions/9f2c_add_backlogs_to_students.py:
def upgrade():
    op.add_column("students",
        sa.Column("backlogs", sa.Integer(), nullable=False,
                  server_default="0"))   # ← you often ADD this by hand:
                                         #    NOT NULL on a full table needs a default
def downgrade():
    op.drop_column("students", "backlogs")
# 4. Apply:
alembic upgrade head

# Everyday commands:
alembic current            # what version is this DB on?
alembic history            # the chain of migrations
alembic downgrade -1       # step back one (dev only, usually)

# 1. RENAME: you renamed cgpa → gpa. Autogenerate emits:
#      op.drop_column("students", "cgpa")     ← DATA GONE
#      op.add_column("students", sa.Column("gpa", sa.Float()))
#    Fix by hand:
def upgrade():
    op.alter_column("students", "cgpa", new_column_name="gpa")

# 2. DATA MIGRATION: new column derived from old data —
def upgrade():
    op.add_column("students", sa.Column("email_domain", sa.String(80)))
    op.execute(
        "UPDATE students SET email_domain = split_part(email, '@', 2)"
    )
    op.alter_column("students", "email_domain", nullable=False)

# 3. Production rules:
#   - migrations run BEFORE new code serves traffic:
#       fly.toml → [deploy] release_command = "alembic upgrade head"
#   - never edit a migration that has run on any shared DB — add a new one
#   - big/risky changes = expand → migrate → contract across TWO deploys:
#       deploy 1: add nullable column, write to both old+new
#       backfill: batched UPDATEs (not one giant locking UPDATE)
#       deploy 2: drop the old column once nothing reads it
#   - index on a busy Postgres table: CREATE INDEX CONCURRENTLY
#     (needs op.execute + autocommit block — plain create_index locks writes)