OverIQ.com

  1. Home
  2. Flask tutorial
  3. Database Migrations with Alembic

Database Migrations with Alembic

Last updated on July 27, 2020

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.

(env) overiq@vm:~/flask_app$ pip install flask-migrate

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
#...
from flask_migrate import Migrate, MigrateCommand

app = Flask(__name__)
app.debug = True
app.config['SECRET_KEY'] = 'a really really really really long secret key'
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:pass@localhost/flask_app_db'

manager = Manager(app)
db = SQLAlchemy(app)
migrate = Migrate(app, db)
manager.add_command('db', MigrateCommand)
#...

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
(env) overiq@vm:~/flask_app$ python main2.py
positional arguments:
  {db,faker,foo,shell,runserver}
    db                  Perform database migrations
    faker               A command to add fake data to the tables
    foo                 Just a simple command
    shell               Runs a Python shell inside Flask application context.
    runserver           Runs the Flask development server i.e. app.run()

optional arguments:
  -?, --help            show this help message and exit

(env) overiq@vm:~/flask_app$

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
(env) overiq@vm:~/flask_app$  python main2.py db -?
Perform database migrations

positional arguments:
  {init,revision,migrate,edit,merge,upgrade,downgrade,show,history,heads,branche
s,current,stamp}
    init                Creates a new migration repository
    revision            Create a new revision file.
    migrate             Alias for 'revision --autogenerate'
    edit                Edit current revision.
    merge               Merge two revisions together. Creates a new migration
                        file
    upgrade             Upgrade to a later version
    downgrade           Revert to a previous version
    show                Show the revision denoted by the given symbol.
    history             List changeset scripts in chronological order.
    heads               Show current available heads in the script directory
    branches            Show current branch points
    current             Display the current revision for each database.
    stamp               'stamp' the revision table with the given revision;
                        don't run any migrations

optional arguments:
  -?, --help            show this help message and exit

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
(env) overiq@vm:~/flask_app$ python main2.py db init
  Creating directory /home/overiq/flask_app/migrations ... done
  Creating directory /home/overiq/flask_app/migrations/versions ... done
  Generating /home/overiq/flask_app/migrations/README ... done
  Generating /home/overiq/flask_app/migrations/env.py ... done
  Generating /home/overiq/flask_app/migrations/alembic.ini ... done
  Generating /home/overiq/flask_app/migrations/script.py.mako ... done
  Please edit configuration/connection/logging settings in
  '/home/overiq/flask_app/migrations/alembic.ini' before proceeding.
(env) overiq@vm:~/flask_app$

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

1
2
3
4
5
6
migrations
├── alembic.ini
├── env.py
├── README
├── script.py.mako
└── versions

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:

(env) overiq@vm:~/flask_app$ python main2.py db revision -m "Initial migration"

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
"""Initial migration

Revision ID: 945fc7313080
Revises: 
Create Date: 2017-12-29 14:39:27.854291

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '945fc7313080'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    pass


def downgrade():
    pass

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.

1
2
3
4
5
6
7
8
9
def upgrade():
    op.create_table(
        'users',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('name', sa.String(50), nullable=False),
    )

def downgrade():
    op.drop_table('users')

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:

(env) overiq@vm:~/flask_app$ python main2.py db upgrade

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.

(env) overiq@vm:~/flask_app$ python main2.py db upgrade 945fc7313080

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.

1
2
3
4
5
6
(env) overiq@vm:~/flask_app$ python main2.py db current
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
945fc7313080 (head)

(env) overiq@vm:~/flask_app$

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:

(env) overiq@vm:~/flask_app$ python main2.py db revision -m "Second migration"

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.

1
2
3
4
5
6
(env) overiq@vm:~/flask_app$ python main2.py db current
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
945fc7313080

(env) overiq@vm:~/flask_app$

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).

1
2
3
4
5
(env) overiq@vm:~/flask_app$ python main2.py db history
945fc7313080 -> b0c1f3d3617c (head), Second migration
<base> -> 945fc7313080, Initial migration

(env) overiq@vm:~/flask_app$

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. 

1
2
3
4
5
6
7
(env) overiq@vm:~/flask_app$ python main2.py db downgrade
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade 945fc7313080 -> , Initial mi
gration

(env) overiq@vm:~/flask_app$

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.

(env) overiq@vm:~/flask_app$ python main2.py db downgrade 645fc5113912

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

(env) overiq@vm:~/flask_app$ python main2.py db downgrade base

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

1
2
3
4
5
(env) overiq@vm:~/flask_app$ python main2.py db current
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.

(env) overiq@vm:~/flask_app$

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.

1
2
3
4
5
(env) overiq@vm:~/flask_app$ python main2.py db history
945fc7313080 -> b0c1f3d3617c (head), Second migration
<base> -> 945fc7313080, Initial migration

(env) overiq@vm:~/flask_app$

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:

1
2
3
4
5
6
(env) overiq@vm:~/flask_app$ python main2.py db migrate
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.env] No changes in schema detected.

(env) overiq@vm:~/flask_app$

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

1
2
3
4
5
6
7
8
#...
class Employee(db.Model):
    __tablename__ = 'employees'
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(255), nullable=False)
    designation = db.Column(db.String(255), nullable=False)
    doj = db.Column(db.Date(), nullable=False)    
#...

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.

(env) overiq@vm:~/flask_app$ python main2.py db migrate -m "Adding employees table"

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
"""Adding employees table

Revision ID: 6e059688f04e
Revises: 
Create Date: 2017-12-30 16:01:28.030320

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '6e059688f04e'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('employees',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('designation', sa.String(length=255), nullable=False),
    sa.Column('doj', sa.Date(), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('employees')
    # ### end Alembic commands ###

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:

1
2
3
4
5
6
7
(env) overiq@vm:~/flask_app$ python main2.py db upgrade
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 6e059688f04e, Adding emplo
yees table

(env) overiq@vm:~/flask_app$

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.

Flask tutorial

Recent Posts