How to use alembic in production

Learning alembic is a difficult task even if you already have previous relational database experience, because most guides don’t include the common pitfalls and instructions for managing a production project. In this guide we outline all the ways you can use alembic and incrementally take you towards a desirable production alembic setup.

Straight from the alembic docs: Alembic is a lightweight database migration tool for usage with the SQLAlchemy Database Toolkit for Python.

In this guide we will use the following technologies specifically as an example, but the same concepts apply to other databases, migration tools and languages:

• Postgres
• Alembic
• SqlAlchemy (Python ORM)

We will only cover the high level overview of the architectures that can be used to manage and implement alembic migrations, as other guides online adequately cover how to actually run alembic commands.

Initially, we will consider a basic web application with two environments: Develop and Production. Both of them use manually managed relational databases. When we work on new features, we can make changes to the develop database manually with sql commands, and make sure that all our new code in our webapp works well in develop. When we want to push our updates through to production

To make this process clear, we will use an example so that we can follow how the process of implementing this can be handled by alembic.

Imagine we want to add the ability for users to store a setting “send me weekly emails”. It will be a boolean value stored in our relational database in our “user” table. We want all existing users to have this new field be set as true, and we want to make sure that users are never created with a null value in this field.

Doing this manually, we would first add that new row into the develop database with an sql command such as this (example with Postgres)

ALTER TABLE user ADD COLUMN "send_weekly_email" BOOLEAN DEFAULT True;
UPDATE "user" SET send_weekly_email = True
ALTER TABLE "user" ALTER COLUMN send_weekly_email SET NOT NULL;

Now that we have the new column in our develop database, we could alter our backend web application code to check the user’s setting before sending them a weekly email.

What is critical to note here, is that if we would deploy this code from develop to production, no users would receive weekly emails! This is because we have to make sure that we manually run these SQL commands against the production environment too, before deploying our code from development.

As you can imagine, this is a very common problem, which is further complicated when you have to add whole new tables, remove tables, alter existing columns, etc. The more complex a feature, the easier it is to forget a critical step that was required to get the relational database setup.

Furthermore, if we want to add an additional environment, such as UAT (User acceptance testing), we’ve just increased our workload and have to also manually do all the changes in that database as well.

Alembic to the rescue.

When you are using SQLAlchemy, you already have a set of model definitions written up in Python, which completely describe your Relational database (or at least the parts you want to interact with).

Alembic is a tool which interprets your SQLAlchemy models, reads your database state, and tries to make your database equal to your SQLAlchemy models. It keeps track of all the changes you make, so that when you want to promote the changes to another database, it is easily repeatable.

Here’s the easiest way to get started.

First, you have to forbid any manual changes to the database, or at least limit them as much as possible. Any changes you make manually will potentially cause you grief later, as alembic will not have any idea of those changes. Stick to issuing SQL commands ONLY through alembic and you won’t run into any issues.

If you have a set of SQLAlchemy model definitions, the first thing you would do is use alembic to generate migrations against your database.

alembic revision --autogenerate -m "initial revision"

Alembic will create a “migration” file which has an upgrade and a downgrade function.

The upgrade function will contain all the sql commands needed to create the tables and columns, whereas the downgrade function will contain everything needed to undo those changes. This is helpful, as if you deploy a big complex change to production, you can safely undo that last change.

You might imagine that you should keep migrations for “dev” “uat” and “production” seperate, but that is not the case. What migrations represent is the one core source of truth for how to construct the database for your application. All the different environments will be at different stages of the migration history, but eventually all the migrations from develop will end up in production.

Migrations, while automatically generated by Alembic, are not set in stone until they have been applied to the database. We highly recommend committing all your migrations into git along with your SQLAlchemy models, so that you can review what the database changes will be.

In our example above, we want to add a new non-nullable column to an existing table. Unfortunately, alembic does not have automatic support for this.

In order to achieve the above with alembic, you would create your migration with the alembic revision command, but you will notice that the upgrade function looks something like this:

