michaelalber/sqlx-migration-manager

SQLx Migration Manager

"A migration that cannot be rolled back is a migration that has not been tested." -- Database Migration Principle

"SQLx's compile-time query verification is a gift — but only if you regenerate the cache after every migration." -- Rust/SQLx Principle

Core Philosophy

SQLx migrations are plain SQL files — no ORM-generated DDL, no code-first schema. This is a strength: the SQL is explicit, reviewable, and portable. But it creates a unique challenge: SQLx's compile-time query verification (sqlx::query! macros) means schema changes can break compilation. The offline query cache (sqlx prepare) must be regenerated after every migration.

This skill manages the full SQLx migration lifecycle with the same safety philosophy as ef-migration-manager and alembic-migration-manager: never apply a migration you have not reviewed, never apply without testing the rollback, and always regenerate the offline cache after applying.

Non-Negotiable Constraints:

1. Review the SQL before applying — sqlx migrate add creates the file; you review it before sqlx migrate run. Never apply unreviewed SQL.
2. Test the rollback before applying — sqlx migrate revert must work before the migration is considered safe. Test on a development database.
3. Regenerate the offline cache after every migration — sqlx prepare must run after sqlx migrate run. Skipping this breaks compilation for other developers.
4. Never apply migrations in the same phase as model changes — schema changes and code changes that depend on them are separate commits. The migration runs first; the code change follows.
5. Idempotency check — migrations must be idempotent where possible. CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS, ALTER TABLE ... ADD COLUMN IF NOT EXISTS (PostgreSQL 9.6+).

Domain Principles Table

| # | Principle | Description | Applied As | |---|-----------|-------------|------------| | 1 | SQL Review Mandatory | SQLx migrations are plain SQL. There is no generated DDL to inspect — the SQL IS the migration. Every line must be reviewed for correctness, data loss risk, and performance impact before applying. | Read the migration file completely before running sqlx migrate run. Flag any destructive operations. | | 2 | Rollback First | A migration that cannot be rolled back is a migration that has not been tested. Test sqlx migrate revert on a development database before applying to staging or production. | Run sqlx migrate revert after every sqlx migrate run in development. Verify the schema returns to the previous state. | | 3 | Offline Cache Discipline | SQLx's compile-time query verification requires an offline cache (.sqlx/ directory). After every migration, the cache is stale. sqlx prepare regenerates it. Committing stale cache breaks CI. | Run sqlx prepare after every sqlx migrate run. Commit the updated .sqlx/ directory. | | 4 | Zero-Downtime Awareness | Some DDL operations lock tables. ALTER TABLE ... ADD COLUMN without a default is fast in PostgreSQL; with a default it rewrites the table. DROP COLUMN is fast but irreversible. Know the locking behavior of every operation. | For each migration, note the locking behavior and whether it is safe for zero-downtime deployment. | | 5 | Migration Naming Convention | Migration files are named <timestamp>_<description>.sql. The description must be meaningful: add_user_email_index not migration_001. The timestamp is generated by sqlx migrate add. | Use descriptive names. The migration file name is permanent — it cannot be changed after the migration is applied. | | 6 | Transactional DDL | PostgreSQL supports transactional DDL — schema changes can be rolled back if the transaction fails. MySQL does not. Know which database you are targeting. | For PostgreSQL: wrap complex migrations in explicit transactions. For MySQL: plan manual rollback procedures. | | 7 | Data Migration Separation | Schema migrations (DDL) and data migrations (DML) should be in separate files. Mixing them makes rollback complex and increases lock duration. | Create separate migration files for DDL and DML changes. Apply DDL first, then DML. | | 8 | Compile-Time Query Verification | sqlx::query! macros verify SQL at compile time against the database schema. After a migration, queries that reference the new schema will fail to compile until sqlx prepare is run. | After every migration, run sqlx prepare and verify cargo build succeeds. | | 9 | Migration Immutability | Applied migrations must never be modified. SQLx tracks applied migrations by checksum. Modifying an applied migration causes sqlx migrate run to fail with a checksum mismatch. | Never edit a migration file after it has been applied to any environment. Create a new migration to fix mistakes. | | 10 | Environment Parity | Migrations must be tested in an environment that matches production (same database version, same data volume order of magnitude). A migration that takes 1 second on an empty table may take 10 minutes on a production table. | Test migrations against a production-like dataset before applying to production. |

Knowledge Base Lookups

| Query | When to Call | |-------|--------------| | search_knowledge("SQLx migration sqlx migrate run revert") | During migration lifecycle — verify command syntax and behavior | | search_knowledge("SQLx compile time query verification sqlx prepare") | During cache regeneration — verify sqlx prepare workflow | | search_knowledge("database migration zero downtime PostgreSQL") | During zero-downtime planning — verify locking behavior | | search_knowledge("SQLx transaction migration rollback") | During rollback planning — verify transaction behavior | | search_knowledge("PostgreSQL ALTER TABLE locking DDL") | During DDL review — verify locking implications |

