George V. Reilly

Alembic: Data Migrations

We use Alembic to perform schema migrations whenever we add (or drop) tables or columns from our databases. It's less well known that Alembic can also perform data migrations, updating existing data in tables.

Here's an example adapted from a migration I put together this afternoon. I added a non-NULL Boolean stooge column to the old_timers table, with a default value of FALSE. I wanted to update certain rows to have stooge=TRUE as part of the migration. The following works with PostgreSQL.

Note the server_de­fault=sa.false() in the de­c­la­ra­tion of the stooge column, which is needed to initially set all instances of stooge=FALSE. I then declare a table which has only the continue.