Most applications fall into a category called “CRUD” apps. CRUD stands for “Create, Read, Update, Delete”. Diesel provides support for all four pieces, but in this guide we’re going to look at the different ways to go about creating SELECT statements.

The examples for this guide are going to be shown for PostgreSQL, but you can follow along with any backend.

This guide will cover how to construct SELECT statements using Diesel and it will also discuss how Diesel maps back query results to your Rust types.

For this guide, our schema will look like this:

diesel::table! {
    users {
        id -> Integer,
        name -> Text,
        hair_color -> Nullable<Text>,
        created_at -> Timestamp,
        updated_at -> Timestamp,
    }
}

Query Building

Diesel supports four ways of constructing SELECT queries:

• Call diesel::select to construct a SELECT statement without FROM clause
• Call any method from the RunQueryDsl trait on a table type to directly execute a SELECT * FROM table statement.
• Call any method from the QueryDsl trait on a table type to construct a specific SELECT statement.
• Call HasQuery::query() on a Rust type deriving the HasQuery trait

For this guide we mostly cover the third and fourth variant, as the first two variants are mostly useful for specific situations, while the other variants are the more general ones.

SELECT Statements without FROM clause

Diesel allows to construct SELECT statements without FROM clauses via the diesel::select function. This function can be used to construct simple queries.

For example you could query information about your PostgreSQL version via the following query:

use diesel::prelude::*;
use diesel::expression::functions::declare_sql_function;

// Declare the `version()` SQL function
#[declare_sql_function]
extern "SQL" {
    fn version() -> Text;
}

let version_text = diesel::select(version()).get_result::<String>(connection)?;
println!("Running PostgreSQL: `{version_text}`");

which is equivalent to the following SQL:

SELECT version();

This simple query already demonstrates several important aspects of constructing SELECT queries using Diesel:

1. Any query needs to end with a method from the RunQueryDsl trait to actually execute the query. The method decides how many results are returned. For this particular example we use the RunQueryDsl::get_result method as we already know that we only expect a single result.
2. Diesel performs a mapping from the SQL types returned by your query to compatible Rust types. This mapping needs to use compatible types on both sides. For this particular example we declared the version() function in such a way that it returns a [Text] value, which is compatible to Rust String values. See the result mapping section of this guide for more details on this topic.

SELECT statements via RunQueryDsl

The simplest way to construct a SELECT statement that loads data from a particular table is by calling any method from the RunQueryDsl trait on the corresponding table type. This always constructs simple SELECT * FROM table queries.

For example to load all data from a particular table you can use the following query:

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

// This returns a Vec<(i32, String, Option<String>, NaiveDateTime, NaiveDateTime)>
let all_users = users::table.load::<(i32, String, Option<String>, NaiveDateTime, NaiveDateTime)>(connection)?;

for user in all_users {
    println!("{}: {}", user.0, user.1); 
}

which is equivalent to the following SQL query:

SELECT id, name, hair_color, created_at, updated_at FROM users;

By default Diesel will always select all columns listed in your table! definition in the same order as listed there.

RunQueryDsl provides the following other methods:

• RunQueryDsl::load to load all data returned by the query into a Vec
• RunQueryDsl::get_results is equivalent to RunQueryDsl::load
• RunQueryDsl::get_result just loads the first result returned by the query and drops any potential remaining result
• RunQueryDsl::first appends a LIMIT 1 to your query and then loads the single result returned by your query.

Similarly to what we already discussed in the previous section Diesel expects that the Rust type used to store the query result in matches what is returned by the query. In this particular case the query returns multiple values for each row, so the Rust side uses a tuple to represent that. See the Result Mapping section for different solutions and more details on this topic.

SELECT statements via QueryDsl

To construct more complex SELECT statements you need to use the methods provided by the QueryDsl trait. Each of the methods provided by this trait corresponds to one specific clause in a SELECT statement. You can usually chain these methods as you like to construct arbitrary complex SELECT statements. If called more than once most methods will just replace the previous content for this particular part of the SELECT statement.

For example the following query selects all users with an hair color of green and orders them by the time of the last update of those data.

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

let users_with_green_hair = users::table.filter(users::hair_color.eq('green'))
    .order_by(users::updated_at.desc())
    .load::<(i32, String, Option<String>, NaiveDateTime, NaiveDateTime)>(connection)?;

for user in users_with_green_hair {
    println!("User {} has grean hair", user.1);
}

This corresponds to the following SQL query:

SELECT id, name, hair_color, created_at, updated_at FROM users
WHERE hair_color = $1
ORDER BY updated_at DESC;

Diesel will always use bind values for to include user provided values in the generated query. This ensures that no user provided value can inject additional SQL in the generated query.

