How to efficiently manage frequent schema changes using sqlalchemy?

Question

I'm programming a web application using sqlalchemy. Everything was smooth during the first phase of development when the site was not in production. I could easily change the database schema by simply deleting the old sqlite database and creating a new one from scratch.

Now the site is in production and I need to preserve the data, but I still want to keep my original development speed by easily converting the database to the new schema.

So let's say that I have model.py at revision 50 and model.py a revision 75, describing the schema of the database. Between those two schema most changes are trivial, for example a new column is declared with a default value and I just want to add this default value to old records.

Eventually a few changes may not be trivial and require some pre-computation.

How do (or would) you handle fast changing web applications with, say, one or two new version of the production code per day ?

By the way, the site is written in Pylons if this makes any difference.

Answer 1

Alembic is a new database migrations tool, written by the author of SQLAlchemy. I've found it much easier to use than sqlalchemy-migrate. It also works seamlessly with Flask-SQLAlchemy.

Auto generate the schema migration script from your SQLAlchemy models:

alembic revision --autogenerate -m "description of changes" 

Then apply the new schema changes to your database:

alembic upgrade head 

More info here: http://readthedocs.org/docs/alembic/

Answer 2

What we do.

1. Use "major version"."minor version" identification of your applications. Major version is the schema version number. The major number is no some random "enough new functionality" kind of thing. It's a formal declaration of compatibility with database schema.

   Release 2.3 and 2.4 both use schema version 2.

   Release 3.1 uses the version 3 schema.

2. Make the schema version very, very visible. For SQLite, this means keep the schema version number in the database file name. For MySQL, use the database name.

3. Write migration scripts. 2to3.py, 3to4.py. These scripts work in two phases. (1) Query the old data into the new structure creating simple CSV or JSON files. (2) Load the new structure from the simple CSV or JSON files with no further processing. These extract files -- because they're in the proper structure, are fast to load and can easily be used as unit test fixtures. Also, you never have two databases open at the same time. This makes the scripts slightly simpler. Finally, the load files can be used to move the data to another database server.

It's very, very hard to "automate" schema migration. It's easy (and common) to have database surgery so profound that an automated script can't easily map data from old schema to new schema.