How to get queries checked at compile time

Short answer: put the SQL in sqlx::query!("...") instead of sqlx::query("..."), and the macro talks to Postgres while the program compiles. A misspelled column or a parameter of the wrong type stops cargo build, and the query comes back as a struct with one field per column. cargo sqlx prepare freezes those checks into a committed folder, so a machine without a database can still build.

The previous page ends with the schema living in migration files. The queries themselves are still strings: the page before that passes a &str to sqlx::query(...), and every way it can be wrong — SQL that does not parse, a column name that does not match, a type the code assumed — surfaces at run time, as the ColumnNotFound panic in that page showed. The compiler cannot check a string. query! replaces the string with a macro that can.

The macro needs a database at build time

When the macro expands, it connects to the database named by DATABASE_URL, sends the SQL as a prepared statement without executing it, and reads back the column names and types. That has to work on every compile, so the URL lives in a .env file at the project root:

echo 'DATABASE_URL=postgres://postgres:dev@localhost:5432/shop' > .env

sqlx reads this file itself while the macro expands — nothing in the program loads it. The shop database is the one the previous page’s migrations built. The file holds a password and describes one machine, so add .env to .gitignore; how a build machine gets by without it is the last section of this page.

The macro itself is behind a feature flag, next to the dependencies the earlier pages already declared:

[dependencies]
sqlx = { version = "0.9", features = ["runtime-tokio", "tls-rustls", "postgres", "macros"] }
tokio = { version = "1", features = ["macros", "rt-multi-thread"] }

The same query, checked

The first query of the chapter again, now through the macro:

use sqlx::postgres::PgPoolOptions;

#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .connect("postgres://postgres:dev@localhost:5432/shop")
        .await
        .expect("failed to connect to Postgres");

    let dishes = sqlx::query!("SELECT id, name, price_cents FROM dishes ORDER BY id")
        .fetch_all(&pool)
        .await
        .expect("query failed");

    println!("{} dishes", dishes.len());
    for dish in dishes {
        println!("{}  {}  {}", dish.id, dish.name, dish.price_cents);
    }
}

3 dishes
1  edamame  350
2  onigiri  480
3  ramen  1450

(The rows are whatever the seed migration on the previous page inserted; with a different seed file, the numbers are yours.)

Three things from the earlier page have disappeared. There is no use sqlx::Row;, because there are no row.get("id") calls to import it for. There are no type annotations, because the macro learned the column types from Postgres while compiling — id is an INT4 column, so dish.id is an i32, decided at build time rather than inferred from a let. And there is no hand-written mapping loop, because query! returns its own generated struct with one named field per column. What the compiler now knows before the program has ever run: the SQL parses, every column it selects exists, and every field the code reads has the type the database says.

The typo that stops the build

The same mistake that caused the ColumnNotFound panic two pages ago — a column name that does not match anything — now fails before there is a program:

use sqlx::postgres::PgPoolOptions;

#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .connect("postgres://postgres:dev@localhost:5432/shop")
        .await
        .expect("failed to connect to Postgres");

    let row = sqlx::query!("SELECT id, nmae FROM dishes WHERE id = 1")
        .fetch_one(&pool)
        .await
        .expect("query failed");

    println!("{}  {}", row.id, row.nmae);
}

error: error returned from database: column "nmae" does not exist at line 3738
  --> src/main.rs:10:15
   |
10 |     let row = sqlx::query!("SELECT id, nmae FROM dishes WHERE id = 1")
   |               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query` (in Nightly builds, run with -Z macro-backtrace for more info)

error: could not compile `sqlx-checked` (bin "sqlx-checked") due to 1 previous error

(The crate name is yours.) The message is Postgres’s own — the macro asked the real server during the build, and the real server answered. There it would have been a 500 on the first request; here it is a red squiggle, because the macro expansion fails and the compiler never gets past it.

Parameters are checked too

The $1 in the SQL is bound to an ordinary Rust variable, and the macro checks those against the schema as well:

use sqlx::postgres::PgPoolOptions;

#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .connect("postgres://postgres:dev@localhost:5432/shop")
        .await
        .expect("failed to connect to Postgres");

    let wanted = 2;
    let dish = sqlx::query!("SELECT id, name, price_cents FROM dishes WHERE id = $1", wanted)
        .fetch_one(&pool)
        .await
        .expect("query failed");

    println!("{}  {}  {}", dish.id, dish.name, dish.price_cents);
}

2  onigiri  480

wanted is an i32 because the macro read the type of the id column while compiling and the parameter has to match it. Pass a string instead and the build fails with an ordinary type error, not a database error:

use sqlx::postgres::PgPoolOptions;

#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .connect("postgres://postgres:dev@localhost:5432/shop")
        .await
        .expect("failed to connect to Postgres");

    let row = sqlx::query!("SELECT name FROM dishes WHERE id = $1", "2")
        .fetch_one(&pool)
        .await
        .expect("query failed");

    println!("{}", row.name);
}

error[E0308]: mismatched types
  --> src/main.rs:10:69
   |
10 |     let row = sqlx::query!("SELECT name FROM dishes WHERE id = $1", "2")
   |                                                                     ^^^
   |                                                                     |
   |                                                                     expected `i32`, found `&str`
   |                                                                     expected due to the type of this binding

For more information about this error, try `rustc --explain E0308`.

The macro wrote the parameter’s expected type into the generated code from the column type it read at compile time, so a &str where the database says INT4 is a mismatch like any other — caught while typing, not while running.

cargo sqlx prepare: checking without a database

The price of all this is that every cargo build wants a database. On the machine where the code is developed, the .env file provides it. A build machine — CI — has neither the .env nor a Postgres, and the build dies:

cargo build

error: set `DATABASE_URL` to use query macros online, or run `cargo sqlx prepare` to update the query cache
  --> src/main.rs:11:16
   |
11 |     let dish = sqlx::query!("SELECT id, name, price_cents FROM dishes WHERE id = $1", wanted)
   |                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query` (in Nightly builds, run with -Z macro-backtrace for more info)

error: could not compile `sqlx-checked` (bin "sqlx-checked") due to 1 previous error

The fix is to freeze the checks. On the machine that has the database, run:

cargo sqlx prepare

    Checking sqlx-checked v0.1.0 (/path/to/project)
    Finished `dev` profile [unoptimized + debuginfo] target(s) in 0.45s
query data written to .sqlx in the current directory; please check this into version control

(The crate name, path, and duration are yours.) The folder holds one JSON file per query, named by a hash of the SQL text:

{
  "db_name": "PostgreSQL",
  "query": "SELECT id, name, price_cents FROM dishes WHERE id = $1",
  "describe": {
    "columns": [
      {
        "ordinal": 0,
        "name": "id",
        "type_info": "Int4",
        "origin": {
          "Table": {
            "table": "dishes",
            "name": "id"
          }
        }
      },

(The file continues with the remaining columns and their origins.) Everything the macro learned from the server is in there: the query text, each column’s name and type, and which table it came from. Commit the folder. From then on, a build with no .env, no DATABASE_URL, and no database uses the committed answers instead:

cargo build

   Compiling sqlx-checked v0.1.0 (/path/to/project)
    Finished `dev` profile [unoptimized + debuginfo] target(s) in 1.05s

SQLX_OFFLINE=true cargo build does the same while refusing the online path — the switch to reach for when the environment happens to have a DATABASE_URL the build should not depend on.

The working loop from here: change a query, build once against the real database, run cargo sqlx prepare, and commit the new files — the name is a hash of the SQL, so a changed query lands in a new file. What ships is a program whose queries the database itself has already agreed to.