lukaseder

Informix also supports MULTISET natively. Many others support ARRAY, which is equivalent for all practical purposes. jOOQ popularised MULTISET over ARRAY because the existing ARRAY support was less user friendly, mapping results to actual Java array types.

ISO/IEC 9075-2:2023(E) 10.9 <aggregate function>:

<array aggregate function> ::= ARRAY_AGG <left paren> <value expression> [ ORDER BY <sort specification list> ] <right paren>

I hope this helps

You get that in Kotlin. Or r.getId(), r.getFirstName(), etc. in Java. So what's the issue here?

jOOQ is also a great option if you do like stored procedures

Not sure what you mean. I meant that without declaration site variance, ("pragmatic") unsafe casting is everywhere in jOOQ's internals. Without being able to capture wildcards in local type variables, ("pragmatic") rawtypes are everywhere as well (check Collections.swap() for an illustration)

jOOQ's internals are full of unsafe casts, though.

jOOQ doesn't get involved in any such prefetch/eager fetch shenanigans, which are often wrong. They work for some cases and do too little/too much for others. So, specifying the exact data to fetch in every query is a much better approach, ideally with nested collections: https://blog.jooq.org/jooq-3-15s-new-multiset-operator-will-...

Everyone has a different set of "serious" flaws to share.

> (with the possible exception of JOOQ, whose open source version is unfortunately also very limited)

How is it "very" limited?

> Not sure how successful jOOQ has been financially, but considering they've been around for many years at this point, I have to imagine it's worked out well enough to pay for the lights and kibble?

It pays roughly EUR 400k ARR

I don't know if your various flatMap / etc methods are purely implemented in the client (it would be quite bad from a performance perspective? But since you're implementing reducers in kotlin, I guess that's what this is), or if you somehow translate the AST to SQL (similar to jinq.org in Java or Slick in Scala or LINQ in .NET).

But in either case, I think that mimicking "idiomatic" client APIs is more of a distraction than something useful. I've explored this here, where I was asked about my opinion on Kotlin's Exposed: https://www.youtube.com/watch?v=6Ji9yKnZ3D8

Obviously, this is ultimately a matter of taste, but just like all these "better SQL languages" (e.g. PRQL) come and go, these translations to "better APIs" also come and go. SQL is the only thing to stay (has been for more than 50 years now!)

> We could add parallelism with a .parallelStream() in the right spot easily enough

You typically don't even need to hint it, the optimiser might choose to parallelise on its own, or not, depending on production load... Anyway, that's an overrated topic, IMO.

> I think you probably overestimate how easy SQL is because you're an expert in it.

I'm happy when coding in any language / paradigm. When working with XML, I will happily use XSLT, XPath, etc. When working with JSON, I don't mind going into JavaScript. I'm just trying to stay curious.

I really don't think that SQL is "harder" than any other language. It may just be something certain people don't really like, for various reasons.

> Your whole product is built on the fact that SQL isn't good enough

I think you're projecting your own distaste into my work here. I love SQL. SQL is wonderful. It's quirky, yes, but what isn't. jOOQ users just don't like working with an external DSL within Java (though many are very happy to write views and stored procedures with SQL and procedural extensions). There's no need for a false dichotomy here. I've worked on large systems that were mostly implemented with SQL written in views, and it was perfect!

Also, jOOQ is much more than just the DSL. SQL transformation, parsing, etc., it's a vast product. The string-y SQL folks could use it as a template engine, without touching the DSL, and still profit from parsing / transformations / mapping: https://blog.jooq.org/using-java-13-text-blocks-for-plain-sq...

Some customers use jOOQ merely to migrate off Oracle to PostgreSQL (via the translating JDBC proxy).

And I'm looking forward to the OpenJDK's Project Babylon. Perhaps we'll get actual macros in Java, soon, which could work well with jOOQ.

