SQLx Explained and How Compile Time Checked Queries Work

SQLx is a database library that checks your SQL while your program compiles. You write plain SQL in a macro, query! or query_as!; during compilation the macro connects to a real database, asks it to parse and describe the statement, and generates Rust types from the answer. A misspelled column, a parameter of the wrong type or a column you treated as non-null when it can be null become compile errors, in the editor, long before a test or a deploy. It is not an ORM and generates no SQL: you keep the query you wrote. This guide builds a small program against PostgreSQL, shows each kind of compile-time error with the compiler's own message, explains the one workflow trap that confuses everyone (the database has to exist and have the right schema when you compile), covers offline mode for CI, writes a transaction with row locking, and ends with the cases where the macros do not apply and you use the runtime API. Everything ran on Rust 1.99 with SQLx 0.9.0, sqlx-cli 0.9.0 and PostgreSQL 17 (in a container), so the error messages and the generated cache files are real.

What happens when you compile

Most libraries find out that a query is wrong when it runs. SQLx moves that moment into the build. The mechanism is worth understanding in order, because every behaviour later in this guide follows from it:

1. rustc expands a query! macro invocation. The macro is a procedure that runs inside the compiler.
2. It reads the DATABASE_URL environment variable (or a .env file) and opens a connection to that database, at compile time.
3. It sends the SQL to the server as a prepared statement and asks for a description: the types of the $1 placeholders and the name, type and nullability of every output column. PostgreSQL answers without running the query.
4. From that description the macro writes Rust: a struct for the row (for query!), or a check that your own struct matches (for query_as!), and typed parameters. Mismatches are reported as ordinary compiler errors pointing at your macro call.

The compiler is therefore verifying your Rust against the actual schema of the database you pointed it at. That is the feature, and it is also the source of the main trap: if the table is not there when you build, the build fails.

Set up

[package]
name = "sqlxlab"
version = "0.1.0"
edition = "2024"

[dependencies]
sqlx = { version = "0.9", features = ["runtime-tokio", "postgres", "macros", "migrate", "chrono"] }
tokio = { version = "1", features = ["full"] }
chrono = "0.4"

The features choose the runtime (runtime-tokio), the database driver (postgres; SQLx also supports MySQL and SQLite), the compile-time macros (macros), embedded migrations (migrate) and the type mapping for date and time (chrono; time is the alternative). TLS is its own set of features in 0.9 (tls-rustls, tls-rustls-aws-lc-rs, tls-native-tls and tls-none, among others), so add the one you want if your database requires encrypted connections; the examples here connect to a local container without it. Install the command-line tool once with cargo install sqlx-cli --no-default-features --features rustls,postgres, and create the schema as a migration:

CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    name        TEXT,
    karma       INTEGER NOT NULL DEFAULT 0,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

The workflow trap: the schema must exist before you compile

Write the program first, as most people do, and run the migrations from the program at startup:

use sqlx::postgres::PgPoolOptions;

#[derive(Debug)]
struct User {
    id: i64,
    email: String,
    name: Option<String>,
    karma: i32,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let url = std::env::var("DATABASE_URL").expect("DATABASE_URL");
    let pool = PgPoolOptions::new().max_connections(5).connect(&url).await?;
    sqlx::migrate!().run(&pool).await?;

    let id = sqlx::query_scalar!(
        "INSERT INTO users (email, name) VALUES ($1, $2) ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name RETURNING id",
        "[email protected]",
        "Ada"
    )
    .fetch_one(&pool)
    .await?;

    let user = sqlx::query_as!(User, "SELECT id, email, name, karma FROM users WHERE id = $1", id)
        .fetch_one(&pool)
        .await?;
    println!("{user:?}");

    let missing = sqlx::query_as!(User, "SELECT id, email, name, karma FROM users WHERE id = $1", -1_i64)
        .fetch_optional(&pool)
        .await?;
    println!("{missing:?}");
    Ok(())
}

The first build fails, and not where you expect. The code calls sqlx::migrate!().run(&pool), which would create the table, but that runs when the program runs, and the query! macros run when it compiles, earlier, against a database that has no users table yet:

