Database Migrations with Alembic¶

The directory packages/divbase-api/src/divbase_api/migrations contains database migrations for the DivBase postgres db instance. We use Alembic to manage database schema changes.

Overview¶

Migration files/scripts are stored in versions/ with descriptive names: YYYY-MM-DD_description.py
Migrations are generated by comparing SQLAlchemy models with the current database instance deployed using the local docker compose stack.
Migrations are applied automatically in local development when the docker compose stack starts up using an init-container (db-migrator).
In production/deployed environments, migrations should be applied as part of the deployment process - multi step and not automatic, see below.
In deployed environments with actual user data, database migrations are a dangerous operation that can lead to data loss if not done carefully. A backup of the database must be taken before applying migrations in production.
FastAPI's lifespan event automatically checks if migrations are up to date during startup. - will raise an error if not.
pytest test/migrations can run tests to ensure all migrations can be applied cleanly to a fresh database.
Celery managed tables (celery_taskmeta and celery_groupmeta) are excluded from alembic (see the migrations/env.py), as Celery handles their creation and updates. Keep this in mind when creating migrations.

Creating a New Migration¶

Note: The FastAPI lifespan event checks (but does not apply) that all migration scripts have been applied on startup - aka at the latest version.

This means when you create a new migration script in watch mode, the app will error on restart until the migration is applied. If you instead make your changes using compose up, then make sure to restart the stack before generating the migration script (step 2) so the changes to your db models are actually included.

The fastapi lifespan event is a little awkward yes, but it is primarily for protecting the deployed instances.

1. Make changes to the SQLAlchemy models¶

If you add a new model, include it in the packages/divbase-api/src/divbase_api/models/__init__.py so Alembic can detect it.

Make sure the divbase stack is running:

docker compose -f docker/divbase_compose.yaml down && docker compose -f docker/divbase_compose.yaml watch

2. Generate the migration script by entering the FastAPI container¶

# Enter the running FastAPI container
docker compose -f docker/divbase_compose.yaml exec -it fastapi sh

# Generate migration (use descriptive names). The data will be prepended automatically so it does not need to be included in the name
alembic revision --autogenerate -m "write_your_useful_slug_here"

# Exit container - if needed - see NOTE below
exit

NOTE: If you've been using docker compose watch up to this point, it is expected that now the FastAPI service will be in a crashed state due to pending migrations -> the lifespan event will fail when the app restarts. NOTE2 The commands can be run as a one-liner with: docker compose -f docker/divbase_compose.yaml exec -it fastapi alembic revision --autogenerate -m "write_your_useful_slug_here"

3. Review the generated migration file¶

The migration script created in the container is automatically synced to your host machine (packages/divbase-api/src/divbase_api/migrations/versions/)
Open the created migration script and check that the auto generated changes correctly represent your intended changes
Be extra careful if you rename a table, or if you are dropping columns or tables.
Modify the upgrade() and downgrade() functions if needed to actually achieve the desired schema changes.
If you have created a new Enum you need to append a drop statement into the downgrade function for the enum.

# rest of downgrade function
# where projectroles is the lower case name of the enum you created
op.execute("DROP TYPE IF EXISTS projectroles")

See the bottom of the first migration script (2025-12-04_initial_migration.py) for an example of this.

You could also run the automatic migration tests now to help verify your migrations scripts are correct.

pytest -s tests/migrations

4. Test out the migration¶

In local development, migrations are applied automatically when the stack starts up using the db-migrator init-container.
The db-migrator ensures that migrations are applied before starting FastAPI and worker services (via condition: service_completed_successfully).
Do not down the volumes (e.g. docker compose -f docker/divbase_compose.yaml down -v), this is a good initial test to ensure the migration works as expected.
To test the migration:

# interrupt if you have a process already running.
docker compose -f docker/divbase_compose.yaml down && docker compose -f docker/divbase_compose.yaml watch

This will make the db-migrator init-container run the migrations, and then start fastapi and workers if successful. If issues check the db-migrator logs.

You can furthermore check that an update to a table schema has been applied as intended by running the postgreSQL \d <TABLE_NAME> command to inspect how the table looks like in the database engine after the restart: docker exec -it divbase-postgres-1 psql -U divbase_user -d divbase_db -c '\d "<TABLE_NAME>";'

To check the current status of applied migrations in the database, you can run:

docker exec -it divbase-postgres-1 psql -U divbase_user -d divbase_db -c 'SELECT * FROM "alembic_version";'

You can also run the pytest-alembic tests to further validate the newly created migration script.

pytest tests/migrations

Migrations in local development (swapping between branches/downgrading)¶

If you pull a branch with new migrations, you will need to restart the stack to apply the new migrations (see above).
If you work on a branch with new migrations, and then switch back to main (or another branch without those migrations), you will need to downgrade the database to the previous migration version, and then restart the stack to apply the downgraded schema, here are the steps you can follows:

Protocol¶

Be in the branch that has the latest migration script (as you need the downgrade function from the latest migration script so trying this from any other branch will not work).

Use the db-migrator container to run the downgrade command (so adjust the command/entry point in the docker compose file to something like this:

# From: docker/divbase_compose.yaml command: ["alembic", "upgrade", "head"]
["alembic", "downgrade", "<REVISION_ID_TO_DOWNGRADE_TO>"] # or -1 to downgrade one step

Down everything
Undo the change to the db-migrator container in the docker compose file.
Swap to the other branch (e.g. main) that does not have the new migration script.
docker compose up/watch and it should just work as you will now be at "head" again.

The validation run by the fastapi lifespan event on startup will help tell you if you succeded or not, as it will error if you are not at the head revision for that branch. So if fastapi is working, it's probably working.

Production Deployment¶

Documentation on how to run migrations in production/deployed environments is covered in our private repository, argocd-divbase.

Troubleshooting¶

"No changes in schema detected"¶

Ensure your models are properly imported in models/base.py
Check that your model changes are actually different from the database.

Migration fails to apply¶

Check the migration file for syntax errors
Ensure the database is in the expected state
Use alembic current to check current migration status or inspect the alembic_version table in the database (see section 4).

Starlette admin/admin panel not showing the models¶

You need the models in your src to match the postgres schema. So if you have pending changes (that you have or have not created migrations for) they won't display until you've actually done the migration.

Migrations that include new fields with non-nullable constraints¶

One way to solve this is to modify the migration scripts upgrade command as follows:

In this example, we're adding a new column "organisation" to the user table, and it cannot be null (and we already have users...), so we need to provide a default value for existing rows.

def upgrade() -> None:
    # Add the server_default param to the add operation for the new column
    # "server_default=sa.text("'Not specified'") was added to below command
    # We use sa.text to ensure the default value is set as a string in the database,
    # see e.g. here for why: https://github.com/sqlalchemy/alembic/discussions/1433
    op.add_column('user', sa.Column('organisation', sa.String(length=200), nullable=False, server_default=sa.text("'Not specified'"),))

    # Any rows in the database table that existed before the migration will now be populated with the default value 'Not specified'.
    # At the end of the same migration script, remove the server_default param from the column (with `server_default=None`) - so our models match exactly with the database schema.
    # New rows will now require the application to provide a value for the organisation column.
    op.alter_column('user', 'organisation', server_default=None)

You don't need to set the server_default in the models.py, just in the migration script.

Alternatively you could set the server_default in the models.py, but then you should be comfortable with it always having a default value. There are also other ways to solve this problem...