Skip to content
Rust for TS/JS Developers

Diesel Query Builder: From a Knex/TypeORM Chain to a Type-Checked DSL

22 min read

In TypeScript, when you outgrow raw SQL strings you reach for a query builder — Knex’s knex('users').where(...).orderBy(...), TypeORM’s repository.createQueryBuilder(), or Prisma’s prisma.user.findMany({ where: ... }). Diesel is Rust’s most established equivalent, and its query builder has one defining feature: the queries you write are checked by the Rust compiler against your schema. A typo in a column name, comparing a Text column to a number, or selecting columns that don’t match your struct are all compile errors, not runtime surprises. This page maps your where/select/orderBy/insert/update/delete instincts onto Diesel’s DSL.

Quick Overview

Section titled “Quick Overview”

Diesel’s query builder is a fluent, chainable API — users.filter(...).order(...).limit(...).load(conn) — that reads much like Knex or a TypeORM QueryBuilder. The crucial difference is that every method is generically typed against the table definition produced by the table! macro. Columns are real Rust values (users::name, users::age), operators like .eq(), .gt(), and .like() only accept the right-typed argument, and the row type you load into must structurally match what you select. The result: the class of bug Knex catches at runtime (or never), Diesel catches at cargo build.

Note: This page assumes you already have a schema and models from diesel-intro.md. Relationships, joins, and eager loading live in diesel-relations.md. For the compile-time-checked-SQL alternative (writing real SQL with macros instead of a builder), see sqlx-queries.md, and for a head-to-head, orm-comparison.md.

TypeScript/JavaScript Example

Section titled “TypeScript/JavaScript Example”

Here is a typical data-access layer using Knex (the patterns are nearly identical in TypeORM’s QueryBuilder). Note how the column names are strings and the row shapes are assertions:

users-repo.ts
import knex from "knex";


const db = knex({ client: "sqlite3", connection: { filename: "./app.db" } });


interface User {
  id: number;
  name: string;
  email: string;
  age: number;
  active: boolean;
}


// INSERT
async function createUser(name: string, email: string, age: number): Promise<number> {
  const [id] = await db("users").insert({ name, email, age, active: true });
  return id;
}


// SELECT + WHERE + ORDER + LIMIT
async function activeAdults(): Promise<User[]> {
  return db<User>("users")
    .where("active", true)
    .where("age", ">=", 18)
    .orderBy("name", "asc")
    .limit(10);
}


// SELECT specific columns
async function nameAndAge(): Promise<{ name: string; age: number }[]> {
  return db("users").select("name", "age").orderBy("name");
}


// UPDATE
async function deactivate(id: number): Promise<number> {
  return db("users").where({ id }).update({ active: false });
}


// DELETE
async function removeMinors(): Promise<number> {
  return db("users").where("age", "<", 18).del();
}

Three things to keep in mind, because Diesel changes all three:

  1. Column names are strings. "age" is unchecked — misspell it as "aeg" and you get a runtime SQL error, not a compile error.
  2. where("age", ">=", "eighteen") type-checks fine in TypeScript even though it compares a number column to a string; the database may coerce or error at runtime.
  3. db<User>("users") is a cast. The <User> is a promise about the row shape that nothing verifies — if the columns don’t match, you discover it when a field is undefined.

Rust Equivalent

Section titled “Rust Equivalent”

Add Diesel with the SQLite backend (use postgres or mysql for those databases; the query DSL is the same):

[dependencies]
diesel = { version = "2.3", features = ["sqlite", "returning_clauses_for_sqlite_3_35"] }

Tip: cargo add diesel --features sqlite resolves the current version for you. cargo add has been built into Cargo since 1.62 — no cargo-edit needed. The returning_clauses_for_sqlite_3_35 feature enables .returning(...) on SQLite (Postgres has it natively).

Given a schema and models from diesel-intro.md, the same repository looks like this. The current stable toolchain is Rust 1.96.0 on the 2024 edition; cargo new selects it automatically.

use diesel::prelude::*;
use diesel::sqlite::SqliteConnection;


