TopolearnDatabases outlive deployments, so schema changes must be scripted, reviewed and applied in order on every environment.
Alembic is SQLAlchemy's migration tool. alembic init migrations creates an alembic.ini and a migrations/ folder with env.py and a versions/ directory. In env.py, set target_metadata = Base.metadata so Alembic knows your models, and point the URL at your database.
Each migration is a Python file with upgrade() and downgrade() functions using op.add_column, op.create_table, op.create_index and friends. Files are chained by revision and down_revision ids, forming an ordered history; the database stores its current revision in an alembic_version table.
The everyday loop:
- Change your models.
alembic revision --autogenerate -m "add user bio"compares models with the live database and writes a migration.- Read and edit the generated file. Autogenerate can't detect renames (it sees a drop plus an add, losing data), and misses some changes like certain server defaults or constraint changes.
alembic upgrade headapplies pending migrations;alembic downgrade -1steps back.
Data migrations (backfilling a new column) go in the same files using op.execute(...) or Core statements on op.get_bind(). For large tables, add nullable columns first, backfill, then add the NOT NULL constraint in a later migration to avoid long locks. Run migrations as a deployment step, not at app startup in every replica.
Example
# migrations/versions/3f2a_add_user_bio.py (generated, then reviewed)
from alembic import op
import sqlalchemy as sa
revision = "3f2a"
down_revision = "9c1e"
def upgrade() -> None:
op.add_column("users", sa.Column("bio", sa.Text(), nullable=True))
op.create_index("ix_users_email", "users", ["email"], unique=True)
def downgrade() -> None:
op.drop_index("ix_users_email", table_name="users")
op.drop_column("users", "bio")
# $ alembic revision --autogenerate -m "add user bio"
# $ alembic upgrade head
# $ alembic current -> 3f2a (head)Remember
- Set target_metadata = Base.metadata in env.py for autogenerate
- Always review autogenerated migrations, especially renames
- upgrade head to apply, downgrade -1 to step back
- Split risky changes: add nullable, backfill, then constrain
Watch out for
- Trusting autogenerate for a column rename, which drops the column and its data.