6 ms·
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 OR
by never_inline 1y 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.
- paulddraper 1y agoString concatenation
- Xss3 1y agoNo, we must build 16 more layers of pointless abstraction in a new DSL.
- minitech 1y agoQuery 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 1y agoI 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/ https://github.com/apache/datafusion-sqlparser-rs/ and convert to string..
- crazygringo 1y agoWhat's wrong with string concatenation?
- t-writescode 1y agoSimpler SQL injection risk and more testing to make sure all potential branching paths don’t result in invalid SQL.
- webstrand 1y agoThere'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 1y agoExactly 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.
- whatevaa 1y agoGuaranteed source of bugs in complex cases.
- crazygringo 1y agoMore 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.
- foobazgt 1y agoJOOQ (http://jooq.org 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).
- sgarland 1y agoSQL has CASE statements, if you’d really like to have all branching logic in pure SQL.