Anyway, I didn't mean to hi-jack too much. It's great when people try out new / different approaches. I'm just triggered whenever someone claims that SQL is harder than anything else, when they should have said, they prefer other things and don't want to learn more SQL (which is fine, but quite a different statement).

> This may be more intuitive for some developers, especially those that don't use SQL all the time.

I tend to recommend my famous talk to such developers: https://www.youtube.com/watch?v=wTPGW1PNy_Y

What, you can't just say that and not link to the issue!

The "object oriented" in jOOQ Object Oriented Querying stands for an object oriented query model, not mapping to object oriented target data structures.

Regarding 4) among the pros, jOOQ does this (opt in): https://www.jooq.org/doc/dev/manual/sql-building/queryparts/....

It's being done at runtime, thus probably not to be done in production, but during your integration tests as part of the diagnostics feature set: https://www.jooq.org/doc/dev/manual/sql-building/dsl-context...

Regarding 2) among the cons: The recommended approach is to use testcontainers: https://blog.jooq.org/using-testcontainers-to-generate-jooq-...

Reverse engineering a SQL script is also possible, though limiting in terms of what syntax is available in DDL: https://www.jooq.org/doc/dev/manual/code-generation/codegen-...

If your cons are too much to handle, then you can still revert to using native SQL and profit from 1) and 4) by using jOOQ's parser: https://www.jooq.org/doc/dev/manual/sql-execution/parsing-co...

Escape hatches are simple, too: https://www.jooq.org/doc/dev/manual/sql-building/plain-sql-t...

All of them

SQL is powerful. A DSL that "fixes" things in this area getting all the other language feature interactions right isn't trivial, all the while users have to learn yet another language. Take PRQL for example: https://prql-lang.org. It looks nice, but the examples are very basic. What about window functions, grouping sets, lateral, DML, recursive SQL, pattern matching, pivot/unpivot etc. Might be doable, but perhaps, they've already made a decision that won't enable one of those features without adding new kludges.

Besides, every single "fix" will be a proprietary solution, while SQL is an ISO/IEC standard that's here to stay and universally adopted.

> A good DSL can do much better.

Stonebraker's QUEL was "better", before SQL, and yet, where is QUEL today?

> There are tradeoffs everywhere though, so with Jooq you still aren't 1-to-1 with raw SQL

You're probably hinting at writing derived tables / CTEs? jOOQ will never keep you from writing views and table valued functions, though. It encourages you do so! Those objects play very well with code generation, and you can keep jOOQ for the dynamic parts, views/functions for the static parts.

Then don't leave

Why would it do that? It's a library for type safe, dynamic SQL building in Java...

I made https://www.jooq.org. Then hired myself in the company I created to maintain jOOQ

>Totally agree. Lukas Eder has some nice presentations about it.

Thanks for the shout out! For the record, that's probably the referenced talk: https://www.youtube.com/watch?v=wTPGW1PNy_Y

Fancy adding the jOOQ parser to your list of Java parsers? https://www.jooq.org/doc/latest/manual/sql-building/sql-pars...

It's probably not advertised enough, but jOOQ also contains a parser that can translate between dialects, and let users work with the expression tree to achieve custom SQL translations. A demo here: https://www.jooq.org/translate

How about charging money for work?

In Java, you don't build SQL queries by gluing strings together. You use https://www.jooq.org, of course.

> Your "parameterized" queries are still serialized strings. Many language bindings for SQL interfaces don't have good support for serializing data types, so you will often see things like ?::datetime in the template string.

How else would you do it, short of a binary query format?

Like any toString() implementation, it is meant for debugging. If the object you're calling toString() on is "attachable" (e.g. a Query), then you get the vendor specific string for additional convenience. If you just call substring(a, b, c).toString(), you'll get a generic rendering.

If you always want the vendor specific SQL string, use DSLContext.render(QueryPart)

Why not just create views and query those with jOOQ, then?

Cheers! :)