When the query builder fights you
You're building a reporting endpoint. You need a query with a window function, a conditional aggregation, and a subquery that filters by a dynamic date range. You try to construct it with the query builder. You're nesting .filter().filter().subquery().window() until the code looks like a pyramid of doom. The compiler is happy, but your brain is melting. You know the SQL. You've tested it in the database console. You just want to run the SQL.
Rust doesn't force you to stay inside an abstraction. You can drop down to raw SQL strings. You just have to handle the bridge between the database's dynamic results and Rust's static types. The database returns rows of data. Rust needs structs or tuples with known shapes. Your job is to declare that shape so the compiler can verify the mapping.
Dynamic SQL, static results
Raw SQL in Rust means you write the query as a string, but you declare the result structure up front. The database executes the string and returns a stream of rows. The database driver reads those rows and tries to convert each column into a Rust value. If the column types don't match your declaration, the conversion fails.
Think of it like ordering from a menu written in a foreign language. You can point at the dish using the SQL string, but the waiter needs a translation sheet to put the food on your plate. Without the translation sheet, the waiter just hands you a bag of raw ingredients and walks away. In Rust, the translation sheet is the FromSqlRow trait. You provide the sheet by deriving QueryableByName on your struct or by using a tuple that matches the column order.
The database doesn't care about your structs. Make it care with aliases and type declarations.
Minimal example: tuples and binds
The simplest way to run raw SQL is to load the results into a tuple. Tuples implement FromSqlRow automatically, provided the number of columns matches the tuple length and the types align.
use diesel::prelude::*;
use diesel::sql_query;
use diesel::sql_types::{Integer, Text};
/// Fetches a user by ID using raw SQL and returns a tuple.
fn get_user_tuple(conn: &mut SqliteConnection, user_id: i32) -> QueryResult<Vec<(i32, String)>> {
// sql_query starts a dynamic query builder.
// The SQL string is sent to the database as-is.
sql_query("SELECT id, name FROM users WHERE id = ?")
// bind attaches a parameter safely.
// The first generic is the SQL type. The second is the Rust type (inferred).
// This prevents SQL injection by using prepared statements.
.bind::<Integer, _>(user_id)
// load fetches all matching rows.
// The generic argument specifies the result type.
// (i32, String) matches the two columns: id and name.
.load::<(i32, String)>(conn)
}
The sql_query function returns a builder that hasn't been executed yet. You chain bind to attach parameters. The bind method is type-safe. You specify the SQL type on the left side of the generic, and the Rust type on the right. The underscore lets the compiler infer the Rust type from the argument. If you pass an i32 but bind it as Text, the compiler rejects you.
Convention aside: always write bind::<SqlType, _> rather than bind::<_, SqlType>. The SQL type is the constraint that matters. The Rust type is usually obvious from the variable. This matches the community style and makes the SQL contract explicit.
The load method executes the query and returns a Vec. If you expect a single row, use get_result instead. It returns the value directly and fails if zero or multiple rows are returned.
Mapping to structs with QueryableByName
Tuples work for quick scripts, but real code needs named fields. You can't derive Queryable for raw SQL because Queryable relies on column order, which is fragile for hand-written queries. Instead, you use QueryableByName. This trait maps columns by name, not position.
You derive QueryableByName on your struct and annotate each field with the expected SQL type. The struct field names must match the column names in the result set, or you must use SQL aliases.
use diesel::prelude::*;
use diesel::sql_query;
use diesel::sql_types::{Integer, Text, Nullable};
/// Represents a post joined with its author.
/// QueryableByName maps columns by name, so aliases are your friend.
#[derive(Debug, QueryableByName)]
struct PostWithAuthor {
#[diesel(sql_type = Integer)]
id: i32,
#[diesel(sql_type = Text)]
title: String,
// Nullable handles LEFT JOIN results where the author might be missing.
#[diesel(sql_type = Nullable<Text>)]
author_name: Option<String>,
}
/// Fetches posts with author names using a LEFT JOIN.
fn get_posts_with_authors(conn: &mut SqliteConnection) -> QueryResult<Vec<PostWithAuthor>> {
sql_query(
"SELECT p.id, p.title, u.name AS author_name \
FROM posts p \
LEFT JOIN users u ON p.user_id = u.id \
WHERE p.published = 1",
)
// load maps the result rows to PostWithAuthor.
// Column names must match struct fields exactly.
.load::<PostWithAuthor>(conn)
}
The #[diesel(sql_type = ...)] attribute is mandatory. Rust types aren't enough. The database has its own type system, and a VARCHAR might map to Text while a BIGINT maps to BigInt. You have to tell Diesel what the database is sending. If you get the SQL type wrong, the conversion fails at runtime.
Convention aside: use AS aliases in your SQL to match struct fields. If your struct has author_name but the column is name, add AS author_name to the query. This keeps the mapping explicit and readable. Don't rely on column order. Rename a column in the database, and your raw SQL breaks silently if you're not careful. Test the schema.
Walkthrough: what happens under the hood
When you call sql_query, Diesel creates a dynamic query object. This object holds the SQL string and a list of bound parameters. No database interaction happens yet.
When you call bind, you add a parameter to the list. Diesel records the SQL type and the Rust value. The driver will later convert the Rust value to a database-compatible format. This is where SQL injection is prevented. The parameter is never interpolated into the string. It's sent separately to the database engine.
When you call load, the driver sends the prepared statement to the database. The database executes it and returns a result set. The driver iterates over the rows. For each row, it looks at the column metadata. It checks the column names against your struct fields. It checks the column types against your sql_type annotations. If everything matches, it converts the column value to the Rust type and constructs the struct. If a column is missing or the type mismatches, the driver returns an error.
The error happens at runtime, not compile time. That's the trade-off of raw SQL. You get flexibility, but you lose some compile-time guarantees. The compiler can check that PostWithAuthor implements QueryableByName, but it can't check that the database actually has a column named author_name. That check happens when the query runs.
Runtime errors in raw SQL are your responsibility. Test the column names.
Pitfalls and compiler errors
Raw SQL introduces a few specific failure modes. Knowing them saves debugging time.
Type mismatches. If you declare a field as Integer but the database returns Text, the conversion fails. The error is a runtime DatabaseError or QueryBuilderError. The compiler won't catch this. If you try to load a type that doesn't implement FromSqlRow, you get a compile error. You'll see E0277 (the trait bound MyStruct: FromSqlRow<...> is not satisfied). This usually means you forgot to derive QueryableByName or missed an sql_type attribute.
Column name mismatches. QueryableByName requires exact name matches. If your struct has user_id but the query returns uid, the mapping fails. The error is runtime. Use aliases to fix this. Don't rename struct fields to match typos in the database. Fix the query.
SQL injection. Never concatenate user input into the SQL string. If you write format!("SELECT * FROM users WHERE id = {}", user_id), you're vulnerable. Always use bind. The bind method uses prepared statements. The database treats the parameter as data, not code.
Missing columns. If your query selects three columns but your struct has four fields, load fails. The struct must match the result set exactly. Extra columns in the query are ignored. Missing columns in the query cause errors.
N+1 queries. Raw SQL doesn't prevent N+1 problems. If you loop over users and run a raw query for each user's posts, you're still doing N+1 work. Use joins or batch queries. Raw SQL gives you the power to write joins, so use it.
The database doesn't care about your structs. Make it care with aliases and type declarations.
When to use raw SQL
Raw SQL is a tool, not a default. Use it when the abstraction costs more than the raw string.
Use raw SQL when the query involves window functions, recursive CTEs, or complex aggregations that the query builder doesn't support. The builder is great for standard joins and filters. It struggles with advanced SQL features. Drop to raw SQL when the builder forces you to write ten lines of code for a three-line query.
Use raw SQL for performance-critical queries where you need to add database-specific hints or optimize index usage. The query builder generates standard SQL. It can't add /*+ INDEX(...) */ hints or use vendor-specific syntax. Raw SQL lets you hand-optimize.
Use raw SQL for migrations, administrative tasks, or one-off data fixes. These queries don't need to be part of the domain model. They run once. A raw string is fine.
Reach for the query builder when you need compile-time safety on joins and filters. The builder catches typos in table names and column names at compile time. It enforces type safety across the query. Use the builder for the core CRUD operations and standard lookups.
Reach for the ORM when you're doing rapid development on simple models and want to avoid writing SQL entirely. ORMs like Diesel's model layer or SeaORM handle serialization and relations automatically. They're slower and less flexible, but they speed up development for straightforward apps.
Start with the builder. Escape to SQL only when the builder fights you.