Adding OR logic forced us to confront why users preferred raw SQL

This discussion revolves around the trade-offs between Object-Relational Mappers (ORMs) and writing raw SQL, with sub-themes exploring query builders, the nature of abstraction, versioning of query logic, and the influence of AI-generated writing styles.

The Case Against ORMs and the Appeal of Raw SQL

Many participants express frustration with ORMs, viewing them as adding unnecessary layers of abstraction that can obscure query logic, hinder performance tuning, and limit access to powerful SQL features. The declarative nature of SQL is often preferred for its directness and power.

• "I still struggle with ORMs. SQL is... declarative. If you're working with multiple RDBMSs, sure? Maybe I want my local dev to be sqlite and scaled be postgres? I've never run into that in production. A DSL on top of a DSL doesn't make a lot of sense." - ryoshu
• "I just want to write one language at a time if I can. I like sql when querying directly, almost as a UI of sorts, but it’s not my favorite when I am just trying to get my code to work, and the database is a relatively minor detail." - dec0dedab0de
• "My main issue with ORMs is they always end up being just another thing to learn, adding needless complexity. They are not an alternative to SQL as you always end up having to understand what kind of SQL they create and how it works for either performance or complex queries." - chillfox
• "I HATE ORMs. I end up spending an hour or two trying to figure out why something isnt working for what should be a simple query. Theyre also seem quite restrictive to what raw sql can do." - andoando
• "I've never had a good experience debugging performance issues in ORM-generated queries. Maybe I haven't used the right ones, but the libraries I've used have gone out of their way to hide how the query is actually being executed and only have obtuse levers to tweak that execution. Sure you can see the raw logs of what queries the ORM executed, but you can't easily figure out why its chosen a particular strategy. Or you can't easily make it stop using a pathological join ordering." - webstrand

The Value and Dangers of String Concatenation for SQL

A significant portion of the discussion centers on dynamically building SQL queries. While some view string concatenation as a risky practice prone to SQL injection and malformed queries, others argue that it's manageable with proper parameterization and testing.

• "How do you do conditional filters in pure SQL from a backend Java / Python app, without doing string concatenation?" - never_inline
• "No, we must build 16 more layers of pointless abstraction in a new DSL." - Xss3 (This is a critical response to the idea of avoiding concatenation.)
• "What's wrong with string concatenation?" - crazygringo
• "Simpler SQL injection risk and more testing to make sure all potential branching paths don’t result in invalid SQL." - t-writescode
• "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." - webstrand
• "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." - 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." - crazygringo
• "Hand-rolling SQL inside another programming language comes with some unpleasantness, like protecting against SQL injection and making sure the SQL is valid, especially when hand-constructing the query based on input parameters: “sort ascending? Descending? Filter all but things in this group? etc.” Parameter management in some languages are unpleasant, like how JDBC only has positional arguments; and any time you do string concat in a language, you start getting in danger of misformed SQL." - t-writescode
• "Ultra basic ORMs, like Exposed (Kotlin), are well-tested frameworks that do exactly what I want. Want a single value in the =? Or want it to be an “in”? Or what if it’s null? Handled. No special string management. Want parameters? Handled. When I see pure ORM’d code, I can feel safe expecting it to be protected from injection and formatting issues. It’s reduced cognitive load and greater safety. When I see raw string SQL management, I have to put another layer of care and attention to try and make sure (and maybe still fail) there’s no application-crashing mistakes in that part of code. It’s kinda like working with typed and compiled code. Greater protection from error." - t-writescode
• "Only for parameterization over scalar values. If you want to do any kind of composition more sophisticated than that, you're either stitching together strings or using some kind of more heavyweight abstraction like an ORM." - ameliaquining

The Role and Limitations of Query Builders

Query builders are presented as a middle ground, offering a more structured way to construct SQL than pure string concatenation, but without the full abstraction of ORMs. However, some argue they can still be a "DSL on top of a DSL" and may not always cleanly map to actual SQL, requiring fallbacks to raw SQL.

• "“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.)”" - minitech
• "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)." - foobazgt
• "It sounds like you're describing a query builders which, unlike true ORMs, don't attempt to mask the object-relational boundary. They only help you build queries in a convenient way and sometimes type-safe way. Query builders are great. ORMs are not query builders. The problem with ORMs is that they hide the query logic from you. It's not clear what's getting joined in, how its getting joined in, or if its actually 1 or N queries. The further you get from a straight query builder, too, the fewer SQL features you have access to, such as parameterized joins, CTEs, window functions, etc. Sometimes you can hack those into the ORM, but often you have to resort to string concat and build the parameterized query and arguments manually." - webstrand
• "I've never used Exposed, but from what I can tell it's kind of a hybrid? the query builder parts look great, but I'd still be wary of the ORM parts." - webstrand
• "Raw SQL is the way to go[0]. Period. You just need a thin wrapper on top of it that adds escaping, arguments, type-safety and other QoL." - kaoD (referencing an article critical of query builders like Knex.js)
• "Query builders are still an antipattern (what we traditionally think of when we say query builders) because they are still a DSL that (1) you have to learn along with SQL and (2) never map cleanly to actual SQL, so you always have to resort to .raw calls when you need anything more complex than a simple SELECT+JOIN. Even for simple SELECTs, I lost count of how many times I had to enable runtime DEBUG logging in my query builders to get a query that I can copy-paste into my DB client for debugging, data exploring, etc. I should be able to copy-paste my query from code and run it in psql with minimal changes." - kaoD
• "The problem isn't version-controlling the SQL; it's making sure that, when a particular build of your app executes a query that calls out to a user-defined function, it uses the SQL function code from the same commit that the app itself was built from. Otherwise you have a potential source of version skew, and those are always really annoying and unpleasant to deal with." - ameliaquining (This highlights a versioning challenge related to database logic, which query builders and ORMs interact with.)
• "It's not just a technical achievement. That's validation that we finally understood the problem we were trying to solve." - giveita (Quoted from an article, used to illustrate a point about capability gaps in query builders before improvement.)

