When raw SQL feels risky
You are building a web service. You have a database. You need to fetch a user by ID, update their email, and list active orders. In many languages, you write SQL strings, interpolate variables, and hope the types match. If a column gets renamed, your code compiles fine and crashes at runtime. If you pass a string where an integer is expected, the database throws an error that bubbles up to the user.
Rust offers a better path. You can use an ORM or query builder that maps your database schema to Rust types. Diesel is a popular choice. It lets you construct queries using Rust methods. The compiler checks that your queries reference existing columns, use correct types, and follow SQL rules. If the query is invalid, the code does not compile. You catch mistakes before the server starts.
Diesel turns your database into a type-checked API.
Diesel as a type-safe query builder
Diesel is not a traditional ORM that hides SQL completely. It is a query builder with ORM-like conveniences. You describe what you want using Rust syntax, and Diesel generates the SQL. The generated SQL is standard and readable. You can inspect it with debug logging if needed.
Think of Diesel like a strict architect who checks your blueprints against the building code before you lay a single brick. You describe the structure (the query), and Diesel produces the plan (the SQL). If you ask for a column that does not exist, or try to insert a string into an integer column, the architect stops you immediately. There are no runtime surprises.
Diesel relies on macros and derive attributes to bridge the gap between Rust and the database. The diesel::table! macro defines the schema structure. Derive macros like Queryable, Insertable, and Selectable generate the code that maps database rows to Rust structs. The query builder uses trait extensions to provide a fluent API.
The compiler is your QA team for SQL.
Setting up the project
Diesel requires a few setup steps. You need the library, the CLI tool, and a database connection. The CLI helps manage migrations and generates the schema file that Diesel uses for type checking.
Add Diesel to your dependencies. The features flag selects the database backend. SQLite is common for development and testing. PostgreSQL and MySQL are available for production.
[dependencies]
# Diesel 2.x uses features to select the database backend.
# "sqlite" enables SQLite support. "r2d2" adds connection pooling.
diesel = { version = "2.2", features = ["sqlite", "r2d2"] }
# libsqlite3-sys provides the C library binding.
# The "bundled" feature compiles the library for you,
# avoiding system dependency issues on CI or user machines.
libsqlite3-sys = { version = "0.30", features = ["bundled"] }
[dev-dependencies]
# diesel_migrations is used in tests to run migrations.
diesel_migrations = "2.2"
Install the Diesel CLI. The CLI is a separate binary that manages your database schema and migrations.
cargo install diesel_cli --no-default-features --features sqlite
Initialize the database and migrations folder. This creates the database file and a migrations directory.
diesel setup
Generate a migration. Migrations are SQL files that modify the database schema. The CLI creates empty up and down files.
diesel migration generate create_users
Edit the generated up.sql file in migrations/<timestamp>_create_users/up.sql.
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
email TEXT NOT NULL UNIQUE,
active BOOLEAN NOT NULL DEFAULT 0
);
Run the migration to apply the schema changes.
diesel migration run
Generate the Rust schema file. This is the critical step. Diesel reads the database and creates src/schema.rs, which defines the table structure using macros. Your code imports this file to get type safety.
diesel print-schema > src/schema.rs
Run diesel print-schema after every migration. Your Rust code must reflect the current database structure.
The minimal query
With the schema generated, you can write queries. The schema.rs file contains a table! macro for each table. This macro defines the column names and types. Diesel uses this information to check your queries at compile time.
Create a struct to hold the query results. Derive Queryable to tell Diesel how to map database rows to the struct. The field order must match the column order in the SELECT clause, or you must use Selectable.
use diesel::prelude::*;
use diesel::sqlite::SqliteConnection;
// Import the generated schema.
mod schema;
// Queryable maps database rows to this struct.
// The field types must match the SQL types defined in schema.rs.
#[derive(Queryable, Debug)]
struct User {
id: i32,
email: String,
active: bool,
}
fn main() {
// Establish a connection to the SQLite database.
// In production, use a connection pool like r2d2.
let mut conn = SqliteConnection::establish("db.sqlite")
.expect("Failed to connect to database");
// Use the dsl module to access column names.
use schema::users::dsl::*;
// Build the query using the query builder.
// The compiler checks that 'users' exists and 'email' is a valid column.
let result = users
.select(email)
.filter(active.eq(true))
.load::<String>(&mut conn)
.expect("Query failed");
println!("Active emails: {:?}", result);
}
Convention aside: diesel::prelude::* is standard practice in every Diesel project. It pulls in the trait extensions that make the query builder fluent, such as RunQueryDsl and QueryDsl. You will see this import in almost all Diesel codebases.
Trust the borrow checker. It usually has a point.
How the pieces fit together
The query builder works by chaining methods that return intermediate query types. Each method narrows down the query structure. The load method executes the query and deserializes the results.
When you call users, you get a reference to the table. Calling select(email) specifies the columns to retrieve. The filter(active.eq(true)) method adds a WHERE clause. The eq method ensures type safety; you cannot compare active to a string. The compiler rejects mismatches.
The load method takes a connection and a type parameter. The type parameter tells Diesel how to deserialize the rows. If you pass User, Diesel expects the query to return columns that match the User struct fields. If the types do not align, you get a compile error.
Diesel also supports get_result for queries that return a single row. This is useful for lookups by primary key.
fn get_user_by_id(conn: &mut SqliteConnection, user_id: i32) -> Option<User> {
use schema::users::dsl::*;
// Filter by ID and limit to one row.
// get_result returns a single item or an error if no rows match.
users
.filter(id.eq(user_id))
.first::<User>(conn)
.optional()
.expect("Query failed")
}
Convention aside: first is a Diesel convenience method that adds LIMIT 1 and returns a single result. It is idiomatic for primary key lookups. Use optional to convert the "not found" error into None instead of panicking.
Treat the schema.rs file as generated code. Do not edit it by hand. Regenerate it when the database changes.
Realistic usage: inserts and filters
Real applications need to insert data and handle complex queries. Diesel provides Insertable for inserts and Selectable for safer struct mapping.
Insertable generates the code to map struct fields to SQL INSERT values. Selectable is the modern way to map query results. It links the struct to the schema table, so you do not need to worry about field order.
use diesel::prelude::*;
use diesel::sqlite::SqliteConnection;
mod schema;
// Insertable maps this struct to SQL INSERT values.
// The field names must match the column names in the schema.
#[derive(Insertable, Debug)]
#[diesel(table_name = schema::users)]
struct NewUser<'a> {
email: &'a str,
active: bool,
}
// Selectable links the struct to the table.
// This allows Diesel to map columns automatically,
// regardless of field order in the struct.
#[derive(Queryable, Selectable, Debug)]
#[diesel(table_name = schema::users)]
struct User {
id: i32,
email: String,
active: bool,
}
fn create_user(conn: &mut SqliteConnection, email: &str) -> User {
use schema::users::dsl::*;
// Create the insertable struct.
let new_user = NewUser {
email,
active: true,
};
// Insert the user and return the result.
// get_result fetches the inserted row.
diesel::insert_into(users)
.values(&new_user)
.get_result::<User>(conn)
.expect("Insert failed")
}
fn list_users(conn: &mut SqliteConnection, limit: i64) -> Vec<User> {
use schema::users::dsl::*;
// Build a query with filtering, ordering, and pagination.
users
.filter(active.eq(true))
.order(id.asc())
.limit(limit)
.load::<User>(conn)
.expect("Query failed")
}
Convention aside: #[diesel(table_name = schema::users)] is crucial for Selectable and Insertable. It links your struct to the generated schema. Without it, Diesel cannot verify that the fields match the columns. Always include this attribute when using these derives.
Convention aside: libsqlite3-sys with the bundled feature compiles the C library for you. This avoids system dependency issues on CI or user machines. If you drop bundled, users must install libsqlite3-dev or equivalent, which breaks builds on clean systems.
Trust the Selectable derive. It saves you from manual mapping errors.
Pitfalls and compiler errors
Diesel catches many errors at compile time, but the error messages can be verbose. Learning to read them saves time.
Type mismatches are common. If your struct field is String but the database column is INTEGER, Diesel rejects the code. You will see E0277 (trait bound not satisfied). The error message lists the missing trait. Look for the root cause: usually a type mismatch between the struct and the schema.
error[E0277]: the trait bound `User: FromSqlRow<(...), Sqlite>` is not satisfied
--> src/main.rs:20:10
|
20 | .load::<User>(&mut conn)
| ^^^^ the trait `FromSqlRow<(...), Sqlite>` is not implemented for `User`
|
= help: the following other types implement trait `FromSqlRow<ST, DB>`:
<User as FromSqlRow<(Integer, Text, Bool), Sqlite>>
The error shows that Diesel expects (Integer, Text, Bool) but User does not match. Check the field types in User against the schema.
Missing derive macros also cause E0277. If you forget #[derive(Queryable)], Diesel cannot map rows to the struct. The error message points to the missing trait. Add the derive and the error disappears.
Field order matters for Queryable if you do not use Selectable. If the struct fields do not match the SELECT column order, you get a type mismatch. Use Selectable to avoid this issue. Selectable maps columns by name, not order.
// Without Selectable, this fails if the SELECT order differs.
#[derive(Queryable)]
struct User {
active: bool, // Wrong order if SELECT is id, email, active
id: i32,
email: String,
}
// With Selectable, field order does not matter.
#[derive(Queryable, Selectable)]
#[diesel(table_name = schema::users)]
struct User {
active: bool,
id: i32,
email: String,
}
Read the compiler error from the bottom up. The root cause is usually a type mismatch or a missing derive macro.
Decision: Diesel vs alternatives
Rust has several ways to interact with databases. Diesel is not the only option. Choose based on your needs.
Use Diesel when you want a type-safe query builder that catches schema mismatches at compile time. The query builder API is fluent and ergonomic. You get safety without writing raw SQL strings.
Use Diesel when you are building a CRUD-heavy application and want the convenience of Queryable, Insertable, and Selectable derives. These macros reduce boilerplate and prevent mapping errors.
Reach for sqlx when you prefer writing raw SQL strings but still want compile-time verification of the query structure. sqlx checks SQL syntax and types at compile time using a macro. It has less runtime overhead than Diesel's query builder.
Pick raw SQL via diesel::sql_query when you need complex database-specific features that the query builder does not support yet. Diesel allows raw SQL fallbacks, but you lose type safety. Use this sparingly.
Avoid Diesel if your project requires zero-cost abstractions above the SQL generation layer. Diesel has some runtime overhead in the query builder. For performance-critical paths, benchmark against sqlx or raw SQL.
Choose the tool that matches your comfort with SQL. Diesel abstracts it; sqlx embraces it.