When strings lie but types don't
You're shipping a feature. The query works in your test database. You merge, deploy, and the production server throws a 500 error. Why? Someone renamed user_id to id in a migration three weeks ago, and your code still has the old string. Or the column type shifted from INTEGER to TEXT, and Rust's type system just shrugged because the SQL was a String. You spent an hour debugging a typo that the compiler could have caught if it understood SQL.
This is the gap sqlx closes. The crate brings the compiler into the database conversation. Instead of treating SQL as opaque text, you use macros like query! and query_as! that read your database schema and verify your queries before the binary even builds. The compiler checks your SQL syntax, validates column names, and ensures the Rust types match the database types. If anything is wrong, the build fails. The error shows up on your machine, not in production logs.
The macro that reads your database
sqlx macros expand during compilation. They don't just wrap your SQL string. The macro code connects to a database using the DATABASE_URL environment variable, runs the query, and inspects the result. It checks that the syntax is valid. It checks that every column you reference exists. It maps the database column types to Rust types. If the database says a column is TEXT but you try to read it as i32, the macro generates code that fails to type-check.
Think of it like a spell-checker that also understands grammar and context. It doesn't just flag typos. It knows that if you're selecting a user_id, the result must be an integer, and if that column doesn't exist, the code won't compile. The macro generates Rust code that enforces these constraints. You get the safety of static typing for your database access.
Minimal example
Set up the dependencies first. The macros feature is required for compile-time checking. Without it, the query! macro doesn't exist.
# Cargo.toml
[dependencies]
sqlx = { version = "0.8", features = ["runtime-tokio", "sqlite", "macros"] }
tokio = { version = "1", features = ["full"] }
Write a simple query. The macro validates the SQL against the schema.
use sqlx::SqlitePool;
/// Runs a compile-time checked query against an in-memory SQLite database.
#[sqlx::main]
async fn main() -> Result<(), sqlx::Error> {
// Connect to an ephemeral database for demonstration.
// The pool manages connections and is safe to share across tasks.
let pool = SqlitePool::connect("sqlite::memory:").await?;
// The macro expands to code that checks syntax and types.
// If the SQL is invalid or columns are missing, compilation fails.
let result = sqlx::query!("SELECT 1 + 1 as result")
.fetch_one(&pool)
.await?;
// Access the column by name. The type is inferred from the database schema.
// For SQLite, integer expressions typically map to i64.
println!("Result: {}", result.result);
Ok(())
}
Run this with DATABASE_URL set. For SQLite, an in-memory database works fine.
DATABASE_URL=sqlite::memory: cargo run
The compiler checks the query. If you change result to res in the code but leave the SQL as result, the build fails. The compiler rejects this with a "no field named res on struct" error. The mismatch is caught immediately.
How the check happens
The macro workflow has two phases. First, the macro connects to the database specified by DATABASE_URL. It executes the query to validate syntax and inspect the schema. It records the column names and types. Second, the macro generates Rust code that matches this schema. The generated code includes a struct with fields for each column. The field types match the database types exactly. The generated code also includes a check that the SQL string matches the expected hash. If the SQL changes but the schema data doesn't, the build fails.
This process requires a live database during compilation. The DATABASE_URL environment variable must point to a database with the correct schema. If the schema is missing or wrong, the macro can't validate the query. This is a feature. It forces your code to stay in sync with the database.
Convention aside: the community treats sqlx-data.json as a critical artifact. When you run cargo sqlx prepare, the tool generates this file containing the schema information for all queries. You commit this file to version control. In CI/CD pipelines, you set RUSTFLAGS="--cfg sqlx_offline" or use the --offline flag. This tells sqlx to read from sqlx-data.json instead of connecting to a database. This makes builds faster and removes the dependency on a live database in CI. Always commit sqlx-data.json. It's the source of truth for your query schemas.
Realistic usage with structs
Real applications map query results to Rust structs. The query_as! macro handles this. You define a struct and derive FromRow. The macro maps columns to struct fields based on name.
use sqlx::FromRow;
use sqlx::SqlitePool;
/// Represents a user record fetched from the database.
/// Fields must match column names exactly.
#[derive(Debug, sqlx::FromRow)]
struct User {
id: i64,
name: String,
email: String,
}
/// Fetches a user by ID using compile-time checked SQL.
async fn get_user(pool: &SqlitePool, id: i64) -> Result<User, sqlx::Error> {
// query_as! maps columns to struct fields.
// The macro checks that all fields are present in the query.
// It also checks that the types match the database schema.
sqlx::query_as!(
User,
"SELECT id, name, email FROM users WHERE id = ?",
id
)
.fetch_one(pool)
.await
}
The struct fields must match the column names exactly. If the database has user_name but the struct has name, the build fails. The compiler rejects this with a "no field named name on struct" error. You can use aliases in the SQL to fix this. SELECT user_name AS name works. The macro sees the alias and maps it to the struct field.
Type mapping is strict. sqlx doesn't perform implicit casts. If the database column is TEXT, the Rust field must be String. If the column is INTEGER, the field must be an integer type that matches the driver's mapping. For SQLite, INTEGER maps to i64. If you use i32, the build fails. The compiler rejects this with a "mismatched types" error. Check the sqlx documentation for exact type mappings per database driver. The mappings are driver-specific.
Convention aside: use query_as! for stable data shapes. If you're fetching a row that maps cleanly to a struct, query_as! is the standard choice. It reduces boilerplate and keeps the mapping logic in one place. Avoid manual row parsing unless you have a specific reason. The macro handles the mapping safely and efficiently.
Pitfalls and workflow
Compile-time checking introduces new constraints. You need a database to compile. The DATABASE_URL environment variable is mandatory. If it's missing, the build fails. The compiler rejects this with a "DATABASE_URL not set" error. Set it in your shell or use a .env file with cargo-watch or similar tools.
Schema drift breaks builds. If you run a migration that changes a column type, your code might stop compiling. This is intentional. The compiler is telling you that your code no longer matches the database. Update the Rust types to match the new schema. This prevents runtime errors caused by schema mismatches.
Offline mode requires preparation. If you use sqlx in CI/CD, you need to generate sqlx-data.json before building. Run cargo sqlx prepare in your development environment. This command connects to the database and generates the JSON file. Commit the file. In CI, use offline mode to avoid database dependencies. This makes builds reproducible and faster.
Dynamic queries bypass checking. If you construct SQL strings at runtime, the macros can't check them. Use sqlx::query (without the !) for dynamic SQL. This function takes a string and parameters. It skips compile-time checking. Use this only when the query structure depends on runtime logic. Accept that you lose compile-time safety for the dynamic parts. Isolate dynamic queries in small functions. Keep the rest of your code using macros.
Convention aside: keep unsafe blocks minimal. sqlx is safe. You rarely need unsafe with sqlx. If you do, it's usually for FFI or custom extensions. Follow the "minimum unsafe surface" rule. Wrap unsafe code in small helpers. Document the safety invariants. The rest of your code should be safe.
Decision matrix
Use query! when you need raw rows and want to access columns by name without defining a struct. Use query_as! when you have a stable data shape and want to map results directly to a Rust struct. Use query_file! when your SQL lives in a separate .sql file and you want to keep code and queries decoupled. Reach for dynamic SQL construction when the query structure depends on runtime logic, like optional filters, and accept that compile-time checking won't apply to the variable parts.
Use cargo sqlx prepare when you need to generate sqlx-data.json for offline builds. Use RUSTFLAGS="--cfg sqlx_offline" when building in CI/CD to avoid database dependencies. Use DATABASE_URL to point to a database with the correct schema during development. Treat sqlx-data.json as a committed artifact that keeps your builds reproducible.