Database Migration Workflow in Macro Using SQLx and SQLX_OFFLINE Mode

Macro uses a three-phase SQLx workflow that applies migrations against a live PostgreSQL database, updates the offline query cache with sqlx prepare, and then compiles the entire workspace using SQLX_OFFLINE=true to eliminate build-time database dependencies.

The macro repository implements its data layer on SQLx (v0.8) with offline query caching enabled. This architecture allows the Rust compiler to verify SQL queries against a cached schema rather than a live database connection, ensuring reproducible builds while maintaining strict type safety. The migration workflow keeps the schema in sync with application code through a sequence of live migration, cache refresh, and offline compilation steps.

Run Live Migrations Against PostgreSQL

Before updating the offline cache, you must apply migration files to a running PostgreSQL instance. The repository provides automation for this step through the xtask_local crate and Just recipes.

Migration Helper Implementation

The core migration logic resides in tooling/xtask/crates/xtask_local/src/local/db.rs, where the migrate() function constructs and executes the SQLx CLI command:

// tooling/xtask/crates/xtask_local/src/local/db.rs
pub fn migrate(stage: &Stage, instance: &Instance) -> Result<()> {
    let mut migrate = Command::new("sqlx");
    migrate
        .arg("migrate")
        .arg("run")
        .arg("--source")
        .arg("../macro_db_client/migrations")
        .arg("--database-url")
        .arg(&instance.database_url);
    stage.run("Running migrations", &mut migrate)
}

This function targets the migration directory at crates/macro_db_client/migrations/ and applies all pending .sql files in sequential order.

Just Recipes for Database Operations

The project uses Just to wrap these commands into developer-friendly recipes. The migrate_db recipe calls the helper function above:

# crates/macro_db_client/justfile

migrate_db:
    just sqlx::migrate_db {{ DATABASE_URL }}

For a complete reset during development, the reset_local recipe drops and recreates the database before running migrations. According to the repository's justfile at lines 56-60, just reset_local orchestrates the full teardown and rebuild sequence, while just migrate_db applies migrations incrementally to an existing database.

Update the Offline Query Cache (.sqlx)

After modifying the schema, you must refresh the offline query cache so that SQLx can validate queries without a database connection. This step generates the .sqlx/ directory at the workspace root containing JSON metadata for all compile-time verified queries.

Run the preparation command from the repository root:

just prepare_db

Under the hood, this command—defined in tooling/just/sqlx.just at lines 14-26—executes sqlx migrate info followed by sqlx prepare for every crate that declares sqlx as a dependency. The sqlx prepare command connects to the database specified by DATABASE_URL, validates all queries in the codebase against the current schema, and serializes the type information to disk.

The STYLE_GUIDE.md at line 48 documents this requirement, emphasizing that the .sqlx/ directory must be committed to version control to support offline builds.

Build Code with SQLX_OFFLINE Mode

Once the cache is current, the entire workspace compiles without database connectivity. Set the SQLX_OFFLINE environment variable to true to instruct SQLx to read query metadata from the .sqlx/ directory instead of connecting to PostgreSQL:

SQLX_OFFLINE=true cargo check --workspace --all-features

The repository's Just files automatically inject this environment variable into build workflows. As implemented in tooling/just/rust.just at lines 6-11, recipes like just build, just check, and just clippy prepend SQLX_OFFLINE=true to all Cargo commands, ensuring consistent offline compilation across the development team.

Critical Restriction: Testing Requires Live Database

Never run tests with SQLX_OFFLINE=true. The test suite requires a live PostgreSQL instance to execute queries against actual data. As documented in CLAUDE.md at lines 251-254, if tests fail with "no cached data" errors, you must run just prepare_db again to refresh the cache rather than enabling offline mode. The offline cache is strictly for compilation; runtime query execution during testing always requires database connectivity.

Complete Developer Workflow

The typical development cycle combines these phases into a reproducible sequence:

# 1. Start local PostgreSQL (Docker Compose)

docker compose up -d postgres

# 2. Apply schema changes to live database

just reset_local          # Full reset: drop, create, migrate

# OR

just migrate_db           # Incremental migration only

# 3. Update offline query cache

just prepare_db

# 4. Compile without database dependency

SQLX_OFFLINE=true cargo build

# Or use the Just wrapper which sets the variable automatically:

just build

This workflow guarantees that the schema used by the application matches the migration history while keeping the compile step fast and reproducible across CI/CD pipelines and developer machines.

Key Implementation Files

Purpose	Path
Migration helper function	tooling/xtask/crates/xtask_local/src/local/db.rs
Migration SQL source files	crates/macro_db_client/migrations/
Offline cache generation	tooling/just/sqlx.just
Build recipes with SQLX_OFFLINE	tooling/just/rust.just
Testing restrictions	CLAUDE.md (lines 251-254)
High-level orchestration	justfile (lines 56-60)

Summary

• Live migrations use the sqlx migrate run command via xtask_local helpers to apply .sql files in crates/macro_db_client/migrations/ against a PostgreSQL instance.
• Cache preparation requires running just prepare_db after any schema change to populate the .sqlx/ directory with query metadata for offline compilation.
• Offline builds set SQLX_OFFLINE=true to compile the workspace without database connectivity, as automated in tooling/just/rust.just.
• Testing exclusion mandates that cargo test always runs against a live database; the offline cache is strictly for compilation-time query verification.

Frequently Asked Questions

How does SQLX_OFFLINE mode work in the Macro repository?

When SQLX_OFFLINE=true is set, SQLx skips establishing database connections during compilation and instead reads query metadata from JSON files in the .sqlx/ directory at the workspace root. This allows the Rust compiler to verify SQL query types and syntax without requiring a running PostgreSQL instance, making builds reproducible in CI environments.

What should I do if compilation fails with "no cached data for query" errors?

Run just prepare_db from the repository root to refresh the offline cache. This error indicates that a query in the Rust code does not have corresponding metadata in the .sqlx/ directory, either because the schema changed or a new query was added. After running the command, commit the updated .sqlx/ files to version control.

Why can't I run tests with SQLX_OFFLINE=true?

Tests execute queries against actual data and require runtime database connectivity to verify application logic. The offline cache only contains compile-time type metadata; it does not store data or support query execution. Running tests with SQLX_OFFLINE=true causes runtime failures because the test suite cannot connect to the database to insert test fixtures or assert on results.

Where are the database migrations stored in the Macro codebase?

Migration files reside in crates/macro_db_client/migrations/ and follow SQLx's sequential naming convention (e.g., 0001_baseline.sql). The xtask_local helper function references this path when invoking sqlx migrate run, ensuring all team members apply identical schema changes during development.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}