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 all the different ways to go about updating records.

An update statement is constructed by calling diesel::update(target).set(changes). The resulting statement is then run by calling either execute, get_result, or get_results.

If you look at the documentation for update, you’ll notice that the type of the argument is any type T which implements IntoUpdateTarget. You don’t need to worry about what this trait does, but it is important to know which types implement it. There are three kinds which implement this trait. The first is tables.

If we have a table that looks like this:

table! {
    posts {
        id -> BigInt,
        title -> Text,
        body -> Text,
        draft -> Bool,
        publish_at -> Timestamp,
        visit_count -> Integer,
    }
}

We could write a query that publishes all posts by doing:

use crate::posts::dsl::*;

diesel::update(posts).set(draft.eq(false)).execute(conn)

We can use the debug_query function to inspect the generated SQL. The output you see may slightly differ from this guide, depending on which backend you’re using. If we run println!("{}", debug_query::<Pg, _>(&our_query));, we’ll see the following:

UPDATE "posts" SET "draft" = $1 -- binds: [false]

This is pretty much one-to-one with the Rust code (the $1 denotes a bound parameter in PostgreSQL, in SQLite/MySQL it would be ?, which will be substituted with false here). It’s quite rare to want to update an entire table, though. So let’s look at how we can scope that down. The second kind that you can pass to update is any query which has only had .filter called on it. We could scope our update to only touch posts where publish_at is in the past like so:

use crate::posts::dsl::*;
use diesel::dsl::now;

diesel::update(posts)
    .filter(publish_at.lt(now))
    .set(draft.eq(false))
    .execute(conn)

That would generate the following SQL:

UPDATE "posts" SET "draft" = $1 WHERE ("posts"."publish_at" < CURRENT_TIMESTAMP) -- binds: [false]

The most common update queries are just scoped to a single record. So the final kind that you can pass to update is anything which implements the Identifiable trait. Identifiable gets implemented by putting #[derive(Identifiable)] on a struct. It represents any struct which is one-to-one with a row on a database table. Importantly, and unlike Queryable, the Identifiable trait requires that the schema generated by the table! macro be in scope, or the compilation will fail with E0433, noting Use of undeclared type or module (your_tablename).

If we wanted a struct that mapped to our posts table, it’d look something like this:

#[derive(Queryable, Identifiable, AsChangeset)]
pub struct Post {
    pub id: i64,
    pub title: String,
    pub body: String,
    pub draft: bool,
    pub publish_at: SystemTime,
    pub visit_count: i32,
}

The struct has one field per database column, but what’s important for Identifiable is that it has the id field, which is the primary key of our table. Since our struct name is just the table name without an s, we don’t have to provide the table name explicitly. If our struct were named something different, or if pluralizing it was more complex than putting an s on the end, we would have to specify the table name by adding #[diesel(table_name = posts)]. We’re using SystemTime here since it’s in the standard library, but in a real application we’d probably want to use a more full-featured type like one from chrono, which you can do by enabling the chrono feature on Diesel.

If we wanted to publish just this post, we could do it like this:

diesel::update(post)
    .set(posts::draft.eq(false))
    .execute(conn)

It’s important to note that we always pass a reference to the post, not the post itself. When we write update(post), that’s equivalent to writing update(posts.find(post.id)), or update(posts.filter(id.eq(post.id))). We can see this in the generated SQL:

UPDATE "posts" SET "draft" = $1 WHERE ("posts"."id" = $2) -- binds: [false, 1]

Now that we’ve seen all the ways to specify what we want to update, let’s look at the different ways to provide the data to update it with. We’ve already seen the first way, which is to pass column.eq(value) directly. So far we’ve just been passing Rust values here, but we can actually use any Diesel expression. For example, we could increment a column:

use crate::posts::dsl::*;

diesel::update(posts)
    .set(visit_count.eq(visit_count + 1))
    .execute(conn)

That would generate this SQL:

