Hello. What's the go to for database (specifically...

# ktor

[Jonathan Hollingsworth]

Hello. What's the go to for database (specifically postgresql) connectivity in the community?
I've eyeballed Exposed, but at this stage I want some far lighter weight - ideally just parameterised SQL queries, not an ORM. I also don't really want to duplicate my SQL Schema in classes, ideally.
SQLDelight looks fairly promising, but the PostgreSQL support is still in development, and again it seems to solve a problem I don't have.
I don't have a Java background, so perhaps there is a really basic answer I'm missing because I keep including "Kotlin" in my Google.
My goal would be something like https://knexjs.org/ for Kotlin. A basic query builder that will also let me execute raw SQL with parameterised input.

[Bruno Medeiros]

I'm not aware of any good alternatives for this in the "pure Java" ecosystem, besides using just plain old JDBC connections, prepared statements, etc.
Assuming you considered this, and that you don't want an ORM, what exactly are you looking for?
Maybe you can write some code here explaining how do you want to interact with the database?

[Jonathan Hollingsworth]

It might be that JDBC directly is what I'm looking for. After spending the last hour or so looking at what things like Ktorm and Exposed use under the hood, I learnt about JDBC for the first time!
Perhaps something like this

query.using(DataClass).select("<tablename">).where("colName", "=", "Peter").where("colName2", ">", 100)

Which would return a list of DataClass matching the built query

Or, we could do something like

query.using(DataClass).raw("SELECT * FROM <tablename> where colName=? and colName2 > ?", ["Peter", 100])

[Łukasz Bednarczyk]

jooq

[Jonathan Hollingsworth]

Interesting, that looks like the kind of API I had in mind

[Bruno Medeiros]

jooq is interesting if you're limited to Java, but Exposed is much more readable with similar overhead. you don't have to manage your schema with exposed, you can just write some metadata to describe your tables and write the queries you want.

[Robert Jaros]

You could try spring-data-r2dbc. It's non-blocking and works nice with coroutines.

[Łukasz Bednarczyk]

@Bruno Medeiros yeah, but the exposed isn’t perfect. If you need to use a database function or trigger you can have a sad surprise 😏 but generally I think that the exposed can be the best solution to working with DB in a year or two.

[Bruno Medeiros]

nothing is perfect, I just think Exposed is not that worse than jooq in terms of allowing the programmers to express themselves. readability is much better, though.

[Jonathan Hollingsworth]

My hesitation around Exposed was really how hard it appeared to be to run raw SQL and then hydrate those results back into a collection of data classes.
If we take the Django ORM (in Python) you've got a lot of flexibility there to run arbitrary queries, which means we can call functions, use CTEs, run geospatial queries, etc. I wouldn't expect an ORM to be able to handle that.

[Bruno Medeiros]

if you news that much flexibility, you're probably better off with plain JDBC. Exposed and jooq are libraries to allow safer queries, at the cost of flexibility.

[Matt Sponer]

I don't feel it's either/or, and my preference would be to use JOOQ when possible and fall back to JDBC if there's something it doesn't support. I've done pretty nutty things in JOOQ (like nested queries that calculate standard deviations on a group by clause, then pull out rows with high z-scores) -- and feel it actually came together more clean looking in their syntax than if I had written it as a raw SQL query. Because you can use intermediate variables for sections of the query or various clauses, so the names add self documentation and break it up.

[nschulzke]

Two points:

First, I personally find jOOQ code much more readable than Exposed, because it sticks to the SQL keywords and term order as closely as possible. Exposed's DSL, in contrast, is designed to be more Kotlinesque. There's nothing wrong with the Exposed approach, but for me I'd rather not learn a new DSL when I can transfer my knowledge of SQL directly to jOOQ and get the same benefits.

Second, on flexibility: jOOQ's DSL is extremely rich, and I almost never need to drop into raw SQL. However, if you ever do, it does provide escape hatches that allow you to write raw SQL either as a complete query or as a portion of a larger, otherwise typesafe query[0]. This means that, as @Matt Sponer says above, you don't have to sacrifice any flexibility to use jOOQ. Just fall back into raw SQL at any point in your query if the DSL is lacking.

[0] https://www.jooq.org/doc/3.15/manual/sql-building/plain-sql/

[nschulzke]

(As an aside, because you're using Kotlin, when you do need to fall back on raw SQL you can do so by defining an extension function that further enriches the jOOQ DSL for your use case going forward.)

[Jonathan Hollingsworth]

I know this is small in the schema of things, but the license situation of JOOQ is not ideal. I feel as a community we should be coalescing around MIT available standards.
(also, sorry about the big delay between comments here)

[nschulzke]

Ktor, Exposed, and jOOQ all use the same license, Apache 2.0. jOOQ has a commercial license as well, but the main difference is support for additional enterprise-type databases that you probably aren't using.

https://github.com/ktorio/ktor/blob/main/LICENSE
https://github.com/JetBrains/Exposed/blob/master/LICENSE.txt
https://github.com/jOOQ/jOOQ/blob/main/LICENSE

[Łukasz Bednarczyk]

@nschulzke ok, but jooq offers only support for the last version of a database under Apache 2.0. IMO it’s not safe. On production, you can have so many unpredictable situations that the version can be not enough.

[nschulzke]

The tradeoff may not make sense in your case.

For my projects, here's how I view the tradeoff:

• In practice, the kinds of jOOQ queries I write are using bog-standard SQL features that haven't changed in years. jOOQ thinking I'm using Postgres 13 when I'm actually using Postgres 11 isn't going to make a difference here.
• jOOQ's type safety addresses a concern that is much more likely: that I'll write a query using JDBC that makes an incorrect assumption about the output types and I cause a runtime error.
For myself, I'll take the remote possibility of a weird incompatibility in exchange for strong guarantees that I don't screw something up. If you're on an especially old database version, or on a project where you don't foresee upgrading your DB version for many, many years, your tradeoffs might be different.

[Łukasz Bednarczyk]

@nschulzke ok, I thought that I must update my DB when I update the jOOQ version. If I can working e.g. with Postgres 11 it is cool

[Jonathan Hollingsworth]

Ah, I totally misread the JOOQ pricing page as being free for use in open source projects - but actually, it's free with open source databases.