Alembic 101

Image Courtesy of Samson Katt via Pexels

What does alembic do?

To understand alembic, we first need to understand SQLAlchemy.

SQLAlchemy

Bruno Krebs of Auth0 provides a great description of SQLAlchemy:

SQLAlchemy is a library that facilitates the communication between Python programs and databases. Most of the time, this library is used as an Object Relational Mapper (ORM) tool that translates Python classes to tables on relational databases and automatically converts function calls to SQL statements. SQLAlchemy provides a standard interface that allows developers to create database-agnostic code to communicate with a wide variety of database engines.

Alembic

Alembic is a database version management system. In the alembic team’s own words “Alembic provides for the creation, management, and invocation of change management scripts for a relational database, using SQLAlchemy as the underlying engine”.

It allows you to define a schema via a sqlalchemy model, and have your database reflect those specifications. Alembic also lets you change (migrate) your schemas relatively painlessly by modifying your sqlalchemy models.

Without alembic, this work would need to be done by hand, which leaves quite a bit of room for error.

Example

If we create the following model using sqlalchemy, alembic will create a table reflecting this schema in our database:

If we decide to add a new column to Dog like in the example below, alembic will update our tables and our data as necessary to reflect these changes.

Now that we have an understanding of the basic idea behind alembic, let’s familiarize ourselves with its use via a basic example.

Part 1. Creating a table from a sqlalchemy model

In this first exercise we will be using alembic and SQLAlchemy to create a table in a SQLite database.

Step 1: pip install both alembic and SQLAlchemy

Run the following command inside of your virtual environment to install alembic and SQLAlchemy:

python -m pip install alembic sqlalchemy

Step 2: create our SQLAlchemy Model

In a file models.py add the following code:

The function declarative_base() creates a SQLAlcehmy Base class that comes with a number of bells and whistles, including a metadata attribute that contains information about all schemas that inherit from Base .

Alembic uses the metadata information from Base to perform its magic.

Docs for SQLAlchemy Declarative

Step 3: initialize alembic

Run alembic init migrations . This will create a migrations/ directory and an alembic.ini file.

Basic alembic components

• alembic.ini - configuration file for alembic
• migrations/ - directory containing our alembic files other than the alembic.ini file
• README - text that explains how to work with alembic
• env.py - contains commands and configurations for alembic to use when running migrations
• versions/ - directory where our migration files will be stored
• migration files are .py files with the commands needed to run a specific migration

Step 4: setup our alembic.ini to point to a specific database

Open the alembic.ini file and search for sqlalchemy.url . Change the URL to point to your database of choice.

For this exercise, we will be using a SQLite database called pets.db, so our URL will be sqlite:///pets.db . The SQLite database does have to already exist for this to work.

Step 5: setup our env.py file

Open your env.py file and search for from myapp import mymodel. You should see it commented out, along with another line target_metadata = mymodel.Base.metadata .

Uncomment the two lines and replace the first line with from models import Base and the second line with target_metadata = Base.metadata .

As mentioned in Step 2, the metadata is used by alembic to figure out the schemas of our models.

This is all of the configuration that we need to have alembic work with our sqlalchemy models.

Step 6: create a migration file

Run alembic revision --autogenerate -m "Create dog model" .

• --autogenerate tells alembic to infer the migration script operations by comparing the sqlalchemy model to what is in the database table.
• -m is our message, and will also be used in the name of our migration file

In the directory migrations/versions you will see a new .py file that contains two functions: upgrade and downgrade. These functions contain the necessary operations to implement the changes that you made to your SQLAlchemy models (upgrade) and to undo the changes once they've been implemented (downgrade). You will also see that the SQLite database has been created.

Step 7: perform the migration

Alembic has the concepts of history, branches, and head. We can see the migration history and the migration version our head is pointing to by running migration history. The hash labeled (head) should correspond to the hash in migrations/versions/ .

Run alembic upgrade head to execute the migration.

Step 8: add rows and admire your handiwork

Run python models.py to add two rows to your new table.

To investigate the results, run sqlite3 pets.db to open the terminal for your SQLite database. Next execute select * from dogs; to see your new entries, which should be a pair of "sparkies". Finally, hit ctrl + d to return to your shell.

Part 2. Perform a schema migration and modify version files

We’re going to modify our Dog model and have alembic update our table to reflect these changes.

Step 1: add a breed column to your Dog model.

Step 2: run alembic revision --autogenerate -m "<your message here>" to create the migration file.

This is the same command that we first ran to create our table, and it’s the command that you will run any time you want to make changes to the schema.

Step 3: run alembic history . You should see something that looks like the following:

fd54d5d92a0c -> cf67ead1561c (head), Add breed column to dog 
<base> -> fd54d5d92a0c, Create dog model

Step 4: run alembic upgrade head .

If everything worked as intended, you would see

INFO  [alembic.runtime.migration] Context impl SQLiteImpl. 
INFO  [alembic.runtime.migration] Will assume non-transactional DDL. INFO  [alembic.runtime.migration] Running upgrade be45a7dbd2c6 -> e5d5bd0d7b74, add breed

However, for this example you should see the error: sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) Cannot add a NOT NULL column with default value NULL.

