7

Diesel's SqliteBackend does not implement the SupportsReturningClause trait, so the get_result method cannot be used to retrieve a newly created value.

Is there another way to find out the id of the inserted row? Python has a solution for this. The only solution I've found so far is to use a UUID for ids instead of an autoincrement field.

Shepmaster
  • 388,571
  • 95
  • 1,107
  • 1,366
Maxim Gritsenko
  • 2,396
  • 11
  • 25
  • 1
    This question does not make much sense as it is currently worded, as it is missing nearly all relevant information like what code you've tried and what dependency versions you use. Additionally there is no `SqlBackend` in diesel, therefore it is not really clear what you are trying to do. I vote for closing this question if it's not improved by the OP. – weiznich Jan 04 '21 at 09:21
  • @weiznich yep, missed "lite" in the name of the backend. Fixed in the question. As for the other questions: all the dependencies are the most recent version. I've tried the `get_result` method (as stated in the question) which does not work, and I don't know if there is anything else I can try. That's the reason I posted the question :-) – Maxim Gritsenko Jan 05 '21 at 16:19

2 Answers2

9

The underlying issue here is that SQLite does not support SQL RETURNING clauses which would allow you to return the auto generated id as part of your insert statement.

As the OP only provided a general question I cannot show examples how to implement that using diesel.

There are several ways to workaround this issue. All of them require that you execute a second query.

  1. Order by id and select just the largest id. That's the most direct solution. It shows directly the issues with doing a second query, as there can be a racing insert at any point in time, so that you can get back the wrong id (at least if you don't use transactions).

  2. Use the last_insert_rowid() SQL function to receive the row id of the last inserted column. If not configured otherwise those row id matches your autoincrement primary integer key. On diesel side you can use no_arg_sql_function!() to define the underlying sql function in your crate.

weiznich
  • 2,910
  • 9
  • 16
1

As pointed out in the comments ever since SQLite 3.35 / Diesel 2.0, you can use .get_result() with an SQLite backend as well by using the returning_clauses_for_sqlite_3_35 feature.

Lets say that we have a table called tournaments

CREATE TABLE tournaments (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name VARCHAR NOT NULL
)

and we want to add a new tournament into it, we can get the inserted row with .get_result()

// Schema
// @generated automatically by Diesel CLI.
diesel::table! {
    tournaments (id) {
        id -> Integer,
        name -> Text,
    }
}

// Model
#[derive(Insertable)]
#[diesel(table_name = tournaments)]
pub struct NewTournament<'a> {
    pub name: &'a str
}

fn new_tournament(name: &str, connection: diesel::sqlite::SqliteConnection) -> i32 {
    println!("Adding new tournament \"{name}\"");
    let new_tournament = NewTournament {name: name};
    let result = diesel::insert_into(tournaments::table)
        .values(new_tournament)
        .get_result::<(i32, String)>(&mut connection).unwrap();
    result.0
Tzane
  • 2,752
  • 1
  • 10
  • 21