// Normally generated by `diesel print-schema` into src/schema.rs.
mod schema {
    diesel::table! {
        users (id) {
            id -> Integer,
            name -> Text,
            email -> Text,
            age -> Integer,
            active -> Bool,
        }
    }
}


use schema::users;


#[derive(Queryable, Selectable, Debug)]
#[diesel(table_name = users)]
#[diesel(check_for_backend(diesel::sqlite::Sqlite))]
struct User {
    id: i32,
    name: String,
    email: String,
    age: i32,
    active: bool,
}


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


// SELECT + WHERE + ORDER + LIMIT
fn active_adults(conn: &mut SqliteConnection) -> QueryResult<Vec<User>> {
    use schema::users::dsl::*;
    users
        .filter(active.eq(true))
        .filter(age.ge(18))
        .order(name.asc())
        .limit(10)
        .select(User::as_select())
        .load(conn)
}


// SELECT specific columns -> a Vec of tuples
fn name_and_age(conn: &mut SqliteConnection) -> QueryResult<Vec<(String, i32)>> {
    use schema::users::dsl::*;
    users.select((name, age)).order(name.asc()).load(conn)
}


// UPDATE
fn deactivate(conn: &mut SqliteConnection, user_id: i32) -> QueryResult<usize> {
    use schema::users::dsl::*;
    diesel::update(users.find(user_id))
        .set(active.eq(false))
        .execute(conn)
}


// DELETE
fn remove_minors(conn: &mut SqliteConnection) -> QueryResult<usize> {
    use schema::users::dsl::*;
    diesel::delete(users.filter(age.lt(18))).execute(conn)
}

The shapes are familiar — .filter() is where, .order() is orderBy, .limit() is limit — but name, age, and active are now typed column values, not strings, and User::as_select() ties the query’s result columns to the User struct at compile time.

Detailed Explanation

Section titled “Detailed Explanation”

The dsl import is what makes columns feel like variables

Section titled “The dsl import is what makes columns feel like variables”
use schema::users::dsl::*;

This brings the table (users) and every column (id, name, age, …) into scope as values. Without it you’d write users::table and users::name. The dsl glob is the idiomatic way to write a query and is the reason .filter(age.ge(18)) reads almost like English. It is conventionally scoped inside the function (not at module top level) because the unqualified id, name, etc. would otherwise collide across tables.

Columns and operators are values with types

Section titled “Columns and operators are values with types”

age is not the string "age" — it is a value of a generated type that implements Diesel’s Expression trait with SQL type Integer. Methods like .eq(), .ne(), .gt(), .ge(), .lt(), .le(), .between(), .like(), .eq_any() (SQL IN), and .is_null() come from Diesel’s ExpressionMethods traits and only accept a right-hand side whose SQL type matches the column. age.ge(18) compiles; name.eq(18) does not (see Common Pitfalls).

.select(User::as_select()) connects the query to your struct

Section titled “.select(User::as_select()) connects the query to your struct”

#[derive(Selectable)] generates User::as_select(), which tells Diesel exactly which columns to fetch and in what order so the row maps cleanly onto the struct. #[diesel(check_for_backend(...))] adds a compile-time check that each struct field’s Rust type is deserializable from its column’s SQL type for that backend. This is the type-safe replacement for Knex’s db<User>("users") cast.

The terminal method decides the result shape

Section titled “The terminal method decides the result shape”

The chain is lazy — nothing touches the database until you call a loading method, and each returns a QueryResult<T> (an alias for Result<T, diesel::result::Error>):

MethodReturnsTypeScript analogue
.load(conn)Vec<T> — every matching rowawait query (array)
.first(conn)T — first row, Err(NotFound) if noneTypeORM findOneOrFail() (throws if none)
.get_result(conn)T — one row (insert/update with RETURNING).returning('*') then [0]
.execute(conn)usize — number of rows affectedthe row-count from update/del
.optional() on a .first()/.get_result()Result<Option<T>, _>query.first() returning undefined

.optional() is the idiomatic “find-or-nothing”: it turns the NotFound error into Ok(None) so a missing row is not an error.