Database Functions, Views, and Versioning of Query Logic

The discussion touches upon using database-level features like views and stored procedures to encapsulate query logic. A key concern raised is the "version skew" that can occur when application code is updated, but the underlying database logic (like functions) is not, leading to potential inconsistencies. The ideal is to version query logic alongside application code.

• "That’s because the composition is supposed to be inside sql. Views, functions, etc. This is another reason why the ORM is a leaky abstraction - it hides all the best features from you." - monkeyelite
• "I suspect the biggest reason those aren't more popular is that they usually have to be stored as state in the database, which isn't what you want when developing an application. You want all of your query logic to be versioned with your application code." - ameliaquining
• "SQL can be stored in version control just as well as any other code. This can include application-level queries as well as the DDL SQL which defines the actual structure of your database. It's sad that tooling for this kind of workflow doesn't seem to be particularly good across the board, Visual Studio being somewhat of an exception." - branko_d
• "The problem isn't version-controlling the SQL; it's making sure that, when a particular build of your app executes a query that calls out to a user-defined function, it uses the SQL function code from the same commit that the app itself was built from. Otherwise you have a potential source of version skew, and those are always really annoying and unpleasant to deal with." - ameliaquining

SQL Dialects and Database Expertise

There's an acknowledgment that SQL itself can be complex, especially with different RDBMS dialects. Some suggest that mastering SQL and utilizing its features like CASE statements or views is a more robust approach than relying on higher-level abstractions that might not fully expose these capabilities.

• "SQL has CASE statements, if you’d really like to have all branching logic in pure SQL." - sgarland
• "The database is a separate component, the biggest mistake nearly every developer makes is trying to make a single application own it. Let me tell you, from experience, the chances that this one single application will be the only thing that every connects to your database past initial development is slim." - snuxoll
• "If you can update the underlying Db, I've developed an ORM pattern that I use in my projects that works very well. The keys is to encapsulate most of the (possibly complex) CRUD logic in Db functions (for retrieval these would be table-valued functions) and access these from the application side as virtual tables." - prmph
• "SQL is just extremely bad on top of being poorly integrated with the host language. Middle-endian order, terrible abstraction capabilities, no test support to speak of, essentially no project management tooling... I use ORMs so that I can write the thing I want to do in a vaguely reasonable language, just like I manipulate XML datastructures in code instead of writing XSLT." - lmm (This is a strong outlier opinion, criticizing SQL fundamentally.)
• "At the risk of getting run off this site... Jira's search query widget, which allows in some sense nearly arbitrary SQL while providing syntax completion, making sure you don't run off the rails with actual arbitrary SQL, and supporting very deeply nested conditionals correctly is probably one of the most impressive things to me about that system. I just wish it was not such a staggeringly large pile of work to get to such a thing in other systems." - jerf
• "JQL only lets you write severely limited "where" clauses that plug into something that probably isn't SQL at all. You cannot use JQL to pull up anything but a list of issues. You cannot aggregate fields within those issues. Many fields aren't even an option. The built-in functions are garbage. Some are redundant. Some are named similarly but do wildly different things. You can't even use subqueries or regex." - sublinear
• "That's right. JQL (Jira Query Language) is based on AQL (Atlassian Query Language), same as CQL (Confluence Query Language), and targets Lucene (IIRC), not SQL, and CQL and JQL are (I suppose!) translated to Lucene queries." - jraph

The Inflection of AI-Generated Writing Styles

A notable tangent in the discussion concerns the prevalence of AI-generated writing styles in technical content. Participants identify specific phrasing patterns commonly associated with LLMs and express a preference for more natural, human-crafted prose, even if it means slightly less broad reach.

• "What is it with the LinkedIn style? No X. No Y. No Z. Isn't A its B." - giveita (Critiquing a common writing structure.)
• "It's not LinkedIn style, this is how ChatGPT generates text." - jjani
• "It's not just ChatGPT—it's part of the inner fabric of Large Language Models. Heh. But seriously, all frontier models do it, it's in the top 3 of tells that even someone with zero LLM experience can spot." - jjani
• "This wasn't a minor limitation; it was a fundamental capability gap." - giveita (Quoted from an article, cited as an example of typical LLM phrasing.)
• "It wasn't just a minor stylistic issue; It was a signal to close the page." - tux3 (Another example of critique regarding LLM writing.)
• "I hate getting that 'is this LLM output?' feeling halfway through so many articles these days. The article is good but sentences like "This wasn't a minor limitation; it was a fundamental capability gap." are painful to read." - gm678
• "Tropes in general I think become distracting. The ChatGPT style isn't bad in itself (just as one Giblhi cartoon isn't bad but 100000 are!)" - giveita