The missing semicolon that crashes production
You push a feature to staging. The code compiles cleanly. The tests pass. Then a user clicks a button and the server returns a 500 error. The logs show column "email_address" does not exist. You renamed the column to email in the database migration, but you forgot to update the raw SQL string in your Rust code. The compiler never caught it because it treats the query as an opaque string.
That gap between your database schema and your Rust code is where most data layer bugs live. sqlx closes it by moving SQL validation from runtime to compile time. Instead of hoping your strings match your tables, the compiler verifies them against a live database before your code ever runs.
How compile-time checking actually works
Compile-time checked queries bridge two worlds that usually ignore each other. SQL databases track their own schemas. Rust tracks its own types. sqlx connects them during the build process. When you wrap a query in the sqlx::query! macro, the compiler does not just parse Rust syntax. It reaches out to your database, asks for the exact column names and data types, and generates Rust code that enforces them.
Think of it like a type-safe adapter for a power outlet. You could plug a lamp in and hope it works, or you could use an adapter that physically prevents you from inserting the wrong plug. sqlx generates that adapter at compile time. If the database changes, the adapter breaks, and your build fails. You catch the mismatch before deployment.
The minimal setup
The setup requires one environment variable and one macro. You point sqlx at a database, wrap your SQL in query!, and let the compiler do the heavy lifting.
use sqlx::query;
#[tokio::main]
async fn main() {
// The macro expands into a type-checked query builder.
// It validates syntax and column names against the database.
let query = query!("SELECT name FROM users WHERE id = $1", 1);
// We intentionally skip execution here to focus on the macro.
// The compiler already verified the query structure.
println!("Query is valid and ready to execute.");
}
This code will not compile until you tell sqlx where to find the schema. Set the DATABASE_URL environment variable to point at a real database, then run cargo sqlx prepare. The command scans your source files, connects to the database, and verifies every macro.
Run cargo sqlx prepare before every build. It is the bridge between your schema and your compiler.
Parameter binding and type safety
Raw SQL strings accept parameters using positional placeholders like $1, $2, and $3. sqlx enforces type safety on these parameters just as strictly as it does on the result columns. When you pass a Rust value to a placeholder, the macro checks that the Rust type can safely encode into the corresponding SQL type.
use sqlx::{query, PgPool};
/// Fetches a user by ID and returns the name.
/// The $1 placeholder is bound to the user_id parameter.
async fn get_user_name(pool: &PgPool, user_id: i64) -> Result<String, sqlx::Error> {
// The macro verifies that i64 maps to the database's BIGINT type.
// It also checks that the query returns exactly one column.
let name = query_scalar!("SELECT name FROM users WHERE id = $1", user_id)
.fetch_one(pool)
.await?;
// The compiler guarantees the result matches the expected type.
// No manual parsing or unsafe casting is required.
Ok(name)
}
The $1 syntax comes from PostgreSQL and SQLite conventions. MySQL uses ? placeholders, but sqlx normalizes them behind the scenes. You write $1 in your code regardless of the database backend. The macro expands to database-specific binding code during compilation.
If you pass a String to a $1 placeholder that expects a BIGINT, the compiler rejects it with E0277 (trait bound not satisfied). sqlx refuses to guess your intent. You must provide a value that matches the expected SQL type, or cast it explicitly in Rust before binding.
Keep your parameter types aligned with your database columns. The macro will enforce the contract at compile time.
What happens under the hood
When you run cargo sqlx prepare, the tool parses your Rust source files looking for sqlx macros. For each macro, it opens a connection to the database specified in DATABASE_URL. It sends the raw SQL string to the database engine and requests metadata. The database returns the exact number of columns, their names, and their native SQL types.
sqlx converts that metadata into a Rust struct definition and writes it to a sqlx-data.json file in your project root. The macro then expands to code that references this struct. When you compile, the Rust compiler checks your code against the generated struct. If you request a column that does not exist, or if you try to map a TEXT column to a u32, the compiler rejects the code with a type mismatch error.
The sqlx-data.json file acts as a snapshot of your database schema at the moment of preparation. You can commit it to version control. Other developers and CI pipelines can build your project without a live database connection by setting SQLX_OFFLINE=true. The compiler trusts the cached metadata instead of reaching out to a server.
Commit sqlx-data.json to your repository. It turns a network dependency into a reproducible build artifact.
A realistic service layer
Real applications rarely fetch raw columns. You usually map query results to Rust structs. sqlx provides query_as! for this exact pattern. It matches database columns to struct fields by name and validates the type conversion.
use sqlx::{query_as, PgPool};
/// Represents a user record fetched from the database.
/// Field names must match the SQL column names exactly.
#[derive(sqlx::FromRow)]
struct User {
id: i64,
name: String,
is_active: bool,
}
/// Fetches a single user by their primary key.
/// Returns the mapped struct or a database error.
async fn get_user(pool: &PgPool, user_id: i64) -> Result<User, sqlx::Error> {
// The macro maps columns to struct fields by name.
// It verifies that the database types match the struct types.
let user = query_as!(User, "SELECT id, name, is_active FROM users WHERE id = $1", user_id)
.fetch_one(pool)
.await?;
// The compiler guarantees the struct is fully populated.
// No partial data or missing fields can slip through.
Ok(user)
}
The #[derive(sqlx::FromRow)] attribute tells sqlx how to decode the database row into memory. The macro checks that every field in User has a corresponding column in the query result. It also verifies that the database type can safely convert to the Rust type. A TEXT column maps to String. A BOOLEAN column maps to bool. A BIGINT column maps to i64.
If you rename a struct field but forget to update the SQL, the compiler stops you. If you change a database column from VARCHAR to JSONB, the compiler stops you. The type system enforces schema consistency.
Keep your struct fields in sync with your database columns. The compiler will enforce the contract for you.
The offline build workflow
Development machines usually have a local database running. Continuous integration servers do not. sqlx solves this mismatch with offline mode. When SQLX_OFFLINE=true is set in the environment, the compiler skips the database connection step. It reads the cached metadata from sqlx-data.json and validates your code against that snapshot.
This convention is standard across the Rust ecosystem. You run cargo sqlx prepare on your local machine after every schema change. You commit the updated sqlx-data.json to your repository. Your CI pipeline sets SQLX_OFFLINE=true and builds successfully without provisioning a database container.
If you forget to update the cache after a migration, your local build will succeed but your CI build will fail with a type mismatch. The error message points directly to the macro that expects a column that no longer exists. You fix it by running cargo sqlx prepare locally and committing the new cache.
Never commit a stale sqlx-data.json. It breaks the trust model of offline compilation.
Where things go wrong
The compile-time checks are strict, which means they catch mistakes early. They also introduce a few workflow friction points that trip up newcomers.
The most common issue is a missing or incorrect DATABASE_URL. If the environment variable points to a database that lacks the required tables, cargo sqlx prepare fails. The compiler cannot generate the type definitions without the schema. You must run the preparation command against a database that matches your production schema, or use a local development database with identical structure.
Another frequent mistake is ignoring the sqlx-data.json cache. Developers often delete it when they see a type mismatch, assuming it is stale. The file is the source of truth for offline builds. If your schema changed, you need to run cargo sqlx prepare again to update the cache. Deleting it breaks offline compilation for everyone else.
Type mapping errors show up as trait bound failures. If you try to map a TIMESTAMP column to a String, the compiler rejects it with E0277 (trait bound not satisfied). sqlx requires explicit type compatibility. You can fix it by changing the Rust field to chrono::DateTime<chrono::Utc> or by casting the column in SQL with ::TEXT.
Dynamic queries break the macro system. If you need to build a WHERE clause based on user input, query! will not work. The macro requires a static SQL string. You must fall back to sqlx::query with a runtime string, or use sqlx::QueryBuilder to assemble the statement safely. The compiler cannot check dynamic strings, so you lose the compile-time guarantees.
The compiler will not guess your intent. Fix the type mismatch at the source.
Picking your query style
Use query! when you need raw column access and want to avoid struct allocation overhead. Use query_as! when you are mapping results to domain structs and want automatic field alignment. Use query_scalar! when you only need a single value like a count or a flag and want to skip row decoding. Use runtime string queries when you are building dynamic search filters with arbitrary WHERE clauses that cannot be known at compile time.
Pick the macro that matches your data shape. The compiler will handle the rest.