(1) JooQ shines for query generation. For instance at work we have a search engine that can search maybe 50 fields that are mostly materialized but involve the occasional subquery. Also you can easily write a template method like

   transitiveQuery(selectClause,relationshipGenerator,where1,...)

and have type inference do the right thing in the compiler and IDE and all of that. You get "hygenic macros", you can write an Java class which is parameterized by one or more related tables which can be specialized by adding more parameters, subclassing, etc.

(2) Circa 2005 coding PHP I came to the conclusion that the ORM I needed was

   insert(table,{col1: val1, col2:val2, ...})

because writing simple inserts and updates against SQL is for birds, let freshers do it and they will forget to escape something. Such a "framework" can be so simple that you can bend it to the needs of your application. JooQ gives you something like that but backed by the Java type system.

I do get it (I think!) - but there is a world of difference between “I have 20 years SQL experience and do not want to spend hours maintaining 200 SQL templates, and believe the overhead of this DSL is worth the trade off” vs “use the DSL and you won’t have to teach junior Devs SQL!”

My comment is more aimed at the second part. SQL Is tied to the implementation and demands coders understand it all. A DSL can allow domain experts to express their understanding without having to worry about software trade offs.

The most successful “DSL” I know of like this is fitnesse tests - just a large number of simple tests where domain experts can spreadsheet style throw in the “gotchas”.

Something like that but more spool is fixated is a holy grail - Behaviour driven tests like cucumber come close but there is that weird intermediate translation from English phrase to random function - now you have to understand the function to use the phrase and suddenly you are reading real Code to be able to use the fake code and it never feels clean

One day I will be clever enough to be able to write a really good test DSL

It’s just whenever I think of “Given used is logged in, visit “textbox” and enter “word” .. it just looks like BDD test not a DSL. Like I said, one day I will be clever enough

Sure. Note though that there's a long tradition of systems for embedding SQL in conventional programming languages such as

http://infolab.stanford.edu/~junyang/cs145/or-proc.html

which for whatever reasons never caught on in the open source world. (I'd blame limitations of current compiler technologies and the values of people who make compilers... If we had composable parsers you could just say "here's a spot for a SQL query in a Java method" in 10 lines of code) JooQ approaches that without requiring any change in the compiler. In the past it was awkward to embed SQL in Java because there were no multi-line strings. In Python you could write

do_query(" ... a really crazy complicated queries with lots of joins and subqueries that is carefully indented to fit in with the rest of the program ... ",{"arg1": val1, "arg2": val2})

but without real map literals, multi-line strings and such this was terribly awkward. (If you think List.of(), Map.of() and such are cool I was writing a computer chess program last month that used List.of(A,B) to create a list that was used in an inner loop and it was terrifying how slow it was compared to using an ArrayList)

I'm not familiar with jooq, but I've used Ecto a ton, and the point was never to avoid learning SQL. It's about making queries composable and mapping to domain objects automatically so eg. there aren't dozens of queries to update when you add a field to a domain object.