Workflow

PLAN (before creating the migration)
    [ ] Identify the schema change needed
    [ ] Assess data loss risk (DROP, TRUNCATE, column type change)
    [ ] Assess locking risk (table rewrites, index creation)
    [ ] Plan the rollback SQL
    [ ] Determine if zero-downtime deployment is required

        |
        v

CREATE
    [ ] Run: sqlx migrate add <descriptive-name>
    [ ] Write the forward migration SQL in the created file
    [ ] Write the rollback SQL (in a comment or separate down migration)
    [ ] Review the SQL completely before proceeding

        |
        v

REVIEW SQL (mandatory gate)
    [ ] Read the migration file completely
    [ ] Verify no unintended data loss
    [ ] Verify locking behavior is acceptable
    [ ] Verify rollback SQL is correct
    [ ] Verify idempotency where applicable

        |
        v

TEST ROLLBACK (on development database)
    [ ] Run: sqlx migrate run (apply the migration)
    [ ] Verify schema is correct: \d <table> or equivalent
    [ ] Run: sqlx migrate revert (roll back)
    [ ] Verify schema returned to previous state
    [ ] Run: sqlx migrate run again (re-apply for production)

        |
        v

APPLY
    [ ] Run: sqlx migrate run
    [ ] Verify: sqlx migrate info (shows migration as applied)
    [ ] Run: cargo build (verify no compile errors)

        |
        v

REGENERATE CACHE
    [ ] Run: sqlx prepare
    [ ] Verify: cargo build succeeds with SQLX_OFFLINE=true
    [ ] Commit: git add .sqlx/ && git commit -m "chore: regenerate sqlx offline cache"

Exit criteria: Migration applied, verified, offline cache regenerated, build passes.

State Block

<sqlx-migration-state>
phase: PLAN | CREATE | REVIEW | TEST_ROLLBACK | APPLY | REGENERATE_CACHE | COMPLETE
migration_name: [name]
database: [postgresql | mysql | sqlite | unknown]
migration_file: [path to .sql file]
sql_reviewed: true | false
rollback_tested: true | false
migration_applied: true | false
cache_regenerated: true | false
build_status: pass | fail | not-run
data_loss_risk: none | low | medium | high
locking_risk: none | low | medium | high
last_action: [description]
next_action: [description]
</sqlx-migration-state>

Output Templates

Migration Review Report

## SQLx Migration Review: [migration-name]

**File**: `migrations/[timestamp]_[name].sql`
**Database**: [PostgreSQL | MySQL | SQLite]
**Review Date**: [date]

### SQL Review

```sql
[migration SQL here]

Risk Assessment

| Risk | Level | Notes | |------|-------|-------| | Data Loss | [None/Low/Medium/High] | [description] | | Table Lock | [None/Low/Medium/High] | [description] | | Zero-Downtime Safe | [Yes/No/Conditional] | [description] | | Rollback Complexity | [Simple/Moderate/Complex] | [description] |

Rollback Plan

-- Rollback SQL
[rollback SQL here]

Checklist

• [ ] SQL reviewed completely
• [ ] No unintended data loss
• [ ] Locking behavior acceptable
• [ ] Rollback SQL verified
• [ ] Tested on development database
• [ ] sqlx prepare run after apply
• [ ] cargo build passes

### Post-Apply Verification

```markdown
## Post-Apply Verification

- [ ] `sqlx migrate info` shows migration as applied
- [ ] `cargo build` succeeds
- [ ] `SQLX_OFFLINE=true cargo build` succeeds (offline cache valid)
- [ ] Application starts without errors
- [ ] Affected queries return expected results

AI Discipline Rules

CRITICAL: Never Apply Without Review

WRONG:

Running sqlx migrate run to apply the migration.

RIGHT:

Before applying, reviewing the migration SQL:

[SQL content]

Risk assessment:
- Data loss: None (adding a nullable column)
- Locking: Low (PostgreSQL adds nullable columns without table rewrite)
- Rollback: Simple (DROP COLUMN)

Proceeding to apply.

CRITICAL: Always Regenerate Cache After Apply

WRONG:

Migration applied successfully. Done.

RIGHT:

Migration applied. Running sqlx prepare to regenerate the offline query cache.
This is required because the schema has changed and compile-time query
verification needs to be updated.

$ sqlx prepare
$ SQLX_OFFLINE=true cargo build

Offline cache regenerated. Commit .sqlx/ directory with the migration.

CRITICAL: Never Modify Applied Migrations

WRONG:

There's a typo in the migration. I'll fix it in the file.

RIGHT:

The migration has already been applied. Modifying it would cause a checksum
mismatch error on the next sqlx migrate run.

Create a new migration to fix the issue:
$ sqlx migrate add fix_typo_in_[original_name]

Anti-Patterns Table

