Using sqlalchemy-migrate for managing a database

Managing database schema is a logistical nightmare if you want to handle it without any real migration tool.

Running our sites with the Pyramid framework and SQLAlchemy means we want a specific tool for this framework. sqlalchemy-migrate is one of the two popular choices.

Setup

First you need to install the tool which is as easy as adding it to a buildout or pip install:

$ pip install sqlalchemy-migrate

Or when using buildout update your buildout.cfg file

eggs =
    sqlalchemy-migrate
$ bin/buildout

This will install two binary apps migrate and migrate-repository of which the first is the only, initially, relevant one. Once installed we need to setup a DB schema repo for managing our database versions.

$ migrate create db_migrate "Project db migration"

This will create a new python package in the current directory called db_migrate which has a few basic files and a versions directory which will store our script for up/downgrading our database. Last part of the setup will be to create a manage script which will quickly load this database and our config files to save us having to pass them to the tool every time.

$ migrate manage dbmanage.py --repository=db_migrate --url=sqlite:///site.db

This will create a new file dbmanage.py in your current directory which is what you will use every time you want manage this database with this migration repository.

NOTE: Alternatively you could pass the --repository and --url to every migrate function you ever call: extremely tedious.

Last thing for setup is to tell our database that it's under version control. This involves migrate setting up a new table (by default called version_control) which has data on what version it is. To do this is a simple one-liner:

$ python dbmanage.py version_control

If you want to check out your database now you'll see a new table called version_control with the version set to 0.

Writing upgrade and downgrade scripts

You can write your upgrade and downgrade scripts in a number of different ways including SQL language or using SQLAlchemy in Python code. I'm going for #2 as it's the way we setup the databases so it'll keep everything in line. To build a version script python file ready for editing run the following command:

$ python dbmanage.py script "Add ordering to projects"

This will add a new file to your migration repo ./db_migrate/versions/001_Add_ordering_to_projects.py which is where your python code goes. Open this file up and you'll see two functions:

def upgrade(migrate_engine):
def downgrade(migrate_engine):

As is obvious by the built-in comments these are your scripts for making changes and undoing changes to your database for this version. Here is a simple example of adding a new column to an existing table:

def upgrade(migrate_engine):
    meta = MetaData(bind=migrate_engine)
    project = Table('projects', meta, autoload=True)
    order_c = Column('order', Integer(), default=10)
    order_c.create(project)

def downgrade(migrate_engine):
    meta = MetaData(bind=migrate_engine)
    project = Table('projects', meta, autoload=True)
    project.c.order.drop()

The upgrade loads the meta data from the database we pass to it. This is automatic so you don't need to define the schema's starting point for any upgrade.

Then we load the table we want to change, create a new column with standard SQLAlchemy code and create a column on the project table.

The downgrade should be easy enough to follow as it does the exact opposite: removes the column!

Now that we are happy with our script we head back to the command line and upgrade our database!

$ python dbmanage.py upgrade
0 -> 1...

Voila! The database has been upgraded from version 0 to 1, using our script. The project table now has a new Integer column called order. You can run this script on all working copies of this project so they are all in line, simple.

To downgrade, because you broke something or you aren't ready for a version, you run the following:

$ python dbmanage.py downgrade 0

This downgrades to the version specified as an integer. You can use the same version parameter with upgrade, if you only want to half upgrade a schema.