« FreeBSD 7.0: installing psycopg 2.07
Replaying apache log files with python and twill »

SQLAlchemy-Migrate upgrade scripts in a transaction

SQLAlchemy Migrate is a really good tool for managing database upgrades for SQLAlchemy centric projects. I've been using it for 6 months on New Metal Army and I haven't had a screwed website upgrade yet!

For those that don't know SQLAlchemy-migrate allows you to version control database changes and easily upgrade and downgrade a database. Basically you write a python script with two functions: upgrade and downgrade. You test the script against the database, commit it to the SQLAlchemy-migrate version repository (not to be confused with your source control mechanism). Finally you upgrade your development database.

When it the time comes to deploy the new application you simply ask sqlalchemy-migrate to upgrade your database to the current version. sqlalchemy-migrate reads the current version of your schema from the database (via a custom table it inserts) and proceeds to upgrade your schema by running each upgrade script in turn.

Often you want your upgrades and downgrades to run within a transaction. Not because you expect them to fail but because while writing them you don't wont to leave the database partially upgraded or downgraded if your script fails. To do this I wrote a transaction decorator. Here is a template for an upgrade script:

  1. #!/usr/bin/env python
  2. # encoding: utf-8
  3.  
  4. from datetime import datetime
  5. from sqlalchemy import *
  6. from migrate import *
  7. from migrate.changeset import *
  8.  
  9. metadata = MetaData(migrate_engine)
  10.  
  11. def transaction(f, *args, **kwargs):
  12.     def wrapper(*args, **kwargs):
  13.         connection = migrate_engine.connect()
  14.         transaction = connection.begin()
  15.         try:
  16.             result = f(*args, **kwargs)
  17.             transaction.commit()
  18.             return result
  19.         except:
  20.             transaction.rollback()
  21.             raise
  22.         finally:
  23.             connection.close()
  24.  
  25.     wrapper.__name__ = f.__name__
  26.     wrapper.__dict__ = f.__dict__
  27.     wrapper.__doc__ = f.__doc__
  28.     return wrapper
  29.  
  30. @transaction
  31. def upgrade():
  32.     pass
  33.  
  34. @transaction
  35. def downgrade():
  36.     pass
  37.  

I fill in the upgrade and downgrade functions and I'm done :)
I include the decorator in every script as it's good practice to make your scripts as independent as possible. If you imported it from a module you may improve it in the future and inadvertently break your old downgrade scripts.

I hope this helps you version control your database schema and data.

This entry was posted on Sunday, July 27th, 2008 at 11:51 pm and is filed under NewMetalArmy, PostgreSQL, Python, SQLAlchemy, TurboGears. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

6 Responses to “SQLAlchemy-Migrate upgrade scripts in a transaction”

  1. Sacha Varma Says:
    July 28th, 2008 at 9:55 am

    A couple of questions:

    1. I can see that this would work when upgrading the database one revision at a time, but if sqlalchemy-migrate applies multiple updates in sequence, you’d probably want a transaction around the whole sequence? (i.e. I reckon sqlalchemy-migrate should do the rollback management)

    2. How do you manage updates that involve not just database changes, but also template & code changes? Is there a ‘turbogears-migrate’ that incorporates sqlalchemy-migrate?

  2. dazza Says:
    July 28th, 2008 at 10:13 am

    @Sacha Varma: your correct, there is no overall upgrade wrapper. Indeed it would be nice if all this was covered by the sqlalchemy-migrate. There has been some chatter on the sqlalchemy-migrate group about this. This is just a small step toward a bigger goal.

    To manage code and database changes I version control the migrate repository in svn. So I can be sure that the code I update checkout syncs with upgrade scripts I am about to run.

  3. Sacha Varma Says:
    July 28th, 2008 at 10:21 am

    If you have an old model.py (A) and a new model.py (B), do you have to write the code to upgrade from A to B, or is there a tool that “diffs” the two and spits out the code to do the upgrade/downgrade?

  4. dazza Says:
    July 28th, 2008 at 10:42 am

    @Sacha Varma: there is a new command added in the latest version which creates a script from a diff between the database and your ‘model’ make_update_script_for_model.

    See the change list here http://code.google.com/p/sqlalchemy-migrate/wiki/ChangesSince0_4_4. I haven’t used this myself but I think it does what you want.

  5. Sacha Varma Says:
    July 28th, 2008 at 11:32 am

    Python nub questions:

    #!/usr/bin/env python
    # encoding: utf-8

    /usr/bin/env? Why not just /usr/bin/python? Is this just a way of not having to explicitly specify the location of python, and have it pick it up from your $PATH?

    The encoding line presumably relates to the source code that follows?

  6. dazza Says:
    July 28th, 2008 at 12:46 pm

    @sacha: you are correct, by using env to launch python it will pick it up from the environment that the script is running in. It is just to stop a hard coded paths in the script. It’s not actually needed for this particular script since it is being invoked by an already running python interpreter but it’s part of my ‘new python template’ in TextMate.

    The encoding is to follow PEP 263 http://www.python.org/dev/peps/pep-0263/ It just makes sure all unicode literals embedded in the code are in UTF8. Everything I do is in UTF8 so it stops non UTF8 string slipping in to the code base and causing interesting and sometimes difficult to track down bugs.

Leave a Reply