Queries that the compiler checks

Step 7 of 15. Step 6 gave the service a links table and startup migrations, but every handler still answers hardcoded data, and the one real query in the codebase — /health’s SELECT 1 — is a plain string the compiler treats as text. This step makes the compiler check queries: query! against query, what the macro actually does at build time, and cargo sqlx prepare so a build works on a machine with no database. The macros feature that has been sitting unused in Cargo.toml since step 5 starts earning its keep.

Start from step 6’s end state: git checkout api-ch06 in your clone of the examples repository, inside axum-sqlx-api/, with the Postgres container from step 5 running and DATABASE_URL exported.

What the runtime path can and cannot catch

The non-macro API is sqlx::query. You write the SQL as a string, attach parameters with .bind(), and pull columns out of rows yourself. Here is the whole program — a scratch project with the same [dependencies] as the service, running against the database from step 5:

use sqlx::postgres::PgPoolOptions;
use sqlx::Row;

#[tokio::main]
async fn main() {
    let url = std::env::var("DATABASE_URL").expect("DATABASE_URL is not set");
    let pool = PgPoolOptions::new().connect(&url).await.unwrap();

    let result = sqlx::query("SELECT code, url FROM links WHERE code = $1")
        .bind("abc123")
        .fetch_optional(&pool)
        .await;
    match result {
        Ok(Some(r)) => println!(
            "{} -> {}",
            r.get::<String, _>("code"),
            r.get::<String, _>("url")
        ),
        Ok(None) => println!("no row for abc123"),
        Err(e) => println!("error: {e}"),
    }
}

The table is empty, so put a row in first — the same docker exec from step 6’s checks:

docker exec shortlink-pg psql -U postgres -d shortlink -c \
  "INSERT INTO links (code, url) VALUES ('abc123', 'https://example.com/very/long/path');"

DATABASE_URL='postgres://postgres:dev@localhost:5432/shortlink' cargo run -q

abc123 -> https://example.com/very/long/path

Now misspell a column — cod instead of code in the SQL string — and run again:

error: error returned from database: column "cod" does not exist at line 3729

It compiled fine and failed at runtime, on a live request. The compiler never saw the SQL; it saw a string literal. Same story for types, one edit later: ask for the id column — a BIGINT in the schema from step 6 — as a String, and the failure moves from the query to the decode:

error: error occurred while decoding column "id": mismatched types; Rust type `alloc::string::String` (as SQL type `TEXT`) is not compatible with SQL type `INT8`

(That one came from row.try_get::<String, _>("id"), which returns the error; the row.get variant panics with the same message underneath.)

How much does the compiler know about these strings? Nothing. A small demonstration — build two queries in a plain fn main, no database involved, and print the SQL back:

use sqlx::postgres::Postgres;
use sqlx::Execute;

fn main() {
    let q = sqlx::query::<Postgres>("SELECT code, url FROM links WHERE code = $1").bind("abc123");
    println!("{}", q.sql().as_str());

    let typo = sqlx::query::<Postgres>("SELEC code FROM links");
    println!("{}", typo.sql().as_str());
}

SELECT code, url FROM links WHERE code = $1
SELEC code FROM links

SELEC compiles, because to the compiler it is text. One side note the demo above shows without advertising it: with no executor around to pin the database, sqlx::query needs the database type up front — that is the ::<Postgres> turbofish. Inside a handler the executor argument infers it.

The macro checks the query while building

The checked version swaps sqlx::query for the query! family of macros. One line changes in the service — the /health handler:

async fn health(Extension(pool): Extension<PgPool>) -> Result<&'static str, StatusCode> {
    // query! macros read the schema through DATABASE_URL at compile time.
    match sqlx::query_scalar!("SELECT 1").fetch_one(&pool).await {
        Ok(_) => Ok("ok"),
        Err(_) => Err(StatusCode::SERVICE_UNAVAILABLE),
    }
}

The full file is one git checkout api-ch07 away. No type annotation on the result this time. The macro knows SELECT 1 produces INT4 — but Postgres does not report nullability for an expression column, so the scalar comes back as Option<i32>; assigning it to a bare i32 fails with expected i32, found Option<i32>. The Ok(_) match in health does not care. A column read from a real table is typed more precisely — see below. Cargo.toml is untouched; the macros feature from step 5 is what makes this compile.

What the macro does at compile time is connect to DATABASE_URL, send the SQL to Postgres, and ask it to describe the statement — the same thing PREPARE does. That means the database is a build dependency now. Proof: stop the container and rebuild.

docker stop shortlink-pg
touch src/main.rs && cargo build

error: error communicating with database: Connection refused (os error 61)
  --> src/main.rs:67:11
   |
