When the ORM fights back
You are building a dashboard and need a specific report. The ORM is fighting you with nested joins, custom aggregations, and a query builder that turns a simple SELECT into forty lines of method calls. You just want to run raw SQL. You need the power of direct database access without losing Rust's type safety or inviting SQL injection.
Rust does not have a built-in database driver. The ecosystem standard is sqlx. Unlike database libraries in other languages where you pass a string and hope it works, sqlx checks your SQL against the actual database schema at compile time. If you typo a column name, the compiler stops you. If you pass a string where an integer is expected, the compiler catches that too. You get raw SQL performance with compile-time guarantees.
Compile-time SQL checking
sqlx connects to your database during compilation to verify queries. It reads the schema, checks column names, validates types, and ensures your parameters match. Think of it like a spellchecker that connects to your dictionary in real-time. If the dictionary changes, the spellchecker knows immediately. If you rename a column in the database, your Rust code fails to compile until you update the query.
This behavior requires a feature flag. You must enable the database driver in Cargo.toml. For SQLite, you add sqlite to the features. For PostgreSQL, you add postgres. You also select an async runtime, usually runtime-tokio. The crate is modular to keep compile times low.
[dependencies]
sqlx = { version = "0.7", features = ["sqlite", "runtime-tokio"] }
tokio = { version = "1", features = ["full"] }
The community convention is to run cargo sqlx prepare during development or CI. This command caches the query metadata in a file, which speeds up subsequent compilations. Without the cache, sqlx queries the database every time you compile. The cache ensures fast builds while keeping safety intact.
Minimal raw query
Start with a connection pool. You never open a new connection for every query. A pool manages a set of open connections and hands one out when you need it. sqlx provides SqlitePool, PgPool, and MySqlPool depending on your database.
The query function takes a SQL string. Use ? as a placeholder for parameters. Bind values using the .bind() method. This separates data from the command structure, preventing SQL injection. The database driver sends the query plan and the data separately. The database treats bound values as data, never as executable code.
use sqlx::{SqlitePool, Row};
/// Demonstrates executing a raw SQL query with parameter binding.
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
// Connect to an in-memory SQLite database.
// In production, use a file path or connection string.
let pool = SqlitePool::connect("sqlite::memory:").await?;
// Prepare the query. The ? marks a parameter slot.
// sqlx checks the SQL syntax and types at compile time.
let rows = sqlx::query("SELECT name FROM users WHERE id = ?")
.bind(42) // Bind the value to the parameter. Prevents injection.
.fetch_all(&pool) // Execute and collect all results.
.await?;
// Process each row.
for row in rows {
// Extract the first column as a String.
let name: String = row.get(0);
println!("User: {name}");
}
Ok(())
}
Bind parameters infer types automatically. .bind(42) infers i32. .bind("text") infers &str. If the inference is ambiguous, you can specify the type explicitly with .bind::<i32, _>(42). The compiler usually figures it out from the database type mapping.
Bind parameters. Always. The database driver is not a string formatter.
Mapping results to structs
Looping over rows and calling row.get(0) works for quick scripts. In real applications, you map results to Rust structs. sqlx::query_as! is a macro that maps database rows to a struct. It verifies that the column names in your SQL match the field names in your struct. It also checks that the types are compatible.
The macro uses column aliases to match fields. If your struct has a field named author, your SQL must select a column aliased as author. This aliasing is the bridge between database schema and Rust types.
use sqlx::{SqlitePool, Row};
#[derive(Debug)]
struct User {
id: i64,
email: String,
}
/// Fetches a user by ID using raw SQL and maps to a struct.
pub async fn get_user(pool: &SqlitePool, id: i64) -> Result<Option<User>, sqlx::Error> {
// Use query_as! to map rows directly to a struct.
// sqlx verifies the column names and types match the struct fields.
let user = sqlx::query_as!(
User,
"SELECT id, email FROM users WHERE id = ?",
id
)
.fetch_optional(pool)
.await?;
Ok(user)
}
The macro version query_as! performs compile-time checks. The function version query_as does not check SQL at compile time; it only checks at runtime. The community convention is to use the macro whenever possible. The macro gives you safety without runtime overhead. If you need dynamic SQL where the structure changes at runtime, you fall back to the function version, but you lose compile-time guarantees.
fetch_optional returns Option<T>. It is efficient for queries that return zero or one row. fetch_all returns Vec<T> and allocates a vector for all results. Use fetch_optional for lookups by ID. Use fetch_all for lists.
Map to structs early. Row-by-row extraction is error-prone and verbose.
Complex queries and joins
Raw SQL shines when you need complex joins or aggregations that ORMs struggle with. You can write standard SQL with joins, subqueries, and window functions. sqlx treats the SQL as a string but still validates it against the schema.
Struct fields must match the selected columns. If you join tables, you might have duplicate column names. Use AS aliases to disambiguate. The macro matches struct fields to aliases, not to raw column names.
use sqlx::{SqlitePool, Row};
#[derive(Debug)]
struct PostSummary {
author: String,
title: String,
view_count: i64,
}
/// Fetches published posts with author names and view counts.
pub async fn get_published_posts(pool: &SqlitePool) -> Result<Vec<PostSummary>, sqlx::Error> {
// Complex joins are often easier to write in raw SQL.
// The macro ensures the selected columns match the struct.
let posts = sqlx::query_as!(
PostSummary,
r#"
SELECT
u.name AS author,
p.title,
COALESCE(p.views, 0) AS view_count
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE p.published = 1
ORDER BY p.created_at DESC
"#
)
.fetch_all(pool)
.await?;
Ok(posts)
}
The COALESCE function handles nulls. If p.views can be null, selecting it directly into i64 fails. COALESCE(p.views, 0) returns 0 if the value is null. Rust types like i64 cannot hold null. Use Option<i64> if you want to preserve nulls, or use SQL functions to default them.
If the compiler complains about types, check your Option usage. Nulls are the enemy of non-optional types.
Pitfalls and compiler errors
SQL injection is the primary risk with raw SQL. String interpolation creates injection vulnerabilities. format!("SELECT * FROM users WHERE id = {}", id) allows an attacker to inject malicious SQL. The compiler does not catch this. sqlx macros warn about string interpolation in some contexts, but the safest practice is to use .bind() for every user-supplied value.
Type mismatches cause compile errors. If you try to map a database column to a Rust type that does not support it, the compiler rejects you with E0277 (trait bound not satisfied). For example, trying to decode a NULL value into a non-optional String fails. The database returns null, but String cannot represent null. Change the struct field to Option<String>.
// This fails if the column can be null.
struct BadUser {
email: String, // E0277 if email is NULL in DB.
}
// This works.
struct GoodUser {
email: Option<String>,
}
Dynamic SQL limits compile-time checking. If you build query strings at runtime based on user input, you cannot use query_as!. You must use query_as or query. You lose schema validation. This is acceptable for search filters where the structure varies, but you must be careful with types.
Connection errors and runtime failures return sqlx::Error. This error type wraps database errors, IO errors, and decode errors. Pattern match on sqlx::Error to handle specific cases. Database errors often contain codes and messages from the underlying engine.
Trust the borrow checker and the type system. If sqlx compiles, your SQL is valid and your types match.
When to use raw SQL vs alternatives
Use sqlx::query_as! when you need to map results to a Rust struct and want compile-time verification of column names and types.
Use sqlx::query when you only need a single value or a scalar result, like SELECT count(*) or SELECT max(id).
Use sqlx::query_with when you have dynamic SQL where the structure changes at runtime, though this sacrifices compile-time checks.
Use parameter binding (.bind()) for every user-supplied value to prevent SQL injection.
Reach for an ORM like SeaORM or Diesel when you are building a standard CRUD application and do not need complex custom queries.
Pick the tool that matches your query complexity. Raw SQL gives you control; macros give you safety. You can have both.