How to use diesel crate in Rust ORM
You're building a CLI tool that tracks your reading list. You started with a JSON file, but now you need to query books by author and filter by year. The JSON file is becoming a nightmare to parse and update. You decide to use a real database. You hear about Diesel, the Rust ORM that promises compile-time safety. You also hear it requires a lot of setup and macros. You want to get your first query running without fighting the compiler for three days.
Diesel is an Object-Relational Mapper, but it behaves differently from ORMs in Python or JavaScript. Those tools often build queries at runtime. If you make a typo in a column name, the error appears only when the code runs and hits the database. Diesel moves that check to compile time. It uses macros to read your database schema and generate Rust types. When you write a query, you are actually composing Rust types. The compiler checks that the types match your schema. If they don't, the build fails. This approach catches mistakes early. It also gives you autocomplete in your editor. You type books:: and the editor shows you every column.
Think of Diesel like a strict architect reviewing blueprints. A runtime ORM is like building a house and hoping the walls align. If they don't, you find out when you try to walk through a door. Diesel is the architect who checks the measurements before you pour concrete. If the blueprint says a door is two meters wide but the wall has a one-meter opening, the architect stops you immediately. You fix the plan, not the building.
Setup and first query
Diesel requires a few pieces to work together. You need the library, the CLI tool for management, and a schema file that bridges the database and your code.
Add the dependency to your Cargo.toml. The features flag tells Diesel which database backend to compile. Pick one. You can add multiple, but it increases compile times.
[dependencies]
# Feature "sqlite" enables the SQLite backend. Use "postgres" or "mysql" for others.
diesel = { version = "2.2", features = ["sqlite"] }
# diesel_cli is a dev-dependency for running migrations and setup commands.
[dev-dependencies]
diesel_cli = "2.2"
Install the CLI tool. This is a one-time step per machine. The CLI handles database creation and migration management.
# Install the CLI with the same features as your project
cargo install diesel_cli --no-default-features --features sqlite
Initialize the project structure. This command creates the database file and the migrations directory.
# Creates db/diesel.sqlite and migrations/ directory
diesel setup
Diesel relies on a schema.rs file. This file defines the structure of your tables as Rust types. You rarely write this by hand. The CLI generates it from your database. If you change the database, you must regenerate this file.
Create a migration to add a table. Migrations are versioned SQL scripts that modify your database schema.
# Creates a timestamped migration directory with up.sql and down.sql
diesel migration generate create_books
Edit the generated up.sql file. This script runs when you apply the migration.
-- migrations/20240101000000_create_books/up.sql
CREATE TABLE books (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
title TEXT NOT NULL,
author TEXT NOT NULL
);
Run the migration. This applies the SQL to your database.
diesel migration run
Generate the schema file. This reads the database and writes src/schema.rs.
# Outputs the schema definition to stdout. Redirect to file or use --print-schema.
diesel print-schema > src/schema.rs
The generated schema.rs looks like this. The diesel::table! macro defines the table structure.
// src/schema.rs
// Generated by diesel print-schema. Do not edit manually.
diesel::table! {
books (id) {
id -> Integer,
title -> Text,
author -> Text,
}
}
Define a model struct. This struct maps to the rows in your table. The #[derive] macros add the traits Diesel needs to read and write data.
// src/models.rs
use diesel::prelude::*;
use crate::schema::books;
// Queryable maps database columns to struct fields.
// Insertable allows creating rows from instances of this struct.
#[derive(Queryable, Insertable, Debug)]
// Links the struct to the generated table definition.
#[diesel(table_name = books)]
pub struct Book {
pub id: i32,
pub title: String,
pub author: String,
}
Write the query. Diesel builds the query using method calls that return types. The compiler validates the chain.
// src/main.rs
use diesel::prelude::*;
use crate::models::Book;
use crate::schema::books;
fn main() {
// Establish a connection to the SQLite database.
let mut conn = SqliteConnection::establish("db/diesel.sqlite")
.expect("Error connecting to database");
// Insert a new book.
// insert_into takes the table type.
// values takes the data.
// execute runs the query and returns the number of affected rows.
diesel::insert_into(books::table)
.values(&Book {
id: 1,
title: "The Rust Programming Language".to_string(),
author: "Steve Klabnik".to_string(),
})
.execute(&mut conn)
.expect("Error inserting book");
// Query books by author.
// filter adds a WHERE clause.
// load fetches all results and maps them to the Book struct.
let results = books::table
.filter(books::author.eq("Steve Klabnik"))
.load::<Book>(&mut conn)
.expect("Error loading books");
println!("{:?}", results);
}
Run diesel print-schema every time you change a migration. Trust the generator, not your memory.
How the types work
Diesel uses a technique called type-level programming. When you write books::table, you are not getting a string or a SQL fragment. You are getting a Rust type that represents the books table. When you call .filter(books::author.eq("Steve")), you get a new type that represents a filtered query on the books table.
The load method has a signature that requires the query type to match the target struct. The compiler checks this match. If your struct has a field that doesn't exist in the table, or if the types don't align, the compiler rejects the code.
This is why schema.rs is critical. The types in schema.rs define what the compiler knows about your database. If schema.rs is out of date, the compiler thinks the database has a different structure than it actually has. You will get errors that look like type mismatches, but the real problem is a stale schema.
The Queryable derive macro generates code that tells Diesel how to map columns to fields. It uses the order of columns in the database by default, or you can specify the order with #[diesel(column_name = ...)]. The Insertable derive macro generates code to map fields to columns for INSERT statements.
Convention aside: The community standard is to put use diesel::prelude::*; at the top of every file that uses Diesel. The prelude brings in common traits like QueryDsl, RunQueryDsl, and ExpressionMethods. Without it, you have to import these traits manually, which clutters the code.
Realistic usage pattern
In a real application, you rarely put queries in main. You wrap them in functions that take a connection and return a Result. This makes the code testable and reusable.
// src/repository.rs
use diesel::prelude::*;
use diesel::result::Error;
use crate::models::Book;
use crate::schema::books;
/// Fetches all books by a specific author.
/// Returns a vector of Book structs or a Diesel error.
pub fn get_books_by_author(conn: &mut SqliteConnection, author: &str) -> Result<Vec<Book>, Error> {
books::table
// Explicit select is optional here, but good practice for clarity.
// It ensures we only fetch the columns we need.
.select((books::id, books::title, books::author))
.filter(books::author.eq(author))
// load returns Result<Vec<T>, Error>.
.load::<Book>(conn)
}
/// Inserts a new book and returns the inserted struct.
pub fn insert_book(conn: &mut SqliteConnection, book: &Book) -> Result<usize, Error> {
diesel::insert_into(books::table)
.values(book)
.execute(conn)
}
Diesel supports nullable columns. If a column in the database can be NULL, the corresponding field in your struct must be Option<T>. The compiler enforces this. If you try to load a nullable column into a non-optional field, you get a type error.
#[derive(Queryable, Debug)]
#[diesel(table_name = books)]
pub struct BookWithOptionalGenre {
pub id: i32,
pub title: String,
// The database column is TEXT, but it allows NULL.
// Rust requires Option<String> to represent this safely.
pub genre: Option<String>,
}
Connection pooling is essential for production. Opening a new connection for every request is slow. Diesel integrates with r2d2, a generic connection pool. Add r2d2 to your features and use PooledConnection.
[dependencies]
diesel = { version = "2.2", features = ["sqlite", "r2d2"] }
use diesel::r2d2::{self, ConnectionManager};
type DbPool = r2d2::Pool<ConnectionManager<SqliteConnection>>;
fn create_pool(database_url: &str) -> DbPool {
let manager = ConnectionManager::<SqliteConnection>::new(database_url);
r2d2::Pool::builder()
.build(manager)
.expect("Failed to create pool")
}
When the compiler complains about types, check schema.rs first. The schema is the contract.
Pitfalls and compiler errors
Diesel's strictness causes specific errors when things drift out of sync. Recognizing these patterns saves time.
Schema drift. You add a column in a migration but forget to run diesel print-schema. Your code tries to use the new column. The compiler says the column doesn't exist.
Fix: Run diesel print-schema > src/schema.rs.
Type mismatch. Your struct field is i32 but the database column is BigInt. Diesel generates a type error.
Error: E0277 trait bound not satisfied. The error message mentions FromSqlRow or Queryable.
Fix: Align the Rust type with the database type. Use i64 for BigInt.
Nullable mismatch. The database column allows NULL, but your struct field is String instead of Option<String>.
Error: E0277 trait bound not satisfied. The error mentions Nullable.
Fix: Change the field to Option<T>.
Moved value. You try to use a connection after moving it into a function.
Error: E0382 use of moved value.
Fix: Pass &mut conn or clone the connection if using a pool.
Trait bound missing. You forget use diesel::prelude::*; and try to call .filter().
Error: E0599 no method named filter found.
Fix: Add the prelude import.
Diesel does not support eager loading of associations out of the box. If you have a Book and an Author, and you want to fetch both in one query, you need to write a JOIN manually or use a library like diesel-derive-enum for custom types. This is a design choice. Diesel prefers explicit joins over magic loading.
Convention aside: Keep unsafe blocks out of Diesel code. Diesel is safe. If you find yourself writing unsafe to make Diesel work, you are likely fighting the type system. Step back and check your schema or model definitions.
Decision: when to use Diesel
Rust has several database tools. Pick the one that matches your mental model and project needs.
Use Diesel when you want compile-time query validation and don't mind the macro-heavy setup. Use Diesel when you are building a complex domain model and need the type system to enforce relationships. Use Diesel when your team values catching database errors before runtime and is willing to invest in learning the schema generation workflow.
Use sqlx when you prefer compile-time SQL checking without the ORM abstraction layer and want a lighter dependency footprint. Use sqlx when you want to write raw SQL strings and still get type safety. Use sqlx when you need async support that feels more native to the Tokio ecosystem.
Use sea-orm when you want an Active Record style that feels more like Python's Django or JavaScript's Prisma. Use sea-orm when you prefer a fluent API that generates SQL at runtime but still offers type safety for models.
Reach for raw SQL strings when you are doing a one-off migration or a highly optimized query that the query builder struggles to express. Reach for raw SQL when performance profiling shows the query builder overhead is significant and you need hand-tuned SQL.
Pick the tool that matches your mental model. Diesel rewards patience with safety.