Update an sqlite database schema with sqlalchemy and elixir

Question

I've created a python application which uses elixir/sqlalchemy to store data. The second release of the software requires any files created in the previous version to be updated in order to add/delete tables and columns.

My question is: how can I achieve this? I'm aware of sqlalchemy-migrate, but I must say I find it confusing. It doesn't mention what happens to existing data. Moreover, sqlite has reduced ALTER TABLE support, so what will migrate do if I try to delete a column? Are there any other approaches to using migrate?

Answer 1

What you're talking about is a well known and quite complex problem. It is known as database migration. Every good project have some policy that describes how database schema and data mutations should be applied to advance from one product version to the other.

Many frameworks such as Django or Ruby on Rails have a migration system built-in or available as a plug-in. Your case with SQLAlchemy has few options:

1. Do not use any system. Just write a /tmp/migrate.sql by hands, write down ALTER/DROP/CREATE statements, cross the fingers and apply it to your SQLite base. It is generally a bad idea since it is error-prone, but the choice is up to you. Absence of full-featured ALTER TABLE statement could be worked around by creating new column with desired properties with temporary name, copying all data from original column to it, removing original column, and renaming new column to the original name. The same technique could be used at table-level.
2. Use some 3rd-party migration system such as liquibase. Liquibase is cool, well designed and powerful, except one drawback. It is really buggy. I tried it for SQLite (and yes for SQLAlchemy, but it doesn't matter actually), and it failed to do some pretty basic things. I googled for a problems and found that they are known bugs.
3. Use SQLAlchemy-migrate you've mentioned. It is not as powerful as ROR-migrations which it was inspired by, neither it is as powerful as liquibase but it works. SQLite limitation could be worked around in same way.

And you've asked about what SQLAlchemy-migrate will do if you'll try to delete a column. Well, it will delete a column and so delete any data that was in it. Other columns in table will be left intact.

Answer 2

A more recent alternative to sqlalchemy-migrate is alembic, written by the author of SQLAlchemy himself. Although the latter ("same author") looks like a strong argument, a downside could be that it does not support table ALTERation with SQLite, i.e. it has no built-in workarounds for SQLite’s missing ALTER support. (One could argue that that is out of scope and could well be solved by a specialized python package or SQLite extension.)