INSERT, UPDATE, and DELETE are free functions, not methods on the table

Section titled “INSERT, UPDATE, and DELETE are free functions, not methods on the table”

Unlike Knex’s db("users").insert(...), Diesel starts mutations with diesel::insert_into(table), diesel::update(target), and diesel::delete(target). The “target” of an update or delete is itself a query (users.find(id) or users.filter(...)), so the same filtering DSL composes into writes.

A complete, runnable tour

Section titled “A complete, runnable tour”

This program builds an in-memory SQLite database and exercises insert, select, filter, order, update, and delete end to end:

use diesel::prelude::*;
use diesel::sqlite::SqliteConnection;


mod schema {
    diesel::table! {
        users (id) {
            id -> Integer,
            name -> Text,
            email -> Text,
            age -> Integer,
            active -> Bool,
        }
    }
}


use schema::users;


#[derive(Queryable, Selectable, Debug)]
#[diesel(table_name = users)]
#[diesel(check_for_backend(diesel::sqlite::Sqlite))]
struct User {
    id: i32,
    name: String,
    email: String,
    age: i32,
    active: bool,
}


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


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


fn setup() -> SqliteConnection {
    let mut conn = SqliteConnection::establish(":memory:").unwrap();
    diesel::sql_query(
        "CREATE TABLE users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL,
            age INTEGER NOT NULL,
            active BOOLEAN NOT NULL
        )",
    )
    .execute(&mut conn)
    .unwrap();
    conn
}


fn main() {
    use schema::users::dsl::*;
    let conn = &mut setup();


    // ---- INSERT (batch) ----
    let new_users = vec![
        NewUser { name: "Alice", email: "alice@example.com", age: 30, active: true },
        NewUser { name: "Bob", email: "bob@example.com", age: 25, active: false },
        NewUser { name: "Carol", email: "carol@example.com", age: 41, active: true },
    ];
    let inserted: usize = diesel::insert_into(users).values(&new_users).execute(conn).unwrap();
    println!("inserted {inserted} rows");


    // INSERT one row and get it back with RETURNING.
    let created: User = diesel::insert_into(users)
        .values(NewUser { name: "Dave", email: "dave@example.com", age: 19, active: true })
        .returning(User::as_returning())
        .get_result(conn)
        .unwrap();
    println!("created via RETURNING: {} (id={})", created.name, created.id);


    // ---- FILTER (chained = AND) ----
    let active_adults: Vec<User> = users
        .filter(active.eq(true))
        .filter(age.ge(30))
        .select(User::as_select())
        .load(conn)
        .unwrap();
    for u in &active_adults {
        println!("active adult: {} ({})", u.name, u.age);
    }


    // ---- ORDER + LIMIT ----
    let oldest_two: Vec<User> = users
        .order(age.desc())
        .limit(2)
        .select(User::as_select())
        .load(conn)
        .unwrap();
    println!("oldest two: {:?}", oldest_two.iter().map(|u| &u.name).collect::<Vec<_>>());


    // ---- find by primary key, and the no-panic Option variant ----
    let alice: User = users.filter(name.eq("Alice")).select(User::as_select()).first(conn).unwrap();
    let missing: Option<User> = users
        .filter(name.eq("Nobody"))
        .select(User::as_select())
        .first(conn)
        .optional()
        .unwrap();
    println!("missing is_none: {}", missing.is_none());


    // ---- complex WHERE: OR + LIKE + IN ----
    let matched: Vec<String> = users
        .filter(name.like("A%").or(name.like("C%")))
        .select(name)
        .load(conn)
        .unwrap();
    println!("like A%/C%: {matched:?}");


    let in_list: Vec<String> = users
        .filter(name.eq_any(vec!["Bob", "Dave"]))
        .select(name)
        .order(name.asc())
        .load(conn)
        .unwrap();
    println!("in list: {in_list:?}");


    // ---- UPDATE one row, returning the new value ----
    let updated: User = diesel::update(users.find(alice.id))
        .set(age.eq(age + 1)) // SQL: age = age + 1
        .returning(User::as_returning())
        .get_result(conn)
        .unwrap();
    println!("after increment: {} is now {}", updated.name, updated.age);


    // ---- UPDATE many with a changeset struct (None fields are skipped) ----
    let changed: usize = diesel::update(users.filter(active.eq(false)))
        .set(&UpdateUser { name: None, age: Some(0), active: Some(true) })
        .execute(conn)
        .unwrap();
    println!("reactivated {changed} rows");


    // ---- DELETE ----
    let deleted: usize = diesel::delete(users.filter(age.lt(20))).execute(conn).unwrap();
    println!("deleted {deleted} rows");


    let remaining: i64 = users.count().get_result(conn).unwrap();
    println!("remaining: {remaining}");
}

