how can I perform a batch upsert for sqlite?

[SantiagoMendez1]

Hi I am relatively new to rust. I am trying to generate a function that allows me to perform an insert or update in batches, trying to use on_clonfict do_update for sqlite.

I validate the conflict by the id because the id I build it, it is not auto incremental, so that when it identifies that this id already exists but comes in the vector of records it will update it, and if it does not exist it will create it.

this is my implementation:

pub fn synchronize_payment_term(
        conn: &mut SqliteConnection,
        payments: Vec<PaymentTerm>,
    ) -> QueryResult<usize> {
        use crate::models::schema::payment_term::dsl::*;

        let rows_affected = diesel::insert_into(payment_term)
            .values(payments)
            .on_conflict(id)
            .do_update()
            .set((
                name.eq(excluded(name)),
                active.eq(excluded(active)),
                description.eq(excluded(description)),
                code.eq(excluded(code)),
                payment_type.eq(excluded(payment_type)),
                create_date.eq(excluded(create_date)),
                write_date.eq(excluded(write_date)),
            ))
            .execute(conn)?;

        Ok(rows_affected)
    }

but when I do this, I get the following problem

rustc: BatchInsert<Vec<ValuesClause<..., ..., ..., ..., ..., false>is not a valid SQL fragment for theSqlite` backend.

the QueryFragment<Sqlite, sqlite::backend::SqliteBatchInsert> trait is not implemented for BatchInsert<Vec<ValuesClause<..., ..., ..., ..., ..., false>.
usually means that the Sqlite database system does not support this SQL syntax.
this SQL syntax
the following types implement the QueryFragment<DB, SP> trait:
BatchInsert<V, Tab, QId, HAS_STATIC_QUERY_ID>implementsQueryFragment. BatchInsert<Vec<diesel::query_builder::insert_statement::ValuesClause<V, Tab>>, Tab, QId, HAS_STATIC_QUERY_ID>`` implements QueryFragment<DB, PostgresLikeBatchInsertSupport>. required for BatchInsert<Vec<ValuesClause<..., ..., ..., ..., ..., false>to implementQueryFragment.

currently sqlite does not support to perform the upsert action in batches? any idea how I could do this? for performance reasons I would not want to iterate over the vector to do it one by one.

Versions

• Rust 1.84.0
• Diesel 2.2.6
• SO Ubuntu 24.04
• Sqlite 3.45.1

[weiznich]

You need to add a #[diesel(treat_none_as_default_value = false)] attribute to you Payment struct to make this work. The documentation has some more details on this, but is arguably pretty vague on that particular topic. We would welcome a PR that clarifies this there and wherever you would expected that info to be present.

[SantiagoMendez1]

thank you for your answer, when adding the attribute #[diesel(treat_none_as_default_value = false)] to the structure, i continue observing the same problem.

Next I add how I have my structure. In case I am implementing the upsert in a wrong way for a batch of data, I add any resource to be able to implement with sqlite the mentioned.

#[derive(Debug, Serialize, Deserialize, Clone, Queryable, Selectable, Insertable, AsChangeset)] 
#[diesel(table_name = super::schema::payment_term)]
#[diesel(check_for_backend(diesel::sqlite::Sqlite))]
#[diesel(treat_none_as_default_value = false)]

  pub struct PaymentTerm {
      pub id: i32,
      pub name: String,
      pub active: Option<bool>,
      pub description: Option<String>,
      pub code: Option<String>,
      pub payment_type: Option<String>,
      pub create_date: NaiveDateTime,
      pub write_date: Option<NaiveDateTime>,
  }

When trying to do an upsert of a single element of type PaymentTerm I have no problem, it is when doing it in batch, that is to say a Vec<PaymentTerm>. If there is something wrong, I thank you for your understanding.

see all error

error[E0271]: type mismatch resolving `<Sqlite as SqlDialect>::InsertWithDefaultKeyword == IsoSqlDefaultKeyword`
    --> src/services/party.rs:102:22
     |
102  |             .execute(conn)?;
     |              ------- ^^^^ expected `IsoSqlDefaultKeyword`, found `DoesNotSupportDefaultKeyword`
     |              |
     |              required by a bound introduced by this call
     |
     = note: required for `BatchInsert<Vec<ValuesClause<..., ...>>, ..., ..., false>` to implement `CanInsertInSingleQuery<Sqlite>`
     = note: 1 redundant requirement hidden
     = note: required for `OnConflictValues<BatchInsert<Vec<...>, ..., ..., false>, ..., ...>` to implement `CanInsertInSingleQuery<Sqlite>`
     = note: required for `InsertStatement<table, OnConflictValues<..., ..., ...>>` to implement `QueryFragment<Sqlite>`
     = note: required for `InsertStatement<table, OnConflictValues<..., ..., ...>>` to implement `ExecuteDsl<diesel::SqliteConnection, Sqlite>`
note: required by a bound in `diesel::RunQueryDsl::execute`
    --> /home/santiago/.cargo/registry/src/index.crates.io-6f17d22bba15001f/diesel-2.2.6/src/query_dsl/mod.rs:1431:15
     |
1428 |     fn execute(self, conn: &mut Conn) -> QueryResult<usize>
     |        ------- required by a bound in this associated function
...
1431 |         Self: methods::ExecuteDsl<Conn>,
     |               ^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `RunQueryDsl::execute`
     = note: the full name for the type has been written to '/home/santiago/presik/develop/neu-pos/src-tauri/target/debug/deps/neu_pos_lib.long-type-5084191621331714313.txt'
     = note: consider using `--verbose` to print the full type name to the console

[weiznich]

Turns out there was a missing impl for this to work. I've opened #4546 to fix that.

[SantiagoMendez1]

Thanksss Georg!!!

I added to the diesel dependency in my cargo.toml the reference to the commit, I tested it and everything seems to work fine. Waiting for the next version to use this feature in my project.

[weiznich]

Just as reference: It's totally fine to use such fixes via cargo's [patch.crates-io] functionality until the fix is released. That's what this cargo feature is for.