67 |     match sqlx::query_scalar!("SELECT 1").fetch_one(&pool).await {
   |           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_scalar` (in Nightly builds, run with -Z macro-backtrace for more info)

error: could not compile `shortlink` (bin "shortlink") due to 1 previous error

Exit code 101, from cargo build — not cargo run. The build is talking to Postgres. (One honest footnote: on this machine’s rustc 1.99.0, an incremental rebuild that hits a macro error sometimes follows it with an internal compiler error before dying. cargo clean -p shortlink and rebuild prints only the error above, which is the real one. CI builds from a clean tree never see the ICE.) Start the container again before moving on.

The payoff for that build-time round trip is what the string version could not give you. Point the macro at a column that does not exist:

sed -i "" "s/SELECT 1/SELECT cod FROM links/" src/main.rs
touch src/main.rs && cargo build

error: error returned from database: column "cod" does not exist at line 3729
  --> src/main.rs:67:11
   |
67 |     match sqlx::query_scalar!("SELECT cod FROM links").fetch_one(&pool).await {
   |           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_scalar` (in Nightly builds, run with -Z macro-backtrace for more info)

error: could not compile `shortlink` (bin "shortlink") due to 1 previous error

Same Postgres message the runtime version printed — but no binary was ever produced. A typo deployed is a 500 at request time; a typo caught here is a red build. Put SELECT 1 back.

Type errors die the same way. id is a BIGINT NOT NULL table column, so the macro types it as a plain i64; ask for a String and the build stops:

error[E0308]: mismatched types
  --> src/main.rs:67:21
   |
67 |     let s: String = sqlx::query_scalar!("SELECT id FROM links").fetch_one(&pool).await.unwrap_or_default();
   |            ------   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ expected `String`, found `i64`
   |            |
   |            expected due to this
   |
help: try using a conversion method
   |
67 |     let s: String = sqlx::query_scalar!("SELECT id FROM links").fetch_one(&pool).await.unwrap_or_default().to_string();
   |                                                                                                           ++++++++++++

For more information about this error, try `rustc --explain E0308`.
error: could not compile `shortlink` (bin "shortlink") due to 1 previous error

The runtime version of this error — the decode failure printed earlier — needed a running server and a request to arrive. Here it is a build error with a help: line pointing at the fix.

Building without a database

The build-time connection is a problem for CI, which usually has no Postgres. cargo sqlx prepare is the answer. With DATABASE_URL pointing at the running database:

cargo sqlx prepare

query data written to .sqlx in the current directory; please check this into version control

(The command rebuilds the project first, so the full output starts with a wall of Compiling lines; the line above is the last one.) .sqlx/ now holds one JSON file per macro query, named after a hash of the SQL string:

ls .sqlx

query-e004ebd5b5532a4b85984a62f8ad48a81aa3460c1ca07701f386135d72cdecf5.json

The file records what the database said about the query — the schema snapshot the macro needs:

{
  "db_name": "PostgreSQL",
  "query": "SELECT 1",
  "describe": {
    "columns": [
      {
        "ordinal": 0,
        "name": "?column?",
        "type_info": "Int4",
        "origin": "Expression"
      }
    ],
    "parameters": {
      "Left": []
    },
    "nullable": [
      null
    ]
  },
  "hash": "e004ebd5b5532a4b85984a62f8ad48a81aa3460c1ca07701f386135d72cdecf5"
}

With that file present, the macro no longer needs the live database. Unset the variable and rebuild:

env -u DATABASE_URL cargo build

   Compiling shortlink v0.1.0 (/Users/rango/www/hackingwithrust/code/axum-sqlx-api)
    Finished `dev` profile [unoptimized + debuginfo] target(s) in 1.86s

That is a build with no database running — CI needs nothing but Rust. For the record, what a build without the variable and without .sqlx/ looks like (a fresh clone whose author skipped prepare):

error: set `DATABASE_URL` to use query macros online, or run `cargo sqlx prepare` to update the query cache
  --> src/main.rs:67:11
   |
67 |     match sqlx::query_scalar!("SELECT 1").fetch_one(&pool).await {
   |           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_scalar` (in Nightly builds, run with -Z macro-backtrace for more info)

error: could not compile `shortlink` (bin "shortlink") due to 1 previous error

Commit .sqlx/ with the rest of the code. It is generated, but it is the contract between the queries and the schema, and it is what makes every clone buildable.

The stale cache, and the check

The failure mode prepare introduces: change a query’s SQL, forget to re-run prepare, and every database-less build breaks. Editing the query to SELECT 2 and building offline produces this:

error: set `DATABASE_URL` to use query macros online, or run `cargo sqlx prepare` to update the query cache

The same message as a missing cache — the macro hashes the SQL string, finds nothing under SELECT 2’s hash, and cannot tell “never prepared” from “stale”. The fix is one command either way: cargo sqlx prepare with a live DATABASE_URL.

Locally you will not hit this. When DATABASE_URL is set, the macro ignores the cache and asks the live database — verified here by editing the query with the old cache in place and building clean. Staleness is a database-less-CI problem, which is why cargo sqlx prepare --check exists. Run against a live database, it rebuilds and compares what the macros want with what .sqlx/ holds. Stale cache:

error: prepare check failed: .sqlx is missing one or more queries; you should re-run sqlx prepare

Exit code 1. Up-to-date cache: Finished ... target(s) in 0.23s, exit 0. In a CI job that has a Postgres service container, cargo sqlx prepare --check is the gate; in one that does not, plain cargo build already fails the build at the macro, which is the same outcome with a less specific message.

Where this leaves the service

/health is now compile-checked, .sqlx/ carries the query’s schema snapshot, and a clone builds with or without a database. Nothing else moved: the two handlers that answer hardcoded data get their real SQL next — the insert into links from POST /links and the lookup behind /{code} are step 8, and the compiler will be checking both, because the macros are in place now.

The end state is tagged api-ch07 in the examples repository — git checkout api-ch07 inside axum-sqlx-api/. Building the tag with no DATABASE_URL set is a working smoke test of the offline story: cargo build succeeds, and with the container up, cargo run serves ok on /health.

Next: inserting and reading links. query! gets its first real workout on the table this track has been building since step 6 — an INSERT ... RETURNING for the shortener and a lookup for the redirect. The track overview keeps the full list current as articles land.