Rust Diesel: SQLite INSERT RETURNING multiple ids

Question

I'm trying to implement a rather trivial workflow using latest SQLite: insert a row with an empty ID column so that it would be auto-generated, and return those auto-generated IDs back from the INSERT statement. Since SQLite 3.35+ supports RETURNING clause and Diesel does so too through returning_clauses_for_sqlite_3_35 feature, that should be possible.

So I'm trying this:

Cargo.toml

[dependencies]
diesel = { version = "2.1.3", features = ["sqlite", "returning_clauses_for_sqlite_3_35"] }

main.rs

use diesel::prelude::*;
use diesel::sql_query;

table! {
    test_table (internal_id) {
        internal_id -> BigInt,
        content -> Text,
    }
}

#[derive(Debug, PartialEq, Clone, Identifiable, Selectable, Queryable, Insertable)]
#[diesel(primary_key(internal_id))]
#[diesel(table_name = test_table)]
#[diesel(check_for_backend(diesel::sqlite::Sqlite))]
pub struct TestTableRow {
    #[diesel(deserialize_as = i64)]
    pub internal_id: Option<i64>,
    pub content: String,
}

fn main() {
    let rows = vec![
        TestTableRow { internal_id: None, content: "Hello!".to_owned() },
        TestTableRow { internal_id: None, content: "World!".to_owned() },
    ];

    let mut conn = SqliteConnection::establish(":memory:").unwrap();
    sql_query(r"
        CREATE TABLE test_table(
            internal_id INTEGER PRIMARY KEY AUTOINCREMENT,
            content     TEXT NOT NULL
        ) STRICT;
    ").execute(&mut conn).unwrap();
    let internal_ids: Vec<i64> = diesel::insert_into(test_table::table)
        .values(&rows)
        .returning(test_table::columns::internal_id)
        .get_results(&mut conn)
        .unwrap();

    assert_eq!(internal_ids, vec![1, 2]);
}

However, this does not typecheck:

error[E0277]: the trait bound `BatchInsert<Vec<diesel::query_builder::insert_statement::ValuesClause<(DefaultableColumnInsertValue<ColumnInsertValue<columns::internal_id, expression::bound::Bound<diesel::sql_types::BigInt, &i64>>>, DefaultableColumnInsertValue<ColumnInsertValue<columns::content, expression::bound::Bound<diesel::sql_types::Text, &String>>>), test_table::table>>, test_table::table, (), false>: QueryFragment<Sqlite, sqlite::backend::SqliteBatchInsert>` is not satisfied
    --> src/main.rs:37:22
     |
37   |         .get_results(&mut conn)
     |          ----------- ^^^^^^^^^ the trait `QueryFragment<Sqlite, sqlite::backend::SqliteBatchInsert>` is not implemented for `BatchInsert<Vec<diesel::query_builder::insert_statement::ValuesClause<(DefaultableColumnInsertValue<ColumnInsertValue<columns::internal_id, expression::bound::Bound<diesel::sql_types::BigInt, &i64>>>, DefaultableColumnInsertValue<ColumnInsertValue<columns::content, expression::bound::Bound<diesel::sql_types::Text, &String>>>), test_table::table>>, test_table::table, (), false>`
     |          |
     |          required by a bound introduced by this call
     |
     = help: the following other types implement trait `QueryFragment<DB, SP>`:
               <BatchInsert<Vec<diesel::query_builder::insert_statement::ValuesClause<V, Tab>>, Tab, QId, HAS_STATIC_QUERY_ID> as QueryFragment<DB, PostgresLikeBatchInsertSupport>>
               <BatchInsert<V, Tab, QId, HAS_STATIC_QUERY_ID> as QueryFragment<DB>>
     = note: required for `BatchInsert<Vec<ValuesClause<(DefaultableColumnInsertValue<...>, ...), ...>>, ..., ..., false>` to implement `QueryFragment<Sqlite>`
     = note: the full type name has been written to '/Users/fs/code/rust-diesel-issue/target/debug/deps/rust_diesel_issue-c1c37efb3df7ba71.long-type-1487086329238728488.txt'
     = note: 1 redundant requirement hidden
     = note: required for `InsertStatement<table, BatchInsert<Vec<ValuesClause<(..., ...), ...>>, ..., ..., false>, ..., ...>` to implement `QueryFragment<Sqlite>`
     = note: the full type name has been written to '/Users/fs/code/rust-diesel-issue/target/debug/deps/rust_diesel_issue-c1c37efb3df7ba71.long-type-8243152461613863375.txt'
     = note: required for `InsertStatement<table, BatchInsert<Vec<ValuesClause<(..., ...), ...>>, ..., ..., false>, ..., ...>` to implement `LoadQuery<'_, diesel::SqliteConnection, _>`
     = note: the full type name has been written to '/Users/fs/code/rust-diesel-issue/target/debug/deps/rust_diesel_issue-c1c37efb3df7ba71.long-type-8243152461613863375.txt'
note: required by a bound in `get_results`
    --> /Users/fs/.cargo/registry/src/index.crates.io-6f17d22bba15001f/diesel-2.1.4/src/query_dsl/mod.rs:1739:15
     |
1737 |     fn get_results<'query, U>(self, conn: &mut Conn) -> QueryResult<Vec<U>>
     |        ----------- required by a bound in this associated function
1738 |     where
1739 |         Self: LoadQuery<'query, Conn, U>,
     |               ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `RunQueryDsl::get_results`

At the same time, it does compile - and work! - when inserting just one row:

    let internal_ids: Vec<i64> = diesel::insert_into(test_table::table)
        .values(&rows[0])                               // <--- Here!
        .returning(test_table::columns::internal_id)
        .get_results(&mut conn)
        .unwrap();

What do I do wrong?

Answer 1

This is an unfortunate edge case in how diesel handles insert statements. Notably diesel supports inserting a default value if a None value is present in your struct. On postgresql diesel uses the DEFAULT value keyword for that. Sqlite does not support this keyword, therefore diesel needs to emulate it. That's done by just skipping the relevant row if the value is None, but that only works for a single row. That's the reason why your single insert query works, while the batch insert query fails to compile. Now there is even another thing here: .execute(&conn) (i.e. not returning the id) works for the batch insert as well. That's because diesel emulates batch inserts there by just using inserting all elements one by one internally. That is unfortunately not possible for returning variant as that runs into conflicting trait implementations in diesels internal trait setup.

Arguably that's all not documented well in the diesel documentation, so it would be certainly great to submit a PR that improves the relevant docs.