Rust Diesel raw SQL gives error "type annotations needed for `std::result::Result<Vec<T>, diesel::result::Error>`"

Question

I am trying out simple raw SQL to MySQL using Diesel as shown in this example:

https://docs.diesel.rs/diesel/fn.sql_query.html

let users = sql_query("SELECT username FROM users").load(&connection);

However this gives me an error message:

error[E0282]: type annotations needed for `std::result::Result<Vec<T>, diesel::result::Error>`
  --> src/main.rs:53:57
   |
53 |     let users = sql_query("SELECT username FROM users").load(&connection);
   |         -----                                           ^^^^ cannot infer type for type parameter `U` declared on the associated function `load`
   |         |
   |         consider giving `users` the explicit type `std::result::Result<Vec<T>, diesel::result::Error>`, where the type parameter `U` is specified

Ps. I need to use raw sql as I need to run dynamic sql.

question from:https://stackoverflow.com/questions/65870030/rust-diesel-raw-sql-gives-error-type-annotations-needed-for-stdresultresul

Answer 1

As far as I have been able to determine, Diesel does not have any capability to perform queries where the 'shape' of the returned rows is not known at compile time. This is one of its strengths (at least for some use cases) because used properly, it will validate interaction with the database at compile time.

However this makes it difficult to use for dynamically generated SQL.

If your queries always generate the same number and types of columns then you can write something like this:

// Define a type to represent the rows returned by your query

#[derive(QueryableByName)]
struct StringColumn {
    #[sql_type = "Text"]
    username: String
}

// Then you can execute your query, telling Rust that you expect
// to get the rows back as the above type
let users:Vec<StringColumn> = diesel::sql_query("SELECT username FROM users").load(&conn)?;

If you are purely using dynamically generated SQL, you might be better off using the mysql crate directly.