def upgrade():
		# ### commands auto generated by Alembic - please adjust! ###
    op.add_column("user", sa.Column("send_weekly_email", sa.Boolean(), nullable=False)
		# ### end Alembic commands ###

If you now try and apply this migration, to a local database for example, it may succeed. If you have zero rows in your user table, this migration is not a problem. Unfortunately even in your develop database you probably have some users, and in that case this migration will totally fail. This is because you cannot add a “non-nullable” column directly to a table.

We solve this by editing the migration file directly.

def upgrade():
    op.add_column(
        "user", sa.Column("send_weekly_email", sa.Boolean(), nullable=True)
    )

    op.execute("""update "user" set send_weekly_email = True""")

    op.alter_column(
        "user",
        "send_weekly_email",
        existing_type=sa.Boolean(),
        nullable=False,
    )

    # ### end Alembic commands ###

Now, we are creating the column as nullable, then setting all existing users to true, then altering the column to be non-nullable.

We had to do a little bit more work, but now that we have this migration file, it is trivial to apply the same migrations to develop, uat, and production. Most importantly, our database migrations can be set to apply at exactly the same time as we release new code that depends on it, allowing us to make radical changes to the database with some safety.

How to setup alembic in CICD:

We can use bitbucket for hosting our projects and continuous integration pipelines, and have alembic set to interact with the database through them.

We will be storing all our alembic migrations in our git repository, and we should configure our database connection with a .env.

In order for alembic to automatically apply changes to your database, you will need to make your database reachable from the pipeline runners, and provide the connection string as a secured deployment variable for each environment. Never hardcode credentials into alembic.ini, instead read them from the environment inside env.py.

How to use alembic in a pipeline:

The pipeline step itself is surprisingly small. After your code is deployed (or ideally, just before it), you run a single command against the target environment’s database:

alembic upgrade head

What makes this work is that alembic keeps a small table in your database called alembic_version, which records exactly which migration that database is currently at. When the pipeline runs, alembic compares that version against the migration history in your repository, and only applies the migrations that are missing. This is what makes the whole process repeatable, because the same pipeline step works whether the database is one migration behind or twenty migrations behind.

A bitbucket pipeline step would look something like this:

- step:
    name: Run database migrations
    script:
      - pip install -r requirements.txt
      - alembic upgrade head

Because the connection string is a deployment variable, this exact same step is used to promote changes through develop, uat and production. No more forgetting to run SQL commands against production before a release.

How to add automatic tests for alembic:

Migrations are code, and just like any other code, they can be wrong. There are two failure modes that come up over and over again, and both of them can be caught with a test in your pipeline.

The first is a broken or missing downgrade. Autogenerated downgrades are easy to ignore because you never run them, right up until the day you desperately need one. The test for this is the full round trip described in Phase 4 below: build the database from migrations, downgrade all the way back to nothing, then upgrade all the way through again, and compare the schema dumps from before and after. If they don’t match, something in your downgrade path is broken.

The second is model drift, where a developer changes the SQLAlchemy models but forgets to generate a migration for it. The test for this is to run alembic revision with autogenerate against a fully migrated database, and assert that the resulting migration is empty. If alembic wants to generate anything at all, it means the models and the migrations have gone out of sync, and the build should fail.

Both of these tests can run against a throwaway Postgres container in the pipeline, so they never need to touch a real environment.

How to solve the most common issue (merging heads):

Sooner or later, two developers will branch off the same commit and both of them will generate a migration. Each of those migrations points to the same parent revision, so once both branches are merged, alembic sees two “heads” in the migration history. The next time anyone runs an upgrade, alembic will refuse with an error saying multiple head revisions are present.

This looks scary the first time it happens, but the fix is one command:

alembic merge heads -m "merge heads"

This creates a new, empty migration whose only job is to declare both heads as its parents, joining the history back into a single line. Nothing about your schema changes.

To catch this in the pipeline rather than in production, add a step that runs alembic heads and fails the build if it reports more than one head. It is a much nicer experience to fix this at merge time than during a deploy.

Phase 1: the first way of using it

In the beginning, each environment had its own compute engine, and the pipeline would generate a migration on every develop deploy, run it, and save the migration files on that machine. This does work, but the migrations live outside of git, so nobody can review what changes are about to be applied, and each environment slowly drifts away from the others.

Phase 2: the second way of using it

The next step was storing all migrations in the repository, generating a migration in the pipeline on deploy to each environment, and keeping a different folder for each environment. This gives you visibility at least, but now you have three sets of migrations all describing the same database, and sooner or later they will disagree with each other. Remember what we said earlier, migrations should be the one core source of truth, and this setup breaks that.

Phase 3: the third way of using it

Then we made migration generation a manual step, where generating a migration creates a branch and pushes it to the repo for review. The downside is that developers can forget to generate the migration entirely, but it prevents dozens of empty autogenerated migrations being committed on every deploy, which was becoming a real mess.

Phase 4: the way we are using it now/next

Finally, we arrived at the setup we recommend. There is only one folder for migrations, and it is the single source of truth for the database schema. In the pipelines, tests build their database using the alembic migrations, so every test run also verifies that the migrations actually work. During local development, tests are allowed to build the schema directly from model.py, because it is faster and developers iterate constantly.

The trick that holds this together is that developers must commit a migration file along with any model changes, otherwise the tests in the pipeline will fail. This solves the “forgetting” problem from Phase 3.

And lastly, we have one test which creates the database from migrations, dumps the schema, downgrades all the way to nothing, then upgrades all the way through and dumps the schema again. If the two dumps don’t match, the test fails, indicating an issue with the downgrades. This is the test that lets you actually trust your rollback path when you need it most.