Database migrations

To bring a database up to date, run make db_migrate. It applies any pending migrations, with PYTHONPATH and the config flag already set. This is one step of updating an existing checkout.

The rest of this page is about writing migrations, which you need only if you are changing the database schema yourself.

Setting up

Alembic needs to know which revision your database is already at. Recording it is called stamping, and it updates only alembic’s own bookkeeping, without changing the schema.

Stamp a database only if its schema already matches the models, such as one you have just created by running make load_demo_data on the latest main branch commit (this should happen on a vanilla main branch, i.e. without any of your changes to the database schema):

PYTHONPATH=. alembic -x config=config.yaml stamp head

Do not stamp a database that is behind the models, and in particular do not stamp one to clear a Multiple head revisions are present error. Alembic will treat every pending migration as applied, make db_migrate will then have nothing left to do, and the app will fail on the columns and tables those migrations were meant to add.

If that has already happened, the recorded revision can be worked out again from the schema itself:

PYTHONPATH=. python tools/db_revision_from_schema.py

It compares the tables and columns each migration creates against the ones the database has, and reports the revision it actually matches. With --stamp it sets alembic_version back to that revision, so make db_migrate can replay the migrations that were skipped, leaving the data in place.

Generate migration script

To generate a migration script, after having stamped the latest main commit (see above), ensure that the app is stopped, check out the branch with the relevant DB schema changes, and run the following:

PYTHONPATH=. alembic -x config=config.yaml revision --autogenerate -m "Revision description"

Review the resulting migration file under alembic/versions at hand of the documentation.

Autogenerate compares the models to the current state of your database, so run it only after applying any pending migrations. Otherwise the generated script will also contain the changes those migrations make.

Delete the file if it does not describe your own schema changes. Any file in alembic/versions counts as a revision, including one that only you have.

Applying migration scripts

If the database has been stamped, as outlined above, the migration manager service will automatically apply any pending migration scripts upon starting the app.

To manually apply migration scripts, after ensuring that the app is stopped, check out the branch with pending migration scripts, and run the following to upgrade:

PYTHONPATH=. alembic -x config=config.yaml upgrade head

Multiple heads

alembic upgrade head fails if more than one revision is a head, since it cannot tell which one is meant:

Multiple head revisions are present for given argument 'head'

To see them:

PYTHONPATH=. alembic -x config=config.yaml heads

Usually one of them is a leftover revision of your own, from an earlier autogenerate run. git status shows it as untracked under alembic/versions. Check whether it has been applied:

PYTHONPATH=. alembic -x config=config.yaml current

If it does not appear there, delete the file. The remaining head is then the one to upgrade to. Do not merge the two, as that would keep the unwanted revision in the history.

If both heads are revisions that belong in the repository, a branch has added a migration alongside one that landed on main. Either edit your migration’s down_revision to point at main’s head (simplest while your migration is unreleased and nobody has applied it), or merge the two:

PYTHONPATH=. alembic -x config=config.yaml merge -m "merge heads" <rev1> <rev2>

The Test SkyPortal migrations CI job builds a database from the models on main, applies the branch’s migrations to it, and compares the result against a database built from the models on the branch, so it will report a divergence before it reaches anyone else.