# How to Update the SQLx Query Cache in Macro

> Learn how to update the SQLx query cache in macro. Simply run just prepare_db after modifying SQL queries or migrations to refresh compile-time metadata.

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

---

**Run `just prepare_db` from the repository root after changing any SQL query or migration to regenerate the compile-time metadata cache.**

The macro-inc/macro codebase relies on SQLx for compile-time query validation against PostgreSQL schemas. When you update the SQLx query cache in macro after modifying `.sql` files or query strings, you ensure the `.sqlx` metadata remains synchronized with your database schema, preventing build failures.

## Prerequisites for Cache Updates

Before regenerating the cache, ensure your environment meets these requirements:

- A running PostgreSQL database accessible via the `DATABASE_URL` environment variable
- The `just` command installed (available through `nix develop` or system packages)
- Shell session at the workspace root where the top-level `justfile` resides

Set your database connection string:

```bash
export DATABASE_URL=postgres://macro:macro@localhost:5432/macro_db

```

## Running the Cache Preparation Command

The cache generation is orchestrated through the `just` build system. According to the source code in `tooling/just/sqlx.just`, the `prepare_db` recipe executes `cargo sqlx prepare --workspace` to scan all crates and record query metadata.

### Standard Cache Update

From the workspace root, run:

```bash
just prepare_db

```

This regenerates the `.sqlx` directory at the workspace root, creating JSON metadata files for each query encountered in the source code.

### Including Test-Only Queries

If tests fail with missing cache errors, the query likely only appears in test code. Run the preparation command with the `--tests` flag as defined in the justfile recipe:

```bash
just prepare_db --tests

```

This ensures SQLx caches queries embedded in `#[cfg(test)]` blocks and integration tests.

## What Triggers Cache Regeneration

Any change to SQL strings used in macros requires an update. For example, modifying this pattern in `macro_db_client` or other crates necessitates running `just prepare_db`:

```rust
#[allow(dead_code)]
pub async fn get_user(pool: &sqlx::PgPool, user_id: i64) -> sqlx::Result<User> {
    sqlx::query_as!(
        User,
        "SELECT id, email, created_at FROM users WHERE id = $1",
        user_id
    )
    .fetch_one(pool)
    .await
}

```

## Where the Cache Lives

The `.sqlx` directory **must remain at the workspace root**. As documented in [`docs/STYLE_GUIDE.md`](https://github.com/macro-inc/macro/blob/main/docs/STYLE_GUIDE.md) under rule CS-09 and reinforced in [`CLAUDE.md`](https://github.com/macro-inc/macro/blob/main/CLAUDE.md), committing the cache inside individual crates violates the project structure. The workspace-level location allows all crates to share the same metadata, including `macro_db_client` which provides a crate-specific shortcut in `crates/macro_db_client/justfile` that ultimately calls the root recipe.

## Critical Rules for Cache Management

When you update the SQLx query cache in macro, adhere to these constraints defined in the style guides:

- **Never edit `.sqlx/query-*.json` files manually.** These are generated artifacts overwritten on every `just prepare_db` run.
- **Do not commit the `.sqlx` directory inside crate folders.** It must stay at the workspace root per CS-09.
- **Keep `SQLX_OFFLINE` unset** when running tests. If you see "no cached data" errors during test execution, simply rerun `just prepare_db` rather than enabling offline mode.

```toml

# Example Cargo.toml triggering cache updates when queries change

[dependencies]
sqlx = { version = "0.7", features = ["postgres", "runtime-tokio"] }

```

## Summary

- Run `just prepare_db` after any SQL modification to regenerate the `.sqlx` cache
- Use `just prepare_db --tests` when test-only queries trigger cache misses
- Store the cache exclusively at the workspace root, never inside individual crates
- Do not manually edit generated JSON files in `.sqlx/`
- Ensure `DATABASE_URL` points to a valid PostgreSQL instance before running the preparation command

## Frequently Asked Questions

### What happens if I manually edit a `.sqlx/query-*.json` file?

Your changes will be lost. The SQLx preparation command in `tooling/just/sqlx.just` regenerates these files completely on each run. Manually editing them also risks creating invalid metadata that causes compilation errors.

### Can I move the `.sqlx` directory into a specific crate like `macro_db_client`?

No. The repository style guide in [`docs/STYLE_GUIDE.md`](https://github.com/macro-inc/macro/blob/main/docs/STYLE_GUIDE.md) (CS-09) explicitly requires the cache to remain at the workspace root. Individual crates like `macro_db_client` reference the root cache through their `justfile` shortcuts.

### Why do my tests fail with "no cached data" after running `just prepare_db`?

The query likely only exists in test code. The standard preparation command scans production code by default. Run `just prepare_db --tests` to include test suites in the cache generation, as documented in [`CONTRIBUTING.md`](https://github.com/macro-inc/macro/blob/main/CONTRIBUTING.md).

### How do I update the cache when working offline?

You cannot generate new cache entries without a live database connection. The `cargo sqlx prepare` command requires `DATABASE_URL` to verify queries against the actual PostgreSQL schema. Set up a local database or use containerized PostgreSQL for development.