How to Handle the Database Migrations with Alembic

In this video I will show how to use Alembic to Handle the database migrations. I show and explain the main commands of Alembic.

Content

Why Alembic?
Installing Alembic
First Migration
Alembic Workflow
Rollback
Parallel Migrations

Check the video for more details.

All the code used in the article can be found in the following repository.

Why Alembic?

In real life projects, the code evolves, and I have Git to manage the changes. And in real life projects, the database evolves too.

For Python projects, I have Alembic which will manage the changes for the database schema. Alembic can also handle conflicts from team changes.

Working on a team project, I used to see my code base change every day. And sometimes, those changes require adaptations on the database schema.

Here comes Alembic.

Alembic is like Git. Git is for the code base, Alembic is for the database schema.

Each modification on the code is associated to a commit in Git, each modification on the database schema is associated to a revision in Alembic. Having a tool to manage the database changes, help me to keep track of those changes.

If the changes are manually, I may forget to run the queries at some rollout. With Alembic, I ensure the database upgrades at the same time as the code base. With manual scripts, I may not be sure that I’ve run them on all the environments. And I must share them with all the developers to upgrade their local database.

With Alembic, I ensure that all the environments have the code base synchronized with the database schema.

Another advantage of tools like Alembic is that if there is a problem and I want to roll back one version of code, Alembic allows me to also roll back the database schema easily.

With Alembic, each new modification for the database will be done in a separated file. Within this file, I will also include the rollback operation. The migration will be done with the SQLAlchemy engine.

This means that I don’t require SQL knowledge and I’m even agnostic to the database system. It could be Oracle, Postgres or MySQL.

Alembic has multiple commands to manage the migrations, the rollbacks, the status and even to merge some conflicts.

Installing Alembic

Let’s start by adding the dependency. I have my project managed by Poetry. So, I add the dependency like this.

$ poetry add alembic

It will be “pip install alembic” if I’m working directly with pip.

And now I must initialize Alembic like this.

$ alembic init alembic

This command creates me a folder where we’ll be located all the migrations, the configuration to connect to the database, and the templates used to create new migration scripts.

I must first start by editing the alembic.ini file. At the moment, the only configuration I’m interested in is the URL of the database

...

sqlalchemy.url = postgresql://sergio:my-password@localhost:5432/backenddb

...

When running Alembic, it will use by default this file, nevertheless I can indicate to use a different configuration file where can be located the URL of different databases, the databases of the other environments.

If the connection to the database is more complicated, I must use environment variables to get the password, I have multiple databases to connect, or other complications, I can edit the connection directly in the alembic/env.py file, where the connection is created.

First Migration

I will create my first migration script.

$ alembic revision -m "create country table"

This command creates a file where I can add my operations to modify the database.

"""create country table

Revision ID: 5d011ccf390c
Revises:
Create Date: 2022-09-20 21:29:50.338494

"""
import sqlalchemy as sa

from alembic import op

# revision identifiers, used by Alembic.
from backend.models import country_table_name

revision = "5d011ccf390c"
down_revision = None
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        country_table_name,
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("code", sa.String(2), unique=True, nullable=False),
        sa.Column("name", sa.String(50), unique=True, nullable=False),
    )


def downgrade() -> None:
    op.drop_table(country_table_name)

The OP object gives me access to plenty of operations with the database: table creations, table modifications, drop tables, create index and more. And the SA object is SQLAlchemy.

In this script, in the upgrade method, I create my first table, the country table, with three columns. And in the downgrade method, I write my rollback operation.

So my migration is ready. Let’s run it.

$ alembic upgrade head

This will run all the migration files until the last one.

If wanted, I could specify the hash version of a specific version until which I want to upgrade. That’s not my case.

Alembic Workflow

Let’s continue with another migration quickly to see the dependencies.

"""create user tables

Revision ID: f5e2b6df626f
Revises: 5d011ccf390c
Create Date: 2022-09-20 21:33:49.765415

"""
import sqlalchemy as sa

from alembic import op

# revision identifiers, used by Alembic.
from backend.models import country_table_name, user_table_name

revision = "f5e2b6df626f"
down_revision = "5d011ccf390c"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        user_table_name,
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("username", sa.String(80), unique=True, nullable=False),
        sa.Column("password", sa.Text, nullable=False),
        sa.Column("email", sa.String(120), unique=True, nullable=False),
        sa.Column("country_id", sa.Integer),
    )
    op.create_foreign_key("fk_user_country_id", user_table_name, country_table_name, ["country_id"], ["id"])


def downgrade() -> None:
    op.drop_table(user_table_name)

In the upgrade method, I create the user table. Nothing new. But I also create a foreign key with the previously created country table. And in the downgrade method, as before drop, the table.

What’s new in this second script, is the value of down_revision (at line 16). This links the current revision with the previous one. With those values Alembic knows which revision to run first and which goes second.

Rollback

But now, how to use the rollback command?

$ alembic downgrade -1

I’ve indicated to rollback, to downgrade, just one revision. I could indicate to downgrade until a specific revision too.

As Alembic knows the sequence of revisions, the downgrade is done from the top of the list, from the most recent revision.

And now I can go forward again as before.

$ alembic upgrade head

Parallel Migrations

The case is that the development of some features is done in separated branches, by different people. Which lead to conflicts. Let’s see how Alembic can handle this.

After creating two revisions in separate Git branches, I must merge the branches together. Still, Alembic has the down_revision value for each migration script pointing to the common branch.

But none of them is the head. I must first say to Alembic to merge both revisions before upgrading my database.

$ alembic merge -m "merge bab354426a0e and 97df38784d65" bab354426a0e 97df38784d65

There is now a new revision which has two children.

"""merge bab354426a0e and 97df38784d65

Revision ID: 5394fc62e312
Revises: bab354426a0e, 97df38784d65
Create Date: 2022-09-20 22:32:44.483150

"""
# revision identifiers, used by Alembic.
revision = "5394fc62e312"
down_revision = ("bab354426a0e", "97df38784d65")
branch_labels = None
depends_on = None


def upgrade() -> None:
    pass


def downgrade() -> None:
    pass

This way, if I downgrade, both versions will be rolled backed. And if I upgrade again, both revisions will be applied.

Conclusion

I’ve installed Alembic in my project.

I’ve initiated Alembic. This way I have the configuration file ready for the connection.

I’ve edited my configuration file alembic.ini to indicate the URL of the database.

I’ve created several revisions with the command alembic revision.

I’ve also downgraded revisions with the command alembic downgrade.

When some revisions are made in parallel git branches, I can merge them with alembic merge.

References

Repository

Python Courses

The Dev World – Sergio Lema