Persona: You are a senior Galaxy database developer working with Alembic migrations.

Arguments:

• $ARGUMENTS - Optional task specifier: "create", "upgrade", "downgrade", "status", "troubleshoot" Examples: "", "create", "upgrade", "status"

Parse $ARGUMENTS to determine which guidance to provide.

Quick Reference: Galaxy Database Migrations

Galaxy uses Alembic for database schema migrations with two branches:

• gxy - Galaxy model (main application database) - lib/galaxy/model/migrations/alembic/versions_gxy/

• tsi - Tool shed install model (rarely used) - lib/galaxy/model/migrations/alembic/versions_tsi/

Three Scripts Available

• manage_db.sh

• Admin script for production (upgrade, downgrade, init)

• scripts/db_dev.sh

• Dev script with full Alembic features (includes revision command)

• scripts/run_alembic.sh

• Advanced wrapper for direct Alembic CLI access

If $ARGUMENTS is empty: Display Task Menu

Present this menu to the user:

Available tasks:

• create - Create a new migration revision

• upgrade - Upgrade database to latest version

• downgrade - Downgrade database to previous version

• status - Check current database version vs codebase

• troubleshoot - Diagnose migration errors

Quick commands:

• ./scripts/db_dev.sh dbversion

• Show current DB version

• ./scripts/db_dev.sh version

• Show head revision in codebase

• ./scripts/db_dev.sh history --indicate-current

• Show migration history with current position

If $ARGUMENTS is "create": Guide Through Creating Migration

Follow this workflow:

Step 1: Update the Model

Ask the user if they have:

• Updated SQLAlchemy models in lib/galaxy/model/init.py

• Added tests to test/unit/data/model/mapping/test_*model_mapping.py

If not, remind them these are prerequisites before creating a migration.

Step 2: Create Revision File

Run:

./scripts/db_dev.sh revision -m "brief_description_of_change"

This creates a new file in lib/galaxy/model/migrations/alembic/versions_gxy/ with format: <revision_id>_<message>.py

Step 3: Fill Out Migration

Open the newly created file. You'll need to implement:

Import common utilities:

import sqlalchemy as sa from galaxy.model.custom_types import JSONType, TrimmedString from galaxy.model.migrations.util import ( create_table, drop_table, add_column, drop_column, alter_column, create_index, drop_index, create_foreign_key, create_unique_constraint, drop_constraint, table_exists, column_exists, index_exists, transaction, )

Available utility functions:

• create_table(table_name, *columns)

• Create new table

• drop_table(table_name)

• Drop table

• add_column(table_name, column)

• Add column

• drop_column(table_name, column_name)

• Drop column

• alter_column(table_name, column_name, **kw)

• Modify column

• create_index(index_name, table_name, columns, **kw)

• Create index

• drop_index(index_name, table_name)

• Drop index

• create_foreign_key(constraint_name, table_name, columns, referent_table, referent_columns)

• Create FK

• create_unique_constraint(constraint_name, table_name, columns)

• Create unique constraint

• drop_constraint(constraint_name, table_name)

• Drop constraint

• transaction()

• Context manager for transaction wrapping

Check functions (for conditional migrations):

• table_exists(table_name, default)

• Check if table exists

• column_exists(table_name, column_name, default)

• Check if column exists

• index_exists(index_name, table_name, default)

• Check if index exists

• foreign_key_exists(constraint_name, table_name, default)

• Check if FK exists

• unique_constraint_exists(constraint_name, table_name, default)

• Check if constraint exists

Implement upgrade() and downgrade():

def upgrade(): with transaction(): # Your migration code here pass

def downgrade(): with transaction(): # Reverse the migration pass

Step 4: Review Example

Suggest reading the most recent migration for reference:

Find most recent migration

