SQL checked at compile time with sqlx

Most database libraries choose between writing SQL (fast, no safety) and an ORM (safe, and now you are learning a query DSL instead of SQL). sqlx does something else: you write SQL, and it is checked against your real schema at compile time.

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

If users has no email column, that is a compile error. If id is a uuid and user_id is an i64, compile error. If User has a field the query does not select, compile error.

How it works

The query! macros connect to a database at compile time, using DATABASE_URL, and ask it to describe the query. Postgres will tell you the result columns, their types and their nullability without executing anything.

The macro then generates a struct matching that shape, or verifies your struct against it. The database’s own knowledge of the schema is the source of truth, which is why this catches things a hand-written mapping cannot.

What it catches that tests do not

Nullability. This is the one that impressed me. Postgres knows email is NOT NULL and deleted_at is not, so sqlx generates String for one and Option<String> for the other. A LEFT JOIN makes columns nullable, and the generated types follow — so a query that joins now produces Option<T> fields and the compiler forces you to handle the absent case.

Type mismatches after a migration. Change id from serial to uuid and every query using it fails to compile, listing each one. Without this, that migration is a runtime error found in production.

Column typos. SELECT emial FROM users is a compile error rather than a 3am page.

The CI problem, and sqlx prepare

The obvious objection: requiring a live database to compile is unworkable for CI, for a fresh clone, and for anyone building without one.

cargo sqlx prepare solves it. It runs the checks once and writes the results to .sqlx/:

cargo sqlx prepare
git add .sqlx

Committed, builds work offline — the macros read the cached metadata instead of connecting.

Two things follow from that, and both need to be in your process:

Re-run prepare whenever a query or the schema changes, and commit the result. Forgetting means CI compiles against stale metadata and passes while production fails.

Verify it in CI:

cargo sqlx prepare --check

That fails the build if the cached data is out of date, which turns “somebody forgot” from a production incident into a red pipeline.

When to use the non-macro API

query! cannot handle a query that is not known at compile time. For dynamic filtering, the runtime API is still there:

let mut builder = sqlx::QueryBuilder::new("SELECT id, name FROM users WHERE 1=1");

if let Some(name) = filter.name {
    builder.push(" AND name ILIKE ").push_bind(format!("%{name}%"));
}
if let Some(active) = filter.active {
    builder.push(" AND active = ").push_bind(active);
}

let users: Vec<User> = builder.build_query_as().fetch_all(&pool).await?;

No compile-time checking, and push_bind still parameterises properly — dynamic SQL does not mean string concatenation, and this is the API that keeps injection impossible.

Migrations

Built in, and plain SQL files:

sqlx migrate add create_users
# edit migrations/20260709120000_create_users.sql
sqlx migrate run

Run them at startup so a deployment cannot run against an old schema:

sqlx::migrate!("./migrations").run(&pool).await?;

The macro embeds the files in the binary, so there is nothing to deploy alongside it.

The honest downsides

Compile times. Every query! is a round trip to the database on a fresh build. With offline mode it is reading files instead, which is faster but not free.

Postgres is the best-supported backend. MySQL and SQLite work; the nullability inference is weaker, because those databases tell sqlx less.

The errors can be cryptic. A mismatch between a struct field and a column produces a macro expansion error that takes a moment to read.

None of that outweighs the main thing: after a year, I have not shipped a single “no such column” or “invalid input syntax for type” error. That entire category of bug is now a compile failure, and it is the strongest argument for the library.