Integration with diesel 2

[Sytten]

Hello!
We currently use diesel for most of our queries, but we are having trouble with some advanced ones where we need conditional joins. We are thinking of using sea-query for that but we still want to use diesel for the rest for now. I am going to build the driver for diesel 2 (because diesel 1.x doesnt have boxed raw queries) and I was also thinking of building a macro to generate the Iden enums from the schema.
Do you think it would worth creating a PR to integrate that in the main repo?
Thanks

[tyt2y3]

Two things:

1. you do not need to generate enums, you can reuse the diesel column structs and derive the Iden trait for them. However I am not aware of a macro-free way to do so.

2. you can directly use the underlying driver's interface so you don't need to wrap atop Diesel; it'd not be too different from how we are supporting the postgres driver (just a thin wrapper)

Btw, I am never aware of Diesel 2.x, how is it different from 1.x?

Finally, yes if we've figured those things, we could integrate that into SeaQuery

[Sytten]

1. Good to know, but yeah since it needs a macro I think it would be easier to just create a new enum. Does working with a struct helps for typing or it is purely for convenience?
2. Unsure how I would do that, it thought the diesel interface was needed to do the mapping from row to struct.

It changed quite a lot internally, but the main change needed for this feature is the return of the bind function on raw sql can now be boxed so it can run in a loop. In v1 it returns a new type of object so you cant easily loop on it.

[tyt2y3]

SeaQuery only requires that a type implements Iden so it can be either struct or enum. I prefer using enum, but struct is fine too. So the advantage is to reuse Diesel's types to reduce the total number of symbols generated.

The crates Diesel use are pq-sys and mysqlclient-sys, but if you want to automatically convert them into the structs, then using Diesel should be slightly easier.

[Sytten]

I got a working version of the driver for common cases in caido@bd0f734

My only problem is with the JSON, Chrono, etc extensions. I realized that I can't use the cfg inside the macro for that, but I don't have a better idea. I can't do like the sqlx driver since the method when not using the feature returns a bool ref which diesel refuses to take as input to bind.

The need for a macro at all is a bit weird, we could just add diesel as a dependency for the feature and save us a lot of problems.

[tyt2y3]

Well, that really seems cool! I never imagined Diesel could be used as a driver.
The problem was discussed in
#15 (comment)
I am not sure if Diesel can be required without any drivers (likely not).
If so, the driver support can be done like postgres which is nicer.

[Sytten]

Yeah I saw the postgres driver, but I don't think diesel has separate types but if we import the core without any feature it wont pull any driver code. Would that be ok? I am not sure how to do it properly since diesel v2 is not released yet though (probably not for a few months still).

Otherwise I think the best we can do is to document the features that need to be created in the host to enable the support for those extensions.

What is the reasoning for not allowing dependencies on the target inside the sea-query under a feature? I feel that if the ORM doesn't want to have that dependency (because it doesnt need it or what not) then it can just not use the feature and thats it?

[tyt2y3]

Hi Emile, just to check in. Have you made any progress in this matter? It's also fine if you have changed your mind.

[Sytten]

I was able to make it work in our project because we have a diesel dep, but not in the framework as a macro. Also it uses an older version and I have not updated it to support more recent breaking changes.

[tyt2y3]

Awesome, nice to know!

[Sytten]

Updated for 0.21, missing the feature flags for json/datetime/array but it is the base

use diesel::query_builder::{BoxedSqlQuery, SqlQuery};
use diesel::sql_types;
use diesel::sqlite::Sqlite;
use sea_query::{Value, Values};

pub trait Bindable<Inner> {
    fn bind_values(self, params: &Values) -> BoxedSqlQuery<Sqlite, SqlQuery<Inner>>;
}

impl<Inner> Bindable<Inner> for SqlQuery<Inner> {
    fn bind_values(self, params: &Values) -> BoxedSqlQuery<Sqlite, Self> {
        let mut query = self.into_boxed();
        for value in params.0.iter() {
            query =
                match value {
                    Value::Bool(v) => query.bind::<sql_types::Nullable<sql_types::Bool>, _>(v),
                    Value::TinyInt(v) => query
                        .bind::<sql_types::Nullable<sql_types::SmallInt>, _>(v.map(|v| v as i16)),
                    Value::SmallInt(v) => {
                        query.bind::<sql_types::Nullable<sql_types::SmallInt>, _>(v)
                    }
                    Value::Int(v) => query.bind::<sql_types::Nullable<sql_types::Integer>, _>(v),
                    Value::BigInt(v) => query.bind::<sql_types::Nullable<sql_types::BigInt>, _>(v),
                    Value::TinyUnsigned(v) => query
                        .bind::<sql_types::Nullable<sql_types::SmallInt>, _>(v.map(|v| v as i16)),
                    Value::SmallUnsigned(v) => query
                        .bind::<sql_types::Nullable<sql_types::SmallInt>, _>(v.map(|v| v as i16)),
                    Value::Unsigned(v) => query
                        .bind::<sql_types::Nullable<sql_types::Integer>, _>(v.map(|v| v as i32)),
                    Value::BigUnsigned(v) => {
                        query.bind::<sql_types::Nullable<sql_types::BigInt>, _>(v.map(|v| v as i64))
                    }
                    Value::Float(v) => query.bind::<sql_types::Nullable<sql_types::Float>, _>(v),
                    Value::Double(v) => query.bind::<sql_types::Nullable<sql_types::Double>, _>(v),
                    Value::String(v) => query.bind::<sql_types::Nullable<sql_types::Text>, _>(
                        v.as_ref().map(|v| v.as_str()),
                    ),
                    Value::Bytes(v) => query.bind::<sql_types::Nullable<sql_types::Blob>, _>(
                        v.as_ref().map(|v| v.as_ref()),
                    ),
                };
        }
        query
    }
}

