> Perhaps the dynamic queries may be easier with the query builder, but you still find out about issues at the same time as raw sql/string templates.
As with everything, its a tradeoff and a matter of taste. SQL Query builders give you the flexibility of SQL, without relying on string concatenation directly, and making sure all the values are passed in order. Also, they usually can be extended to support multiple databases/adapters (imagine specifying values as $1, $2... vs ?,?, quoting identifiers using " or `, etc). They also usually fit better than plain strings in heavily programmatic contexts (imagine a query with different fields and filters depending on the value of a given field).
> You cannot check that the query is correct for a given schema (missing tables/cols etc), but I think many typed languages would allow you to ensure the AST is correct at compile time.
There are usually 2 types of query builders - SQL query builders, and DSL query builders. DSL ones are usually integrated with an ORM or something similar, and those are usually abstract enough to guarantee that the generated SQL and passed types are syntactically correct, even if invalid (ex. mentioning a column or a table that doesn't exist). SQL query builders (at least the ones I've seen) don't use an AST, as their purpose is to map methods to SQL chunks. They basically keep a list of different parts to assemble and then just build from those lists when generating the actual SQL. Some may provide object mapper capabilities, and in that case, some type checking may be performed. "May" in the sense that eg. a 14th century date is a valid date, but SQL Server will most certainly reject it, so in the end there are no "compile time guarantees" in this case.
As with everything, its a tradeoff and a matter of taste. SQL Query builders give you the flexibility of SQL, without relying on string concatenation directly, and making sure all the values are passed in order. Also, they usually can be extended to support multiple databases/adapters (imagine specifying values as $1, $2... vs ?,?, quoting identifiers using " or `, etc). They also usually fit better than plain strings in heavily programmatic contexts (imagine a query with different fields and filters depending on the value of a given field).
> You cannot check that the query is correct for a given schema (missing tables/cols etc), but I think many typed languages would allow you to ensure the AST is correct at compile time.
There are usually 2 types of query builders - SQL query builders, and DSL query builders. DSL ones are usually integrated with an ORM or something similar, and those are usually abstract enough to guarantee that the generated SQL and passed types are syntactically correct, even if invalid (ex. mentioning a column or a table that doesn't exist). SQL query builders (at least the ones I've seen) don't use an AST, as their purpose is to map methods to SQL chunks. They basically keep a list of different parts to assemble and then just build from those lists when generating the actual SQL. Some may provide object mapper capabilities, and in that case, some type checking may be performed. "May" in the sense that eg. a 14th century date is a valid date, but SQL Server will most certainly reject it, so in the end there are no "compile time guarantees" in this case.