[steve_barham]

I don't like this. I'm strongly of the opinion that SQL is a beautiful language, able to express in terse yet readable statements operations which would take many pages of Java to execute. Why try and wrap it up, and pretend that you are writing Java, when you're still writing SQL?

Please don't take this as a dig at you or your code - everyone has an itch to scratch, and I'm quite sure writing this was fun.

[ocharles]

SQL as a string is not composable. I cannot give you a small query and have you arbitrarily join against it - I have to give you bits of strings and you have to glue them together in the right way. Expressing SQL as its AST lets you write combinators between ASTs, and thus get at least some composability. The stronger your type system, the better guarantees you can get that the composition is sound (closure).

[masklinn]

> Why try and wrap it up, and pretend that you are writing Java, when you're still writing SQL?

That's not what query builders (/expression languages) do. They just express the SQL in the host language to allow for compositions and the like.

Because like it or not, expressing SQL as a string is a pain in the ass to compose, you can't easily build a query incrementally.

With a query builder, you can. You're still writing SQL, it's not an object layer/ORM and it has (should have) a fairly direct mapping to SQL. You're just writing SQL in a way which fits better in the host languages.

Oh, it can also provide some measure of database-independence by having database-specific backend take care to handle specific incompatibilities between databases.

For instance for string concatenation[0] the standard is `||` which works in SQLite, Postgres and Oracle, MSSQL uses `+` instead of `||` and in MySQL `||` is the boolean OR unless you've activated a specific sql mode so you've got no choice but to use CONCAT (you could accept a performance hit and use CONCAT everywhere, except Oracle only supports an arity of 2 and SQLite doesn't support this function at all).

That's the kind of stuff a builder can handle for you under the hood, providing much improved cross-platform compatibility.

[0] http://troels.arvin.dk/db/rdbms/#functions-concat

[lukaseder]

SQL is a beautiful language, indeed. Writing vendor-specific SQL in stored procedures and views is great. But embedding SQL in other languages such as Java is still very hard in 2013.

jOOQ is the first and only API to fully embed the SQL language as an internal domain-specific language in Java, using an internal BNF for API definition: http://blog.jooq.org/2012/01/19/jooqs-fluent-api-in-bnf-nota...

Being a DSL with an AST, advanced SQL transformation can be implemented to standardise SQL where common SQL constructs aren't supported. These include:

- DISTINCT predicate: http://blog.jooq.org/2012/09/21/the-is-distinct-from-predica...

- Row value expression predicates: http://blog.jooq.org/2013/05/03/sql-query-transformation-fun...

- Derived column lists: http://blog.jooq.org/2013/01/07/simulating-the-sql-standard

Both vendor-specific SQL and embedded SQL have their merits.

[jhh]

One advantage is that the compiler checks your queries. In case of the jOOQ library, it also makes your queries typesafe and abstracts away some specifics of SQL dialects.

Having typesafety transcend from the database to your "main" language is of advantage so long as you are using a statically typed language anyway.

All this is possible while maintaining much of the expressive power of SQL.

[steve_barham]

I'm with you on the type checking (although this particular library doesn't seem to offer much in that space). That said, I'd argue that modern tooling (e.g. IDEA, and by association the static analysis toolchain in Teamcity) are capable of identifying and validating embedded SQL, particularly when provided with database metadata.

Essentially, I'm deeply uneasy about embedding translators from one language into another. As an example, not many people like using BigDecimal to describe arithmetic operations, despite it having a fluent syntax and improved type safety (e.g. around implicit conversions) compared to just writing an expression.

[ivanceras]

Manipulating string in java/IDE is a bit odd. I wrote the code in order to get away with the awkward strings manipulation in java. Plus I have this generator tools which generates list of schema-tables and table-columns combinations. All of my SQL code is check at dev time by the power of IDE static analyzer. So I don't have to deal with misspelled columns,tables anymore at runtime.

[lukaseder]

Do you have an example of this static analysis of table/column names? From what I see in your documentation, these things are string-based...? How can they be statically analysed?