4 ms·

Is there any point in having a query builder API in a dynamic language? I can see the point in a typed language where you can ensure the query is correct at co

Is there any point in having a query builder API in a dynamic language? I can see the point in a typed language where you can ensure the query is correct at compile time. But in a dynamic language, you never know if the query is correct until the interpreter hits that line in the program at runtime. This is true with both raw SQL (error returned from server) and the query builder API (error returned from interpreter).

aforwardslash 5y ago
> Is there any point in having a query builder API in a dynamic language? 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. > I can see the point in a typed language where you can ensure the query is correct at compile time. 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.
- mattashii 5y ago
  > 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. JOOQ [0] does a great job of providing a type-safe query builder that can guarantee correct queries at compile-time. It indeed also supplies arbitrary strings for names and sql components which will break that guarantee, but apart from that you can extract your databases' schema into typed classes and use those to run your queries. I really like it, but it is definately not a replacement for an ORM. [0] https://github.com/jOOQ/jOOQ https://github.com/jOOQ/jOOQ
  - aforwardslash 5y ago
    That is basically the description of an object mapper, with all the guarantees of an object mapper :). It seems if you actually use the query builder as such, no guarantees exist. I'm pretty picky regarding query builders and ORM's, to the extent of having written several of them over the years, in different languages (both dynamic and strong typed, unfortunately closed-source). I'm a strong advocate of schema-first design, and usually a query builder will allow you to design your queries explicitly, but having some internal behaviors (such as string concatenation, identifier quoting and automatic in-order separation of parameters and values to be bound) taken care of. As good examples of this, I'd mention golang's goqu (https://github.com/doug-martin/goqu https://github.com/doug-martin/goqu) and - to some extent - C# SqlKata (https://sqlkata.com/ https://sqlkata.com/). Following my frustrations with Python ORMs, I built my own toy project, sort-of-in-beta, called rickdb (https://github.com/oddbit-project/rick_db https://github.com/oddbit-project/rick_db).
- justsomeuser 5y ago
  > 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.
  - aforwardslash 5y ago
    > 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.
- nerdponx 5y ago
  Also: composability and reusability.

aforwardslash 5y ago

> Is there any point in having a query builder API in a dynamic language? 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. > I can see the point in a typed language where you can ensure the query is correct at compile time. 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.

mattashii 5y ago
> 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. JOOQ [0] does a great job of providing a type-safe query builder that can guarantee correct queries at compile-time. It indeed also supplies arbitrary strings for names and sql components which will break that guarantee, but apart from that you can extract your databases' schema into typed classes and use those to run your queries. I really like it, but it is definately not a replacement for an ORM. [0] https://github.com/jOOQ/jOOQ https://github.com/jOOQ/jOOQ
- aforwardslash 5y ago
  That is basically the description of an object mapper, with all the guarantees of an object mapper :). It seems if you actually use the query builder as such, no guarantees exist. I'm pretty picky regarding query builders and ORM's, to the extent of having written several of them over the years, in different languages (both dynamic and strong typed, unfortunately closed-source). I'm a strong advocate of schema-first design, and usually a query builder will allow you to design your queries explicitly, but having some internal behaviors (such as string concatenation, identifier quoting and automatic in-order separation of parameters and values to be bound) taken care of. As good examples of this, I'd mention golang's goqu (https://github.com/doug-martin/goqu https://github.com/doug-martin/goqu) and - to some extent - C# SqlKata (https://sqlkata.com/ https://sqlkata.com/). Following my frustrations with Python ORMs, I built my own toy project, sort-of-in-beta, called rickdb (https://github.com/oddbit-project/rick_db https://github.com/oddbit-project/rick_db).
justsomeuser 5y ago
> 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.
- aforwardslash 5y ago
  > 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.
nerdponx 5y ago
Also: composability and reusability.