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]

In this case the repository path is under the source tree, and should be in version control from the latest version. 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(....

(Note: it's not clear whether the above is true - ie about commenting out the open() block in 001...

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. Python is recommended, as sqlalchemy-migrate can do both upgrade and downgrade migrations with Python scripts. Always be safe and have a way to reverse a particular migration. Some useful references for writing Python migrations include:

• Column operations

In order to create the new migration, run the following command, which will add a new file with some basic scaffolding to a file similar to: giveaminute/migrations/versions/XXX_action_taken_by_migration.py. You can then add the necessary logic (see the other migrations for examples).:

python manage.py script "Action taken by migration"

Remember to (first backup your database then) test your migrations with:

python manage.py test

This will run your upgrade and downgrade functions to ensure that they return the database to the same state. Backup your database before running test; if the test doesn't go right the first time, you should always have the backup to restore your database and try again.

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.

NOTE: It is recommended that you use a separate database for this, something like cbu_blank, so that you don't risk losing the information in your main database. However, if you don't want to create a separate DB, back up your current database first:

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

Clear the database that you will use as your blank:

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 giveaminute/migrations/manage.py version_control mysql://[dbuser]:[dbpassword]@localhost/[dbname] giveaminute/migrations
python giveaminute/migrations/manage.py upgrade mysql://[dbuser]:[dbpassword]@localhost/[dbname] giveaminute/migrations

.

NOTE: If you are using your main DB (not recommended) and have set up migrations previously, you can simply run:

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

.

NOTE: If you have used your primary database, at this point you can restore your old data:

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

There is a script template to make it easier for you to generate a models.sql file that can be found in scripts/generate_models.sh.template in the root of the repository. Replace the variables in the template with the appropriate values for your setup, save it as generate_models.sh, and make it executable with chmod +x generate_models.sh. It includes the backup and restore operations, but it is again recommended to use a database other than your primary (why tempt fate?). Basics:

cp ./scripts/generate_models.sh.template generate_models.sh
(edit variables)
./scripts/generate_models.sh

Troubleshooting

You may get:

raise errorclass, errorvalue
sqlalchemy.exc.OperationalError: (OperationalError) (1060, "Duplicate column name 'feedback_type'") '\nALTER TABLE site_feedback ADD feedback_type VARCHAR(100)' ()

This problem is fixed by the latest version of migration 006, which adds some exception handling to the create_column.

If you're migrating the data, you may end up with:

OperationalError) (1054, "Unknown column 'user.full_display_name' in 'field list'"

The problem is that the Initial_models_asof_2.0.3.sql does not alter table structure, and existing tables may not have this field. Migration 007 handles this.