Upgrade and Downgrade
26 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)A database is always at one point in the chain of migrations. upgrade moves it forward and downgrade moves it back. Knowing exactly where you are, and where each command takes you, is what makes migrations safe.
Revisions form a chain
Head is the newest revision in the migration chain. Base is the state before any migration has run, when the database has no Alembic version recorded.
Each migration names its parent, so the files form a line from the first to the last. The last one is the head. The empty state before any migration is the base. A table records where the database is now.
from alembic.script import ScriptDirectory
scripts = ScriptDirectory.from_config(cfg)print(scripts.get_heads())for script in scripts.walk_revisions(): print(script.revision, "<-", script.down_revision)Example 1 · Read the chain
import os, tempfileimport sqlalchemy as safrom alembic import commandfrom alembic.config import Configfrom alembic.migration import MigrationContextfrom alembic.script import ScriptDirectory
ENV = ( "from alembic import context\n" "context.configure(connection=context.config.attributes['connection'])\n" "with context.begin_transaction():\n" " context.run_migrations()\n")
def project(revisions): root = tempfile.mkdtemp() os.makedirs(os.path.join(root, "versions")) open(os.path.join(root, "env.py"), "w").write(ENV) for rev, (down, up, dn) in revisions.items(): lines = [f'revision = "{rev}"', f"down_revision = {down!r}", "from alembic import op", "import sqlalchemy as sa", "", "def upgrade():"] lines += [" " + s for s in up] + ["", "def downgrade():"] + [" " + s for s in dn] open(os.path.join(root, "versions", rev + ".py"), "w").write("\n".join(lines) + "\n") cfg = Config() cfg.set_main_option("script_location", root) return cfg
REVS = { "0001": (None, ['op.create_table("product", sa.Column("id", sa.Integer, primary_key=True), sa.Column("name", sa.String(50), nullable=False))'], ['op.drop_table("product")']), "0002": ("0001", ['op.add_column("product", sa.Column("stock", sa.Integer, nullable=True))'], ['with op.batch_alter_table("product") as batch:', ' batch.drop_column("stock")']), "0003": ("0002", ['op.create_index("ix_product_name", "product", ["name"])'], ['op.drop_index("ix_product_name", table_name="product")']),}
engine = sa.create_engine("sqlite://")conn = engine.connect()
def state(): names = sa.inspect(conn).get_table_names() columns = [c["name"] for c in sa.inspect(conn).get_columns("product")] if "product" in names else [] return MigrationContext.configure(conn).get_current_revision(), columns
def run(cfg, action, target): cfg.attributes["connection"] = conn action(cfg, target) conn.commit() return state()
cfg = project(REVS)scripts = ScriptDirectory.from_config(cfg)print(scripts.get_heads())for script in scripts.walk_revisions(): print(script.revision, "<-", script.down_revision)Output
['0003'] 0003 <- 0002 0002 <- 0001 0001 <- None
What this code does
walk_revisionsstarts at the head and followsdown_revisionback to the first file.- The first migration has
Noneas its parent, so it starts from base.
walk_revisions goes from head back to base. The database stores its position in the alembic_version table, which holds one revision id.
Upgrade moves forward, downgrade moves back
Upgrade is the Alembic command that runs the upgrade() function of every migration between the database's current revision and the target, in order.
You choose the target. head is the newest revision. base is the start. A number with a sign is a relative step, such as one forward or one back. Alembic runs each step in order and updates the version table.
command.upgrade(cfg, "head")command.upgrade(cfg, "+1")command.upgrade(cfg, "0002")command.downgrade(cfg, "-1")command.downgrade(cfg, "base")Example 2 · Move forward and back
import os, tempfileimport sqlalchemy as safrom alembic import commandfrom alembic.config import Configfrom alembic.migration import MigrationContextfrom alembic.script import ScriptDirectory
ENV = ( "from alembic import context\n" "context.configure(connection=context.config.attributes['connection'])\n" "with context.begin_transaction():\n" " context.run_migrations()\n")
def project(revisions): root = tempfile.mkdtemp() os.makedirs(os.path.join(root, "versions")) open(os.path.join(root, "env.py"), "w").write(ENV) for rev, (down, up, dn) in revisions.items(): lines = [f'revision = "{rev}"', f"down_revision = {down!r}", "from alembic import op", "import sqlalchemy as sa", "", "def upgrade():"] lines += [" " + s for s in up] + ["", "def downgrade():"] + [" " + s for s in dn] open(os.path.join(root, "versions", rev + ".py"), "w").write("\n".join(lines) + "\n") cfg = Config() cfg.set_main_option("script_location", root) return cfg
REVS = { "0001": (None, ['op.create_table("product", sa.Column("id", sa.Integer, primary_key=True), sa.Column("name", sa.String(50), nullable=False))'], ['op.drop_table("product")']), "0002": ("0001", ['op.add_column("product", sa.Column("stock", sa.Integer, nullable=True))'], ['with op.batch_alter_table("product") as batch:', ' batch.drop_column("stock")']), "0003": ("0002", ['op.create_index("ix_product_name", "product", ["name"])'], ['op.drop_index("ix_product_name", table_name="product")']),}
engine = sa.create_engine("sqlite://")conn = engine.connect()
def state(): names = sa.inspect(conn).get_table_names() columns = [c["name"] for c in sa.inspect(conn).get_columns("product")] if "product" in names else [] return MigrationContext.configure(conn).get_current_revision(), columns
def run(cfg, action, target): cfg.attributes["connection"] = conn action(cfg, target) conn.commit() return state()
cfg = project(REVS)print(MigrationContext.configure(conn).get_current_revision())print(run(cfg, command.upgrade, "+1"))print(run(cfg, command.upgrade, "head"))print(run(cfg, command.downgrade, "-2"))print(run(cfg, command.downgrade, "base"))Output
None
('0001', ['id', 'name'])
('0003', ['id', 'name', 'stock'])
('0001', ['id', 'name'])
(None, [])What this code does
- The database starts at base, so no revision is recorded.
+1ran only 0001.headran 0002 and 0003.-2undid both.baseremoved the table.
The command line does the same: alembic upgrade head, alembic downgrade -1. The Python command functions are what the CLI calls.
A downgrade cannot bring back lost data
Irreversible change is a schema change that destroys data, such as dropping a column or table. Its downgrade can restore the structure but not the lost values.
A downgrade undoes the shape of the schema, not the data. Dropping a column removes its values for good. Write a real downgrade() for each migration, but treat a destructive upgrade as a one-way step.
def upgrade(): op.drop_column("product", "old_note")
def downgrade(): # the column returns, but its old values are gone op.add_column("product", sa.Column("old_note", sa.String(20)))Example 3 · A downgrade loses data
import os, tempfileimport sqlalchemy as safrom alembic import commandfrom alembic.config import Configfrom alembic.migration import MigrationContextfrom alembic.script import ScriptDirectory
ENV = ( "from alembic import context\n" "context.configure(connection=context.config.attributes['connection'])\n" "with context.begin_transaction():\n" " context.run_migrations()\n")
def project(revisions): root = tempfile.mkdtemp() os.makedirs(os.path.join(root, "versions")) open(os.path.join(root, "env.py"), "w").write(ENV) for rev, (down, up, dn) in revisions.items(): lines = [f'revision = "{rev}"', f"down_revision = {down!r}", "from alembic import op", "import sqlalchemy as sa", "", "def upgrade():"] lines += [" " + s for s in up] + ["", "def downgrade():"] + [" " + s for s in dn] open(os.path.join(root, "versions", rev + ".py"), "w").write("\n".join(lines) + "\n") cfg = Config() cfg.set_main_option("script_location", root) return cfg
REVS = { "0001": (None, ['op.create_table("product", sa.Column("id", sa.Integer, primary_key=True), sa.Column("name", sa.String(50), nullable=False))'], ['op.drop_table("product")']), "0002": ("0001", ['op.add_column("product", sa.Column("stock", sa.Integer, nullable=True))'], ['with op.batch_alter_table("product") as batch:', ' batch.drop_column("stock")']), "0003": ("0002", ['op.create_index("ix_product_name", "product", ["name"])'], ['op.drop_index("ix_product_name", table_name="product")']),}
engine = sa.create_engine("sqlite://")conn = engine.connect()
def state(): names = sa.inspect(conn).get_table_names() columns = [c["name"] for c in sa.inspect(conn).get_columns("product")] if "product" in names else [] return MigrationContext.configure(conn).get_current_revision(), columns
def run(cfg, action, target): cfg.attributes["connection"] = conn action(cfg, target) conn.commit() return state()
cfg = project(REVS)run(cfg, command.upgrade, "head")conn.execute(sa.text("insert into product (name, stock) values ('Pen', 40)"))conn.commit()print(conn.execute(sa.text("select * from product")).all())run(cfg, command.downgrade, "0001")print(conn.execute(sa.text("select * from product")).all())run(cfg, command.upgrade, "head")print(conn.execute(sa.text("select * from product")).all())Output
[(1, 'Pen', 40)] [(1, 'Pen')] [(1, 'Pen', None)]
What this code does
- Going back to 0001 dropped the
stockcolumn, so the value 40 was deleted. - Upgrading again recreates the column, but the stock of the Pen is now empty (
None).
Example 4 · Useful commands for the terminal
Runs in: Your terminal
alembic currentalembic historyalembic upgrade 0002:0003 --sqlSample output
0003 (head) 0002 -> 0003 (head), add index on name 0001 -> 0002, add stock <base> -> 0001, create product BEGIN; CREATE INDEX ix_product_name ON product (name); UPDATE alembic_version SET version_num='0003' WHERE alembic_version.version_num = '0002'; COMMIT;
What this code does
currentshows where the database is.historylists the chain with the newest first.- With
--sqland a range such as0002:0003, Alembic prints the statements for review instead of running them.
Back up before a destructive migration. In production, many teams run only upgrades and fix problems by adding a new migration forward.
Next step
Check your understanding
11 quick questions