To add a NOT NULL column with alembic's auto-generated migration, we need to set a server_default value (a regular default value won't work) which we didn't include. See this link for more information about sqlalchemy default values.

If we just try to change the code in our models.py file and then create a new migration file, we'll get a generic FAILED: Target database is not up to date. error. Instead we need to modify the code in our version file in addition to our models file.

Step 5: in your models.py file, add server_default="Not provided" to your breed column.

Your Dog model should now look like:

Step 6: in your migrations/versions/<hash>_add_breed_column.py file add server_default="Not provided" to your Column parameter in the upgrade() function.

Your code should now look like:

Step 7: Now run alembic upgrade head and everything should work as intended

Part 3. Gracefully downgrading alembic

Alembic performs downgrades by executing the downgrade function in the relevant version file that we are downgrading from.

Methods for downgrading alembic

Alembic can revert your database schema to previous versions in three ways.

The first is calling alembic downgrade base which undoes all alembic migrations

The second is calling alembic downgrade -<n> where n is the number of versions back you want to go. You would undo the latest change with alembic downgrade -1.

The third is calling alembic downgrade <hash>. This will undo all of the most recent changes to your database schema down to, but not including, the migration file associated with the hash.

To clarify this, let’s imagine we have added the additional columns owner and favorite_toy to Dog. If we run alembic history we will see hashes associated with each of the migrations that added columns, and we will see these changes reflected in our database.

$ alembic history  
454f7c92c5cf -> 424fb5ccd5f2 (head), add favorite toy to dog 64bd06dc772f -> 454f7c92c5cf, add owner to dog 
c5659978a869 -> 64bd06dc772f, add breed to dog 
<base> -> c5659978a869, create dog

If we run alembic downgrade 64bd06dc772f (the hash of the migration where we add the breed column) we will see all of the latest changes down to, but not including 64bd06dc772f have been undone.

$ alembic downgrade 64bd06dc772f 
INFO  [alembic.runtime.migration] Context impl SQLiteImpl. 
INFO  [alembic.runtime.migration] Will assume non-transactional DDL. 
INFO  [alembic.runtime.migration] Running downgrade 424fb5ccd5f2 -> 454f7c92c5cf, add favorite toy to dog 
INFO  [alembic.runtime.migration] Running downgrade 454f7c92c5cf -> 64bd06dc772f, add owner to dog

Part 4. Other useful information

Dealing with “FAILED: Target database is not up to date.”

Let’s imagine that we removed the columns owner and favorite_toy because we decided that they don’t belong in the table. We have also decided that we should add a voice column to reflect whether the dog in question prefers to “woof”, “bark”, or “yip”.

If you add a voice column to your model and then try to run alembic revision --autogenerate -m "add voice to dog" you will get the error FAILED: Target database is not up to date.. This error means that the version that your database is in does not match the version that head is point to.

You can find the version that your database is currently in by looking at the alembic_version table in your database or using the command alembic current.

$ alembic current
64bd06dc772f
$ sqlite3 pets.db
sqlite> select * from alembic_version;
64bd06dc772f

We can use alembic history to see that our head is still pointing to add favorite toy to dog.

We can change the position of our head back to add breed to dog by deleting the version files that we no longer need. No special command is needed to delete them - move them to the trash or use rm. If we would like to keep them for our records, we can just move them to another directory.

Once the the files have been deleted, running alembic history again will show head at add breed to dog and the records of the deleted version will be gone from our history.

We can now run alembic revision --autogenerate -m "add voice to dog" and alembic upgrade head without issue.

Taking your schema to a new database

Your alembic version files also work for databases other than the database initially specified in your alembic.ini file. Continuing with our imaginary journey, let’s pretend that the previous work that we’ve done with pets.db has been development work. We now want to create our production SQLite database, prod.db.

All that we need to do to create this database, up to date with our latest changes, is change the database URL in our alembic.ini file. Set sqlalchemy.url = sqlite:///prod.db and then run alembic upgrade head. All done!

Multiple ways to upgrade your database

We have several ways to specify how to upgrade our database.

The first is alembic upgrade head which will take our database from its current version to the version pointed to by head (typically the latest version)

The second is calling alembic upgrade +<n> where n is the number of versions up you want to go. You would migrate to the next version with alembic upgrade +1

The third is calling alembic upgrade <hash>. This will take you through all necessary changes to migrate from your current version to the specified version. This will not work if the hash you specify is a version earlier than your current version. Use alembic downgrade <hash> to move backward.

Undoing a few things without starting from scratch

This is taken directly from this article by Mike Huls

Image that you’ve inserted some data and found a bug in one of your migrations; maybe a wrong column name. Just adjust the errors in the upgrade functions and call alembic downgrade base && alembic upgrade head. This will reset your entire database in just one statement!

Starting from scratch

We need to do three things to start from scratch:

1. undo all of our changes to our database
2. remove the alembic_version table
3. remove the alembic version files

We can undo all of the changes to our database by running alembic downgrade base. We then remove the alembic_version table by running the SQL statement drop table alembic_version;. Finally, we delete the version files manually.

Additional materials not previously linked

• The official alembic tutorial
• This talk is pretty solid, especially the discussion around rewrites and some of the tips at the end
• Good article if you need another look at this material