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. Even if someone cites some sort of open source library that supports this, simply defining to that library what columns and operations you support would be a lengthy task of specification, refinement, and testing.
It'd be neat if you could let more users just have SQL but there's so many ways for that to go terribly wrong nowadays, with all those nice juicy SQL features that so many engines now support.
Something I have been considering is a ETL pipeline that, for each customer in our system, writes only their data to a SQLite file. Then, just expose a full SQLite query facility on that file.
This only works when your customers are of a reasonable size (e.g. small businesses or individuals) but could provide arbitrary analytics power.
It's also a safe target for AIs to write sql against, if you're into that sort of thing.
JQL is a very powerful tool. No one sets up Jira perfectly. Not at first. People use anything like a label: the epic, the release version. etc. And JQL let's you get around that in the short term and find stuff.
> v3 couldn't do this. No OR support. No complex boolean expressions. No parentheses for precedence.
This wasn't a minor limitation; it was a fundamental capability gap. Users were forced to learn ClickHouse SQL, write raw queries, and maintain them as our schemas evolved. We'd built a query builder that couldn't handle real-world queries.
What is it with the LinkedIn style?
No X
No Y
No Z
Isn't A its B
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.
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.
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.
You’re arguing against a straw man. All major language sql libraries are not based on string manipulation and provide things like escaping, arguments, etc out of the box.
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.)
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.
String concatenation
No, we must build 16 more layers of pointless abstraction in a new DSL.
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.
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.
Is it actually hard to build a DSL for the kind of query’s they are talking about? Seems like it would be a 50 line SICP exercise.