Diesel : how to avoid creating structures for every raw sql_query?

help
1

Hello,

I'm migrating an sqlx project to Diesel/Diesel-async, because Sqlx doesn't handle Oracle databases.

but I've hit a problem with Raw sql queries :confused:

To just do a count(*), we need to create a type, for example, using type i64 does not work :

diesel::sql_query(r#"SELECT COUNT(*) FROM table_xxxx"#)
        .get_result::<i64>(conn);

Error message :

 .get_result::<i64>(conn)
   |          ^^^^^^^^^^ the trait `CompatibleType<i64, Pg>` is not implemented for `Untyped`, which is required by `SqlQuery: diesel_async::methods::LoadQuery<'_, _, i64>`
   |
   = help: the trait `CompatibleType<U, DB>` is implemented for `Untyped`
   = note: required for `SqlQuery` to implement `diesel_async::methods::LoadQuery<'_, _, i64>`

to resolve this problem, we need to create a type to represent the count(*) :

#[derive(QueryableByName)]
pub struct CountType {
    #[diesel(sql_type = diesel::sql_types::BigInt)]
    pub count: i64,
}

diesel::sql_query(r#"SELECT COUNT(*) FROM table_xxxx"#)
        .get_result::<CountType>(conn); // <CountType> instead of <i64>

but if we want to select other fields? aren't we going to create several types for each combination? :thinking:

for example, if we use this query ( dummy query, just to show the idea ) :

SELECT COUNT(*) , 888 as column_b, column_b FROM table_xxxx

we need to create another struct composed of ( count, column_a ,column_b) ?!

I really hope this isn't the case, otherwise I'll have wasted a week migrating for nothing. :pensive:

if anyone knows more about Diesel, please tell me about a simpler solution without having to create Structs for each query. :pray: :pray: :pray:

2

I don't really use Diesel myself but looking at the documentation I found this:

When this method is called on sql_query, the return type can only be a struct which implements QueryableByName

Seeing other examples of get_results I think you can just pass a tuple and it should work, I haven't tried it myself though.

diesel::sql_query(r#"SELECT COUNT(*) FROM table_xxxx"#)
        .get_result::<(i64)>(conn);
1 Like
3

thanks for this suggestion @jonnyso , I had tried with a tuple, but it doesn't work :pensive:

4

A one-element tuple type is written with a required comma: (i64,)

5 Likes
5

The information that you can use a tuple as return type if your query uses sql_query is just wrong. The cited documentation already explicitly states that it's only possible to use structs that derive QueryableByName in that position.

The reasoning for that is that diesel don't know at compile time how many and which fields are returned by the database. This information needs to be provided by the user, therefor you need to use an struct that is explicitly annotated with this information.

The DSL provided by diesel does not have this limitation as it contains the relevant information at compile time. Therefore you can use the typed DSL in combination with tuples.

1 Like
6

How odd, I never seen that before, what does that mean ? Is that equivalent to doing (i64, _) ?

7

No (i64,) mean one element in a tuple and (i64, _) means two element.

2 Likes
8

Just think about it. (i64) can't be a tuple type, because a parenthesized type (and expression) has to be equivalent with its contents in order to be non-surprising. You wouldn't want 2 * (1 + 3) to fail typecheck due to (1 + 3) being a 1-tuple instead of an integer.

3 Likes
9

Thank you for this remark, I totally forgot to add "," for a 1-element tuple.

But even with <(i64,)> it doesn't work, and it's described in the documentation :

@weiznich Is DSL more practical? Less cumbersome code?

I'm a beginner on Diesel, but when I see the DSL generated in schema.rs, it only describes tables, not specific sql query returns.

do you have an example that could inspire me? (that would be very kind of you :pray: )

and what about performance? DSL vs Struct with derive(QueryableByName)?

I have lots of compound queries that select tables and PLSql stored procedures, and even records (like virtual tables).

10

That really depends on your use cases. You can checkout the official diesel guides for some examples on how to use the DSL. The DSL has some restrictions on what is provided out of the box so it might fit your usecase or not. In the later case, there is also the possibility to extend the DSL outside of diesel. That gives you mostly the same level of DSL support as having that extension in diesel itself (See the "Extending Diesel" guide for details).

As for performance: Usually using the DSL yields better performance for the same query as diesel can skip certain steps there based on the at compile time known information.

1 Like
11

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.