How to Perform Database Queries in Macro: SQLx Compile-Time Macros Explained

Macro exclusively uses SQLx compile-time checked macros—query!, query_as!, and query_scalar!—for all database interactions to ensure type-safe, schema-validated queries that catch errors at compile time rather than runtime.

The macro repository is an open-source project that leverages Rust's type system to eliminate runtime SQL errors. For developers working with this codebase, understanding the preferred approach to database queries in macro is essential, as the project enforces strict compile-time validation through SQLx macros rather than dynamic query construction.

Why Compile-Time Checked Macros Are Preferred for Database Queries in Macro

According to the project's architectural guidelines documented in CLAUDE.md (lines 46-49), the codebase explicitly mandates compile-time verification:

"Prefer SQLx compile-time checked macros (query!, query_as!, query_scalar!) for database queries whenever possible instead of dynamic sqlx::query calls."

This architectural decision ensures that schema mismatches are caught during compilation rather than in production. When you modify the database schema, the Rust compiler immediately flags any affected queries, preventing runtime failures. The repository maintains this standard across all crates, from the core database client to webhook handlers.

The Three Essential SQLx Macros for Database Queries in Macro

The macro codebase utilizes three primary macros for type-safe database access:

sqlx::query! for Raw Row Access

The sqlx::query! macro executes SQL and returns anonymous rows with compile-time verified column types. This macro appears in crates/webhook/src/outbound/pg_repository.rs, where the codebase inserts and updates webhook records without needing custom structs for intermediate operations.

sqlx::query_as! for Struct Mapping

The sqlx::query_as! macro maps query results directly onto Rust structs, providing the most common pattern for database queries in macro. The macro validates that SQL column names and types match the target struct's fields at compile time. This approach is demonstrated in crates/macro_db_client/src/document/list_documents_with_access.rs.

sqlx::query_scalar! for Single Values

The sqlx::query_scalar! macro retrieves individual scalar values (such as counts or IDs) with type safety, eliminating boilerplate when you need only a single column from a row.

Practical Implementation of Database Queries in Macro

When fetching data in the macro repository, you bind parameters using positional placeholders ($1, $2) and let the compiler verify the types match your database schema.

Fetching a user record with automatic struct mapping:

let user = sqlx::query_as!(
    User,                         // Rust struct to map the row to
    r#"SELECT id, email, name FROM "User" WHERE id = $1"#,
    user_id                       // bind parameter
)
.fetch_one(&pool)
.await?;                         // Returns a `User` instance

Inserting data and returning the generated record:

let doc = sqlx::query_as!(
    Document,
    r#"
    INSERT INTO "Document" (title, owner_id, created_at)
    VALUES ($1, $2, now())
    RETURNING id, title, owner_id, created_at
    "#,
    title,
    owner_id
)
.fetch_one(&pool)
.await?;

Both examples demonstrate how database queries in macro validate SQL syntax and schema compatibility during compilation, ensuring that column names, table names, and type signatures remain synchronized with the actual PostgreSQL schema.

Maintaining Query Validity with Offline Mode

The macro repository supports offline query validation through the SQLX_OFFLINE environment variable. This workflow enables compilation without a live database connection by using cached query metadata.

After any schema change, you must update the query cache using the standardized task runner:

just prepare_db

This command regenerates the .sqlx directory containing query metadata, allowing the compiler to continue validating database queries in macro even in CI/CD environments without database connectivity.

Real-World Examples from the Macro Codebase

The convention of using compile-time checked macros appears consistently throughout the repository.

In crates/macro_db_client/src/document/list_documents_with_access.rs, the codebase implements complex document retrieval using sqlx::query! macros to ensure that access control queries remain valid across schema migrations.

Similarly, crates/webhook/src/outbound/pg_repository.rs demonstrates persistent storage operations for webhook delivery attempts, utilizing sqlx::query! for inserts and updates while maintaining type safety for JSON payload handling and status tracking.

These implementations illustrate that database queries in macro follow a uniform pattern: compile-time verification first, runtime execution second.

Summary

• Macro mandates SQLx compile-time macros (query!, query_as!, query_scalar!) over dynamic queries for all database access.
• Schema changes are caught at compile time, preventing runtime SQL errors in production environments.
• The CLAUDE.md architectural guide (lines 46-49) explicitly documents this preference as a project requirement.
• Offline mode via SQLX_OFFLINE and the just prepare_db command maintains compile-time checking without requiring live database connections during builds.
• Real-world implementations in crates/macro_db_client/src/document/list_documents_with_access.rs and crates/webhook/src/outbound/pg_repository.rs demonstrate consistent application of these patterns.

Frequently Asked Questions

What happens if I use dynamic sqlx::query instead of the compile-time macros in macro?

Dynamic sqlx::query calls bypass compile-time schema validation, violating the architectural standards defined in CLAUDE.md. While the code may compile, you lose the guarantee that your SQL matches the current schema, risking runtime errors when columns are renamed or types change.

How do I update the query cache after modifying the database schema?

Run just prepare_db from the project root after any schema migration. This command updates the .sqlx query cache files, ensuring that subsequent compilations validate against the new schema structure even when SQLX_OFFLINE is enabled.

Can I use sqlx::query_as! with custom structs that don't match the table schema exactly?

Yes, but the macro requires that the SQL column names and types are compatible with the struct fields. You can use SQL aliases and transformations to match struct fields, but the compiler will reject mismatches. For partial mappings, consider selecting specific columns or using sqlx::query! and manually constructing your structs.

Where are the architectural guidelines for database queries documented in the macro repository?

The primary architectural documentation resides in CLAUDE.md at the repository root, specifically lines 46-49, which explicitly state the preference for SQLx compile-time checked macros over dynamic queries.

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