Creating Migrations
26 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)Your models change, and the live database must change with them. A migration is a small, versioned file that describes one schema change. Alembic is the migration tool that goes with SQLAlchemy.
A migration is a versioned file
Migration is a versioned file that describes one change to the database schema, with code to apply it (upgrade) and code to undo it (downgrade).
Running create_all once is not enough, because it cannot change a table that exists. Alembic keeps each change in its own file with an id. The file says how to apply the change and how to undo it.
revision = "0002"down_revision = "0001"
from alembic import opimport sqlalchemy as sa
def upgrade(): op.add_column("product", sa.Column("stock", sa.Integer(), nullable=True))
def downgrade(): op.drop_column("product", "stock")down_revision points to the migration that comes before. Together the files form one chain, and the database remembers which link it has reached.
The op object holds the changes
Operations is the Alembic class behind op. Its methods, such as create_table and add_column, change the schema of the connected database.
Inside a migration you call methods on op. Each method is one schema change: create a table, add a column, create an index. Alembic turns the call into the right SQL for your database.
op.create_table( "product", sa.Column("id", sa.Integer, primary_key=True), sa.Column("name", sa.String(50), nullable=False),)op.add_column("product", sa.Column("price", sa.Integer))op.create_index("ix_product_name", "product", ["name"])Example 1 · Create a table and add a column
import sqlalchemy as safrom alembic.migration import MigrationContextfrom alembic.operations import Operations
engine = sa.create_engine("sqlite://")conn = engine.connect()op = Operations(MigrationContext.configure(conn))
def cols(table): return [c["name"] for c in sa.inspect(conn).get_columns(table)]
op.create_table( "product", sa.Column("id", sa.Integer, primary_key=True), sa.Column("name", sa.String(50), nullable=False),)print(cols("product"))op.add_column("product", sa.Column("price", sa.Integer))op.create_index("ix_product_name", "product", ["name"])print(cols("product"))print([i["name"] for i in sa.inspect(conn).get_indexes("product")])['id', 'name'] ['id', 'name', 'price'] ['ix_product_name']
What this code does
Operations(MigrationContext.configure(conn))gives the sameopthat a migration file uses.- Each call ran at once on the in-memory database, so we can inspect the result.
Example 2 · Batch mode for SQLite
import sqlalchemy as safrom alembic.migration import MigrationContextfrom alembic.operations import Operations
engine = sa.create_engine("sqlite://")conn = engine.connect()op = Operations(MigrationContext.configure(conn))
def cols(table): return [c["name"] for c in sa.inspect(conn).get_columns(table)]
op.create_table("product", sa.Column("id", sa.Integer, primary_key=True), sa.Column("name", sa.String(50)), sa.Column("old_note", sa.String(20)))with op.batch_alter_table("product") as batch: batch.drop_column("old_note") batch.alter_column("name", existing_type=sa.String(50), nullable=False)print([(c["name"], c["nullable"]) for c in sa.inspect(conn).get_columns("product")])[('id', False), ('name', False)]What this code does
- SQLite cannot drop a column or change nullability in place. Batch mode rebuilds the table for us.
- The
namecolumn is now NOT NULL andold_noteis gone.
You can try these calls without a project: Operations(MigrationContext.configure(connection)) gives an op that works on any open connection.
Autogenerate drafts the file
Autogenerate is the Alembic feature that compares your SQLAlchemy models with the database and drafts a migration file containing the differences.
Writing every change by hand is slow. Alembic can compare your models with the real database and write the difference as migration code. It is a draft: you must read it.
# in a terminal# alembic revision --autogenerate -m "add stock to product"
from alembic.autogenerate import produce_migrationsscript = produce_migrations(context, Base.metadata)Example 3 · What autogenerate would write
import sqlalchemy as safrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_columnfrom alembic.migration import MigrationContextfrom alembic.operations import Operationsfrom alembic.autogenerate import produce_migrations, render_python_code
class Base(DeclarativeBase): pass
class Product(Base): __tablename__ = "product" id: Mapped[int] = mapped_column(primary_key=True) name: Mapped[str] = mapped_column(sa.String(50)) stock: Mapped[int] = mapped_column(default=0)
engine = sa.create_engine("sqlite://")conn = engine.connect()op = Operations(MigrationContext.configure(conn))
def preview(): script = produce_migrations(MigrationContext.configure(conn), Base.metadata) code = render_python_code(script.upgrade_ops) return "\n".join(line.strip() for line in code.splitlines() if not line.strip().startswith("#"))
print(preview())op.create_table("product", sa.Column("id", sa.Integer, primary_key=True), sa.Column("name", sa.String(50), nullable=False))print("---")print(preview())op.create_table('product',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=50), nullable=False),
sa.Column('stock', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('id')
)
---
op.add_column('product', sa.Column('stock', sa.Integer(), nullable=False))What this code does
- With an empty database, autogenerate proposes
create_tablefor the whole model. - After we created the table without
stock, it proposes onlyadd_columnfor the difference.
Example 4 · The command line steps
Runs in: Your terminal
pip install alembicalembic init migrations# set sqlalchemy.url in alembic.ini and target_metadata in migrations/env.pyalembic revision --autogenerate -m "create product"alembic upgrade headCreating directory migrations ... done Generating migrations/versions/3f2a1b9c4d5e_create_product.py ... done INFO [alembic.runtime.migration] Running upgrade -> 3f2a1b9c4d5e, create product
What this code does
alembic initmakes the folder withenv.pyand an emptyversionsfolder.- In
env.py, settarget_metadata = Base.metadata, so autogenerate can see your models.
Autogenerate sees new tables, new columns and type changes. It can miss renames (it sees a drop and an add), so always review the generated file before running it.
Next step
Check your understanding
11 quick questions