# Arash77 Galaxy Claude Marketplace Galaxy DB Migration

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

- Skill: `tomevault-io/arash77-galaxy-claude-marketplace-galaxy-db-migration` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add tomevault-io/arash77-galaxy-claude-marketplace-galaxy-db-migration`
- Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/arash77-galaxy-claude-marketplace-galaxy-db-migration/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: tomevault-io (https://skillmd.com/u/tomevault-io)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/tomevault-io/arash77-galaxy-claude-marketplace-galaxy-db-migration

---


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

1. **`manage_db.sh`** - Admin script for production (upgrade, downgrade, init)
2. **`scripts/db_dev.sh`** - Dev script with full Alembic features (includes `revision` command)
3. **`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:**

1. **create** - Create a new migration revision
2. **upgrade** - Upgrade database to latest version
3. **downgrade** - Downgrade database to previous version
4. **status** - Check current database version vs codebase
5. **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:
1. Updated SQLAlchemy models in `lib/galaxy/model/__init__.py`
2. 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:
```bash
./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:**
```python
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():**
```python
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:
```bash
# 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

```bash
./manage_db.sh upgrade
```

### Step 6: Verify

Check that:
1. Migration runs without errors
2. Database schema matches model
3. 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:**
```bash
./manage_db.sh upgrade
```

This upgrades both gxy and tsi branches to head.

**Upgrade to specific release:**
```bash
./manage_db.sh upgrade 22.05
# or
./manage_db.sh upgrade release_22.05
```

**Upgrade only gxy branch:**
```bash
./scripts/run_alembic.sh upgrade gxy@head
```

**Upgrade by relative steps:**
```bash
./scripts/run_alembic.sh upgrade gxy@+1  # One revision forward
```

**Check status before upgrading:**
```bash
./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:**
```bash
./manage_db.sh downgrade <current_revision_id>-1
```

**Downgrade to specific revision:**
```bash
./manage_db.sh downgrade <revision_id>
```

**Downgrade to specific release:**
```bash
./manage_db.sh downgrade 22.01
# or
./manage_db.sh downgrade release_22.01
```

**Downgrade gxy branch only:**
```bash
./scripts/run_alembic.sh downgrade gxy@-1  # One revision back
```

**Downgrade to base (empty database):**
```bash
./scripts/run_alembic.sh downgrade gxy@base
```

**Check current position first:**
```bash
./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:**
```bash
./scripts/db_dev.sh dbversion
```

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

**Check head revision in codebase:**
```bash
./scripts/db_dev.sh version
```

Shows latest revision IDs for both branches.

**View migration history:**
```bash
./scripts/db_dev.sh history --indicate-current
```

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

**Show specific revision details:**
```bash
./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:**
1. Shut down all Galaxy processes (web servers, job handlers, workflow schedulers)
2. Run migration again
3. Restart Galaxy after successful migration

### Problem: migrations.IncorrectVersionError

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

**Solution:**
1. Backup database
2. Check `migrate_version` table - should be version 180
3. If < 180: Checkout 22.01 branch, run old `manage_db.sh upgrade`
4. If = 181 (rare): Downgrade to 180 using old manage_db.sh
5. Switch back to current branch
6. Run `./manage_db.sh upgrade`

### Problem: Database version mismatch on startup

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

**Solution:**
1. Check which is ahead:
   ```bash
   ./scripts/db_dev.sh dbversion
   ./scripts/db_dev.sh version
   ```
2. If database behind: `./manage_db.sh upgrade`
3. 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:**
1. Check if table/column already exists in database
2. Use check functions in migration:
   ```python
   from galaxy.model.migrations.util import table_exists

   def upgrade():
       if not table_exists("my_table", False):
           create_table("my_table", ...)
   ```
3. 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:**
1. Clean up orphaned rows before adding constraint
2. Add data migration in upgrade() before schema change
3. 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

---
> Converted and distributed by [TomeVault](https://tomevault.io/claim/arash77) — claim your Tome and manage your conversions.
<!-- tomevault:4.0:skill_md:2026-04-13 -->