There are the following notable exceptions for methods that have requirements on the order you call them:

• QueryDsl::inner_join and QueryDsl::left_join must be called before any column from the joined table is used in your query as otherwise Diesel doesn’t know about them.
• QueryDsl::group_by must be called before you call QueryDsl::select as otherwise Diesel doesn’t know which expressions are aggregated and which not.

Most methods of the QueryDsl trait like filter() or select() accept SQL expressions. This enables you to construct various complex expressions via the different *ExpressionMethods traits provided by Diesel. Some of the traits and also the filter() function place additional constraints on the SQL side type of the expression. filter() always expects a boolean value, while for example any method of the TextExpressionMethods trait expects you to call it on an expression of with a SQL side TEXT type.

For example the following constructs are valid expressions in Diesel:

• users::name represents a table column with the corresponding SQL side type
• users::id.eq(42) represents the expression users.id = 42
• users::id.eq(users::age + users::age) represents the expression users.id = (users.age + users.age)
• 42.into_sql::<Integer>().eq(users::id) represents a comparision with the constant on the left side via the following SQL $1 = users.id
• users::id.eq(users::id + 5).eq(false) represents a chained expression including an arithmetic operation via the following SQL (users.id = (users.id + 5)) = false

Again you can mostly mix and match these function calls as wanted as long as you make sure your SQL side types line up correctly.

Using such QueryDsl::select allows you to customize the SELECT clause of your SELECT statement. For example the following query will use a WINDOW function as part of the select clause. Keep in mind that changing the SELECT clause of your query naturally changes what your query returns, which in turn affects the structure of your Rust side type.

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

let users = users::table.select((
    users::id,
    users::name,
    users::hair_color, 
    dsl::lag(users::id).partition_by(users::hair_color)
)).order_by(users::updated_at.asc())
  .load::<(i32, String, Option<String>, Option<i32>)(connection)?;

for user in users {
    println!("User {} has the hair color {:?}", user.1, user.2);
    if let Some(id) = users.3 {
        println!("The user with the {id} has the same hair color");
    }
}

This is equivalent to the following SQL:

SELECT id, name, hair_color, lag(id) OVER(PARTITION BY hair_color) FROM users ORDER BY updated_at ASC;

SELECT statements via HasQuery::query()

The #[derive(HasQuery)] macro allows you to associate a particular query with your Rust side type. This ensures that query is always prepopulated with the correct select clause to match your Rust side data structure.

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

#[derive(HasQuery)]
#[diesel(table_name = users)]
struct User {
    name: String,
    hair_color: Option<String>,
    id: i32,
}

// This now returns a `Vec<User>`, which is automatically infered
// by using `User::query()` to construct your query
let users_with_green_hair = User::query()
    .filter(users::hair_color.eq("green"))
    .order_by(users::updated_at.desc())
    .load(conn)?;

which is equivalent to the following SQL:

SELECT name, hair_color, id FROM users WHERE hair_color = $1 ORDER BY updated_at DESC;

By default the derive constructs a base query using the provided table including a SELECT clause matching the fields of your struct. These fields are mapped to the relevant table columns.

The #[derive(HasQuery)] derive macro also allows to customize both the field mapping and also the base query construction via attributes. The macro nevertheless will always apply a SELECT clause matching your struct definition to the provided base query, so make sure that the base query actually fits that SELECT clause.

To mirror the example from the previous section you would need to write code like this:

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

#[derive(HasQuery)]
#[diesel(base_query = users::table.order_by(users::updated_at.asc()))]
struct CustomUser {
    id: i32,
    name: String,
    hair_color: Option<String>,
    #[diesel(select_expression = dsl::lag(users::id).partition_by(users::hair_color))]
    last_user_with_same_hair_color: Option<i32>,
}

let users = Users::query().load(connection)?;

for user in users {
    println!("User {} has the hair color {:?}", user.name, user.hair_color);
    if let Some(id) = users.last_user_with_same_hair_color {
        println!("The user with the {id} has the same hair color");
    }
}

Result Mapping

Diesel maps results of queries to Rust types. This is an important difference to the table based mapping performed by most ORM’s. As result you usually end up with several Rust types belonging to the same table in your Diesel code base. At the same time a single Rust type can belong to several tables (e.g. via joins). This pattern gives you the necessary flexibility to both select only what you really need and also to evolve specific queries based on your needs and not what the table model expects you have.

As mentioned several times before Diesel performs checks at compile times to verify that the result returned by a particular query matches the Rust side data structure it is trying to load the result into. If there is a mismatch this will lead to a compilation error. Specifically Diesel checks that:

• The number of returned fields match
• The order of fields match in your query and in your Rust side type
• The SQL side type of each field is compatible with the Rust type at this position.

