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
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
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:
- 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.
- Write the application code such that it dynamically updates and populates the database.