| # | Anti-Pattern | Why It Fails | Correct Approach | |---|-------------|-------------|-----------------| | 1 | Apply Without Review | Applying unreviewed SQL can cause data loss, table locks, or incorrect schema changes. | Always read the migration file completely before sqlx migrate run. | | 2 | Skip Rollback Test | A migration with an untested rollback is a one-way door. If the migration causes problems, you cannot recover. | Test sqlx migrate revert on a development database before applying to staging. | | 3 | Forget sqlx prepare | The offline cache becomes stale after a migration. Other developers get compile errors. CI fails. | Run sqlx prepare after every sqlx migrate run. Commit the updated .sqlx/ directory. | | 4 | Modify Applied Migrations | SQLx tracks migrations by checksum. Modifying an applied migration causes sqlx migrate run to fail with a checksum mismatch error. | Never edit applied migrations. Create a new migration to fix mistakes. | | 5 | DDL + DML in One Migration | Mixing schema changes and data migrations increases lock duration and makes rollback complex. | Separate DDL and DML into different migration files. | | 6 | Ignore Locking Behavior | ALTER TABLE ... ADD COLUMN DEFAULT 'value' rewrites the entire table in older PostgreSQL versions. On a large table, this causes a multi-minute outage. | Check locking behavior for every DDL operation. Use pg_repack or online DDL tools for large tables. | | 7 | Non-Descriptive Migration Names | migration_001.sql tells you nothing about what the migration does. | Use descriptive names: add_user_email_unique_index, drop_legacy_sessions_table. | | 8 | Destructive Operations Without Backup | DROP TABLE, DROP COLUMN, TRUNCATE are irreversible. | Verify a recent backup exists before any destructive operation. Consider a soft-delete pattern first. | | 9 | Not Committing Cargo.lock | If Cargo.lock is not committed, sqlx version can change between developers, causing different migration behavior. | Commit Cargo.lock for application crates. | | 10 | Skipping sqlx migrate info | Not verifying the migration was applied correctly can lead to running the same migration twice or missing a migration. | Always run sqlx migrate info after sqlx migrate run to verify the applied state. |

Error Recovery

Checksum Mismatch Error

Symptoms: sqlx migrate run fails with "migration checksum mismatch"

Recovery:
1. Identify which migration has the mismatch: sqlx migrate info
2. Determine if the migration was already applied to this database
3. If applied and modified: this is a serious error — the schema may be inconsistent
4. Options:
   a. If development: sqlx migrate revert to the previous migration, then re-apply
   b. If production: DO NOT attempt to fix automatically — escalate to DBA
5. Root cause: someone modified an applied migration file
6. Prevention: never edit migration files after they are applied

sqlx prepare Fails

Symptoms: sqlx prepare fails with "error connecting to database" or query errors

Recovery:
1. Verify DATABASE_URL is set and the database is accessible
2. Verify the migration was applied: sqlx migrate info
3. If query errors: the migration may have changed the schema in a way that
   breaks existing sqlx::query! macros — update the queries to match the new schema
4. Run: cargo build (to see which queries fail)
5. Fix the failing queries, then re-run sqlx prepare

Migration Applied But Build Fails

Symptoms: sqlx migrate run succeeds but cargo build fails after

Recovery:
1. Run: cargo build 2>&1 | grep "error"
2. Identify which sqlx::query! macros are failing
3. Check if the migration changed the schema in a way that breaks the queries
4. Options:
   a. Update the queries to match the new schema
   b. If the migration was wrong: sqlx migrate revert, fix the migration, re-apply
5. After fixing: run sqlx prepare, then cargo build

Zero-Downtime Migration Required

Symptoms: Migration involves a table rewrite or long-running lock

Recovery:
1. Identify the locking operation (ALTER TABLE with DEFAULT, CREATE INDEX without CONCURRENTLY)
2. For PostgreSQL:
   - Use CREATE INDEX CONCURRENTLY instead of CREATE INDEX
   - Use ALTER TABLE ... ADD COLUMN (nullable, no default) then UPDATE in batches
   - Use pg_repack for table rewrites
3. Split the migration into multiple steps:
   Step 1: Add nullable column (fast, no lock)
   Step 2: Backfill data in batches (slow, no lock)
   Step 3: Add NOT NULL constraint (fast, brief lock)
4. Document the multi-step approach in the migration file comments

Integration with Other Skills

| Skill | Relationship | |-------|-------------| | rust-feature-slice | When a new feature requires database tables, sqlx-migration-manager provides the migration lifecycle. The feature's service implementation uses sqlx::query! macros against the migrated schema. | | rust-architecture-checklist | The checklist verifies that sqlx::query! macros are used (not raw SQL strings) and that the offline cache is committed. | | rust-security-review | The security review checks for SQL injection via format!() in SQL contexts. sqlx-migration-manager enforces parameterized queries in the application code. | | ef-migration-manager | Parallel skill for .NET/EF Core. Same safety philosophy; different tooling. | | alembic-migration-manager | Parallel skill for Python/Alembic. Same safety philosophy; different tooling. |