Skip to content

Database Migrations

Alembic owns the schema. Neither the API nor the worker creates or alters a table: they check on startup that the database is at the revision they were built against, and stop if it is not.

An application that quietly altered the schema it found would make deploys unreviewable, would apply DDL nobody read at a moment nobody chose, and would race every other replica starting at the same time.

A fresh database

createdb forms
export FORMS_DATABASE_URL=postgresql+psycopg://forms:forms@localhost:5432/forms
alembic upgrade head

That is the whole setup. Migrations read FORMS_DATABASE_URL, the same setting the application reads, so there is nothing extra to configure and no credentials in any tracked file.

To migrate a different database without changing your environment:

alembic -x database_url=postgresql+psycopg://user:pass@host/other upgrade head

Upgrading an existing database

alembic upgrade head

Run it before starting the new build. The usual order for a deploy is: stop the old processes, migrate, start the new ones. Migrating while an old build is still running is only safe if the change happens to be backwards compatible, and this project does not promise that for any particular migration.

The startup check

Both processes refuse to serve against a schema they were not built for:

the database is at migration '0003' but this build expects '0004'.
Run 'alembic upgrade head' before starting.

A database that has never been migrated reports:

the database has no schema. Run 'alembic upgrade head' before starting.

The operator CLI makes the same check, so a management key cannot be written into a database at a revision this build does not understand.

Useful commands

alembic current             # what revision is this database at
alembic history --verbose   # what revisions exist
alembic upgrade head --sql  # print the SQL instead of applying it, for review
alembic downgrade -1        # step back one revision

--sql is worth knowing about: it lets whoever owns the production database read the DDL before anything touches it.

SQLite

Migrations through 0004 replay against SQLite too, in batch mode, because SQLite cannot ALTER a column in place and has to rebuild the table instead. 0006 is a plain ADD COLUMN, which SQLite performs happily, but it sits behind 0005 and so is unreachable there for the reason below.

A fresh SQLite database cannot reach head

Revision 0005 alters webhook_deliveries and delivery_attempts directly rather than through batch mode, because both tables carry foreign keys between them and rebuilding one while SQLite enforces the other's key is not a rebuild batch mode can do safely. That revision's own docstring explains the reasoning: it is written for PostgreSQL, which performs every one of its operations in place, and SQLite is not its migration target.

So alembic upgrade head against a fresh SQLite database applies 0001 through 0004 and then fails on 0005 with a plain SQL syntax error, not with a schema this build can serve. There is no workaround short of PostgreSQL: a database stopped at 0004 is a schema this build's startup check refuses to run against, the same as any other outdated revision.

This is why the test suite does not migrate its SQLite database at all: it builds the schema straight from the models with create_all and stamps it as fully migrated, and a PostgreSQL-only test asserts that what that produces and what the real migrations produce are the same schema. See Drift is a build failure and Limitations.

SQLite remains unsupported as a production target, and is no longer usable for trying the service out end to end either. Use PostgreSQL.

Writing a migration

alembic revision --autogenerate -m "what changed"

Read what autogenerate produces before committing it

Autogenerate is a starting point, not an answer. It does not always render custom column types in a usable way, and it cannot see anything the models do not declare.

Conventions this repository follows:

  • A migration never imports application code. Timestamps are written as sa.DateTime(timezone=True) rather than the application's UtcDateTime decorator. The DDL is identical, and a migration that imported application code would break the moment that code was refactored. A migration is a frozen record of a change, not a view of the current models.
  • Every constraint and index has an explicit name, through the metadata naming convention, so a later migration can reference it. An autogenerated foreign key name cannot be dropped portably.
  • A migration that adds a non-nullable column to a populated table adds it nullable, backfills it, then tightens it, with the tightening in op.batch_alter_table so SQLite can rebuild.
  • Every migration has a working downgrade. The PostgreSQL suite exercises the round trip on populated data.

Drift is a build failure

The PostgreSQL integration suite asserts that the migrations and the models describe the same schema, using Alembic's own compare_metadata. If they drift apart, CI fails rather than the difference surfacing in production.

That test is also what makes it safe for the fast test suite to build its schema with create_all and stamp it, instead of replaying every migration a few hundred times.

Other migration tests upgrade a PostgreSQL database that already holds endpoints, submissions, deliveries and attempts, and check that the data is exactly what it was afterwards, that a downgrade removes only what the newer revision added, and that the data survives that too. See Testing.

No zero-downtime story

There is none, and none is claimed. A migration that rewrites a table will lock it, and a build whose expected revision does not match the database refuses to start rather than serving against a schema it does not understand. Plan a deploy as migrate-then-restart.