Diesel ORM

Schema Definition

// Generated by diesel migration run
// src/schema.rs
diesel::table! {
    users (id) {
        id -> Int4,
        name -> Varchar,
        email -> Varchar,
        created_at -> Timestamp,
    }
}

Model Structs

use diesel::prelude::*;
use crate::schema::users;

#[derive(Queryable, Selectable, Debug)]
#[diesel(table_name = users)]
pub struct User {
    pub id: i32,
    pub name: String,
    pub email: String,
    pub created_at: NaiveDateTime,
}

#[derive(Insertable)]
#[diesel(table_name = users)]
pub struct NewUser<'a> {
    pub name: &'a str,
    pub email: &'a str,
}

#[derive(AsChangeset)]
#[diesel(table_name = users)]
pub struct UpdateUser<'a> {
    pub name: Option<&'a str>,
    pub email: Option<&'a str>,
}

Connection Pool

use diesel::pg::PgConnection;
use diesel::r2d2::{Pool, ConnectionManager};

type DbPool = Pool<ConnectionManager<PgConnection>>;

fn establish_pool(database_url: &str) -> DbPool {
    let manager = ConnectionManager::<PgConnection>::new(database_url);
    Pool::builder()
        .max_size(15)
        .build(manager)
        .expect("Failed to create pool")
}

CRUD Operations

use diesel::prelude::*;
use crate::schema::users::dsl::*;

// Create
fn create_user(conn: &mut PgConnection, new_user: &NewUser) -> QueryResult<User> {
    diesel::insert_into(users)
        .values(new_user)
        .get_result(conn)
}

// Read
fn find_user(conn: &mut PgConnection, user_id: i32) -> QueryResult<User> {
    users.find(user_id).first(conn)
}

fn list_users(conn: &mut PgConnection) -> QueryResult<Vec<User>> {
    users.load(conn)
}

// Update
fn update_user(conn: &mut PgConnection, user_id: i32, changes: &UpdateUser) -> QueryResult<User> {
    diesel::update(users.find(user_id))
        .set(changes)
        .get_result(conn)
}

// Delete
fn delete_user(conn: &mut PgConnection, user_id: i32) -> QueryResult<usize> {
    diesel::delete(users.find(user_id)).execute(conn)
}

Query Building

// Filtering
let active_users = users
    .filter(active.eq(true))
    .filter(created_at.gt(cutoff_date))
    .load::<User>(conn)?;

// Ordering and pagination
let page = users
    .order(created_at.desc())
    .limit(10)
    .offset(20)
    .load::<User>(conn)?;

// Select specific columns
let emails: Vec<String> = users
    .select(email)
    .load(conn)?;

// Joins
let user_posts = users
    .inner_join(posts::table)
    .select((users::name, posts::title))
    .load::<(String, String)>(conn)?;

Transactions

conn.transaction(|conn| {
    diesel::insert_into(users)
        .values(&new_user)
        .execute(conn)?;

    diesel::insert_into(audit_log)
        .values(&log_entry)
        .execute(conn)?;

    Ok(())
})

Migrations

# Create migration
diesel migration generate create_users

# Run pending migrations
diesel migration run

# Revert last migration
diesel migration revert

# Redo last migration
diesel migration redo

Best Practices

1. Schema sync: Run diesel migration run before compile
2. Pool sizing: Match to expected concurrent connections
3. Derive macros: Use Queryable for reads, Insertable for writes
4. Nullable: Use Option<T> for nullable columns
5. Raw SQL: Use sql_query() for complex queries when needed