# Database Migration Workflow in Macro Using SQLx and SQLX_OFFLINE Mode

> Learn Macro's three-phase SQLx workflow for database migration. Apply migrations, update cache with sqlx prepare, and compile with SQLX_OFFLINE=true for zero build-time dependencies.

- Repository: [Macro/macro](https://github.com/macro-inc/macro)
- Tags: how-to-guide
- Published: 2026-08-17

---

**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`](https://github.com/macro-inc/macro/blob/main/tooling/xtask/crates/xtask_local/src/local/db.rs), where the `migrate()` function constructs and executes the SQLx CLI command:

```rust
// 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:

```toml

# 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:

```bash
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:

```bash
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:

```bash

# 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`](https://github.com/macro-inc/macro/blob/main/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`](https://github.com/macro-inc/macro/blob/main/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`](https://github.com/macro-inc/macro/blob/main/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.