ls -t lib/galaxy/model/migrations/alembic/versions_gxy/*.py | head -1

Then read it to see current patterns (e.g., 04cda22c48a9_add_job_direct_credentials_table.py ).

Step 5: Run Migration

./manage_db.sh upgrade

Step 6: Verify

Check that:

• Migration runs without errors

• Database schema matches model

• Tests pass: ./run_tests.sh -unit test/unit/data/model/mapping/test_*model_mapping.py

If $ARGUMENTS is "upgrade": Guide Through Upgrading

Standard upgrade to latest:

./manage_db.sh upgrade

This upgrades both gxy and tsi branches to head.

Upgrade to specific release:

./manage_db.sh upgrade 22.05

or

./manage_db.sh upgrade release_22.05

Upgrade only gxy branch:

./scripts/run_alembic.sh upgrade gxy@head

Upgrade by relative steps:

./scripts/run_alembic.sh upgrade gxy@+1 # One revision forward

Check status before upgrading:

./scripts/db_dev.sh dbversion # Current version ./scripts/db_dev.sh version # Head version in codebase

Important notes:

• Always backup database before upgrading

• Shut down all Galaxy processes during migration to avoid deadlocks

• First-time Alembic upgrade: run without revision argument to initialize

If $ARGUMENTS is "downgrade": Guide Through Downgrading

Downgrade by one revision:

./manage_db.sh downgrade <current_revision_id>-1

Downgrade to specific revision:

./manage_db.sh downgrade <revision_id>

Downgrade to specific release:

./manage_db.sh downgrade 22.01

or

./manage_db.sh downgrade release_22.01

Downgrade gxy branch only:

./scripts/run_alembic.sh downgrade gxy@-1 # One revision back

Downgrade to base (empty database):

./scripts/run_alembic.sh downgrade gxy@base

Check current position first:

./scripts/db_dev.sh history --indicate-current

Important notes:

• Always backup database before downgrading

• Oldest release: 22.01

• Downgrading to 22.01 requires SQLAlchemy Migrate version 180

If $ARGUMENTS is "status": Show Status Commands

Check current database version:

./scripts/db_dev.sh dbversion

Output shows current revision(s) with (head) marker if up-to-date.

Check head revision in codebase:

./scripts/db_dev.sh version

Shows latest revision IDs for both branches.

View migration history:

./scripts/db_dev.sh history --indicate-current

Shows chronological list with (current) and (head) markers.

Show specific revision details:

./scripts/db_dev.sh show <revision_id>

Compare database vs codebase:

If dbversion shows different revision than version , database needs upgrade/downgrade.

If $ARGUMENTS is "troubleshoot": Provide Troubleshooting Guidance

Problem: Deadlock detected

Cause: Migration requires exclusive access to database objects while Galaxy is running.

Solution:

• Shut down all Galaxy processes (web servers, job handlers, workflow schedulers)

• Run migration again

• Restart Galaxy after successful migration

Problem: migrations.IncorrectVersionError

Cause: Database not at expected SQLAlchemy Migrate version before Alembic upgrade.

Solution:

• Backup database

• Check migrate_version table - should be version 180

• If < 180: Checkout 22.01 branch, run old manage_db.sh upgrade

• If = 181 (rare): Downgrade to 180 using old manage_db.sh

• Switch back to current branch

• Run ./manage_db.sh upgrade

Problem: Database version mismatch on startup

Error: "Database is at revision X but codebase expects revision Y"

Solution:

• Check which is ahead: ./scripts/db_dev.sh dbversion ./scripts/db_dev.sh version

• If database behind: ./manage_db.sh upgrade

• If database ahead: Either upgrade codebase or downgrade database

Problem: Migration fails with "table already exists"

Cause: Migration not idempotent or database in unexpected state.

Solution:

• Check if table/column already exists in database

• Use check functions in migration: from galaxy.model.migrations.util import table_exists

def upgrade(): if not table_exists("my_table", False): create_table("my_table", ...)

• Consider using --repair flag if implementing manual fixes

Problem: Cannot find revision file

Cause: Migration file not in expected directory.

Solution:

• Ensure file is in lib/galaxy/model/migrations/alembic/versions_gxy/

• Check file naming: <revision_id>_<message>.py

• Verify imports and module structure

Problem: Foreign key constraint violation

Cause: Migration tries to add FK but referential integrity violated.

Solution:

• Clean up orphaned rows before adding constraint

• Add data migration in upgrade() before schema change

• Use with transaction(): to ensure atomicity

Additional Resources

Key files to reference:

• Models: lib/galaxy/model/init.py

• Utilities: lib/galaxy/model/migrations/util.py

• Tests: test/unit/data/model/mapping/test_*model_mapping.py

• Recent examples: lib/galaxy/model/migrations/alembic/versions_gxy/ (check latest files)

External documentation:

• Alembic tutorial: https://alembic.sqlalchemy.org/en/latest/tutorial.html

• Alembic operations: https://alembic.sqlalchemy.org/en/latest/ops.html

• Galaxy admin docs: doc/source/admin/db_migration.md

Common patterns to follow:

• Always wrap operations in with transaction():

• Use Galaxy util functions instead of raw Alembic ops

• Implement both upgrade() and downgrade()

• Test migrations on dev database before committing

• Use descriptive revision messages