Real output:

inserted 3 rows
created via RETURNING: Dave (id=4)
active adult: Alice (30)
active adult: Carol (41)
oldest two: ["Carol", "Alice"]
missing is_none: true
like A%/C%: ["Alice", "Carol"]
in list: ["Bob", "Dave"]
after increment: Alice is now 31
reactivated 1 rows
deleted 2 rows
remaining: 2

Note .set(age.eq(age + 1)): because age is a typed column, age + 1 builds the SQL expression age = age + 1 (an in-database increment), not a Rust addition. That is the kind of expression you cannot write fluently with a stringly-typed builder.

Key Differences

Section titled “Key Differences”
ConceptKnex / TypeORM (TypeScript)Diesel (Rust)
Column referencestring: "age"typed value: age (from dsl)
Misspelled columnruntime SQL errorcompile error
Type-mismatched comparisontype-checks; coerced/errs at runtimecompile error
Result row typingdb<User>(...) cast (unchecked)User::as_select() (checked)
WHERE a AND b.where(a).where(b).filter(a).filter(b)
WHERE a OR b.orWhere(b).filter(a.or(b)) or .or_filter(b)
IN (...).whereIn("id", [...]).filter(id.eq_any(vec![...]))
Start an INSERTdb("t").insert(...)diesel::insert_into(t).values(...)
Execution modelevery method returns a Promise (eager)chain is lazy; runs on .load/.execute
Asyncalways awaitsynchronous by default (see note)

Note: Diesel is synchronous — .load(conn) blocks the thread. This is unlike await db(...) in Node and unlike SQLx, which is async-first. In an async web server you run Diesel calls on a blocking thread pool (e.g. tokio::task::spawn_blocking) or use the third-party diesel-async crate. See diesel-intro.md for the synchronous-model discussion and Section 11: Async for why Rust futures are lazy and runtime-driven — the opposite of eager JS Promises.

Inspecting the generated SQL

Section titled “Inspecting the generated SQL”

Diesel can print the SQL for any query without running it via diesel::debug_query. This is the equivalent of Knex’s .toString() / .toSQL() and is invaluable for learning and debugging:

use diesel::debug_query;
use diesel::prelude::*;
use diesel::sqlite::Sqlite;


mod schema {
    diesel::table! {
        users (id) {
            id -> Integer,
            name -> Text,
            email -> Text,
            age -> Integer,
            active -> Bool,
        }
    }
}


fn main() {
    use schema::users::dsl::*;


    let select = users
        .filter(active.eq(true))
        .filter(age.ge(18))
        .order(name.asc())
        .limit(10)
        .select((id, name));
    println!("{}", debug_query::<Sqlite, _>(&select));


    let upd = diesel::update(users.filter(id.eq(1))).set(age.eq(age + 1));
    println!("{}", debug_query::<Sqlite, _>(&upd));


    let del = diesel::delete(users.filter(active.eq(false)));
    println!("{}", debug_query::<Sqlite, _>(&del));
}

Real output:

SELECT `users`.`id`, `users`.`name` FROM `users` WHERE ((`users`.`active` = ?) AND (`users`.`age` >= ?)) ORDER BY `users`.`name` ASC LIMIT ? -- binds: [true, 18, 10]
UPDATE `users` SET `age` = (`users`.`age` + ?) WHERE (`users`.`id` = ?) -- binds: [1, 1]
DELETE FROM `users` WHERE (`users`.`active` = ?) -- binds: [false]

