Developing database migrations

LTD Keeper uses Alembic (via Flask-Migrate) to manage SQL database schema migrations. The entire schema is encoded in Alembic such that Alembic can build a DB schema from scratch. This page describes how to create a schema migration. See Applying Database Migrations in Production on Kubernetes for applying these migrations to production environments.

Creating a migration

Migrations occur whenever the DB schema changes. This includes adding, renaming or removing a column. This also includes changing indices or constraints on columns.

In the same Git commit as the DB model is being changed, create the migration:

FLASK_APP=keeper flask db migrate -m "message"

Use the message to briefly describes the schema change. This command will auto-generate a migration script in migrations/versions. Review that script and commit it.

Testing a migration

The migration can be tested locally if you have a development DB running a previous version of the schema:

make db-upgrade
make run

With a staging deployment, it is also possible to test migrations against a copy of the production database. This is now recommended practice.

Coding for migrations

By default, migrations only update the schema of a DB, not the data within it. It is up to the developer to package data updates that go along with the migration. There are two ways to do this:

1. Write an update script that can be run after the schema migration that populates any new columns or tables. Ideally, this code can be part of the Alembic migration itself using the sqlalchemy.execute API. See the blog post Alembic: Data Migrations for an example.

2. Write the application code such that it dynamically updates and populates the database.