[weiznich]

To just leave a comment here: I think such a potential integration can be done at a lower level, without needing to go through diesel::query_builder::SqlQuery/BoxedSqlQuery. I mostly leave the comment here, because I do see a good arguments for having a more dynamic dsl than that was diesel provides. Now is diesel much more than the DSL, as it also provides quite generic connection implementations and a flexible serialization/deserialization framework, so it might be useful for others to just use those foundational layers to provide other dsl variants on top. Especially as this would make it really easy to mix and match different dsl styles in a common application then.

Long story short: In the end diesel will accept anything that implements the correct traits.
The fundamental loading primitive in diesel (for using the diesel provided type mapping) is LoadQuery::internal_load. Now that signature is a bit complicated due to many involved traits, but in the end it boils down the the following things to implement for a fully custom query node:

• QueryFragment which describes how to transform a type into an sql query.
• QueryId which controls whether a prepared statement for the query should be cached or not
• RunQueryDsl just to enable the corresponding loading methods (that does not need to implement any function, just use the default methods)
• Query which describes whats returned by a query at compile time. For diesel that's either some tuple of types from diesel::sql_types (or other sql_types) or diesel::expression::expression_types::Untyped which just says that this type is not known at compile time and the user need to use QueryableByName to receive fields by name. Using Untyped could be a good starting point. By providing a custom type you could also customize the deserialization behaviour, but that's another topic.

[Sytten]

Hi @weiznich good to see you here!
I will definitely take a look at those links thanks for the initial direction.
I would say Diesel code is a lot harder to understand for my current level than SeaQuery but I can definitely see a better integration with diesel that would allow people to use diesel when it makes sense and sea-query when they need more flexibility. On that note, it would interesting to check if we could reuse the diesel schema to general the Iden used by sea-query.
Will report back when I have something.

[tyt2y3]

Thank you for the information !

[weiznich]

On that note, it would interesting to check if we could reuse the diesel schema to general the Iden used by sea-query.
Will report back when I have something

From a quick look at the sea-query documentation I would say that shouldn't be that hard. For a first quick try you can likely just manually implement that trait in your crate, as all types generated by diesels table! macro are local to your crate. For a more generic solution you likely would use a macro, maybe just a module wide proc macro that parses table! calls and generates the corresponding impls on the fly.

[weiznich]

I just want to leave another comment here to notice all interested persons that diesel 2.0 is finally released. This means work on such an integration has now a stable basis. For anyone interested in developing an integration between diesel and sea-query: Please reach out, I can provide pointers where to start and how to structure such an integration.

[Sytten]

I might give it another try a bit later this year. We are on the process of rewriting the way parameterized values are handled in sea-query so before this is done I think the integration should wait. I have been using the Bindable trait I wrote in production for almost a year now and it works correctly but its not perfect.

One thing I want to tackle too is the generation of the Iden of sea-query. Ideally doing it at the same time as the diesel schema via macros.

[Sytten]

I am reviving this post since I plan on working on it. I read the code of the traits required for sea-query to avoid using the SqlQuery object, that would require us to implement the traits in the sea-query crate and not a children one like we currently do for other integrations.
Otherwise we can wrap the sea-query struct in another struct, but that would essentially be a replica of SqlQuery (though we can wrap it in another struct if we want to change that in the future).
Or we can replicate the interface that diesel uses to make it more seamless.

In all cases I think we can infer the query builder from the diesel connection backend with some trait implementation magic, correct me if I am wrong @weiznich.

Essentially:

Query::select()
                    .columns(Requests::Id)
                    .from(Requests::Table)
                    .compile() // Trait extension on SelectStatement, returns a DieselSelectStatement
                    .get_results::<Request>(conn) // Diesel trait

VS:

Query::select()
                    .columns(Requests::Id)
                    .from(Requests::Table)
                    .get_results::<Request>(conn) // Trait extension that calls internally the diesel function

Thoughts @tyt2y3, @ikrivosheev, @billy1624?

For the Iden generation from the diesel schema file, I am not sure how to do it so I will have to look into it a bit more before I can propose something.

[weiznich]

You can get the correct query builder type via <<ConnectionType as Connection>::Backend as Backend>::QueryBuilder. The query builder types provided by diesel itself do just implement Default. That written: I continue to think it's a better idea to implement QueryFragment for some/all of the sea-query types (or a new type wrapper).

As for the Iden generation: The table! macro passes through any attribute that you apply to a column/table. So if you do something like

table! {
    #[derive(Iden)]
    users {
         id -> Integer,
     }
}

it will put the #[derive(Iden)] onto the zero sized table struct. (I don't know whether #[derive(Iden)] can handle such types.)

[Sytten]

I will check that. Yes I agree I will implement QueryFragment on the sea-query SelectStatement and others like it. It would be nicer to implement in lower level but that can't easily be done in a child crate.

Iden is usually on an enum, I will likely have to create another macro but its fine. I imagine I can derive the Iden trait on each column of the table struct.