Tuesday, July 22, 2008

SQLAlchemy-Migrate for dummies

I'm gave sqlalchemy-migrate a try today. I like it, and I'm going to keep using it. The one downside is that it's a bit hard to find "the least you need to know" in the documentation, especially if you lean old-school like me and prefer to write your upgrade scripts in raw sql. So here's my stab at it.

Create a "repository" for upgrade scripts:

migrate create path/to/upgradescripts "comment"

Create your manage script. If you have development/production dbs with different connection urls, create two scripts with the same repository but different urls:

migrate manage dbmanage.py --repository=path/to/upgradescripts --url=db-connection-url

For each database, create the Migrate metadata (a migrate_version table):

./dbmanage.py version_control

Create an upgrade script. This will create a script [next version number]-[database type]-upgrade.sql in the "versions" subdirectory of your "repository." That's all, so you could certainly do this by hand if you prefer, but letting the script do it is less error-prone:

./dbmanage.py script_sql sqlite

Edit the script.

For each database, apply the upgrade:

./dbmanage.py upgrade

Repeat the script/upgrade process as needed. That's it! Everything else is optional!

(What this gives you is a process where all your developers can have their own local database for development, and all they have to do is "svn up; ./dbmanage.py upgrade" without having to worry about which upgrade scripts have been applied or not.)


Tim Lesher said...

Maybe it's just me, but every time I look at the migrate command line, I see "dbdamage.py" rather then "dbmanage.py"...

Anonymous said...

It's hard to find a good tutorial about using migrate, though. Especially if you want to use it from the Python app itself.