> Yes, it eases the creation of queries and ensuring both escaping of parameters and quoting of columns and fields; It also allows the easy creation of dynamic queries based on lists of parameters, and facilitates both naming of tables and fields.
But in Python all these are checked at runtime.
Just like it is possible to not quote columns and fields in raw SQL, it is possible to mis-use the query builder API and only find out at runtime on the same line as you would with raw SQL.
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.
> Given that most query builders allow arbitrary parameters (such as table and column names), you can't actually ensure the query is correct at compile time, regardless of the type of language.
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.
> 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.
But in Python all these are checked at runtime.
Just like it is possible to not quote columns and fields in raw SQL, it is possible to mis-use the query builder API and only find out at runtime on the same line as you would with raw SQL.
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.
> Given that most query builders allow arbitrary parameters (such as table and column names), you can't actually ensure the query is correct at compile time, regardless of the type of language.
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.