Comment by never_inline
3 days ago
How do you do conditional filters in pure SQL from a backend Java / Python app, without doing string concatenation?
Not a fan of all the proxy object circus ORMs do but I'd leave row-> domain object mapping and filter building to some library. Sweet spot is probably something akin to Android Room / Micronaut Data JDBC.
Query builders that operate at the SQL level. (A popular example of that in Python is SQLAlchemy Core, but there are better ways to do it, especially in better-typed languages.)
I was just pondering, for a little Rust project of mine, whether to suffer some weird ORM-smelling query builder, or to just build the AST with https://github.com/apache/datafusion-sqlparser-rs/ and convert to string..
JOOQ (http://jooq.org) is pretty fantastic for this, and it's my go-to for working with RDBMs' on the JVM. It provides a DSL-like API that lets you write pretty much any SQL you need in a type-safe way (without string concatenation).
What's wrong with string concatenation?
Guaranteed source of bugs in complex cases.
More complex cases are more likely to have bugs period, just in their logic.
String concatenation isn't really a major source of that. Just make sure your parentheses match, as you need to do no matter what, and include a space at the start and end of each string to make sure you don't accidentally smush terms together likethis.
Simpler SQL injection risk and more testing to make sure all potential branching paths don’t result in invalid SQL.
There's zero danger of sql injection so long as everything is being passed by parameters. You just concatenate placeholders when you need string concatenation to build the query.
1 reply →
SQL has CASE statements, if you’d really like to have all branching logic in pure SQL.
String concatenation
No, we must build 16 more layers of pointless abstraction in a new DSL.