How to efficiently manage frequent schema changes using sqlalchemy?

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/

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.

Use sqlalchemy-migrate.

It is designed to support an agile approach to database design, and make it easier to keep development and production databases in sync, as schema changes are required. It makes schema versioning easy.

Think of it as a version control for your database schema. You commit each schema change to it, and it will be able to go forwards/backwards on the schema versions. That way you can upgrade a client and it will know exactly which set of changes to apply on that client's database.

It does what S.Lott proposes in his answer, automatically for you. Makes a hard thing easy.