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.
- find the location of the dev database
- create a new migration
- rename the file with a short summary
- edit the migration
- test the migration
- 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. |