Python Database Migrations with SQLAlchemy and Alembic

If your Python application has a database, the schema will change. A new column gets added, a nullable field becomes required, or an index is needed because a query became slow. Editing the production database by hand works once. It is not a migration strategy.

SQLAlchemy is the Python toolkit that maps application objects and SQL queries to a database. Alembic is the migration tool commonly used alongside it. SQLAlchemy describes the schema your code expects; Alembic records the small, ordered steps needed to move an existing database from one version to the next.

Why you need migrations

Your database has state that must survive deployments. A model change in models.py does not automatically change an existing PostgreSQL or SQLite table. If one developer adds a column locally and another deploys code that expects it in production, the application and database are out of sync.

A migration gives the change a name, a timestamp, an upgrade operation, and usually a downgrade operation. It can be reviewed in Git, tested in CI, and run in the same order on every environment.

Install SQLAlchemy and Alembic

Create a virtual environment and install both packages:

python -m venv .venv
source .venv/bin/activate       # macOS/Linux
# .venv\Scripts\activate    # Windows
pip install sqlalchemy alembic

For a real project, pin versions in requirements.txt or pyproject.toml so a future install does not silently change your migration behavior.

Initialise an Alembic project

Run this once from the project root:

alembic init migrations

Alembic creates an alembic.ini file, an environment module, and a versions/ directory. Each revision file goes in versions/. Never delete an old revision just because it is no longer the newest one; deployed databases may already depend on it.

Set the database URL in configuration. For local SQLite, a simple value is enough:

sqlalchemy.url = sqlite:///./app.db

For production, load the URL from an environment variable rather than committing a password to Git.

Create your first migration

Suppose the application needs a users table:

alembic revision -m "create users table"

Open the generated file and write the schema change in upgrade() and its reverse in downgrade():

from alembic import op
import sqlalchemy as sa

revision = "001_create_users"
down_revision = None

def upgrade():
    op.create_table(
        "users",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("email", sa.String(255), nullable=False, unique=True),
    )

def downgrade():
    op.drop_table("users")

Apply it with:

alembic upgrade head

Alembic stores the current revision in an alembic_version table. That is how it knows which revisions have already run.

Add a column safely

The common change is adding a field to an existing table:

alembic revision -m "add display name"

Then:

def upgrade():
    op.add_column("users", sa.Column("display_name", sa.String(120)))

def downgrade():
    op.drop_column("users", "display_name")

Adding a nullable column is usually safe for a live system. Adding a non-null column to a table that already contains rows is not: existing rows have no value. Use a staged migration instead—add it nullable, backfill data, update the application, then enforce NOT NULL in a later revision.

Autogenerate: useful, not automatic truth

If Alembic is connected to SQLAlchemy metadata, it can compare the model schema with the database:

alembic revision --autogenerate -m "add display name"

Always review the generated file. Autogenerate can miss data migrations, renamed columns, custom SQL, and changes that are ambiguous to a database engine. A migration is code that runs against valuable data, not a file to blindly accept.

Upgrade, downgrade, and inspect history

Useful commands:

alembic current       # revision installed in this database
alembic history       # all revisions
alembic upgrade head  # move forward to the newest revision
alembic downgrade -1  # roll back one revision locally

Use downgrades for development and tests. In production, a corrective forward migration is often safer than reversing a migration that may already have changed or deleted data.

A practical deployment checklist

1. Commit the migration file with the application change.
2. Run migrations against a fresh test database.
3. Test the migration against a copy of realistic data.
4. Deploy code that is compatible with both the old and new schema when possible.
5. Run alembic upgrade head as a release step.
6. Verify alembic current after deployment.

Do not run Base.metadata.create_all() as a replacement for migrations in production. It can create missing tables, but it does not reliably describe renames, constraints, data backfills, or the history of changes.

Practice the workflow in PyRun

You can practise the Python concepts behind migrations without installing Python locally. Start a runnable Python lesson on PyRun, then continue into the database and SQL lessons when you are ready to work with real schemas.

Key idea: SQLAlchemy describes what your application expects. Alembic records how an existing database gets there, one reviewable revision at a time.