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 artist_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.