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

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

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:

just prepare_db

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

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

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 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.

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