Hacker News Reader

Comment by never_inline | Hacker News Reader

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.

View on HN

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?

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.

Exactly this.

And if you're testing, you've got to test every query combination anyways. It's not just syntax that can be wrong, but logic and performance.

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.

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.

Reply on news.ycombinator.com