Database Upgrade with SqlAlchemy Migrate

The last article did introduce SqlAlchemy as a potential tool for developing Audacious.
I'm still looking at Sql Alchemy YouTube video... and I must admit that it looks a great tool.

In the meanwhile, I was also concerned by the PostgresSql database setup and upgrade with SqlAlchemy.
A software developpement live cycle imply regular database update/upgrade in both development and production environment where schema (and data) could be upgraded to the latest version.

Home Made Software
Until know, I was using an Home Made tool that query the database to extract the current schema version number (something written by the home made Upgrade software) and then execute all the update statement over that version. Each update executed one after the other.

This is simple and efficient... you only update your database with this tools (even in developement environnement) and you can be ensured that changes would also been applied smoothly into the production database.

The update files contains a series of SQL statements that are all identified by a unique and increasing number.
Example of db-update.dat
[001.001]
type=sql
descr=first sql to create the first table
content=
...sql..statement...here...

[001.002]
type=sql
descr=add user table
content=
....other...sql...statement....

Home Made Software: the limitations
This approach rocks and is reliable. However, it is tuned for only one target database server (eg: either PostgreSql, either MsSqlServer, either ...).
It is still possible to maintain one file per kind of database (or limit ourself to only one database kind).

However, one of the Advandages of SqlAlchemy is to easlisy switch from one database to the other.

So, the best would be to consider database upgrading with SqlAlchemy himself... the response is available in the project Alchemy-migrate

Alchemy Migrate

Alchemy-migrate was initialy released on Google Code and now available on GitHub .
This tool and project is fully documented! :-)

See the sqlalchemy-migrate-doc-0.7.2.pdf document.This document properly explain how SqlAlchemy-migrate works and how to use it... the principles are similars to the Home Made approach and allows upgrade/downgrade the database with SqlAlchemy code or with SQL Statements.
That's pretty nice... maybe a bit cumbersome to many newcomer but definitively the path to follow when we need to control and tame the upgrade process of the database.

The online documentation is also available here on readthedocs.org

Alternative: Alembic
I did also read some thread about Alembic.
This manage revision of schema as revision of source file. It then store the differences between revisions to be reapply them on other database (eg: your production database).
With this approach the user modifies its database with tools... and the Alembic software tries to figure out the changes in the schema.Those changes are collected and registered to be re-applied later on the production database.
If this approach is a good idea, it don't really like it because it do the job "at best" with the changes you applied in your database.
If you accidently drop an unrelated table or rename a field (or table) during your test/development phase, this will also been applied to the production database.
That accidental operation not identified during developement may kill your production setup (because of the number of users using it).

So, I do prefer write the changes up-front. When I do this, it is clear in my mind that changes will be applied to development environnement but also in production environment.
So, even if the operation is a bit more cumbersome, you always think twice before doing it. And thinking twice is better when creating softwares that manage "money".

My preference will go to something like SqlAlchemy-migrate.