Diesel #

Diesel is a Rust ORM (Object-Relational Mapper) that puts type safety above everything else. Unlike sqlx which checks raw SQL queries at compile time, Diesel provides a query builder based on a Rust DSL — you build queries using Rust functions and methods, not SQL strings. This means wrong queries (missing columns, mismatched types, incorrect joins) become compile errors, not runtime errors. The tradeoff: a steeper learning curve, and some complex queries are easier written in raw SQL. Diesel uses synchronous connections (not async) by default — use diesel-async for async needs. This article covers Diesel 2.x with PostgreSQL.

Diesel vs sqlx — Choosing the Right One #

flowchart TD
    Q{Main priority?}
    Q --> A["Type-safe query builder\nDon't want to write SQL\nComplete ORM relationships"]
    Q --> B["Write SQL directly\nNative async\nFlexible for complex queries"]
    Q --> C["ORM with async\nType-safe AND async"]

    A --> D["Diesel\nComprehensive Rust DSL\nBuilt-in migrations\nSync (or diesel-async)"]
    B --> E["sqlx\nNative async\nCompile-time query check\nPure SQL"]
    C --> F["SeaORM\nAsync ORM based on sqlx\nRich relationships"]

Aspect	Diesel	sqlx
Approach	Query builder DSL	Pure SQL
Async	Via `diesel-async`	Native async
Type safety	Rust DSL	Compile-time SQL check
Migrations	Built-in + diesel-cli	sqlx-cli
Learning curve	Steeper	Gentler
Complex queries	Hard (needs raw SQL)	Easy (direct SQL)
Best for	Standard CRUD, simple relationships	Diverse queries, full control

Installation and Setup #

[dependencies]
diesel = { version = "2.1", features = ["postgres", "r2d2", "chrono"] }
dotenvy = "0.15"
serde = { version = "1", features = ["derive"] }
chrono = { version = "0.4", features = ["serde"] }

[dev-dependencies]
diesel_migrations = "2.1"

Install diesel-cli:

# PostgreSQL only
cargo install diesel_cli --no-default-features --features postgres

# Diesel setup (creates .env, migrations/, src/schema.rs)
echo DATABASE_URL=postgres://user:***@localhost/mydb > .env
diesel setup

Migrations #

# Create a new migration file
diesel migration generate buat_tabel_pengguna

# Run migrations
diesel migration run

# Undo the last migration
diesel migration revert

# Redo (revert + run)
diesel migration redo

# Check the status of all migrations
diesel migration list

The generated migration files:

