SQLAlchemy Migrations

Migrations are useful for emerging projects because they allow developers to evolve their models, and incrementally define schema change scripts so that old database records can be upgraded and downgraded easily.

When I started using Pylons, I set up a tinderbox to allow a 2nd copy to live for our team to play with the system and create data structures. In order to allow this data to live between major model changes, this meant I had to find a way to manage migrations. Using "sqlalchemy-migrate", I was able to achieve just that. Here's my quickstart on how to get up and running with sqlalchemy migrations under pylons. Now whenever I update my tinderbox (it's not automatic quite yet), I just run "svn up; python dbmanage.py upgrade".

Quickstart Guide:

1. Install sqlalchemy-migrate

sudo easy_install sqlalchemy-migrate

2. Go to your project directory

cd myapp

3. Create your sqlalchemy-migrate repository in your project

migrate create myapp/migrations/ "MyApp"

This will create the directory "migrations" in your myapp/myapp/ directory.

4. Add the migrate tables to your database:

migrate version_control mysql://user:pass@myhost:3306/database myapp

5. See what version the repository is at:

migrate version banyan/migrations

4. Create the dbmanage script. This is mostly a configuration script to make sa-migrate easier to use.

migrate manage dbmanage.py --repository=myapp/migrations/ --url=mysql://user:pass@myhost:3306/database

5. Create the first migration script

migrate script "Add initial tables"

Other documentation says to do 'migrate script script.py', but this didn't work for me properly. What this will do is create

6. Edit your first migration script.

Open up the file in your favorite editor, and change it to meet your needs. In my case, it looks like this:

import time, datetime
from sqlalchemy import *
from migrate import *
 
meta = MetaData(migrate_engine)
 
users_table = Table('users', meta,
                    Column('id', Integer, primary_key=True),
                    Column('email_address', Unicode(255), unique=True),
                    Column('display_name', Unicode(255)),
                    Column('password', Unicode(40)),
                    Column('created', DateTime, default=datetime.datetime.now)
                    )
 
def upgrade():
    # Upgrade operations
    users_table.create()
 
def downgrade():
    # Operations to reverse the above upgrade
    users_table.drop()

7. Test your new script.

python dbmanage.py test

8. "Commit" your changes.

python dbmanage.py upgrade

Note, the documentation I read said to use the command "commit" instead of "upgrade". I have no idea why this is, the dbmanage script doesn't know what to do when you ask it to commit. When you upgrade though, it will run the changes to your database, and change the migrations version from 0 to 1.

9. Rinse and repeat.
Do steps 5 through 8 to whenever you have new database changes you want to add to your database.

Here's another example to show an alter. The module migrate.changeset allows you to alters that SQLAlchemy at this time does not.

1. Create another revision script.
Lets say we wanted to change the column display_name to first_name, and add a last_name column.

python dbmanage script "Change display_name column to real names"

2. Edit that file.

My one looks like this:

import time, datetime
from sqlalchemy import *
from migrate import *
import migrate.changeset
 
meta = MetaData(migrate_engine)
 
users_table_new = Table('users', meta,
                    Column('id', Integer, primary_key=True),
                    Column('email_address', Unicode(255), unique=True),
                    Column('first_name', Unicode(255)),
                    Column('last_name', Unicode(255)),
                    Column('password', Unicode(40)),
                    Column('created', DateTime, default=datetime.datetime.now)
                    )
users_table_old = Table('users', meta,
                    Column('id', Integer, primary_key=True),
                    Column('email_address', Unicode(255), unique=True),
                    Column('display_name', Unicode(255)),
                    Column('password', Unicode(40)),
                    Column('created', DateTime, default=datetime.datetime.now),
                    useexisting=True
                    )
 
def upgrade():
    # This assumes we're moving all existing accounts to have their first_name
    # be their previous display_name
    users_table_old.c.display_name.alter(name="first_name")
    # Note the above users_table_new has a new last_name column already defined in
    # it. This column has not been created yet in the database, so we will do that here:
    users_table_new.c.last_name.create()
 
def downgrade():
    # Again, for every upgrade, we have to have a downgrade
    users_table_new.c.first_name.alter(name="display_name")
    # Any downgrade will result in loss of last_name data with this script.
    users_table_new.c.last_name.drop()

3. Test the changes (again).

python dbmanage test

Everything good?

4. "Commit" the changes (yes, again):

python dbmanage upgrade

Awesome! If you have anything to add or catch any mistakes, please drop me a comment.