# How to Set Up SQLx Compile-Time Query Validation with `just prepare_db` in Macro

> Learn how to set up SQLx compile-time query validation with just prepare_db in Macro. Ensure your SQL queries are valid against your PostgreSQL schema before compilation.

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

---

**Macro uses SQLx to validate database queries at compile time by regenerating a workspace-wide `.sqlx` cache via the `just prepare_db` command, ensuring all `query!` macros are checked against the current PostgreSQL schema before compilation.**

The Macro repository employs **SQLx** macros to enforce database correctness at compile time. This approach catches mismatched column names, wrong types, and missing tables before your code even compiles. The `just prepare_db` command serves as the central mechanism for synchronizing the offline query cache with your current PostgreSQL schema.

## Why SQLx Compile-Time Validation Matters in Macro

The Macro style guide **CS-08** mandates that all production queries use compile-time checked macros (`sqlx::query!`, `sqlx::query_as!`, `sqlx::query_scalar!`) rather than raw string queries. This requirement, documented in [`docs/STYLE_GUIDE.md`](https://github.com/macro-inc/macro/blob/main/docs/STYLE_GUIDE.md), ensures schema mismatches surface as compilation errors rather than runtime failures.

The validation metadata resides in a workspace-wide `.sqlx` directory at the repository root. As specified in **CS-09**, this cache is not committed on a per-crate basis but regenerated from the current schema each time you run `just prepare_db`.

## The Architecture of `just prepare_db`

The `just prepare_db` command is defined in `tooling/just/sqlx.just` and serves as a wrapper around `sqlx::prepare_db`. It connects to your PostgreSQL instance using the `DATABASE_URL` environment variable and generates JSON metadata files in the `.sqlx` directory.

When invoked from the repository root, the command scans all `query!` macros in your workspace, executes them against the live database to infer result types, and serializes this metadata into `.sqlx/*.json` files. This allows the Rust compiler to validate SQL correctness during `cargo build` without requiring a live database connection.

## Setting Up the Database Schema

Before running `just prepare_db`, ensure your database schema is current. Migration files reside in each `*_db_client` crate, such as `crates/macro_db_client/migrations`. These migrations define the tables, columns, and types that SQLx will validate against.

Start the required PostgreSQL containers and apply migrations:

```bash
docker compose -f docker/docker-compose.yml up -d postgres
just setup_macrodb

```

The `setup_macrodb` recipe creates or updates the macro database schema to match your migration files. You must complete this step before generating the SQLx cache; otherwise, `prepare_db` will fail when it cannot resolve table references.

## Running `just prepare_db` to Generate the Cache

Once your database schema is up to date, generate the compile-time validation cache:

```bash
just prepare_db

```

Verify the cache was generated by inspecting the `.sqlx` directory:

```bash
ls -R .sqlx

```

If you add or modify a SQL query, repeat this step before building or testing to ensure the offline metadata matches your schema.

## Testing vs. Compile-Time Validation

Macro distinguishes between offline compilation and runtime testing. While `just prepare_db` populates the offline cache for compilation, tests run against a live Docker Postgres instance.

Do not set `SQLX_OFFLINE=true` when running tests. As documented in [`CLAUDE.md`](https://github.com/macro-inc/macro/blob/main/CLAUDE.md), the test suite requires active database connections for validation. The `just prepare_db` command keeps your offline cache synchronized so that compilation succeeds, but runtime verification still requires the Docker environment.

## Customizing the Database URL

For environments requiring different database configurations, such as CI pipelines or local development variants, pass a custom URL explicitly:

```bash
just sqlx::prepare_db DB_URL=postgres://user:pw@localhost:5432/macro_dev

```

This flexibility allows you to generate query metadata against specific schema versions without modifying your default `DATABASE_URL` environment variable.

## Summary

- **Compile-time safety**: Macro mandates SQLx macros (`query!`, `query_as!`) per style guide **CS-08** in [`docs/STYLE_GUIDE.md`](https://github.com/macro-inc/macro/blob/main/docs/STYLE_GUIDE.md) to catch database errors during compilation.
- **Workspace-wide cache**: The `.sqlx` directory lives at the workspace root and is regenerated via `just prepare_db` (**CS-09**), not committed per-crate.
- **Command location**: The `just prepare_db` recipe is defined in `tooling/just/sqlx.just` and wraps `sqlx::prepare_db` using your `DATABASE_URL`.
- **Database prerequisites**: Run `just setup_macrodb` against your Docker Postgres instance before `just prepare_db` to ensure migrations are applied.
- **Test distinction**: Tests require live database connections; `SQLX_OFFLINE=true` is only for compilation, not testing.

## Frequently Asked Questions

### What does `just prepare_db` actually do?

`just prepare_db` connects to your PostgreSQL database, analyzes every `sqlx::query!` macro in your codebase, and generates JSON metadata files in the `.sqlx` directory. These files allow the Rust compiler to validate SQL queries against your schema without requiring a database connection during `cargo build`.

### Why am I getting "error: column does not exist" during cargo build?

This error indicates your `.sqlx` cache is out of sync with your database schema. Run `just setup_macrodb` to apply the latest migrations, then execute `just prepare_db` to regenerate the cache. The error will persist until the offline metadata in `.sqlx` matches your actual PostgreSQL schema.

### How often should I run `just prepare_db`?

Run `just prepare_db` every time you modify SQL queries or apply new migrations. The command is idempotent—you can run it safely multiple times, and it will refresh the cache to reflect your current database state.

### Can I skip the Docker setup and use `SQLX_OFFLINE=true` for everything?

No. While `SQLX_OFFLINE=true` enables compilation without a database, you must first generate the `.sqlx` cache using `just prepare_db` against a live PostgreSQL instance. Additionally, Macro's test suite requires a live Docker Postgres connection and will fail if you attempt to run tests in offline mode, as noted in [`CLAUDE.md`](https://github.com/macro-inc/macro/blob/main/CLAUDE.md).