-- migrations/2024-08-24-000001_buat_tabel_pengguna/up.sql
CREATE TABLE pengguna (
    id          BIGSERIAL PRIMARY KEY,
    nama        TEXT NOT NULL,
    email       TEXT NOT NULL UNIQUE,
    password    TEXT NOT NULL,
    peran       TEXT NOT NULL DEFAULT 'user',
    aktif       BOOLEAN NOT NULL DEFAULT TRUE,
    dibuat_pada TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    diperbarui  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_pengguna_email ON pengguna(email);
CREATE INDEX idx_pengguna_aktif ON pengguna(aktif);

-- migrations/2024-08-24-000001_buat_tabel_pengguna/down.sql
DROP TABLE pengguna;

After diesel migration run, Diesel generates src/schema.rs automatically:

// src/schema.rs — generated automatically, don't edit manually
diesel::table! {
    pengguna (id) {
        id -> Int8,
        nama -> Text,
        email -> Text,
        password -> Text,
        peran -> Text,
        aktif -> Bool,
        dibuat_pada -> Timestamptz,
        diperbarui -> Timestamptz,
    }
}

Models and Structs #

// src/models.rs
use crate::schema::pengguna;
use chrono::{DateTime, Utc};
use diesel::prelude::*;
use serde::{Deserialize, Serialize};

// Struct for reading from the database
#[derive(Debug, Queryable, Selectable, Serialize, Clone)]
#[diesel(table_name = pengguna)]
#[diesel(check_for_backend(diesel::pg::Pg))]
pub struct Pengguna {
    pub id: i64,
    pub nama: String,
    pub email: String,
    #[serde(skip_serializing)]
    pub password: String,
    pub peran: String,
    pub aktif: bool,
    pub dibuat_pada: DateTime<Utc>,
    pub diperbarui: DateTime<Utc>,
}

// Struct for INSERT — without database-generated fields
#[derive(Debug, Insertable, Deserialize)]
#[diesel(table_name = pengguna)]
pub struct PenggunaBaru {
    pub nama: String,
    pub email: String,
    pub password: String,
    pub peran: Option<String>,
}

// Struct for UPDATE — all fields optional
#[derive(Debug, AsChangeset, Deserialize)]
#[diesel(table_name = pengguna)]
pub struct PembaruanPengguna {
    pub nama: Option<String>,
    pub email: Option<String>,
    pub aktif: Option<bool>,
}

Connections and Pools #

// src/database.rs
use diesel::pg::PgConnection;
use diesel::r2d2::{self, ConnectionManager};
use std::env;

pub type Pool = r2d2::Pool<ConnectionManager<PgConnection>>;
pub type PooledConnection = r2d2::PooledConnection<ConnectionManager<PgConnection>>;

pub fn buat_pool() -> Pool {
    let url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    let manager = ConnectionManager::<PgConnection>::new(&url);

    r2d2::Pool::builder()
        .max_size(10)
        .min_idle(Some(2))
        .connection_timeout(std::time::Duration::from_secs(5))
        .build(manager)
        .expect("Failed to create connection pool")
}

// A single connection for testing or CLI tools
pub fn buat_koneksi() -> PgConnection {
    let url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    PgConnection::establish(&url).expect("Failed to connect to the database")
}

CRUD Operations #

Create — INSERT #

use diesel::prelude::*;
use crate::schema::pengguna;
use crate::models::{Pengguna, PenggunaBaru};

pub fn buat_pengguna(
    conn: &mut PgConnection,
    input: &PenggunaBaru,
) -> QueryResult<Pengguna> {
    diesel::insert_into(pengguna::table)
        .values(input)
        .returning(Pengguna::as_returning())  // return the inserted row
        .get_result(conn)
}

// Insert many at once
pub fn buat_banyak_pengguna(
    conn: &mut PgConnection,
    input_list: &[PenggunaBaru],
) -> QueryResult<Vec<Pengguna>> {
    diesel::insert_into(pengguna::table)
        .values(input_list)
        .returning(Pengguna::as_returning())
        .get_results(conn)
}

// Insert or update (UPSERT)
pub fn upsert_pengguna(
    conn: &mut PgConnection,
    input: &PenggunaBaru,
) -> QueryResult<Pengguna> {
    use diesel::pg::upsert::excluded;

    diesel::insert_into(pengguna::table)
        .values(input)
        .on_conflict(pengguna::email)
        .do_update()
        .set((
            pengguna::nama.eq(excluded(pengguna::nama)),
            pengguna::diperbarui.eq(chrono::Utc::now()),
        ))
        .returning(Pengguna::as_returning())
        .get_result(conn)
}

Read — SELECT #

use diesel::prelude::*;
use crate::schema::pengguna;
use crate::models::Pengguna;

// Get all
pub fn ambil_semua_pengguna(conn: &mut PgConnection) -> QueryResult<Vec<Pengguna>> {
    pengguna::table
        .select(Pengguna::as_select())
        .order(pengguna::dibuat_pada.desc())
        .load(conn)
}

// Get by ID
pub fn ambil_pengguna_by_id(conn: &mut PgConnection, id: i64) -> QueryResult<Pengguna> {
    pengguna::table
        .find(id)
        .select(Pengguna::as_select())
        .first(conn)
}

// With filters
pub fn cari_pengguna(
    conn: &mut PgConnection,
    kata_kunci: &str,
    hanya_aktif: bool,
    batas: i64,
    offset: i64,
) -> QueryResult<Vec<Pengguna>> {
    let kata = format!("%{}%", kata_kunci);

    pengguna::table
        .select(Pengguna::as_select())
        .filter(pengguna::aktif.eq(hanya_aktif))
        .filter(
            pengguna::nama.ilike(&kata)
                .or(pengguna::email.ilike(&kata))
        )
        .order(pengguna::nama.asc())
        .limit(batas)
        .offset(offset)
        .load(conn)
}

// Count
pub fn hitung_pengguna(conn: &mut PgConnection) -> QueryResult<i64> {
    pengguna::table.count().get_result(conn)
}

// Existence check
pub fn email_ada(conn: &mut PgConnection, email: &str) -> QueryResult<bool> {
    use diesel::dsl::exists;
    diesel::select(exists(
        pengguna::table.filter(pengguna::email.eq(email))
    ))
    .get_result(conn)
}

Update #

use diesel::prelude::*;
use crate::schema::pengguna;
use crate::models::{Pengguna, PembaruanPengguna};

pub fn perbarui_pengguna(
    conn: &mut PgConnection,
    id: i64,
    perubahan: &PembaruanPengguna,
) -> QueryResult<Pengguna> {
    diesel::update(pengguna::table.find(id))
        .set(perubahan)
        .returning(Pengguna::as_returning())
        .get_result(conn)
}

// Update specific fields
pub fn nonaktifkan_pengguna(
    conn: &mut PgConnection,
    id: i64,
) -> QueryResult<usize> {
    diesel::update(pengguna::table.find(id))
        .set((
            pengguna::aktif.eq(false),
            pengguna::diperbarui.eq(chrono::Utc::now()),
        ))
        .execute(conn)
}

// Update many rows
pub fn nonaktifkan_semua_pengguna_peran(
    conn: &mut PgConnection,
    peran: &str,
) -> QueryResult<usize> {
    diesel::update(pengguna::table.filter(pengguna::peran.eq(peran)))
        .set(pengguna::aktif.eq(false))
        .execute(conn)
}

Delete #

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

pub fn hapus_pengguna(conn: &mut PgConnection, id: i64) -> QueryResult<usize> {
    diesel::delete(pengguna::table.find(id)).execute(conn)
}

// Soft delete (set aktif = false, don't actually delete)
pub fn soft_delete(conn: &mut PgConnection, id: i64) -> QueryResult<usize> {
    diesel::update(pengguna::table.find(id))
        .set(pengguna::aktif.eq(false))
        .execute(conn)
}

Advanced Queries and JOINs #

// src/schema.rs — add the artikel table
diesel::table! {
    artikel (id) {
        id -> Int8,
        pengguna_id -> Int8,
        judul -> Text,
        konten -> Text,
        diterbitkan -> Bool,
        dibuat_pada -> Timestamptz,
    }
}

diesel::joinable!(artikel -> pengguna (pengguna_id));
diesel::allow_tables_to_appear_in_same_query!(pengguna, artikel);

use diesel::prelude::*;
use crate::schema::{artikel, pengguna};

#[derive(Debug, Queryable, Selectable)]
#[diesel(table_name = artikel)]
pub struct Artikel {
    pub id: i64,
    pub pengguna_id: i64,
    pub judul: String,
    pub konten: String,
    pub diterbitkan: bool,
    pub dibuat_pada: chrono::DateTime<chrono::Utc>,
}

// JOIN query
pub fn artikel_dengan_nama_penulis(
    conn: &mut PgConnection,
) -> QueryResult<Vec<(Artikel, String)>> {
    artikel::table
        .inner_join(pengguna::table)
        .select((Artikel::as_select(), pengguna::nama))
        .filter(artikel::diterbitkan.eq(true))
        .order(artikel::dibuat_pada.desc())
        .load::<(Artikel, String)>(conn)
}

// Articles belonging to a specific user
pub fn artikel_pengguna(
    conn: &mut PgConnection,
    user_id: i64,
) -> QueryResult<Vec<Artikel>> {
    artikel::table
        .filter(artikel::pengguna_id.eq(user_id))
        .select(Artikel::as_select())
        .order(artikel::dibuat_pada.desc())
        .load(conn)
}

Transactions #

use diesel::prelude::*;
use diesel::Connection;

pub fn transfer_poin(
    conn: &mut PgConnection,
    dari_id: i64,
    ke_id: i64,
    jumlah: i32,
) -> QueryResult<()> {
    conn.transaction(|conn| {
        // All operations in this closure are one transaction
        // If any fails (returns Err), the whole transaction rolls back automatically

        // Deduct the sender's points
        let baris = diesel::update(pengguna::table.find(dari_id))
            .set(pengguna::diperbarui.eq(chrono::Utc::now()))
            .execute(conn)?;

        if baris == 0 {
            return Err(diesel::result::Error::NotFound);
        }

        // Add the recipient's points
        diesel::update(pengguna::table.find(ke_id))
            .set(pengguna::diperbarui.eq(chrono::Utc::now()))
            .execute(conn)?;

        Ok(())
    })
}

// Transaction with savepoints (nested transactions)
pub fn operasi_batch(
    conn: &mut PgConnection,
    operasi: Vec<PenggunaBaru>,
) -> QueryResult<Vec<Result<Pengguna, diesel::result::Error>>> {
    let mut hasil = Vec::new();

    for input in &operasi {
        // A savepoint per operation — partially roll back if one fails
        let result = conn.transaction::<Pengguna, diesel::result::Error, _>(|conn| {
            diesel::insert_into(pengguna::table)
                .values(input)
                .returning(Pengguna::as_returning())
                .get_result(conn)
        });
        hasil.push(result);
    }

    Ok(hasil)
}

Raw SQL — When the DSL Isn’t Enough #

For queries too complex for the Diesel DSL, use raw SQL:

use diesel::prelude::*;
use diesel::sql_query;
use diesel::sql_types::{BigInt, Text, Bool};

#[derive(Debug, QueryableByName)]
pub struct HasilAnalitik {
    #[diesel(sql_type = Text)]
    pub peran: String,
    #[diesel(sql_type = BigInt)]
    pub jumlah: i64,
    #[diesel(sql_type = BigInt)]
    pub aktif: i64,
}

pub fn statistik_per_peran(conn: &mut PgConnection) -> QueryResult<Vec<HasilAnalitik>> {
    sql_query(
        "SELECT peran, COUNT(*) as jumlah, SUM(CASE WHEN aktif THEN 1 ELSE 0 END) as aktif
         FROM pengguna
         GROUP BY peran
         ORDER BY jumlah DESC"
    )
    .load(conn)
}

// Raw SQL with parameter binding
pub fn cari_pengguna_raw(
    conn: &mut PgConnection,
    kata: &str,
) -> QueryResult<Vec<Pengguna>> {
    use diesel::sql_types::Text;

    diesel::sql_query(
        "SELECT * FROM pengguna WHERE nama ILIKE $1 OR email ILIKE $1 LIMIT 20"
    )
    .bind::<Text, _>(format!("%{}%", kata))
    .load(conn)
}

Axum Integration (Async) #

Since Diesel is synchronous, wrap it in spawn_blocking for Axum:

use axum::{extract::State, http::StatusCode, response::Json};
use std::sync::Arc;

type DbPool = Arc<crate::database::Pool>;

pub async fn handler_daftar_pengguna(
    State(pool): State<DbPool>,
) -> Result<Json<Vec<Pengguna>>, StatusCode> {
    tokio::task::spawn_blocking(move || {
        let mut conn = pool.get()
            .map_err(|_| StatusCode::SERVICE_UNAVAILABLE)?;
        ambil_semua_pengguna(&mut conn)
            .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)
    })
    .await
    .map_err(|_| StatusCode::INTERNAL_SERVER_ERROR)?
    .map(Json)
}

