Data and Schema Migrations

Migrations

The story so far...

The project originally didn't have any migrations. Just a SQL file.

We started keeping track of changes to the original schema in a directory named sql/migrations. We'd write consecutively numbered SQL files to be applied to existing installations, and we'd export a new models.sql file from our migrated schema. This worked alright, and had we developed some way to know which migrations had been applied, it may have sufficed if we continued to use straight up SQL.

Now we're using SQLAlchemy going forward to manage the models in CBU. We're not redoing all the models from scratch though. Not yet. Our migrations can begin to be handled at the ORM level though. We now use sqlalchemy-migrate to manage migrations.

Getting set up for migrations

To set up your database to record migration versions, first, create a migration management script:

migrate manage manage.py --repository=giveaminute/migrations/ --url=mysql://[username]:[password]@localhost/[dbname]

This provides you a shortcut so that you don't have to specify your repository and db url each time. Next run:

python manage.py version_control

This will initialize your database with the migrations table. Finally, if you have data in your database that you don't want to go away, edit the giveaminute/migrations/versions/001_Initial_models.py file and comment out the entire block that begins with open(....

Running migrations

To migrate your database to the most recent version, run:

python manage.py upgrade

Creating new migrations

You can write new migrations in either SQL or Python. Some useful references for writing Python migrations include:

• Column operations

Regenerating models.sql

After you've written a new set of migrations, you'll want to generate a new models.sql file for the project. First, back up your current database:

mysqldump -u [dbuser] -p [dbname] > backup.sql

Then, clear the database:

mysql -u [dbuser] -p [dbname] <<EOF
    drop database [dbname];
    create database [dbname]
EOF

Now run the migrations from start to finish to generate a blank database with the latest updates:

python manage.py version_control
python manage.py upgrade

You want to generate your database by running the migrations so that the generated DDL includes the most recent migration number. This way, when future migrations are supplied, you can safely run upgrade and the database will know which migration it needs to start with.

Finally, dump your database. This will become the new models.sql:

mysqldump -u [dbuser] -p [dbname] > sql/models.sql

At this point you can restore your old data:

mysql -u [dbuser] -p [dbname] < backup.sql