[never_inline]

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.

[minitech]

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.)

[yencabulator]

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..

[foobazgt]

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).

[crazygringo]

What's wrong with string concatenation?

[whatevaa]

Guaranteed source of bugs in complex cases.

[crazygringo]

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.

[t-writescode]

Simpler SQL injection risk and more testing to make sure all potential branching paths don’t result in invalid SQL.

[webstrand]

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.

[crazygringo]

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.

[sgarland]

SQL has CASE statements, if you’d really like to have all branching logic in pure SQL.

[paulddraper]

String concatenation

[Xss3]

No, we must build 16 more layers of pointless abstraction in a new DSL.