database migrations for SQLAlchemy part duex

Well, as I was looking at making miruku more reliant on sqlalchemy-migrate, I discovered the expirmental command: migrate update_db_from_model! So much for an afternoons work, but at least I’m much more familiar with the migration tools. So here’s how I’ve implemented an auto upgrade for Pylons.

First, easy_install sqlalchemy-migrate

Now, in your pylons development.ini, add the following to app:main:

# SQLAlchemy migration
# if managed, the migration repository is here
migrate.repository = %(here)s/changes
# automatically do database upgrades
migrate.auto = 1

Then, in PRJNAME.config.environment, in load_environment, after the call to init_model add the following:

    
    # sqlalchemy auto migration
    if asbool(config.get('migrate.auto')):
        try:
            # managed upgrades
            cschema = schema.ControlledSchema.create(engine, config['migrate.repository'])
            cschema.update_db_from_model(meta.Base.metadata)
        except exceptions.InvalidRepositoryError, e:
            # unmanaged upgrades
            diff = schemadiff.getDiffOfModelAgainstDatabase(
                meta.Base.metadata, engine, excludeTables=None)
            genmodel.ModelGenerator(diff).applyModel()

Of course, don’t forget the imports you need:

from paste.deploy.converters import asbool
from migrate.versioning.util import load_model
from migrate.versioning import exceptions, genmodel, schemadiff, schema


Run your app: paster serve –reload development.ini

Now with most basic changes in the model, when paste reloads your database will be updated to reflect the new model. This of course can fail sometimes, such as adding a new column with nullable=False.

I’m only using the unmanaged upgrades right now, so the managed section may need some tweaking, I’ll see when I get there.