Summary #

Diesel = query builder DSL, sqlx = pure SQL — choose Diesel for more Rust-idiomatic code that’s more protected against SQL injection; choose sqlx for complex queries or teams more familiar with SQL.
diesel setup and diesel migration run generate schema.rs automatically — don’t edit schema.rs manually; change it via migrations and regenerate.
Three important derive macros — Queryable for SELECT, Insertable for INSERT, AsChangeset for UPDATE. Selectable enables .select(Struct::as_select()).
get_result vs execute — get_result for operations returning rows (INSERT RETURNING, UPDATE RETURNING), execute for those only returning the affected row count.
Use r2d2 for connection pools — Diesel has no built-in async pool; r2d2 is the standard choice for sync/spawn_blocking applications.
conn.transaction(|conn| {...}) — a closure that returns Err automatically rolls back the transaction. No manual BEGIN/COMMIT/ROLLBACK needed.
.filter() can be combined — multiple chained .filter() calls are interpreted as AND. Use .or() for OR, .or_filter() for OR between filter groups.
sql_query + QueryableByName for raw SQL — when the DSL isn’t enough, use #[diesel(sql_type = ...)] on struct fields for result mapping.
Diesel is synchronous → spawn_blocking for async runtimes — use tokio::task::spawn_blocking so database operations don’t block the event loop. Alternative: use the diesel-async crate.

← Previous: Warp Next: Selenium RS →