Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 37 ยท Alembic and Migrations

Alembic: Your First Database Migrations

Set up Alembic for SQLAlchemy, write a migration with op.create_table, run upgrade and downgrade, read the version table and understand revision files. Runs in your browser.

Base.metadata.create_all(engine) creates tables that do not exist yet. It never changes a table that does exist. The day you add a column, rename a table or split a field in two, create_all does nothing, and you need a tool that evolves an existing database safely and repeatably. For SQLAlchemy that tool is Alembic, written by the same author.

Alembic keeps a chain of small Python files called migrations (or revisions). Each one knows how to move the schema forward (upgrade) and back (downgrade). The database records which revision it is at, so running alembic upgrade head on any copy of the database, a laptop or production, brings it to the same state.

Alembic is real here: the examples install it with micropip and run its actual command API against a SQLite file in your browser's virtual file system. On your own machine you run the same operations from the terminal with the alembic command, so each example shows the matching command line.

The moving parts

flowchart LR
    M["models.py<br/>(your metadata)"] --> E["env.py<br/>connects Alembic to your app"]
    I["alembic.ini<br/>settings"] --> E
    E --> V["versions/<br/>0001_*.py, 0002_*.py ..."]
    V -- "upgrade / downgrade" --> DB[("Database<br/>+ alembic_version table")]

alembic init creates the ini file, the env.py script and an empty versions folder. Each revision you add becomes a file in versions.

  • alembic.ini: settings, mainly script_location and the database URL.
  • env.py: runs every time you migrate. It builds the engine and opens a transaction. This is where you point Alembic at your models' metadata.
  • versions/: one file per revision, each with a revision id, a down_revision pointing at its parent, and upgrade() / downgrade() functions.
  • alembic_version: a one-row table in your database holding the current revision id.

Setting up a project

On the command line the set-up is one command: alembic init migrations. The example below does the same through Alembic's Python API and then writes a first revision by hand.

Python alembic init and an empty revision

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The generated file has two empty functions. Everything interesting happens when you fill them in.

Writing a migration by hand

Inside a migration you use the op object, which exposes schema operations: create_table, drop_table, add_column, drop_column, alter_column, create_index, create_foreign_key, execute and more. They take sqlalchemy Column objects, so the syntax is familiar.

Always write the downgrade() as the exact inverse of upgrade(). It is the safety net when a release goes wrong.

Python Create, upgrade, inspect and downgrade

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice the alembic_version table appears on the first upgrade and keeps the revision id 0001. After the downgrade to base (the empty state before any revision) the application table is gone again.

The everyday commands

Command What it does
alembic init migrations Create the folder, env.py and alembic.ini
alembic revision -m "add phone" Create an empty revision file
alembic revision --autogenerate -m "..." Create a revision filled in by comparing models and database (next lesson)
alembic upgrade head Apply every revision not yet applied
alembic upgrade +1 / alembic downgrade -1 Move one step
alembic upgrade 0002 Move to a specific revision
alembic current Show the revision the database is at
alembic history --verbose List the chain of revisions
alembic stamp head Record a revision without running anything

alembic stamp head is how you adopt Alembic on a database that was created with create_all: you tell Alembic "this database is already at the latest state".

How revisions chain together

Each file names its parent with down_revision. The first has None. Alembic walks that chain to decide what to run. Revision ids are random hex by default (ae1027a6acf), so merges between teammates rarely collide; the filename prefix is only a label.

flowchart LR
    B["base"] --> R1["0001<br/>create user"] --> R2["0002<br/>add phone"] --> R3["0003<br/>add index<br/>(head)"]

A linear history: upgrade head runs 0001, 0002 and 0003 in order on an empty database, or only the missing tail on a database already at 0002.

Making upgrade safe to rerun

Alembic runs all pending revisions inside one transaction where the database supports transactional DDL (PostgreSQL does; MySQL and Oracle do not, SQLite mostly does). If a migration fails midway on PostgreSQL everything rolls back. On MySQL a failure can leave the schema half-changed, so keep each revision small and single-purpose: one logical change per file.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is create_all() not enough once an application is in production?

    create_all skips tables that already exist, so it cannot add a column or change a type. Migrations can.
  2. 2. Where does Alembic record which revision a database is at?

    The one-row alembic_version table holds the current revision id.
  3. 3. What does alembic upgrade head do?

    head is the newest revision; upgrade runs the chain from the current revision up to it.
  4. 4. How do you adopt Alembic on a database already created with create_all()?

    stamp marks a revision as applied without executing it.

Summary

  • Alembic evolves a live schema through a chain of revision files, each with upgrade() and downgrade().
  • alembic init creates alembic.ini, env.py and versions/; env.py connects Alembic to your database and metadata.
  • The op object provides schema operations such as create_table and add_column.
  • The database stores its current revision in the alembic_version table.
  • upgrade, downgrade, current, history and stamp are the commands you will use daily.
  • Keep revisions small and always write a working downgrade().

Next: Autogenerate and Reviewing Migrations