Skip to content

Alembic Database Migration Workflow

This guide provides a reference for using Alembic to manage your database schema. Alembic allows you to make changes to your database structure in a systematic and version-controlled way.

Core Concepts

  • What is Alembic? Alembic is a database migration tool for Python. It allows you to modify your database schema (e.g., add a new table or column) and keep a versioned history of those changes.
  • Why use it? It prevents you from having to manually write SQL ALTER TABLE statements. It automatically compares your SQLModel classes to the current state of the database and generates the necessary migration scripts.

Standard Schema Change Workflow

This is the recommended process for making schema changes during development.

Step 1: Modify SQLModel Classes

Edit the model files in src/ca_biositing/datamodels/ca_biositing/datamodels/models/. Models are organized into domain subdirectories (e.g., field_sampling/, places/, external_data/).

If adding a new model, also add its import to models/__init__.py so it is registered with SQLModel.metadata.

Step 2: Auto-Generate a Migration

pixi run migrate-autogenerate -m "Description of your changes"

This compares your SQLModel class definitions against the current database state and generates a migration script in alembic/versions/.

Step 3: Review the Migration

Always review the generated migration script to ensure it accurately reflects your intended changes. Alembic autogenerate is not perfect and may miss some operations (such as changes to column types or server defaults).

Step 4: Apply the Migration

pixi run migrate

This applies alembic upgrade head against the Docker-hosted database.

Materialized Views

Materialized views are defined in src/ca_biositing/datamodels/ca_biositing/datamodels/views.py and are not auto-generated by Alembic. They must be managed through manual migration scripts.

Creating or modifying a view: Write a manual Alembic revision that uses CREATE MATERIALIZED VIEW / DROP MATERIALIZED VIEW in the upgrade() and downgrade() functions.

Refreshing views after data loads:

pixi run refresh-views

Checking view status:

pixi run schema-analytics-list

Manual Migration Workflow

If you need to perform manual Alembic operations (e.g., manual revisions or downgrades), follow these steps.

Important: Run Alembic commands locally using pixi run to ensure the correct environment and to avoid performance issues with Docker on macOS.

Create a manual revision (empty migration):

DATABASE_URL=postgresql://biocirv_user:biocirv_dev_password@localhost:5432/biocirv_db pixi run alembic revision -m "Your message"

Apply the migration:

pixi run migrate

Collaborative Workflows and Resetting the Database

When working in a team, you may pull new migration files. The standard workflow is to run pixi run migrate after pulling the latest code.

If you encounter a "multiple heads" conflict or your database gets into an inconsistent state, you can reset the environment.

Resetting the Environment:

  1. Stop and wipe volumes: 
    pixi run teardown-services-volumes
  2. Start services: 
    pixi run start-services
  3. Apply all migrations: 
    pixi run migrate

How to Downgrade a Migration (Optional)

If you need to undo a migration:

# Downgrade by one version
DATABASE_URL=postgresql://biocirv_user:biocirv_dev_password@localhost:5432/biocirv_db pixi run alembic downgrade -1