Hi, I'm helping a client improving performance of ...

# server

[stefano]

Hi, I'm helping a client improving performance of their web application and the best option for their workload is ClickHouse. They're using Python and I want to propose switching to Kotlin since we'll have to rewrite most of their code anyway, but to make the proposal complete I need to find a database driver and a SQL library that offers decent support for CH. Since we'll have to write analytical queries I'd use a sql builder library in the fashion of jOOQ rather than a full-fledged ORM like Hibernate. Does anyone have experience/recommendation on such a setup in the Java/Kotlin world?

[stefano]

I'm aware of the usual suspects like exposed, sqldelight, jooq, jdbi and so on, but I'm having a hard time figuring out how well they can integrate with ClickHouse without resorting to writing raw queries. So I'd appreciate if anyone has specific experience with this problem and solutions to share or libraries to avoid

[hfhbd]

How do you write your queries now? Raw sql? If so, I would recommend sqldelight because you don’t need to rewrite your sql code.

[Klitos Kyriacou]

I've used ClickHouse from Kotlin in the past, but used raw SQL. I haven't used SQLDelight yet; it looks interesting but doesn't appear to support CH: https://cashapp.github.io/sqldelight/2.0.1/#supported-dialects-and-platforms

[stefano]

right now the queries are managed through Django ORM in a very inefficient way, and that's why I'm looking for a SQL building library. I don't want to wrestle an ORM to get the query I need but I also would like to have some proper support and type safety when building dynamic filters

[gpopides]

Go with JOOQ, you wont' regret it. One of the best libraries i have used across different ecosystems. And it works without major issues. One issue would be the generation of tables pojos as data classes where nullability of kotlin data classes becomes an issue. We are using java records for this though.

[stefano]

@gpopides jooq would have been my first choice but then I saw this, plus I wasn’t sure if I can add a new dialect myself like in exposed or komapper. What dialect are you using for it?

[hfhbd]

Well, if you need to write a (jOOQ) dialect, you could also write a sqldelight dialect instead.

[gpopides]

ah sorry my bad, missed Click house completely, we are using it with common SQL databases (mysql, postgres) which means jooq is not an option here.

[stefano]

@gpopides ah ok all good

[stefano]

@hfhbd fair point. The only concern/big question mark I have with sqldelight is how do you apply dynamic filters? I.e. WHERE clauses that might or might not be applied to the query? From what I understand there are only workaround solutions but it’s not a use case that’s well supported. Other than that I find the idea of having my queries in a separate file appealing

[hfhbd]

Oh, okay that’s not supported but you can use a view, which is supported by ClickHouse.

[dave08]

There's also #komapper, it doesn't directly support ClickHouse, but if you find a similar dialect, it might not be too hard to make a custom dialect based on it.

[stefano]

@dave08 thanks, that's exactly what I'm playing around with rn. I used the H2 dialect just to try it out and I manage to get a simple query running, so that's promising. Next thing I'm doing is trying to write a custom dialect

[stefano]

The customer actually approved my proposal of switching from python to kotlin (to my suprise and delight), so this is becoming a reality I'll have to deal with

[dave08]

I've used #komapper with nice success in a few of our microservices. Good luck!

[stefano]

thanks, I'm really looking forward to it