Python Friday #86: Database Migrations With Alembic and SQLAlchemy

SQLAlchemy does not modify existing tables. What can we do to keep our database in sync with our application?

This post is part of my journey to learn Python. You can find the other parts of this series here. You find the code for this post in my PythonFriday repository on GitHub.

 

Alembic?

Alembic is a lightweight database migration tool written by the author of SQLAlchemy. You can create migrations on your own and make changes to the database structure (schemas, tables, indexes, sequences and so on), or you let Alembic figure out the difference between the database and your Python models and create the migration for you.

Alembic is similar to Active Record Migrations for Ruby on Rails or Migrations in Entity Framework and lets you upgrade or downgrade your migrations.

There are a lot of edge cases Alembic covers. If you get stuck, check the documentation and the tutorials for a solution to your problem.

 

Installation

You can install Alembic with this pip command:

 

Initialise Alembic

Alembic needs a few files to work that are specific to your project. The location for these files depends on your project structure. As a rule of thumb, I put the alembic configuration next to the tests folder. As with tests these files belong to the project, but I do not need them at runtime.

We can initialise Alembic with this command that creates the files it needs in a folder called alembic (the last part of the command):

 

Edit the .ini file

The alembic.ini file is the only file that is outside our newly created alembic folder. In this file we need to set the connection string for our database.

Search for this part of the alembic.ini file:

Replace the dummy connection string with the one for your database. For SQLite we can use a relative path from the alembic.ini file to our *.sqlite file:

 

Edit the alembic/env.py file

Alembic can only generate migrations for us when it knows our models. Open the file alembic/env.py and search for this part:

We need to add our data folder (the one with our models) to the Python path, import our models and then hand the metadata to Alembic. We can do this with this code:

Save the file and check if Alembic works:

INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.

 

Disable the automatic table creation in SQLAlchemy

You can save yourself a lot of trouble if you switch off table creation in SQLAlchemy. In SQLAlchemy ORM we have something link these two lines we no longer need:

In SQLAlchemy Core we used this line to create our tables that you can comment out:

Without the automatic table creation, you can create your models, generate migrations with Alembic and apply them to your database.

However, if you already have some tables in your database that got created by SQLAlchemy and not by Alembic, you have a consistency problem. Your current database works but you cannot recreate it. In the next post I show you a way to solve that problem.

 

Create an auto-generated migration for your changed model

To have a change for Alembic, we add the description property to our BookDetails class (form the Bookstore example):

With this change in place, we can autogenerate the migration with this command:

INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.autogenerate.compare] Detected added column ‘BookDetail.Description’
Generating D:\Python\ORM\alembic\versions\49367a22b507_add_description.py … done

Alembic created the file 49367a22b507_add_description.py with our migration to add the description to the BookDetail table in the folder alembic/versions:

The upgrade() method runs when we migrate our database forward, while the downgrade() method helps us to revert changes to our database.

The first part of the file name is the revision number and is generated at random. Your migration will get a different name and to apply it you need to use the one you get.

 

Run your migration

Alembic offers us a few ways to run our migrations. If we want to run all, we can use upgrade head:

INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> 49367a22b507, Add description

With the first migration Alembic creates the table alembic_version with one column where it stores the revision number of our last applied migration:

If we now ask Alembic what the current state is, it gives us our revision number back:

INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
49367a22b507 (head)

If we want to run migrations up to a specific migration, we can use the revision number:

We do not need to type the full revision number; it is enough to write enough of the number to get a unique value:

We can even do relative upgrades and say apply the next two migrations with this command:

 

Revert a migration

If you run your migration too early, you can revert the last applied migration with this command:

 

Manually creating a migration

For the case that you want to create the migration all by yourself, you can create an empty migration with this command:

Generating D:\Python\ORM\alembic\versions\b3259a323bf6_add_manual_change.py … done

The generated migration looks like this:

It is now up to you to fill it with code.

 

Next

Alembic is a great help for SQLAlchemy and allows us to keep our database in sync with our changing application. But what do we do when we want to start with Alembic after we already have some tables in the database? Next week we look at how we can solve this problem.