SQL Migrations with Alembic

Migrations should be performed after each modification of the SQL schema. Since we use SQLAlchemy as our ORM, we leverage Alembic to handle database migrations seamlessly.

Migration Workflow

Caution

Before running any migration, ensure your stack is stopped and create a backup of your database in case anything goes wrong.

Step 1: Generate a Migration Script

When to generate a migration script ?

This command should be run whenever you make changes to the SQLAlchemy models. If not, skip to Step 2.

When generating migrations, always provide a clear, descriptive revision message. This helps maintain a readable migration history.

Example revision messages:

• "create users table"
• "add index to email column"
• "rename column full_name to name"
• "add foreign key from orders to users"
• "remove deprecated table sessions"
• "update default value for created_at"

Command:

Before generating a migration script

Replace <revision_message> with your descriptive message before running the following command. The command will automatically generate a new migration script based on detected model changes.

Generate a migration script...

docker compose up -d rdb
docker compose \
    run --no-deps --build --rm \
    --entrypoint "uv run alembic -c /app/openrag/scripts/migrations/alembic/alembic.ini revision --autogenerate -m '<revision_message>'" \
    openrag

It will create a new migration script in the openrag/scripts/migrations/alembic/versions/ directory.

Step 2: Apply the Migration

Once you’ve reviewed the generated migration script and confirmed it looks correct, apply it to your database:

Apply migrations

docker compose up -d rdb
docker compose \
    run --no-deps --build --rm \
    --entrypoint "uv run alembic -c /app/openrag/scripts/migrations/alembic/alembic.ini upgrade head" \
    openrag; docker compose down

This upgrades your database schema to the latest revision.

Automatic Migrations on Code Update

When you pull the latest changes to your local code, check if a new migration script has been added. If so, make sure to apply the migration before restarting the service.

---

Troubleshooting: Multiple Alembic Heads

If you encounter an error like:

ERROR [alembic.util.messaging] Multiple head revisions are present for given argument 'head'; please specify a specific target revision, '<branchname>@head' to narrow to a specific head, or 'heads' for all heads

This usually happens when two or more migration scripts are created independently from the same base revision (for example, when working on separate features that both modify the RDB schema). Alembic then detects multiple “heads” in the migration history.

How to resolve:

1. Create a merge migration to join the two heads. Replace the revision IDs below with your actual head revisions:

   Merge Alembic heads

   docker compose up -d rdb
   docker compose run --no-deps --build --rm \
       --entrypoint "uv run alembic -c /app/openrag/scripts/migrations/alembic/alembic.ini merge -m 'merge heads' <head1> <head2>" \
       openrag

   This will generate a new migration script that merges the two branches.

2. Apply the migrations as usual:

   Apply migrations after merge

   docker compose up -d rdb
   docker compose run --no-deps --build --rm \
       --entrypoint "uv run alembic -c /app/openrag/scripts/migrations/alembic/alembic.ini upgrade head" \
       openrag; docker compose down

---

Best Practices

• Always review generated migration scripts before applying them
• Test migrations in a development environment first with the entire stack down
• Keep revision messages clear and concise
• Backup your database before running migrations in production
• Version control all migration scripts