database migrations for SQLAlchemy

It was a dark and stormy day, so I skipped stepping outside and worked a while. There’s Vancouver for you.

At Mozilla Messaging we’ve been using Pylons and SQLAlchemy on a couple projects.  One of the features this setup misses that Django and Rails provides is database migration.  Looking around, there’s but one choice for SA since it’s not built in, sqlalchemy-migrate.  Oh, there’s that other project, miruku.  Hmm.

At first glance, miruku seems much simpler, and actually it is a layer on top of migrate-sqlalchemy (sort of).  It doesn’t have upgrade/downgrade versioning, you can only upgrade, but you also don’t have to build up a set of migration scripts.  Just change the table, run the upgrade command, move on.  Sounds much better than figuring out, building and maintaining a set of migration scripts (of course it may not be) at least during development.  So that is where I focused my time.

The first problem I ran into is that it didn’t work with SA declarative classes.  The second problem, despite it being saved from oblivion, there doesn’t seem to be any active maintenance.

Damn, the gauntlet is thrown.  There goes my Sunday afternoon.

The result is a new, working miruku.  I’ve only tested it lightly in a simple Pylons app, and it’s unit tests are specific to using Elixir, but it upgraded my tables for me, even correctly handling a drop column in SQLite (which doesn’t support drop column, miruku does some heavy lifting to make this work).

To use with Pylons, I have to add a section to the ini files (how have ini files survived this long?)
sqlalchemy.PRJNAME.url = sqlite://path
miruku.PRJNAME.metadata = PRJNAME.model.meta:Base.metadata

Then setup the miruku support by running miruku:

miruku create --config my.ini --section app:miruku

After that, I can run upgrades with:

miruku upgrade --config my.ini --section app:miruku

There’s a bit more work I plan on doing it, then it may well bit rot again, but here’s my personal wish list for miruku:

  • support altering column properties, miruku only supports add and drop column
  • use more of sqlalchemy-migrate to reduce code size
  • examine table level changes to see if anything major is missing in miruku
  • paster command support
  • better Pylons/Paste integration for configs

If you’re interested in trying it out, drop me a note and let me know how it works for you. You can find miruku in my bitbucket repo.

0 Responses to “database migrations for SQLAlchemy”

Comments are currently closed.