3 ms·

Any serious application beyond the example given in this article will include conditional SQL constructs which go beyond SQL query parameters and will therefore

Any serious application beyond the example given in this article will include conditional SQL constructs which go beyond SQL query parameters and will therefore require string formatting to build the SQL. Think a simple UI switch to sort some result either ascending or descending, which will require you format either an `ASC` or a `DESC` in your SQL string. The moment you build SQL with string formatting is the moment you're rewriting the SQL formatter from an ORM, meeting plenty of opportunities to shoot yourself in the foot.

williamdclt 3y ago
There’s a world between a query builder and an ORM. The point of ORMs isn’t to build queries, if that’s the only need might as well just use a query builder which is a lot more lightweight and doesn’t come with all the downsides of orms
- masklinn 3y ago
  The OP literally says to ignore query builders, not just ORMs. When they state “just write SQL” that’s their actual thesis.
coldtea 3y ago
What you describe just needs a query builder (e.g. in Java something like jOOQ), not necessarily an ORM.
- masklinn 3y ago
  OK but TFA is not just against ORMs, it’s also against query builders. That’s what GP is replying to.
nicoburns 3y ago
> The moment you build SQL with string formatting is the moment you're rewriting the SQL formatter from an ORM, meeting plenty of opportunities to shoot yourself in the foot. I used to think this, but at my last company we ended up rewriting all these queries to use conditional string formatting as we found it much more readable. The key was having named parameter binding for that string, so you didn't have to worry about matching up position arguments. That along with JavaScripts template string interpolation actually made the string-formatted version pretty nice to work with.
- boxed 3y ago
  Sounds like just begging for SQL injection attacks.
  - nicoburns 3y ago
    Values were still provided separately. The string-interpolated SQL would include a placeholder just like static SQL does. That's pretty easy to audit for in code review: no variables in interpolated code.
    - boxed 3y ago
      That makes no sense. What are you interpolating? Some variable. And you now have to audit that THAT VARIABLE is safe.
      - nicoburns 3y ago
        > What are you interpolating? Some variable. Nope, I'm generally interpolating an inline expression consisting entirely of string literals.
        boxed 3y ago
        > generally Does not inspire confidence.
        nicoburns 3y ago
        In the rare case that you're interpolating a variable, you'd need to audit it in review. This is similar to carefully auditing the rare use cases of raw SQL expression when using an ORM.
      - squeaky-clean 3y ago
        sortable_fields = ["name", "age", "gpa"] selected_filter = sortable_fields[form.filterIndex] if form.sortBy == "asc": query += "ORDER BY {} ASC" elif form.sortBy == "desc": query += "ORDER BY {} DESC" Doesn't have any opportunity for SQL injection unless you have rogue programmers able to change code running in prod.
tracker1 3y ago
Depends on the abstraction... for example .Net's extensions for LINQ are pretty good at this, I haven't generally used the LINQ syntax, but the abstraction for query constructs are pretty good, combined with Entity Framework. Of course, there's a lot that I don't care for and would prefer Dapper. In the end, the general environment of .Net dev being excessively "enterprisey" has kept me at bay the past several years.
- dqv 3y ago
  Just looked at LINQ and it looks like Ecto [0] used a lot of its ideas for inspiration! I haven't used LINQ, but in Ecto, there are so many useful constructs for composing queries. If you get a stinker of a query, you have multiple escape hatches such as fragments [1] or just writing the queries directly as needed [2]. For beginners, the Elixir language constructs can be a little clunky, but once you get it, it's so productive and I miss that productivity when doing more advanced queries in other languages. [0]: https://hexdocs.pm/ecto/Ecto.Query.html https://hexdocs.pm/ecto/Ecto.Query.html [1]: https://hexdocs.pm/ecto/Ecto.Query.html#module-fragments https://hexdocs.pm/ecto/Ecto.Query.html#module-fragments [2]: https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#query/4 https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#query/4