Every value is a ? placeholder with the actual value in binds: — Diesel parameterizes everything, so the DSL is immune to SQL injection by construction (the same protection SQLx bind parameters give you).

Common Pitfalls

Section titled “Common Pitfalls”

Pitfall 1: Comparing a column to the wrong type

Section titled “Pitfall 1: Comparing a column to the wrong type”

In Knex, .where("name", 42) is valid TypeScript. In Diesel, comparing the Text column name to an integer is a compile error:

// does not compile (error[E0277]: trait bound not satisfied)
let _bad: Vec<(i32, String)> = users
    .filter(name.eq(42)) // name is Text, 42 is an integer
    .select((id, name))
    .load(&mut conn)
    .unwrap();

The real error from cargo build (first of several, the rest cascade from it):

error[E0277]: the trait bound `{integer}: AsExpression<diesel::sql_types::Text>` is not satisfied
  --> src/main.rs:22:22
   |
22 |         .filter(name.eq(42))
   |                      ^^ the trait `AsExpression<diesel::sql_types::Text>` is not implemented for `{integer}`
   |
   = help: the following other types implement trait `AsExpression<T>`:
             `&f32` implements `AsExpression<diesel::sql_types::Float>`
             `&f64` implements `AsExpression<diesel::sql_types::Double>`
             `&i16` implements `AsExpression<diesel::sql_types::SmallInt>`
             `&i32` implements `AsExpression<diesel::sql_types::Integer>`
             ...

AsExpression<diesel::sql_types::Text> is “can this value be used where a Text SQL expression is expected?” The fix is to compare against a string: name.eq("Alice"). Diesel’s error messages are famously verbose — read the first error[E0277] and ignore the cascade.

Pitfall 2: Forgetting .select(...) when loading into a struct

Section titled “Pitfall 2: Forgetting .select(...) when loading into a struct”

If you load::<User>(...) directly off the table, Diesel uses the column order in the table! macro. If your struct’s field order differs, or you later add a column, you can get a mismatch. The robust idiom is to always pair #[derive(Selectable)] with .select(User::as_select()), which makes the column-to-field mapping explicit and order-independent. (Selectable is why the examples on this page never silently break when a column is added.)

Pitfall 3: .first() errors on an empty result — use .optional()

Section titled “Pitfall 3: .first() errors on an empty result — use .optional()”

.first(conn) returns Err(diesel::result::Error::NotFound) when nothing matches — the same surprise as TypeORM’s findOneOrFail. If “not found” is a normal case, append .optional():

let maybe_user: Option<User> = users
    .filter(email.eq("ghost@example.com"))
    .select(User::as_select())
    .first(conn)
    .optional()?; // Ok(None) instead of Err(NotFound)

Pitfall 4: An UPDATE or DELETE with no filter touches every row

Section titled “Pitfall 4: An UPDATE or DELETE with no filter touches every row”

diesel::update(users).set(active.eq(false)) (note: the bare users table, no .filter(...)) compiles and updates every row — exactly like UPDATE users SET active = false with no WHERE. This is intentional and matches SQL, but it surprises people expecting a guard. Always pass a filtered target (users.find(id) or users.filter(...)) unless a full-table change is truly what you want.

Pitfall 5: Expecting the chain to run eagerly

Section titled “Pitfall 5: Expecting the chain to run eagerly”

let q = users.filter(active.eq(true)); does not hit the database. Diesel queries are lazy values; the SQL runs only when you call .load/.first/.get_result/.execute. This is the opposite of Knex, where the builder is a thenable and await-ing it (or even leaving it dangling in some setups) triggers execution.

Best Practices

