Something as fully expressive as SQL is hard to design well. I think jOOQ is one of the best, but it relies on code generation to get good typing. The context

Something as fully expressive as SQL is hard to design well. I think jOOQ is one of the best, but it relies on code generation to get good typing. The context for my generators was querying over data whose schema was customer defined. I wrote two generators in Ruby and one in Java. They were designed to take advantage of quirks and performance characteristics of MySQL when doing arbitrary ad-hoc sorts and filters, and the optimization was mostly about delaying joins if they weren't necessary for the predicates, and creating an inside out nest of subtable queries to control the execution plan. Doing this without a generator of some kind is impossible - the columns are dynamic - and then you leave a lot of performance on the table if you don't tune.