Alembic

Installation and Setup

Install Alembic into the project’s virtual environment, then create a migration environment:

• Windows
• Linux + macOS

PS> py -m pip install alembic
PS> alembic init alembic

$ python -m pip install alembic
$ alembic init alembic

The init command creates an alembic.ini configuration file and an alembic/ directory holding env.py and a versions/ folder for migration scripts. Set sqlalchemy.url in alembic.ini to point at the target database.

Key Features

• Stores each schema change as a Python script with upgrade() and downgrade() functions.
• Tracks the applied revision in an alembic_version table inside the database.
• Autogenerates candidate migrations by comparing SQLAlchemy models against the live schema.
• Upgrades or downgrades to any revision, including relative steps like +2 or -1.
• Emits plain SQL scripts in offline mode for teams that review DDL before running it.
• Supports branching and merging of migration histories.
• Provides a batch mode that works around SQLite’s limited ALTER TABLE support.

Usage

Say a new email column was just added to a User model. With target_metadata in env.py set to the models’ Base.metadata, Alembic can draft the migration from the difference:

$ alembic revision --autogenerate -m "add email to user"

Autogenerate can’t detect renamed tables or columns, which show up as a drop plus an add. Review the generated script in versions/, then apply every pending migration:

$ alembic upgrade head

Show the database’s current revision and the full migration history:

$ alembic current
$ alembic history --verbose

Roll back the most recent migration:

$ alembic downgrade -1

Both upgrade and downgrade walk the database along the chain of revision scripts, running their upgrade() or downgrade() functions in order and recording the new revision in alembic_version. Step a three-revision project up and down that chain to see how the schema follows:

Generate a SQL script covering the migration history, for a database administrator to review and run:

$ alembic upgrade head --sql > migration.sql

For projects that keep settings in pyproject.toml, alembic init --template pyproject alembic moves most of the configuration into a [tool.alembic] table. The Alembic documentation covers the remaining commands and options.

Related Resources

Tutorial

Data Management With Python, SQLite, and SQLAlchemy

In this tutorial, you'll learn how to store and retrieve data using Python, SQLite, and SQLAlchemy as well as with flat files. Using SQLite with Python brings with it the additional benefit of accessing data with SQL. By adding SQLAlchemy, you can work with data in terms of objects and methods.

intermediate databases web-dev

For additional information on related topics, take a look at the following resources:

• SQLite and SQLAlchemy in Python: Move Your Data Beyond Flat Files (Course)
• Python REST APIs With Flask, Connexion, and SQLAlchemy – Part 1 (Tutorial)
• Python REST APIs With Flask, Connexion, and SQLAlchemy – Part 2 (Tutorial)
• Python REST APIs With Flask, Connexion, and SQLAlchemy – Part 3 (Tutorial)
• Data Management With Python, SQLite, and SQLAlchemy (Quiz)