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.

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?

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])

jooq

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

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.

You could try

spring-data-r2dbc

. It's non-blocking and works nice with coroutines.

@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.

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.

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.

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.

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.

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/

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)

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

@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.

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.

@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

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.