A SQLx Offline Build Can Pass With Stale Database Metadata

Article · Derived state

A Rust build passes in offline mode. The deployment then fails when it reads a database column. Both results can be correct: the build checked one description of the query result, while the database returned another.

SQLx query macros can use saved metadata from a .sqlx directory. That lets the compiler check queries without contacting the database. It also makes that directory a build input whose relationship to the schema must be maintained.

The distinction matters when a database migration changes a column without changing the SQL string. A metadata file can still match the query text while describing an older result type.

This reproduction uses SQLx 0.8.6 and PostgreSQL 18. It changes an integer primary key to bigint. A stale offline build still compiles. With a small value, it even keeps running. A later value outside the range of i32 exposes the stale assumption.

One query, two descriptions

The example crate has one macro and one explicit Rust type:

#[tokio::main(flavor = "current_thread")]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let pool = sqlx::PgPool::connect(&std::env::var("DATABASE_URL")?).await?;
    let record = sqlx::query!("SELECT id FROM metadata_items")
        .fetch_one(&pool)
        .await?;
    let id: i32 = record.id;
    assert_eq!(id, 7);
    pool.close().await;
    Ok(())
}

With the initial schema, PostgreSQL describes id as its four-byte integer type. SQLx maps that to Rust's i32. The explicit assignment agrees with the macro's generated record, and the program expects the value 7.

The baseline live compilation writes metadata using SQLX_OFFLINE_DIR, the output directory used by the pinned macro implementation. The trial then runs the program offline against the initial database. This checks that the source and saved description work before introducing the migration.

The driver changes the column to bigint, PostgreSQL's eight-byte integer type. It does not change the query or the Rust assignment.

The SQLx 0.8.6 query macro documentation explains saved query descriptions. The PostgreSQL type mappings show the corresponding Rust types. A cached description of an integer remains a description of an integer even after a separate database has changed.

Check three different boundaries

The trial forces the macro crate to rebuild at each stage. Reusing a previously compiled binary would not demonstrate an offline macro check after the migration.

The live expansion also writes a different metadata description. The trial compares its bytes with the baseline file. The SQL text stayed the same; the description changed because the result type changed.

The passing small value needs an explanation. For an exact result type, the macro output in SQLx 0.8.6 uses try_get_unchecked: it relies on the description checked at compilation. The PostgreSQL integer decoder reads the integer and uses a checked conversion into i32. The value 7 fits. The value 2,147,483,648 does not.

That is different from a dynamically checked row access which rejects an incompatible SQL type before decoding. Do not generalize one access path's error to every SQLx query. Small development values can hide stale metadata even after a runtime smoke test passes.

These assertions live in pg_metadata_trial.rs, which drives the separate offline metadata crate. The driver launches Cargo only inside GitHub CI. It uses a fresh schema, confirms the small value still works after the migration, then changes the value and captures the expected compiler and runtime failures. Those failures must match the intended diagnostics; an arbitrary failed command does not count as a successful experiment.

Give each CI check a clear job

Use an offline build to verify that the committed source can compile with the committed metadata. Use a disposable database at the intended schema version to verify that the descriptions still match the database.

The SQLx CLI 0.8.6 documentation describes cargo sqlx prepare --check for detecting stale metadata. Run it against the schema and query features you intend to support. Include targets or features that hide additional queries; a default build cannot inspect code excluded by configuration.

This lab uses live macro compilation and metadata comparison rather than installing the CLI. The live check exposes this concrete type mismatch. It is not a substitute for exercising the project's full preparation procedure, migrations, feature matrix, and schema permissions in CI.

Set SQLX_OFFLINE=true when you need an explicitly offline build. Otherwise, a present DATABASE_URL can cause a build to contact the database. Record which mode ran so a successful build has an interpretable meaning.

Treat metadata regeneration as a reviewable change. If a migration changes result types or nullability, inspect the changed description and the application code together. Do not regenerate the cache simply to make a warning disappear.

A passing query check does not give a migration a time budget

Suppose a coding agent adds a nullable text column. The old query still selects only id. Regenerated SQLx metadata and an integration test against the final schema can both pass. Neither result establishes that the migration can acquire its lock while the old application is running.

The additional type_compatible_migration_can_still_wait_for_a_reader fixture keeps a transaction open after reading a row. Another session attempts to add the column, then a third session issues an ordinary read. The observer must identify both links: the old reader blocks the migration, and the waiting migration blocks the later read. An unrelated failure cannot satisfy those assertions.

PostgreSQL's ALTER TABLE documentation describes the locks required by each form. Even a change that preserves the query's result type can need an incompatible lock. A long-lived reader can delay it, and a queued schema change can affect later requests. Query compatibility and deployment behavior need separate evidence. PostgreSQL's blocking-session function can report a session waiting ahead in the lock queue as a blocker. The migration does not have to acquire its requested lock before it affects later readers.

Here is the bounded migration excerpt from that fixture:

let mut tx = pool.begin().await?;
sqlx::raw_sql("SET LOCAL lock_timeout = '2s'; SET LOCAL statement_timeout = '5s'")
    .execute(&mut *tx)
    .await?;
sqlx::query("ALTER TABLE items ADD COLUMN note text")
    .execute(&mut *tx)
    .await?;
tx.commit().await?;

The blocked migration must fail with SQLSTATE 55P03 and leave no new column. The later read must then return its row while the original transaction is still open. Only afterward does the original reader roll back. A fresh bounded migration must succeed and preserve the row. The deadlines are choices for this CI fixture; choose the deployment budget from the service's requirements.

Before accepting a generated migration, decide how much blocking your release can tolerate, what happens after that budget expires, and which application versions must keep working. Ask for the observations that establish those properties. A longer timeout can change who waits without resolving why the lock is held.

A current description still has limits

Compile-time query checking does not prove that a migration is safe for old application replicas. If a rolling deployment leaves an old binary reading the table, the database and both application versions must remain compatible during that overlap.

For this column change, decide how the application will handle i64, existing APIs, serialized values, and any later range conversion. An explicit checked conversion can express a product limit. An unchecked cast can lose information. The PostgreSQL enum migration article shows another case where the old decoder matters after a schema change.

Metadata also does not prove authorization, transaction isolation, lock behaviour, or query cost. A type-correct query can still wait indefinitely for a lock or scan far more rows than expected. Keep those checks near the behaviour they establish.

When a build's result seems inconsistent with production, record its actual inputs: source revision, lockfile, features, metadata, and schema used for live checks. The Cargo rebuild investigation uses the same habit at a different boundary. A successful check becomes useful evidence when you can say exactly what it checked.