Rust Diesel sql_query

Question

I have a problem with diesel with SQLite where I can't use sql_query and get the inserted row.

Code

   let conn = &mut get_connection();

    let new_post = NewPost{title,body};
    diesel::insert_into(posts::table)
    .values(new_post)
    .execute(conn);

    // Get the ID of the last inserted row
    let result = diesel::sql_query("SELECT last_insert_rowid()")
    .load(conn);

Error

type annotations needed
cannot infer type of the type parameter `U` declared on the associated function `load`

In the Example there is no Generic U used but i also tried it with :: which has the Error:

the trait bound `Untyped: load_dsl::private::CompatibleType<diesel::sql_types::Integer, _>` is not satisfied
the trait `load_dsl::private::CompatibleType<U, DB>` is implemented for `Untyped`
required for `SqlQuery` to implement `LoadQuery<'_, _, diesel::sql_types::Integer>`rustcClick for full compiler diagnostic
crud.rs(29, 6): required by a bound introduced by this call
mod.rs(1499, 15): required by a bound in `diesel::RunQueryDsl::load`
the trait bound `diesel::sql_types::Integer: FromSqlRow<_, _>` is not satisfied
the following other types implement trait `FromSqlRow<ST, DB>`:
  <(T1, T0) as FromSqlRow<(ST1, Untyped), __DB>>
  <(T1, T2, T0) as FromSqlRow<(ST1, ST2, Untyped), __DB>>
  <(T1, T2, T3, T0) as FromSqlRow<(ST1, ST2, ST3, Untyped), __DB>>
  <(T1, T2, T3, T4, T0) as FromSqlRow<(ST1, ST2, ST3, ST4, Untyped), __DB>>
  <(T1, T2, T3, T4, T5, T0) as FromSqlRow<(ST1, ST2, ST3, ST4, ST5, Untyped), __DB>>
  <(T1, T2, T3, T4, T5, T6, T0) as FromSqlRow<(ST1, ST2, ST3, ST4, ST5, ST6, Untyped), __DB>>
  <(T1, T2, T3, T4, T5, T6, T7, T0) as FromSqlRow<(ST1, ST2, ST3, ST4, ST5, ST6, ST7, Untyped), __DB>>
  <(T1, T2, T3, T4, T5, T6, T7, T8, T0) as FromSqlRow<(ST1, ST2, ST3, ST4, ST5, ST6, ST7, ST8, Untyped), __DB>>
and 23 others
required for `SqlQuery` to implement `LoadQuery<'_, _, diesel::sql_types::Integer>`rustcClick for full compiler diagnostic
crud.rs(29, 6): required by a bound introduced by this call
mod.rs(1499, 15): required by a bound in `diesel::RunQueryDsl::load`

Also for get Result i got the Struct:

Cargo.toml

[dependencies]
diesel = { version = "2.0.0", features = ["sqlite"] }
dotenv = "0.15.0"
libsqlite3-sys = "0.25.2"
mysqlclient-sys = "0.2.5"
serde = "1.0.140"

I also tried:

- get by get_result with the insert which seems not to work with SQLite in this diesel Version.
- table.order(id.desc()).first(&conn) which I can't used cause i got no AUTOINCREMENT id.
- .load::<i32>
- .load::<BigInt>

Artikels I already tried

https://github.com/diesel-rs/diesel/issues/771
How to get the id of a newly-created value with Diesel and SQLite? where by the way no_sql_function seems to be deprecated.
Rust Diesel sql_query Insert example with Returning which isn't the same Problem because i just try to use sqlquery with Integer

Question

Can someone give me a proper Example how to get the inserted Row in SQLite or explain how to solve the Error above

Your error message looks like all you need is to tell the `load` method what type to expect. I think `let result = diesel::sql_query("SELECT last_insert_rowid()").load::(conn);` should work. Then `result` will be a `QueryResult>` where the vector has one element. Read more about `load` here: https://docs.diesel.rs/2.0.x/diesel/prelude/trait.RunQueryDsl.html#method.load — Jonas Fassbender, Feb 21 '23 at 16:08
Hallo, thanks for the quick response! I already tried to fill the generic type which leads to the error below. — Simon, Feb 21 '23 at 16:10

score 0 · Answer 1 · answered Feb 22 '23 at 07:47

So there are several things to write here:

First of all the answer to the actual concrete issue. As already written in a comment below your question: You need to specify the target type for .load() via .load::<TargetType>(). In contrast what was written in this comment, you cannot use i64 there. The documentation of sql_query that the target type needs to implement QueryableByName. That's not the case for i64. You need to use a struct deriving QueryableByName there. That should solve the concrete presented issue.

Now as the OP already mentioned: There are other solutions to make this work. As already linked you could extend the dsl to define your own last_inserted_row. As you noticed: no_arg_sql_function! as deprecated, but the documentation also mentions that you just can use sql_function! instead.

The other variant is to use support for returning clauses. That's there in diesel 2.0, behind a feature flag. As soon as you enable the corresponding feature flag + use at least sqlite 3.35 you should be able to use get_result() on your insert statement.