If I were to write a query builder, which should I look to for inspiration and why?
1) Implementation agnostic queries. For better or worse SQL is a VERY loose standard with tons of little quirks for each underlying DB. Using a query builder lets you write a standardized syntax that then gets translated to match the quirks of the actual SQL implementation for you. This _somewhat_ although not entirely insulates you from the underlying implementations details.
2) Works with existing language tooling. Using the actual language you can take advantage of things like intellisense and syntax highlighting to write code and spot typos. In a more traditional SQL client you'll be embedding your queries inside of Strings and in the worst case constructing ad-hoc queries by appending strings together.
3) Related to point 2, you get compile time checks that your queries are well formed and that your types make sense. This is where the first major problems also come in, but more on that later.
4) Possible compile time optimization of your queries. I'm not aware of any builder that does this, but in theory just like language compilers run optimization passes over the AST generated from your code, you could in theory optimize the query that results from your builders AST (this could possibly even be done at compile time).
And now for the cons.
1) Dependent on the query builder to support every weird quirk and advanced feature, and raises the problem of what to do when a feature is used in a query that isn't supported by the underlying implementation. If you want to use some slightly obscure feature supported by your particular DB but isn't supported by the query builder, you might just be SOL.
2) Compile time headaches due to either generating or validating code. Often times these tools work best when at compile time they can connect to your actual DB to read its schema and either generate code (such as enums of tables and columns) or to validate queries (E.G. checking that a varchar column is being treated as a string and not an int). If you have a conveniently accessible local instance or dev env this might not be a problem, but then you often also need to find a solution for your CICD server. This also says nothing about generated code which is it's own set of headaches.
3) Yet another DSL to learn. You're now no longer writing SQL, but instead something SQL adjacent that has been projected onto another language in a no doubt imperfect fashion. You're now needing to use knowledge of both SQL and your language of choice simultaneously in order to write queries. In theory the compile time checks and language support might make this a wash, but it could become relevant if you run into some weird edge cases and need to debug.
As for things I'd wish for in an ideal query builder, I think it's VERY important to provide options to avoid needing to establish DB connections at compile time, while also still providing the advantages that often provides. Being able to E.G. point the tools at files checked into version control containing your schema DDL and validate queries or generate code based on that would be a great feature to have.
Beyond that providing adequate escape hatches for unusual features when building queries is also important. There should be a way to invoke arbitrary functions or chunks of raw SQL outside of the confines of the query DSL (with the understandable restriction that query checking of such chunks will be minimal at best).
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...