Levandor

sqlx Shared `_sqlx_migrations` Table Trap

5 min read

Why you cannot run two separate sqlx::migrate! sets against the same database, why you must never reuse a migration version number, and why you must never edit an already-applied migration file. This gotcha forced the position-manager-interface-design migration-ownership walk-back: a crate can own a table’s CODE without owning its DDL/migrator.

For Agents

Reusable lesson, not project-specific in essence. The trigger was the position_manager design proposing PM run a second sqlx::migrate! to “adopt” the shared pmv2_autotrade_orders table. That is unsafe for the reasons below. Rule of thumb: ONE migrator per database / per _sqlx_migrations table.

The core mechanism

sqlx 0.8 tracks ALL applied migrations in a single hardcoded table named _sqlx_migrations, keyed by version (a BIGINT primary key). There is one such table per database, shared by every migrator that runs against that pool. On each run, a migrator calls validate_applied_migrations, which by default has ignore_missing = false.

This means a migrator expects the set of version rows in _sqlx_migrations to be exactly the set of migrations it knows about. Anything it doesn’t recognize is treated as a fatal inconsistency.

The three failure modes

1. Two sqlx::migrate! sets → MigrateError::VersionMissing

Run TWO separate migrator sets (e.g. an upstream crate’s ~43 migrations AND a new crate’s own migrations) against the same database/pool, and each migrator throws VersionMissing on the OTHER’s applied versions. Because validate_applied_migrations defaults ignore_missing = false, each migrator sees version rows in _sqlx_migrations it has no source file for, decides the DB is inconsistent, and boot fails. Setting ignore_missing(true) papers over it but defeats the integrity check and is not a real fix.

2. Reusing the same version integer across two dirs → VersionMismatch

If two migration directories both define version 20260614000000 (even with CREATE TABLE IF NOT EXISTS), the second migrator to run finds a row for that version with a DIFFERENT checksum/description than its own file and throws VersionMismatch. Version numbers are a global primary key in _sqlx_migrations — they cannot be reused across migrator sets.

3. Editing an ALREADY-APPLIED migration file → VersionMismatch (bricks deployed DBs)

sqlx stores a checksum of each migration’s bytes when it applies it. Editing an already-applied migration file (e.g. to remove a table block) changes its stored checksum. On the next run against a DB where that version already ran, sqlx compares the file’s new checksum to the stored one, they differ, and it throws VersionMismatch — bricking boot on every deployed database. Applied migration files are immutable; new changes go in NEW migration files.

CREATE TABLE IF NOT EXISTS does NOT save you

A common (wrong) intuition: “both migrators use IF NOT EXISTS, so the table is created at most once — order doesn’t matter.” False. sqlx does not track table existence; it tracks the version row in _sqlx_migrations. Even if the DDL is a no-op because the table already exists, the migrator still tries to INSERT its version row, and that is what collides (VersionMismatch) or what the other migrator trips over (VersionMissing). The IF NOT EXISTS on the DDL is irrelevant to sqlx’s bookkeeping.

Takeaways

  • One migrator per database / per _sqlx_migrations table. If multiple crates share a Postgres database, exactly one of them runs sqlx::migrate!.
  • Never reuse a migration version number across directories — it is a global PK.
  • Never edit an already-applied migration file — it changes the stored checksum and bricks boot on deployed DBs. New changes get new files.
  • Owning a table’s CODE is separate from owning its DDL/migrator. A crate can run all the queries against a table (ledger.rs reads/writes pmv2_autotrade_orders) while a different crate owns the migration that creates it. Split ownership by meaning per column/operation, not by who runs migrate!.
  • If a physical table relocation between crates is genuinely needed, do it as a coordinated single-migrator change, not by adding a second migrator.

The escape hatch — set_ignore_missing(true) to restructure a deployed sqlx set

The “never edit an applied migration file” rule above is about checksum integrity (failure mode #3). When you genuinely must restructure history on a deployed DB — squash many migrations into one baseline, or prune dropped tables — replace the files with an idempotent CREATE TABLE IF NOT EXISTS baseline and set set_ignore_missing(true) on the Migrator (sqlx 0.8.6). That tells sqlx to tolerate applied version rows it no longer has a source file for (the orphaned rows the squash leaves behind), so boot succeeds on deployed DBs while fresh DBs apply the single baseline. This was used to ship cohort-algo-migration-squash-cleanup.

"One migrator per DB" — first verify how many sqlx migrators there actually ARE

[[cohort-algo-shared-db-migrator-ownership|babylon 331]] refined this rule to “one migrator per (distinct) history table” so two sqlx migrators could coexist. But the implementation revealed the assumption was moot: position_manager uses Supabase, not sqlx, so there is only ONE sqlx migrator (cohort) on that shared DB. With a single sqlx owner there is no cross-migrator VersionMissing trip to design around — the distinct-history-table machinery is unnecessary, and squash + ignore_missing is the simpler answer. Lesson: before reaching for history-table isolation, count the actual sqlx migrators — a non-sqlx peer (Supabase, Diesel, Flyway, raw psql) is not a sqlx migrator and doesn’t collide on _sqlx_migrations.

Where this bit us

In the position-manager-interface-design the original plan had position_manager adopt the shared pmv2_autotrade_orders table via its own CREATE TABLE IF NOT EXISTS migration. The audit walked that back: PM runs NO migrator (no migrations/ dir, the sqlx migrate feature omitted from Cargo.toml, the upstream supabase/migrations/20260614000000_pmv2_autotrade.sql untouched). PM owns ledger.rs — the query code — only. Physical relocation is deferred.

  • cohort-algo-migration-squash-cleanup — supersedes this note’s approach for cohort’s DB: cohort is the sole sqlx owner (PM = Supabase), so it squashed 44 migrations → ONE idempotent baseline + set_ignore_missing(true) rather than isolating history tables; also the deliberate, documented use of the “edit a deployed sqlx set” escape hatch
  • cohort-algo-shared-db-migrator-ownership — the decision that REFINED the “one migrator per DB” rule to “one migrator per (distinct) history table” (then found moot in implementation since there is only one sqlx migrator)
  • position-manager-interface-design
  • polymarket-fetch

Graph View

Backlinks