error: error returned from database: relation "users" does not exist at line 1449
  --> src/main.rs:17:14
   |
17 |       let id = sqlx::query_scalar!(
   |  ______________^
...
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_scalar`

The fix is a workflow, not a code change: apply migrations to your development database before building, using the same tool every time.

export DATABASE_URL=postgres://postgres:secret@localhost:55432/app
sqlx migrate run
cargo run

Use sqlx migrate run and not a hand-run psql -f. When the table was created by hand in an earlier attempt, the program's embedded migrate!().run then failed at startup with relation "users" already exists, because SQLx records applied migrations in its own _sqlx_migrations table (it was empty, count 0) and, finding migration 1 unrecorded, tried to run it again. Let the tool apply them so the bookkeeping matches. After sqlx migrate run reported Applied 1/migrate users, the program built and printed:

User { id: 1, email: "[email protected]", name: Some("Ada"), karma: 20 }
None
 --> src/main.rs:5:5
  |
4 | struct User {
  |        ---- fields in this struct
5 |     id: i64,
  |     ^^
6 |     email: String,
  |     ^^^^^
7 |     name: Option<String>,
  |     ^^^^
8 |     karma: i32,
  |     ^^^^^
  |
  = note: `User` has a derived impl for the trait `Debug`, but this is intentionally ignored during dead code analysis
  = note: `#[warn(dead_code)]` (part of `#[warn(unused)]`) on by default

The row has name: Some("Ada") because the macro learned from the schema that name can be null, and typed the field as Option<String>. The lookup for id -1 returned None from fetch_optional, the right call for "maybe no row" (with fetch_one the same query would be an Error::RowNotFound).

The errors the compiler now catches

Each of these was run against the live database. They are the reason to use the macros.

A misspelled column. SELECT id, emial FROM users:

error: error returned from database: column "emial" does not exist at line 3723
 --> src/bin/err_column.rs:4:15
  |
4 |     let row = sqlx::query!("SELECT id, emial FROM users WHERE id = $1", 1_i64).fetch_one(&pool).await?;
  |               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The message is PostgreSQL's own, delivered by the compiler, with your macro call underlined. A wrong field type. A struct whose karma field is a String against an integer column:

error[E0277]: the trait bound `std::string::String: From<i32>` is not satisfied
 --> src/bin/err_type.rs:9:13
  |
9 |     let u = sqlx::query_as!(User, "SELECT id, karma FROM users WHERE id = $1", 1_i64).fetch_one(&pool).await?;
  |             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ the trait `From<i32>` is not implemented for `std::string::String`

The message is about a missing From impl, which is how SQLx converts column values into your fields; read it as "the column is an i32 and your field is not". A nullability mistake. A name: String field for the nullable name column gives the same shape of error with From<Option<String>>: the database says null is possible and the struct says it is not. A wrong parameter type. Passing the integer 42 for email = $1:

error[E0308]: mismatched types
 --> src/bin/err_param.rs:4:67
  |
4 |     let r = sqlx::query!("SELECT id FROM users WHERE email = $1", 42_i32).fetch_one(&pool).await?;
  |                                                                   ^^^^^^
  |                                                                   |
  |                                                                   expected `&str`, found `i32`

The placeholder type (text) became the expected Rust type (&str), and the mismatch is reported at the argument. None of these four needed a test. In a code base with hundreds of queries, the guarantee that every query was valid against the schema at the last compile is the thing you are buying.

Nullability, and how to override what the macro infers

PostgreSQL can report whether a column is nullable for table columns, but not for expressions: a COALESCE, a join or a computed value is "unknown", and SQLx conservatively types it Option<T>. You tell the macro what you know by adding a suffix to the column alias: "name!" means non-null (it will panic or error at run time if the database sends null), "name?" forces nullable, and "name: Type" overrides the Rust type.

struct User {
    id: i64,
    name: String,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = sqlx::PgPool::connect(&std::env::var("DATABASE_URL").unwrap()).await?;
    let u = sqlx::query_as!(User, r#"SELECT id, COALESCE(name, 'anonymous') AS "name!" FROM users WHERE id = $1"#, 1_i64)
        .fetch_one(&pool)
        .await?;
    println!("{} {}", u.id, u.name);
    Ok(())
}

Here COALESCE(name, 'anonymous') AS "name!" is non-null by construction, and with the ! the field can be a plain String. This compiled and ran with the cache file recording "nullable": [false, null]: the first column is non-null because it is the primary key, and the second is unknown, so the override is what makes it work. Use ! only when the SQL guarantees it, as here. Putting it on a left join column that can be missing converts a type error into a run-time failure.

Offline mode: building without a database

CI servers and Docker builds usually have no database at build time. SQLx's answer is to record the query descriptions in files you commit. Run the prepare command against your development database:

cargo sqlx prepare

It wrote a .sqlx/ directory with one JSON file per distinct query, named by a hash of the SQL (three files for this project, since the same SQL text used twice shares one). Each file holds exactly what the macro asked the database: the query text, the column names and types with their origin table, the parameter types and the nullable flags. When a .sqlx directory exists and no DATABASE_URL is set, the macros read the files instead of connecting. To prove it, the project built successfully with DATABASE_URL=postgres://[email protected]:1/none SQLX_OFFLINE=true, pointing at a database that does not exist. The cache is keyed by the SQL text, so editing a query invalidates its entry and the offline build fails with an instruction:

error: `SQLX_OFFLINE=true` but there is no cached data for this query, run `cargo sqlx prepare` to update the query cache or unset `SQLX_OFFLINE`
  --> src/main.rs:25:16
   |
25 |     let user = sqlx::query_as!(User, "SELECT id, email, name, karma FROM users WHERE id = $1 LIMIT 1", id)

Add LIMIT 1 to a query, forget to re-run prepare, and CI fails with exactly that. The discipline that keeps it honest is a CI step that runs cargo sqlx prepare --check against a database built from the migrations, and fails if the committed cache differs from what the code needs. Commit .sqlx; it is a build input like Cargo.lock.

A transaction with a row lock

Real programs change several rows together. A transaction groups statements so they all apply or none do. The transfer below moves karma between two users, locking the sender's row first so that two concurrent transfers cannot both read the same balance:

use sqlx::PgPool;

async fn transfer_karma(pool: &PgPool, from: i64, to: i64, amount: i32) -> Result<(), sqlx::Error> {
    let mut tx = pool.begin().await?;
    let balance = sqlx::query_scalar!("SELECT karma FROM users WHERE id = $1 FOR UPDATE", from).fetch_one(&mut *tx).await?;
    if balance < amount {
        return Err(sqlx::Error::Protocol(format!("user {from} has {balance} karma, needs {amount}")));
    }
    sqlx::query!("UPDATE users SET karma = karma - $1 WHERE id = $2", amount, from).execute(&mut *tx).await?;
    sqlx::query!("UPDATE users SET karma = karma + $1 WHERE id = $2", amount, to).execute(&mut *tx).await?;
    tx.commit().await
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = PgPool::connect(&std::env::var("DATABASE_URL").unwrap()).await?;
    sqlx::query!("INSERT INTO users (email, karma) VALUES ('[email protected]', 10) ON CONFLICT (email) DO UPDATE SET karma = 10").execute(&pool).await?;
    sqlx::query!("UPDATE users SET karma = 50 WHERE email = '[email protected]'").execute(&pool).await?;
    let ada = sqlx::query_scalar!("SELECT id FROM users WHERE email = '[email protected]'").fetch_one(&pool).await?;
    let bob = sqlx::query_scalar!("SELECT id FROM users WHERE email = '[email protected]'").fetch_one(&pool).await?;

    println!("first transfer: {:?}", transfer_karma(&pool, ada, bob, 30).await);
    println!("second transfer: {:?}", transfer_karma(&pool, ada, bob, 30).await);
    let rows = sqlx::query!("SELECT email, karma FROM users ORDER BY id").fetch_all(&pool).await?;
    for r in rows {
        println!("{} has {}", r.email, r.karma);
    }
    Ok(())
}

first transfer: Ok(())
second transfer: Err(Protocol("user 1 has 20 karma, needs 30"))
[email protected] has 20
[email protected] has 40

The sender started with 50 karma. The first transfer of 30 committed (20 and 40). The second asked for 30 from a balance of 20 and returned an error before changing anything; because the function returned early with tx still open, the transaction was dropped, which rolls it back, so the balances are unchanged. That drop-means-rollback behaviour is the safety net; call commit() explicitly on the success path. FOR UPDATE makes a concurrent transfer for the same user wait for this one, which turns a lost-update race into a queue. Note that the statements run on &mut *tx: the transaction borrows a single connection, so all its statements use that connection and see its uncommitted changes. For the isolation levels and when FOR UPDATE is not enough, see PostgreSQL isolation levels, and for the pool the connection comes from, pool sizing.

When the SQL is not known at compile time

The macros need a string literal. A search form with optional filters builds its SQL at run time, and for that SQLx has QueryBuilder, which keeps values out of the SQL text by sending them as bound parameters. The row type derives FromRow and is checked at run time instead:

use sqlx::{FromRow, PgPool, QueryBuilder, Postgres};

#[derive(Debug, FromRow)]
struct UserRow {
    id: i64,
    email: String,
    karma: i32,
}

struct Filter {
    min_karma: Option<i32>,
    email_contains: Option<String>,
}

async fn search(pool: &PgPool, f: &Filter) -> Result<Vec<UserRow>, sqlx::Error> {
    let mut q: QueryBuilder<Postgres> = QueryBuilder::new("SELECT id, email, karma FROM users WHERE 1 = 1");
    if let Some(min) = f.min_karma {
        q.push(" AND karma >= ").push_bind(min);
    }
    if let Some(text) = &f.email_contains {
        q.push(" AND email ILIKE ").push_bind(format!("%{text}%"));
    }
    q.push(" ORDER BY id");
    q.build_query_as::<UserRow>().fetch_all(pool).await
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = PgPool::connect(&std::env::var("DATABASE_URL").unwrap()).await?;
    let everyone = search(&pool, &Filter { min_karma: None, email_contains: None }).await?;
    println!("{} users", everyone.len());
    let rich = search(&pool, &Filter { min_karma: Some(30), email_contains: None }).await?;
    println!("karma >= 30: {:?}", rich);
    let hostile = search(&pool, &Filter { min_karma: None, email_contains: Some("' OR '1'='1".into()) }).await?;
    println!("injection attempt matched {} rows", hostile.len());
    Ok(())
}

2 users
karma >= 30: [UserRow { id: 2, email: "[email protected]", karma: 40 }]
injection attempt matched 0 rows
7 |     karma: i32,

The injection attempt (' OR '1'='1) matched zero rows: it was bound as a parameter, the text %' OR '1'='1% that no email contains, not spliced into the SQL. push_bind is the safe way to add a value; the unsafe way would be format! into push. The trade-off is what you lose: the compiler cannot check a dynamic query against the schema, so a wrong column name fails when the code path runs. Keep dynamic queries few and test each one. The sibling approach for typed dynamic queries is covered in QueryBuilder, and mapping rows to structs without macros in query_as and FromRow.

Migrations: what the tool records and enforces

A migration is a SQL file with a number, and the tool's job is to apply each exactly once, in order, and to refuse to continue if history was rewritten. sqlx migrate add name creates the next numbered file; adding -r creates a pair, NNNN_name.up.sql and NNNN_name.down.sql, so the change can be reverted. sqlx migrate info lists each migration as installed or pending, and the state lives in the _sqlx_migrations table, including a checksum of each file's contents. The checksum is what protects you from the most dangerous kind of edit. Appending a comment to the already-applied 0001_users.sql and running the tool again produced:

error: migration 1 was previously applied but has been modified

That refusal is correct: other environments have applied the old text, and silently accepting a changed file would leave your databases with different schemas under the same version number. Fix mistakes with a new migration, never by editing an old one. A reversible pair worked as expected in the experiment: sqlx migrate run applied 0002 (a CREATE INDEX), sqlx migrate revert undid it by running the down file, and info then showed 2 as pending again. One thing to see for yourself: the down file the tool generates is empty, and an empty down migration "reverts" by only removing the record, leaving the index in place. Write the DROP INDEX in the down file when you create the pair, and test that up, down, up works on a scratch database. Embed the migrations in the binary with sqlx::migrate!() (as the program above does) so that the same artifact carries its schema, and run them at startup only if a single instance starts at a time; with many instances starting together, take an advisory lock (SQLx does this for Postgres) or run migrations as a separate deployment step. The zero-downtime constraints on what a migration may contain are the subject of zero-downtime migrations.

Testing code that uses SQLx

Since the queries need a database to compile, they need one to test as well, and mocking the database defeats the purpose of checking real SQL. SQLx ships a test attribute that gives each test its own throwaway database, created from your migrations and dropped afterwards, so tests run in parallel without sharing rows:

#[sqlx::test(migrations = "./migrations")]
async fn inserting_twice_updates_the_name(pool: sqlx::PgPool) -> sqlx::Result<()> {
    for name in ["Ada", "Ada Lovelace"] {
        sqlx::query!("INSERT INTO users (email, name) VALUES ($1, $2) ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name", "[email protected]", name)
            .execute(&pool)
            .await?;
    }
    let row = sqlx::query!("SELECT count(*) AS \"n!\", min(name) AS name FROM users").fetch_one(&pool).await?;
    assert_eq!(row.n, 1);
    assert_eq!(row.name.as_deref(), Some("Ada Lovelace"));
    Ok(())
}

The test receives a pool connected to a fresh database named after the test; the harness creates it from DATABASE_URL (so the user needs the right to create databases), applies the migrations and drops it afterwards. This test passed in 0.33 seconds, including creating and dropping its database. That is slower than an in-memory fake and exactly as accurate as production. The full treatment, including fixtures and parallelism limits, is in the sqlx test attribute.

SQLx 0.9 enforces the rule at the type level. The plain runtime functions, sqlx::query, query_as and query_scalar, now accept only string literals (the trait SqlSafeStr is implemented for &'static str). Passing a String built with format! is a compile error: "dynamic SQL strings should be audited for possible injections", with the note "prefer literal SQL strings with bind parameters or QueryBuilder to add dynamic data to a query". When you have audited the string (an identifier chosen from a fixed list, never user input), wrap it in sqlx::AssertSqlSafe(...), which makes the promise visible in code review. Code written for 0.8 that formats SQL strings will hit this on upgrade, and each hit is worth reading as a prompt to check for injection, not just to add the wrapper.

Compile-time cost and what it does to your build

Every macro invocation is a round trip to the database during compilation, and the compiler waits. A hundred queries means a hundred prepare calls on every full build, which is why the first builds in this project took several seconds longer than a build with plain query() calls. Incremental builds only redo changed macros, and offline mode removes the network entirely by reading local files, which is also faster. Two practical consequences: keep a developer database always running (a container started by docker compose up -d), and in CI use the offline cache so the build does not depend on a service. The macro also makes cargo check and rust-analyzer connect to your database, so a stopped database makes the editor show errors on every query; set SQLX_OFFLINE=true in the editor environment if that bothers you and the cache is current.

What goes wrong

When SQLx is not the right tool

Use it when you want to write SQL, you have a database available at build time or can commit the cache, and your schema is the source of truth. It is the wrong fit when you want the library to generate queries and manage relations (look at an ORM such as SeaORM or Diesel; the trade-offs are in SQLx versus Diesel versus SeaORM), when the schema changes underneath you faster than you can keep the cache current, or when your queries are mostly assembled at run time. The macros also check only what the database can describe: they cannot verify that a WHERE clause is selective, that an index is used or that a transaction has the right isolation. For those, read the EXPLAIN output, as in PostgreSQL EXPLAIN and indexes. A good division of labour: let the compiler prove the queries are well-formed, and let tests against a real database prove they are correct.