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.
I didn't know JIRA can assist you with writing JQL, looks like I'm lost in the UI hell. I always just click Advanced and then click what I want together. Although I only need this kind of functionality very rarely, to make up for other UI grievances, so w/e.
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.
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.
> Currently, logs and traces live in separate worlds. You can see that a trace has an error, and you can see related logs, but you can't query them together.
I've looked into SigNoz a few times but still using Grafana. The former does look promising, and features like this would start to make the case for maybe switching.
> 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.
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.
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.
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.
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.
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.
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.
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.
> You want all of your query logic to be versioned with your application code.
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.
In most organizations a database is broader than any individual application - both in lifecycle and scope. So it makes sense that this state exists in a different way.
I suspect it’s because people never learned to use them, but they did learn to use the ORM.
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.)
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).
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.
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.
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.
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.
If you're writing out data for analytical read only use - go with DuckDb all the way, over SQLite.
I didn't know JIRA can assist you with writing JQL, looks like I'm lost in the UI hell. I always just click Advanced and then click what I want together. Although I only need this kind of functionality very rarely, to make up for other UI grievances, so w/e.
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.
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.
> Currently, logs and traces live in separate worlds. You can see that a trace has an error, and you can see related logs, but you can't query them together.
I've looked into SigNoz a few times but still using Grafana. The former does look promising, and features like this would start to make the case for maybe switching.
> 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
It's not LinkedIn style, this is how ChatGPT generates text
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.
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.
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.
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.
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.
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.
> Only for parameterization over scalar values.
ADO.NET has full support for table-valued parameters.
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.
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.
> You want all of your query logic to be versioned with your application code.
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.
In most organizations a database is broader than any individual application - both in lifecycle and scope. So it makes sense that this state exists in a different way.
I suspect it’s because people never learned to use them, but they did learn to use the ORM.
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.)
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.
String concatenation
No, we must build 16 more layers of pointless abstraction in a new DSL.
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.
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.
Salesforce’s SOQL, like was mentioned with Jira’s JQL, also handles this type of thing _okay_ without explicit joins.
I think “SQL is the interface” even for telemtry is the thing that truly makes sense, but it is messy with logs compared to splunk for example
[delayed]
> Stop making decisions for users.
yes! please stop making webpages background dark. It's a terrible design for ppl with astigmatism like me...
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.
Conveniently, it's open core, so you can look: https://github.com/SigNoz/signoz/tree/main/pkg/querybuilder
Seems to be 3978 lines of Go at present.
Go is pretty wordy, so 50 lines of scheme would be a bit of an exaggeration, but a good enough schemer could probably get it down there