Section titled “Best Practices”
  • Always use #[derive(Selectable)] + .select(T::as_select()) for reads, and add #[diesel(check_for_backend(...))] so column/field type mismatches are caught at compile time.
  • Put use schema::<table>::dsl::*; inside the function, not at module scope, to avoid column-name collisions between tables.
  • Use a changeset struct (#[derive(AsChangeset)]) for partial updates. Option<T> fields that are None are skipped, giving you PATCH-style “update only what’s set” semantics for free.
  • Use .returning(T::as_returning()) with .get_result() to insert or update and read the row back in one round trip (Postgres always; SQLite ≥ 3.35 with the returning_clauses_for_sqlite_3_35 feature).
  • Reach for .into_boxed() when the WHERE clause is dynamic (see the real-world example) — it is the clean way to conditionally add filters.
  • Prefer .eq_any(vec) over building an OR chain for IN (...) lists.
  • Use diesel::debug_query while learning to see exactly what SQL the DSL produces.
  • Keep blocking Diesel calls off the async executor — wrap them in spawn_blocking or use diesel-async. See connection-pooling.md for pooling Diesel connections with r2d2.

Real-World Example

Section titled “Real-World Example”

A common requirement is a search endpoint with optional filters and pagination — the kind of thing a web handler (Section 16: Web APIs) passes through. The challenge is that the WHERE clause depends on which filters the caller sent. Diesel’s .into_boxed() “boxes” the query into a single type so you can conditionally chain filters in if let branches — the type-safe equivalent of Knex’s query = query.where(...) reassignment in a loop:

use diesel::prelude::*;
use diesel::sqlite::SqliteConnection;


mod schema {
    diesel::table! {
        products (id) {
            id -> Integer,
            name -> Text,
            category -> Text,
            price_cents -> Integer,
            in_stock -> Bool,
        }
    }
}


use schema::products;


#[derive(Queryable, Selectable, Debug)]
#[diesel(table_name = products)]
#[diesel(check_for_backend(diesel::sqlite::Sqlite))]
struct Product {
    id: i32,
    name: String,
    category: String,
    price_cents: i32,
    in_stock: bool,
}


#[derive(Insertable)]
#[diesel(table_name = products)]
struct NewProduct<'a> {
    name: &'a str,
    category: &'a str,
    price_cents: i32,
    in_stock: bool,
}


// What a web handler would deserialize from the query string.
#[derive(Default)]
struct ProductFilter {
    category: Option<String>,
    max_price_cents: Option<i32>,
    only_in_stock: bool,
}


// Build ONE query whose WHERE clause depends on which filters are present.
fn search_products(
    conn: &mut SqliteConnection,
    filter: &ProductFilter,
    page: i64,
    per_page: i64,
) -> QueryResult<Vec<Product>> {
    use schema::products::dsl::*;


    let mut query = products.into_boxed(); // erase the type so we can branch


    if let Some(cat) = &filter.category {
        query = query.filter(category.eq(cat.clone()));
    }
    if let Some(max) = filter.max_price_cents {
        query = query.filter(price_cents.le(max));
    }
    if filter.only_in_stock {
        query = query.filter(in_stock.eq(true));
    }


    query
        .order(price_cents.asc())
        .limit(per_page)
        .offset((page - 1) * per_page)
        .select(Product::as_select())
        .load(conn)
}


fn main() -> QueryResult<()> {
    use schema::products::dsl::*;


    let mut conn = SqliteConnection::establish(":memory:").unwrap();
    diesel::sql_query(
        "CREATE TABLE products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            category TEXT NOT NULL,
            price_cents INTEGER NOT NULL,
            in_stock BOOLEAN NOT NULL
        )",
    )
    .execute(&mut conn)?;


    diesel::insert_into(products)
        .values(vec![
            NewProduct { name: "Keyboard", category: "tech", price_cents: 4999, in_stock: true },
            NewProduct { name: "Mouse", category: "tech", price_cents: 2599, in_stock: false },
            NewProduct { name: "Mug", category: "home", price_cents: 1299, in_stock: true },
            NewProduct { name: "Monitor", category: "tech", price_cents: 19999, in_stock: true },
        ])
        .execute(&mut conn)?;


    let filter = ProductFilter {
        category: Some("tech".to_string()),
        max_price_cents: Some(10_000),
        only_in_stock: true,
    };


    for p in search_products(&mut conn, &filter, 1, 10)? {
        println!("{} — ${}.{:02}", p.name, p.price_cents / 100, p.price_cents % 100);
    }


    let all = search_products(&mut conn, &ProductFilter::default(), 1, 10)?;
    println!("no filters -> {} products", all.len());
    Ok(())
}