At the most basic level Diesel allows to map query results directly to basic Rust types. For queries returning only a single field you can directly use the field type like this:

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

let ids: Vec<i32> = users::table.select(users::id).load::<i32>(connection)?;

You can usually get a list of supported Rust types for a particular SQL side type by checking out the Diesel API documentation of that particular SQL type. This example uses the users::id column in your select type. This column is of the type Integer so you would need to check the documentation of diesel::sql_types::Integer to get a list of all compatible Rust side types.

For queries returning more than one field you can use tuples to represent the result type like this:

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

let id_and_name: Vec<(i32, String)> = users::table.select((users::id, users::name)).load(connection)?;

It is also possible to nest tuples by applying the same nesting both in the SQL representation and the Rust representation:

use diesel::prelude::*;
use crate::schema::users;
    
users::table.select((users::id, users::name, (users::id, users::name)))
    .load::<(i32, String, (i32, String))>(connection)?;

This feature is particularly useful if you want to seperate values from different tables later on.

Mapping query results to tuples is a good idea if your query returns a small number of columns and you plan to destruct the returned result directly. Otherwise it’s often a better idea to map query results to Rust structs. This gives you named fields, which in turn makes it harder to mix up the meaning of an particular field.

Diesel provides a #[derive(Queryable) proc macro derive to easily map query results to a rust data type. The assumption of this derive is simple: It gets a tuple of values (as shown before) and maps each field of the tuple to the field of the struct in the order of the struct fields. So the first tuple field is mapped to the first struct field and so on.

use diesel::prelude::*;
use crate::schema::users;
    
#[derive(Queryable)]
struct User {
    id: i32,
    name: String,
}

let users: Vec<User> = users::table.select((users::id, users::name)).load::<User>(connection)?;

Diesel performs checks if the returned types match your struct and fail compilation if that’s for any of the reasons listed before is not the case. #[derive(Queryable)] is the most low level way to map query results provided by Diesel to Rust data structures. It offers the largest level of control at the cost of the worst compilation errors if something goes wrong.

To improve this situation Diesel provides a #[derive(Selectable)] proc macro derive. This derive allows to associate a SELECT clause with your Rust struct. It also provides a way to check on struct field level if each field is compatible with the corresponding SQL side type. You usually combine this derive with #[derive(Queryable)] like this:

use diesel::prelude::*;
use crate::schema::users;
    
#[derive(Queryable, Selectable)]
#[diesel(table_name = users)]
#[diesel(check_for_backend(diesel::pg::Pg))]
struct User {
    id: i32,
    name: String,
}

let users: Vec<User> = users::table.select(User::as_select()).load(connection)?;

By calling .select(User::as_select()) you instruct Diesel to construct whatever SELECT clause matches your Rust side type. This locks in the query result type to be only User as long as you do not call .select() again. The #[diesel(check_for_backend(diesel::pg::Pg))] attribute generates additional optional code to improve error messages for the case of a field mismatch for the PostgreSQL backend. You can also pass several backends at once to this attribute by chaining them: #[diesel(check_for_backend(diesel::pg::Pg, diesel::sqlite::Sqlite))]. This will generate the necessary check code for all listed backends.

The #[derive(Selectable)] macro allows to nest other types implementing Selectable via the #[diesel(embed)] attribute. It will construct a nested select clause as shown before for tuples.

The #[diesel(select_expression = users::id.eq(42))] attribute allows you to provide custom select expressions for specific fields. By default the derive interprets the field name as column name of the corresponding table. The API Documentation of the trait provides several examples for these attributes.

The #[derive(Seletable)] marco still requires you to take care to manually apply the right select clause and line it up with #[derive(Queryable)]. The #[derive(HasQuery)] macro presented before takes another step and essentially combines both derives into a single derive with some additional functionality added.

use diesel::prelude::*;
use crate::schema::users;
    
#[derive(HasQuery)]
#[diesel(table_name = users)]
struct User {
    id: i32,
    name: String,
}

let users: Vec<User> = User::query().load(connection)?;

Notable differences are to #[derive(Selectable)] and #[derive(Queryable)] are:

• #[derive(HasQuery)] automatically performs the checks for improved error messages for all enabled backends
• #[derive(HasQuery)] provides a User::query() method to easily construct the correct query
• #[derive(HasQuery)] accepts a #[diesel(base_query)] attribute to associate a specific more complex base query with the particular struct. The default base query is just users::table, either based on the explicitly specified table name or based on the infered table name.

We recommend to use #[derive(HasQuery)] in any place that is covered by what this derive provided and only fall back to #[derive(Queryable)] and #[derive(Selectable)] for more specific use cases.