Database Migrations with Alembic

Alembic is a database migration tool for SQLAlchemy. Think of database migration as a version control for databases. Recall that the SQLAlchemy create_all() method only creates missing tables from the models. Once the table is created, it doesn’t alter table schema based upon changes in the model.

While developing application it is common to change table schema. This is where Alembic comes into the play. A tool like Alembic allows us to change database schema as our application evolves. It also keeps track of the changes made to the database, so that you can move forward or backward in time. If we don’t use a tool like Alembic then we have to keep track of all the changes and manually alter database schema by entering ALTER statements.

Flask-Migrate is an extension which integrates Alembic with the Flask application. Install Flask-Migrate and its dependencies using the following command.

To integrate Flask-Migrate with our application import Migrate and MigrateCommand class from flask_migrate package and create an instance of Migrate class by passing application instance (app) and SQLAlchemy object (db), as follows (changes are highlighted):

flask_app/main2.py

The MigrateCommand class defines some database migration commands which can be made available through the Flask-Script. In line 12, we exposing these commands via db command line argument. To view the newly added command head back to the terminal and enter the following command:

As you can see, we now have a new command named db to perform database migrations. To view a complete list of all possible subcommands of db type the following:

These are the actual commands we will be using while performing database migrations.

Before Alembic can start tracking changes, we have to initialize migration repository. Migration repository is simply a directory which contains Alembic configurations and migration scripts. To create the migration repository execute the init command:

This command will create a ‘migrations’ directory under the flask_app directory. The structure of the migrations directory should look like this:

Here is a quick rundown of what each file and folder does:

  • alembic.ini – A configuration file for Alembic.
  • env.py – A Python file that is run everytime Alembic is invoked. It is responsible for connecting to the database, starting a transaction and calling the migration engine.
  • README – A README file.
  • script.py.mako – A Mako template file that will be used to create migration scripts.
  • version – A directory for storing migration scripts.

Creating Migration Script

Alembic stores database migrations in migration scripts which are just Python files. A migration script defines two functions upgrade() and downgrade(). The job of upgrade() function is to apply a set of changes to the database and the downgrade() function reverse those changes. When we apply a migration its upgrade() function is executed and when we rollback a migration its downgrade() function is executed.

Alembic provides two ways of creating migrations:

  1. Manually via revision command.
  2. Automatically via migrate command.

Manual Migration

Manually or Empty migration creates a migration script with empty upgrade() and downgrade() function. And it is our job to populate those methods using Alembic directives that will apply a set of changes to the database. Manual migration is used when we want to have complete control over the migration process. To create an empty migration enter the following command:

This command will create a new migration script in the migrations/version directory. The name of the file should be of the form someid_initial_migration.py. Open the file and it should look like this:

The file starts with a comment section which contains the message we specified using the -m flag, revision ID, and the timestamp at which the file was created. The next important section is the revision identifiers. Each migration script is given a revision ID which is stored in the revision variable. In the next line, we have down_revision variable which is set None. Alembic uses down_revision variable to determine which the migration should run and in what order. The down_revision variable points to the revision id of the parent migration. In our case, it is set to None because this is our first migration script. At the end of the file there are empty upgrade() and downgrade() functions.

With the migration script in place. Let’s edit the migration file to add create table and drop table operation to the upgrade() and downgrade() function respectively.

In the upgrade() function, we are using create_table() directive of Alembic. The create_table() directive issues CREATE TABLE statement.

In the downgrade() function, we are using drop_table() directive which issues DROP TABLE statement.

When you run this migration it creates users table and when you rollback this migration it deletes the users table.

We are now ready to apply our first migration. Enter the following command to run the migration:

This command will invoke the upgrade() function of the migration script. The db upgrade command migrates the database to the latest migration. Note that db upgrade doesn’t just run the latest migration but runs all the migrations that haven’t been run yet. It means that, if we had created a slew of migrations then db upgrade would have run all those migrations in the order in which they are created.

Instead of running the latest migration, you can also pass the revision id of the migration you want to run. In this case, db upgrade would stop after running the specified migration and will not proceed to run the latest migration.

As this is the first time, we are applying migration, Alembic will also create a table named alembic_version. The table consists of a single column named version_num which stores revision id of latest applied migration. That’s how Alembic knows the current state of migration and from where it should proceed. Currently, the alembic_version table looks like this:

We can determine the last applied migration using the db current command. It returns the revision id of the last applied migration. In case, you haven’t applied any migration it will return nothing.

The output shows that we are currently on migration 945fc7313080. Also, notice the string (head) after the revision id, it indicates that migration 945fc7313080 is the latest migration.

Create another empty migration using db revision command as follows:

Run the db current command again. This time you will get just the revision id without the string (head) because migration 945fc7313080 is not the latest anymore.

To view a complete list of migrations (applied and unapplied) use the db history command. It returns a list of migrations in reverse chronological order (i.e latest first).

The output shows that 945fc7313080 is our first migration, which is then followed by b0c1f3d3617c which is the latest migration. As usual (head) indicates the latest migration.

The users table we created by via migration was purely for testing purpose. We can bring the database to back to its original state where it was prior to executing db upgrade command by downgrading migration. To downgrade or rollback the last applied migration, we use db downgrade command.

This will invoke the downgrade() method of migration 945fc7313080 which deletes the users table from the database. Just like db upgrade command, we can also pass the revision id of the migration we want to downgrade to. For example, to downgrade to migration 645fc5113912, we would use the following command.

To rollback all the applied migrations, use the following command:

At this point, we have no migration applied to the database. We can verify this by running db current command as follows:

As you can see the output returns no revision id. Note that downgrading migration only undo the changes made on the database, it doesn’t delete the migration script itself. As a result, we still have two migration scripts, to view them run db history command.

So What will happen if we now run db upgrade command?

The db upgrade command would first run migration 945fc7313080, followed by migration b0c1f3d3617c.

If that’s what you thought. Well Done! You should now have a pretty good understanding of migrations. Our database is again in perfect state and we don’t want to apply changes in migration scripts so we can safely delete them.

Automatic Migration

Note: Before proceeding make sure you have deleted all the migration from the previous section.

Automatic migration creates code for upgrade() and downgrade() function after comparing models with the current version of the database. To create automatic migration we use migrate command which is just an alias of revision --autogenerate. In the terminal enter the migrate command as follows:

Notice the last line of the output, it says "No changes in schema detected." It means that our models and database are in sync.

Open main2.py and add the Employee model just after Feedback model as follows:

flask_app/main2.py

Run db migrate command again, this time Alembic will detect the addition of new employees table and will generate a migration script along with the logic to create and drop the employees table.

The migration script created by the preceding command should look like this:

Nothing new here, the upgrade() function is using create_table directive to create table and downgrade() function is using drop_table directive to drop table.

Let’s run this migration using the db upgrade command:

This will add employees table in your database. We can verify the changes by looking into the database as follows:

Limitations of Automatic Migration

Automatic Migrations are not perfect. It doesn’t detect every possible change.

Operations that Alembic can detect:

  • Addition or removal of tables
  • Addition or removal or columns
  • Changes in foreign keys
  • Changes in column type
  • Changes in indexes and explicitly named unique constraints

Operations that Alembic can’t detect:

  • Change in table name
  • Change in column name
  • Constraints without explicit names

To create migration scripts for operations that Alembic can’t detect, we have to create an empty migration script and then populate the upgrade() and downgrade() functions accordingly.

Leave a Comment