Real output:

Keyboard — $49.99
no filters -> 4 products

With the tech + under-$100 + in-stock filters, only the Keyboard qualifies (the Mouse is out of stock, the Monitor is too expensive, the Mug is the wrong category). With no filters, every product comes back. The same search_products function serves both, because each filter is added only when present — and it is all still compile-checked against the products schema.

Further Reading

Section titled “Further Reading”

Official documentation

Section titled “Official documentation”
Section titled “Related sections in this guide”

Exercises

Section titled “Exercises”

Exercise 1: Filter, order, and project

Section titled “Exercise 1: Filter, order, and project”

Difficulty: Easy

Objective: Reproduce a SELECT name, age FROM users WHERE active = true ORDER BY age DESC with the DSL.

Instructions: Using the users schema and User/NewUser models from this page, write fn active_by_age(conn: &mut SqliteConnection) -> QueryResult<Vec<(String, i32)>> that returns the name and age of all active users, ordered oldest first. In main, seed an in-memory database with a few users (some inactive) and print the result.

Solution 
use diesel::prelude::*;
use diesel::sqlite::SqliteConnection;


mod schema {
    diesel::table! {
        users (id) {
            id -> Integer,
            name -> Text,
            email -> Text,
            age -> Integer,
            active -> Bool,
        }
    }
}
use schema::users;


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


fn active_by_age(conn: &mut SqliteConnection) -> QueryResult<Vec<(String, i32)>> {
    use schema::users::dsl::*;
    users
        .filter(active.eq(true))
        .order(age.desc())
        .select((name, age))
        .load(conn)
}


fn main() -> QueryResult<()> {
    use schema::users::dsl::*;
    let mut conn = SqliteConnection::establish(":memory:").unwrap();
    diesel::sql_query(
        "CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL,
         email TEXT NOT NULL, age INTEGER NOT NULL, active BOOLEAN NOT NULL)",
    )
    .execute(&mut conn)?;
    diesel::insert_into(users)
        .values(vec![
            NewUser { name: "Alice", email: "a@x.com", age: 30, active: true },
            NewUser { name: "Bob", email: "b@x.com", age: 25, active: false },
            NewUser { name: "Carol", email: "c@x.com", age: 41, active: true },
        ])
        .execute(&mut conn)?;


    println!("{:?}", active_by_age(&mut conn)?);
    Ok(())
}

Real output:

[("Carol", 41), ("Alice", 30)]

Exercise 2: Partial update with a changeset and RETURNING

Section titled “Exercise 2: Partial update with a changeset and RETURNING”

Difficulty: Medium

Objective: Update only the fields a caller supplied and read the new row back in one query.

Instructions: Add a #[derive(AsChangeset)] struct UserPatch { name: Option<String>, age: Option<i32> }. Write fn patch_user(conn: &mut SqliteConnection, user_id: i32, patch: &UserPatch) -> QueryResult<User> that updates the matching user and returns the updated User via .returning(User::as_returning()).get_result(conn). In main, insert a user, then patch only their age (leave name as None) and print the result. (Enable the returning_clauses_for_sqlite_3_35 feature.)

Solution 
use diesel::prelude::*;
use diesel::sqlite::SqliteConnection;


mod schema {
    diesel::table! {
        users (id) {
            id -> Integer,
            name -> Text,
            email -> Text,
            age -> Integer,
            active -> Bool,
        }
    }
}
use schema::users;


#[derive(Queryable, Selectable, Debug)]
#[diesel(table_name = users)]
#[diesel(check_for_backend(diesel::sqlite::Sqlite))]
struct User {
    id: i32,
    name: String,
    email: String,
    age: i32,
    active: bool,
}


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


#[derive(AsChangeset)]
#[diesel(table_name = users)]
struct UserPatch {
    name: Option<String>,
    age: Option<i32>,
}


