Schema management with SQLAlchemy
At work I’m developing a database system for tracking quantum mechanics calculations. It’s been done before, but a) this focuses more on job tracking than job submission and analysis, b) it’s really more about the command-line side of things with a web-based search, c) it’s more about some home-grown dynamics calculations than general-purpose “QM for the masses”, and d) it’s what a professor hired me to do, so I don’t ask questions.
The core of the system is Python-based, with database handling care of SQLAlchemy and a web interface using Pylons. Pylons is a mixed blessing. It’s relatively lean and eminently customizable, but it has the feel of a large number of developing (i.e. beta) projects glued together with chewing gum. They’re very high-quality beta, and the chewing gum is well-placed. For the most part, it’s better than inventing or re-inventing a web framework. My main gripe is that the documentation is scattered everywhere, and the bulk of Pylons-specific documentation is not a reference work, but a set of FAQs and cookbook recipes. That said, it’s the least intrusive framework I investigated, and I’m sticking with it for now. If I have spare time, perhaps I’ll contribute some development effort or some documentation.
The question of the day relates not to Pylons but SQLAlchemy. I’m working on a feature enhancement which will require some minor database restructuring, which raises the broader issue of schema migration. So far, there have only been beta deployments of this software, so hand-coded migration scripts have been much handier than anything else. As soon as the package is released to the project director’s collaborators, it’ll need to be a more automatic process.
So, just having gone public on this blog, and not knowing if there will be any readership, I pose the question: what are people’s experiences with schema migration? I’m poking around sqlalchemy-migrate. It seems well-thought-out, but I have two concerns:
- The version management is not exactly lightweight. I currently have a one-row entry in the DB for schema version. I’d rather not have to add another table to the DB, or another entire tree in the source code.
- The fewer dependent packages I have to make scientific end-users install, the less trouble support will be. It’d be great not to have to include another package in the installation instructions, and it’s one less thing that can break.
So, as I said…comments on experiences with schema migration would be helpful.
Tags: Pylons, Python, SQLAlchemy
You can comment below, or link to this permanent URL from your own site.