drdaeman 3y ago
IMHO the real proper solution is to have an SQL parser, so you can have your SQL represented as an AST, do some operations on it, then compile it back to a query. Sadly, I'm not aware of any good solutions to this. SQLAlchemy Core can build an operates on an AST, but it doesn't parse raw SQL into a query (so one has to write their queries in Python, not SQL). Some parser libraries I've seen were able to parse the query but didn't have much in terms of manipulations and compiling it back.

williamdclt 3y ago

There’s a world between a query builder and an ORM. The point of ORMs isn’t to build queries, if that’s the only need might as well just use a query builder which is a lot more lightweight and doesn’t come with all the downsides of orms

masklinn 3y ago
The OP literally says to ignore query builders, not just ORMs. When they state “just write SQL” that’s their actual thesis.

coldtea 3y ago

What you describe just needs a query builder (e.g. in Java something like jOOQ), not necessarily an ORM.

masklinn 3y ago
OK but TFA is not just against ORMs, it’s also against query builders. That’s what GP is replying to.

nicoburns 3y ago

> The moment you build SQL with string formatting is the moment you're rewriting the SQL formatter from an ORM, meeting plenty of opportunities to shoot yourself in the foot. I used to think this, but at my last company we ended up rewriting all these queries to use conditional string formatting as we found it much more readable. The key was having named parameter binding for that string, so you didn't have to worry about matching up position arguments. That along with JavaScripts template string interpolation actually made the string-formatted version pretty nice to work with.

boxed 3y ago
Sounds like just begging for SQL injection attacks.
- nicoburns 3y ago
  Values were still provided separately. The string-interpolated SQL would include a placeholder just like static SQL does. That's pretty easy to audit for in code review: no variables in interpolated code.
  - boxed 3y ago
    That makes no sense. What are you interpolating? Some variable. And you now have to audit that THAT VARIABLE is safe.
    - nicoburns 3y ago
      > What are you interpolating? Some variable. Nope, I'm generally interpolating an inline expression consisting entirely of string literals.
      - boxed 3y ago
        > generally Does not inspire confidence.
        nicoburns 3y ago
        In the rare case that you're interpolating a variable, you'd need to audit it in review. This is similar to carefully auditing the rare use cases of raw SQL expression when using an ORM.
    - squeaky-clean 3y ago
      sortable_fields = ["name", "age", "gpa"] selected_filter = sortable_fields[form.filterIndex] if form.sortBy == "asc": query += "ORDER BY {} ASC" elif form.sortBy == "desc": query += "ORDER BY {} DESC" Doesn't have any opportunity for SQL injection unless you have rogue programmers able to change code running in prod.

tracker1 3y ago

Depends on the abstraction... for example .Net's extensions for LINQ are pretty good at this, I haven't generally used the LINQ syntax, but the abstraction for query constructs are pretty good, combined with Entity Framework. Of course, there's a lot that I don't care for and would prefer Dapper. In the end, the general environment of .Net dev being excessively "enterprisey" has kept me at bay the past several years.

dqv 3y ago
Just looked at LINQ and it looks like Ecto [0] used a lot of its ideas for inspiration! I haven't used LINQ, but in Ecto, there are so many useful constructs for composing queries. If you get a stinker of a query, you have multiple escape hatches such as fragments [1] or just writing the queries directly as needed [2]. For beginners, the Elixir language constructs can be a little clunky, but once you get it, it's so productive and I miss that productivity when doing more advanced queries in other languages. [0]: https://hexdocs.pm/ecto/Ecto.Query.html https://hexdocs.pm/ecto/Ecto.Query.html [1]: https://hexdocs.pm/ecto/Ecto.Query.html#module-fragments https://hexdocs.pm/ecto/Ecto.Query.html#module-fragments [2]: https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#query/4 https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#query/4

drdaeman 3y ago

IMHO the real proper solution is to have an SQL parser, so you can have your SQL represented as an AST, do some operations on it, then compile it back to a query. Sadly, I'm not aware of any good solutions to this. SQLAlchemy Core can build an operates on an AST, but it doesn't parse raw SQL into a query (so one has to write their queries in Python, not SQL). Some parser libraries I've seen were able to parse the query but didn't have much in terms of manipulations and compiling it back.