fn patch_user(conn: &mut SqliteConnection, user_id: i32, patch: &UserPatch) -> QueryResult<User> {
    use schema::users::dsl::*;
    diesel::update(users.find(user_id))
        .set(patch)
        .returning(User::as_returning())
        .get_result(conn)
}


fn main() -> QueryResult<()> {
    use schema::users::dsl::*;
    let mut conn = SqliteConnection::establish(":memory:").unwrap();
    diesel::sql_query(
        "CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL,
         email TEXT NOT NULL, age INTEGER NOT NULL, active BOOLEAN NOT NULL)",
    )
    .execute(&mut conn)?;
    let id_val: i32 = diesel::insert_into(users)
        .values(NewUser { name: "Alice", email: "a@x.com", age: 30, active: true })
        .returning(id)
        .get_result(&mut conn)?;


    let patch = UserPatch { name: None, age: Some(31) }; // only age changes
    let updated = patch_user(&mut conn, id_val, &patch)?;
    println!("{} is now {}", updated.name, updated.age);
    Ok(())
}

Real output:

Alice is now 31

The name: None field is skipped, so only age is written.

Exercise 3: Dynamic filtering with into_boxed

Section titled “Exercise 3: Dynamic filtering with into_boxed”

Difficulty: Hard

Objective: Build a query whose WHERE clause is assembled from optional criteria, like a real search endpoint.

Instructions: Define struct Search { min_age: Option<i32>, name_prefix: Option<String> }. Write fn search(conn: &mut SqliteConnection, s: &Search) -> QueryResult<Vec<String>> that starts from users.into_boxed(), conditionally adds age.ge(min) and name.like(format!("{prefix}%")) when those options are present, orders by name ascending, and returns the matching names. Test it with a couple of different Search values.

Solution 
use diesel::prelude::*;
use diesel::sqlite::SqliteConnection;


mod schema {
    diesel::table! {
        users (id) {
            id -> Integer,
            name -> Text,
            email -> Text,
            age -> Integer,
            active -> Bool,
        }
    }
}
use schema::users;


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


struct Search {
    min_age: Option<i32>,
    name_prefix: Option<String>,
}


fn search(conn: &mut SqliteConnection, s: &Search) -> QueryResult<Vec<String>> {
    use schema::users::dsl::*;
    let mut q = users.into_boxed();
    if let Some(min) = s.min_age {
        q = q.filter(age.ge(min));
    }
    if let Some(prefix) = &s.name_prefix {
        q = q.filter(name.like(format!("{prefix}%")));
    }
    q.order(name.asc()).select(name).load(conn)
}


fn main() -> QueryResult<()> {
    use schema::users::dsl::*;
    let mut conn = SqliteConnection::establish(":memory:").unwrap();
    diesel::sql_query(
        "CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL,
         email TEXT NOT NULL, age INTEGER NOT NULL, active BOOLEAN NOT NULL)",
    )
    .execute(&mut conn)?;
    diesel::insert_into(users)
        .values(vec![
            NewUser { name: "Alice", email: "a@x.com", age: 30, active: true },
            NewUser { name: "Albert", email: "al@x.com", age: 22, active: true },
            NewUser { name: "Bob", email: "b@x.com", age: 25, active: false },
            NewUser { name: "Carol", email: "c@x.com", age: 41, active: true },
        ])
        .execute(&mut conn)?;


    let by_prefix = Search { min_age: None, name_prefix: Some("Al".to_string()) };
    println!("prefix 'Al': {:?}", search(&mut conn, &by_prefix)?);


    let by_both = Search { min_age: Some(26), name_prefix: Some("Al".to_string()) };
    println!("prefix 'Al' & age>=26: {:?}", search(&mut conn, &by_both)?);


    let everyone = Search { min_age: None, name_prefix: None };
    println!("no filters: {:?}", search(&mut conn, &everyone)?);
    Ok(())
}

Real output:

prefix 'Al': ["Albert", "Alice"]
prefix 'Al' & age>=26: ["Alice"]
no filters: ["Albert", "Alice", "Bob", "Carol"]