Database Schema Migration

Any time the data model is changed, the database must be updated so that it has the right columns. Any time a table or column is added, removed, or changed in any way, we say the database schema [1] has changed. We need to be sure the database schema always matches the model. This common task is called schema migration, and it is handled well by Alembic.

Flask-Diamond makes it quick and easy to rebuild the schema during development with make db, which will delete the development database and rebuild it with all new columns matching your models. However, it’s a little drastic to always throw everything away and start from scratch. Furthermore, when the data in your database is important, it’s actually impossible to throw it away and start over. Luckily, there’s Flask-Migrate, which makes Alembic integration easy so we can handle migrations smoothly.

Introducing Migrations

Schema migrations, which are handled using Flask-Migrate, can be used to change in-production databases by recording the minimal set of steps will make your database schema match your model. Migrations are versioned, so it is possible to experiment with schemas before deploying them. It is also possible to roll back schemas in case something went wrong.

Creating a Migration

A database migration is potentially complex, so creating one takes several steps.

1. find the location of the dev database
2. create a new migration
3. rename the file with a short summary
4. edit the migration
5. test the migration
6. done

find the location of the dev database

We will work on the dev database, which should not have any important data in it.

grep SQLALCHEMY_DATABASE_URI etc/conf/dev.conf

create a new migration

This works by comparing python model to last migration version

make newmigration

This will create a new python script within the migrations/versions subdirectory of your application module directory. There will be two functions automatically created for you: upgrade() and downgrade(). The upgrade() function will individually add tables and columns to your old database schema until it matches your current model. The downgrade() function is the opposite. In this manner, it is possible to “roll back” to a previous schema.

rename the file with a short summary

cd ${MODULE}/migrations/versions
mv ${CHECKSUM}_.py ${CHECKSUM}_${SUMMARY}.py

edit the migration

• for sqlite, remove all drop_column() operations
• for sqlite, remove all create_foreign_key() operations

test the migration

• delete the database again (rm /tmp/daemon-dev.db)
• perform a new migration (make migrate)
• verify that it is empty (i.e. all tables are reflected)
• delete the empty migration file (rm ${MODULE}/migrations/versions/${CHECKSUM}_.py)

done

The migration is ready. It can now be applied in the production environment by specifying the configuration for the production system.

First, ensure you are using a configuration file that specifies the target database you will apply the migration to. Then, invoke the schema upgrade directly.

export SETTINGS=/etc/production.conf
manage.py db upgrade

An Example Migration File

It is easy to see how a migration uses SQLAlchemy directly to create tables if we examine an example. One of the migrations that ships with Flask-Diamond is in flask_diamond/migrations/versions/20f04b9598da_flask-diamond-020.py. In this case, the upgrade() function adds several new columns to the user table. The file looks like this:

"""update to flask-diamond 0.2.0

Revision ID: 20f04b9598da
Revises: cf0f5b45967
Create Date: 2015-02-07 22:54:24.608403

"""

# revision identifiers, used by Alembic.
revision = '20f04b9598da'
down_revision = 'cf0f5b45967'

from alembic import op
import sqlalchemy as sa


def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column('user', sa.Column('current_login_at', sa.DateTime(), nullable=True))
    op.add_column('user', sa.Column('current_login_ip', sa.String(length=255), nullable=True))
    op.add_column('user', sa.Column('last_login_at', sa.DateTime(), nullable=True))
    op.add_column('user', sa.Column('last_login_ip', sa.String(length=255), nullable=True))
    op.add_column('user', sa.Column('login_count', sa.Integer(), nullable=True))
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('user', 'login_count')
    op.drop_column('user', 'last_login_ip')
    op.drop_column('user', 'last_login_at')
    op.drop_column('user', 'current_login_ip')
    op.drop_column('user', 'current_login_at')
    ### end Alembic commands ###

Applying a Migration

To apply a migration to the development database, enter the virtualenv and run:

make migrate

This will inspect your database and automatically apply migrations, in order, until it is at the latest. By default, this applies the migration to your development database.

Migrations in Production

In order to affect the production database, you must set SETTINGS so that it points to your production configuration. Then, you must invoke Flask-Migrate explicitly, like so:

bin/manage.py db upgrade

Displaying a Migration as SQL

It can be helpful to inspect a migration before it is applied to the database. The following command will display a preview of the changes that will be made once a migration is applied:

bin/manage.py db upgrade --sql

Accessing Flask-Migrate directly

In fact, the full functionality of Flask-Migrate is easily available on the command line:

bin/manage.py db help

Version Control and Migrations

Because each migration has a unique checksum, and because each migration is in a separate file, it is easy to use a version control mechanism like git to closely control your schemas.

Footnotes

[1]	A database schema is a list of all the tables in a database, all the columns in those tables, and the data types for each column. Schemas are often expressed using SQL CREATE statements, which is a concise way of describing exactly which tables and columns need to exist.