How to define a Queryable and Insertable struct with AUTO_INCREMENT column using Diesel ORM in Rust?

Question

I am struggling with a seemingly basic task with Diesel ORM. I am trying to define a struct that I can both insert (without specifying id manually) and query, for a simple table.

CREATE TABLE users(
    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    name TEXT NOT NULL,
    PRIMARY KEY(id),
);

To be able to call Diesel's insert_into and select functions, I create the following model in models.rs:

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

This leads to an issue: when inserting, I cannot leave id unspecified and rely on auto-increment. In the Diesel docs, it's mentioned that using Option<T> and setting the field to None when inserting is the intended workflow. However, that results in an error that the field should be NotNull to be Queryable (which makes perfect sense):

error[E0271]: type mismatch resolving `<Integer as SqlType>::IsNull == IsNullable`
  --> src/database/models.rs:13:13
   |
13 |     pub id: Option<i32>,
   |             ^^^^^^ expected `NotNull`, found `IsNullable`
   |
   = note: required for `Option<i32>` to implement `Queryable<diesel::sql_types::Integer, Sqlite>`
   = note: required for `Option<i32>` to implement `FromSqlRow<diesel::sql_types::Integer, Sqlite>`
   = help: see issue #48214

Is there a way to have both Queryable and Insertable derived, and have a auto-incrementing PRIMARY KEY? If that matters, I am using SQLite.

Note: I do not want to define two structs with the same fields, and have them inherit one trait each, as that is repeat code, and would become unruly for more complex schemas.

Answer 1

This isn't directly possible due to the library design, when using AUTO_INCREMENT ids. Instead, the intended workflow is to create separate types for Insertable and Queryable data, even if that leads to duplication of a large part of those types. A draft guide for Diesel's insertable data says:

When implementing Insertable, you probably won't be setting the auto-incremented id field of the row. Usually you will also ignore fields such as created_at and updated_at. For this reason, it's not advisable to use Queryable and Insertable on the same struct due to the field number constraints of Queryable. Create another struct that you may use for database insertions that will have all the fields you would like to set.

However, in my use case this isn't a good solution. So, if you still want to have the same struct derive both to avoid code duplication, the simplest solution I have found is to use a GUID for the PRIMARY KEY. Since GUIDs are 128-bit, BINARY(16) is the fitting data type, which would correspond to a Vec<u8> in Rust. According to this question this should not introduce significant performance issues.

CREATE TABLE users(
    id BINARY(16) NOT NULL,
    name TEXT NOT NULL,
    PRIMARY KEY(id),
);

Since fully-random GUIDs don't work well as efficiently-indexable database keys, one thing to note is, it's best to use timestamp-based keys. The documentation for Cargo uuid crate says:

If you want to use UUIDs as database keys or need to sort them then consider version 7 (v7) UUIDs.

With that in mind, the Rust code would look like this:

/* models.rs */
#[derive(Queryable, Debug, Selectable, Insertable)]
pub struct User {
    pub id: Vec<u8>,
    pub name: String,
}

/* wherever you interact with the DB */
/// For readability
type DbGuid = Vec<u8>;

/// Insert, return id. We don't worry about auto increment etc., since every time uuid::Uuid::now_v7() is guaranteed to be unique
fn insert_user(db: &mut SqliteConnection, name: &str) -> Result<DbGuid> {
    let to_add = models::User {
        id: uuid::Uuid::now_v7().into(),
        name: name.into(),
    };

    let result: Vec<DbGuid> = diesel::insert_into(schema::users::table)
        .values(to_add)
        .returning(schema::users::id)
        .get_results(db)?;

   match result.len() {
        1 => Ok(result[0].clone()),
        0 => Err(anyhow!("No row id when inserting user {}", name)),
        _ => Err(anyhow!(">1 row when inserting user {}", name)),
    }
}

/// Query, get user struct if one exists or None
fn get_user_id(db: &mut SqliteConnection, name: &str) -> Result<Option<models::User>> {
    let result: Vec<models::User> = schema::users::table
        .filter(schema::users::name.eq(name))
        .select(models::User::as_select())
        .load(db)?;

    match result.len() {
        0 => Ok(None),
        1 => Ok(Some(result[0].clone())),
        _ => Err(anyhow!(">1 user id for name {}", name)),
    }
}

As you can see, you end up cloning a value, but it's not the end of the world. Maybe someone can suggest a way not to in the comments, but it's not the scope of the question. What matters is: this is a reliable way to have a struct be both Insertable and Queryable, with a unique primary key, and without the need to manage keys manually.