UPDATE "posts" SET "visit_count" = ("posts"."visit_count" + $1) -- binds: [1]

Assigning values directly is great for small, simple changes. If we wanted to update multiple columns this way, we can pass a tuple.

use crate::posts::dsl::*;

diesel::update(posts)
    .set((
        title.eq("[REDACTED]"),
        body.eq("This post has been classified"),
    ))
    .execute(conn)

This will generate exactly the SQL you’d expect:

UPDATE "posts" SET "title" = $1, "body" = $2 -- binds: ["[REDACTED]", "This post has been classified"]

AsChangeset

While it’s nice to have the ability to update columns directly like this, it can quickly get cumbersome when dealing with forms that have more than a handful of fields. If we look at the signature of .set, you’ll notice that the constraint is for a trait called AsChangeset. This is another trait that diesel can derive for us. We can add #[derive(AsChangeset)] to our Post struct, which will let us pass a &Post to set.

diesel::update(posts::table).set(post).execute(conn)

The SQL will set every field present on the Post struct except for the primary key.

UPDATE "posts" SET "title" = $1, "body" = $2, "draft" = $3, "publish_at" = $4, "visit_count" = $5 -- binds: ["", "", false, now, 0]

Changing the primary key of an existing row is almost never something that you want to do, so #[derive(AsChangeset)] assumes that you want to ignore it. The only way to change the primary key is to explicitly do it with .set(id.eq(new_id)). However, note that #[derive(AsChangeset)] doesn’t have the information from your table definition. If the primary key is something other than id, you’ll need to put #[diesel(primary_key(your_primary_key))] on the struct as well.

If the struct has any optional fields on it, these will also have special behavior. By default, #[derive(AsChangeset)] will assume that None means that you don’t wish to assign that field. For example, if we had the following code:

#[derive(AsChangeset)]
#[diesel(table_name = posts)]
struct PostForm<'a> {
    title: Option<&'a str>,
    body: Option<&'a str>,
}

diesel::update(posts::table)
    .set(&PostForm {
        title: None,
        body: Some("My new post"),
    })
    .execute(conn)

That would generate the following SQL:

UPDATE "posts" SET "body" = $1 -- binds: ["My new post"]

If you wanted to assign NULL instead, you can either specify #[diesel(treat_none_as_null = true)] on the struct, or you can have the field be of type Option<Option<T>>. Diesel doesn’t currently provide a way to explicitly assign a field to its default value, though it may be provided in the future.

If you are using PostgreSQL or SQLite, all of these options will work with INSERT ON CONFLICT DO UPDATE as well. See the upsert docs for more details.

Executing your query

Once you’ve constructed your query, we need to actually execute it. There are several different methods to do this, depending on what type you’d like back.

The simplest method for running your query is execute. This method will run your query, and return the number of rows that were affected. This is the method you should use if you simply want to ensure that the query executed successfully, and don’t care about getting anything back from the database.

For queries where you do want to get data back from the database, we need to use get_result or get_results. If you haven’t explicitly called returning, these methods will return all of the columns on the table. Similar to load on a select statement, you will need to specify the type you’d like to deserialize to (either a tuple or a struct with #[derive(Queryable)]). You should use get_results when you are expecting more than one record back. If you are only expecting a single record, you can call get_result instead.

It should be noted that receiving 0 rows from get_result is considered an error condition by default. If you want to get back 0 or 1 row (e.g. have a return type of QueryResult<Option<T>>), then you will need to call .get_result(...).optional().

Finally, if your struct has both #[derive(AsChangeset)] and #[derive(Identifiable)], you will be able to use the save_changes method. Unlike the other methods mentioned in this guide, you do not explicitly build a query when using save_changes. Doing foo.save_changes(&conn) is equivalent to doing diesel::update(&foo).set(&foo).get_result(&conn). Like get_result and get_results, you will need to specify the type you’d like to get back.

All of the code for this guide can be found in executable form in this Diesel example.