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.
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.
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.
alternatively, why not use database views? That way you can make sure that customers can only see their own data. Permissions can be handled with normal database users by only granting them SELECT rights on the views.
If you're afraid of users tanking performance, read replicas. As instantaneous as it gets, and no customer can tank others.
I've been using and frustrated by Jira for a long time, so I'd love to be pointed in the right direction if I'm wrong here, but 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. Is there documentation disproving me somewhere that I'm not aware of?
These are all huge problems with endless unresolved tickets on Atlassian's support pages that point out very good and very common use cases that people have desperately wanted for over a decade. The answer is always yet another plugin. Security and data integrity can't be the reason Jira search sucks since many of those plugins are buggy and have more access to the backend.
As far as I can tell "JQL" was a name devised by their marketing department to make it seem more powerful than it really is and otherwise prop up a nasty plugin ecosystem that nobody wants. I sometimes feel like my outlook inbox rules are more sophisticated and that's pretty sad.
Count how many places have a search on a website that let you run "((A and B) or (C and D) XOR E) AND NOT F". It's not many.
The very complaints you are making are kind of my point; that you can even consider making them means they've put a lot of work into a search interface that few other search interfaces have. The vast majority of "search fields" I run into can't even do a single AND or OR; one does not get so far as to wish for those things because clearly we're miles away from them.
> JQL only lets you write severely limited "where" clauses that plug into something that probably isn't SQL at all.
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.
These query languages are a subset of what you can do with a Solr / Lucene query string (basically the same thing as Solr is a fork of Lucene; Lucene is what is behind Atlassian products' search features IIRC). If you squint a little, you can liken it to some limited SQL where clause without the joins and the sub queries, but the target is a search platform, not an SQL database.
AQL fields map to Lucene fields, and there are functions that are also (likely) translated to Lucene expressions or replaced with some stuff from the context (currentContent() for instance in CQL is replaced to the id of the current Confluence page, and parentContent() to the id of the parent of the current page - again, this is a guess).
I suppose they invented this family or query languages to provide something more high level / user-friendly than raw lucene query strings, or possibly didn't want to give full access to the Lucene search capabilities for some reason.
There are Maven packages provided by Atlassian to deal with AQL [1], but they are not open source (and I have not studied their code, disassembled or otherwise).
I have reimplemented an AQL parser [2] from scratch using their limited docs and guesses, and, with this, implemented a CQL to Solr query translator for XWiki, mapping Confluence features to corresponding XWiki features [3]. Because people are migrating from Confluence to XWiki and their content is sprinkled with CQL-based macros that we needed to make work.
If you are curious about how a CQL statement can be translated to a Solr query (although querying fields in XWiki, not Confluence), you can look at the tests [4].
I actually find AQL pretty elegant, although I'll admit I haven't used it much.
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.
This article is part of our launch week and our main distribution channel is LinkedIn.
Our articles are now being read by stakeholders beyond our ICP.
I agree that LinkedIn/ChatGPT style isn't the best route. We cringe on it too. We are experimenting to find a middle ground between what gets more reach, while not giving into the trending writing styles.
Can I please get some more feedback from you?
- would you prefer more technical details in this article?
- or just a change in the sentence structure that is more natural (like this response)?
- or both?
The technical content is okay, but there's some fluff with a characteristic LLM signature that cheapens the whole thing. Instead of an article hand-crafted by human hands, it screams to the reader that they are currently reading slop.
I would rather not read other people's slop. I could pass your article through an LLM myself, if I wanted that. Here's just one of the most tired snowclones that current LLMs love, everywhere in your content:
>This wasn't a minor limitation; it was a fundamental capability gap
>context-switch not just between data types, but between entirely different mental models of how to query data.
>This wasn't something we asked them to do. They discovered that the query builder could now handle their complex cases, and they preferred it over raw SQL.
>That's not just a technical achievement. That's validation that we finally understood the problem we were trying to solve.
It wasn't just a minor stylistic issue; It was a signal to close the page.
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.
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).
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.
Can’t relate this comment to the article. They can’t just run user sql on DB because they are changing internal db schema between releases. And they can’t implement real sql because it is massive compared to some simple query dsl
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.
One of my guiding principles is "avoid indirection unless you add abstraction." If what you're doing is a query language, then keeping that transparent and using existing languages is a good idea, unless you can motivate a new language with a new mental model. E.g. it might be better to write an SQL query validator than a DSL.
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.
SoQL's lack of joins and subqueries makes it a real pain in my life. `having` and `group by` are not enough when doing a lot of things - like confirming data migration of 40m objects.
Can't do a query builder in one step, or must write Apex.
This is a chronic problem in software. What I do instead is either omit the complexity or make it as visible as everything else. If it's not worth making it discoverable, it's not worth having. If you omit it, you get customer feedback about its importance instead of them struggling in silence.
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.
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.
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.
We do this with DuckDB, and leave the connection in read only mode, exposing the database over HTTP. SQL injection by design?
2 replies →
If you're writing out data for analytical read only use - go with DuckDb all the way, over SQLite.
1 reply →
I use a desktop system which has python as its scripting layer, and exposes data to python nodes using pandas.
(In case you're wondering, https://www.knime.com/)
Zuora (billing and payment SaaS) has a user facing SQL interface backed by Trino that works pretty well
alternatively, why not use database views? That way you can make sure that customers can only see their own data. Permissions can be handled with normal database users by only granting them SELECT rights on the views.
If you're afraid of users tanking performance, read replicas. As instantaneous as it gets, and no customer can tank others.
2 replies →
I've been using and frustrated by Jira for a long time, so I'd love to be pointed in the right direction if I'm wrong here, but 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. Is there documentation disproving me somewhere that I'm not aware of?
These are all huge problems with endless unresolved tickets on Atlassian's support pages that point out very good and very common use cases that people have desperately wanted for over a decade. The answer is always yet another plugin. Security and data integrity can't be the reason Jira search sucks since many of those plugins are buggy and have more access to the backend.
As far as I can tell "JQL" was a name devised by their marketing department to make it seem more powerful than it really is and otherwise prop up a nasty plugin ecosystem that nobody wants. I sometimes feel like my outlook inbox rules are more sophisticated and that's pretty sad.
Count how many places have a search on a website that let you run "((A and B) or (C and D) XOR E) AND NOT F". It's not many.
The very complaints you are making are kind of my point; that you can even consider making them means they've put a lot of work into a search interface that few other search interfaces have. The vast majority of "search fields" I run into can't even do a single AND or OR; one does not get so far as to wish for those things because clearly we're miles away from them.
1 reply →
> JQL only lets you write severely limited "where" clauses that plug into something that probably isn't SQL at all.
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.
These query languages are a subset of what you can do with a Solr / Lucene query string (basically the same thing as Solr is a fork of Lucene; Lucene is what is behind Atlassian products' search features IIRC). If you squint a little, you can liken it to some limited SQL where clause without the joins and the sub queries, but the target is a search platform, not an SQL database.
AQL fields map to Lucene fields, and there are functions that are also (likely) translated to Lucene expressions or replaced with some stuff from the context (currentContent() for instance in CQL is replaced to the id of the current Confluence page, and parentContent() to the id of the parent of the current page - again, this is a guess).
I suppose they invented this family or query languages to provide something more high level / user-friendly than raw lucene query strings, or possibly didn't want to give full access to the Lucene search capabilities for some reason.
There are Maven packages provided by Atlassian to deal with AQL [1], but they are not open source (and I have not studied their code, disassembled or otherwise).
I have reimplemented an AQL parser [2] from scratch using their limited docs and guesses, and, with this, implemented a CQL to Solr query translator for XWiki, mapping Confluence features to corresponding XWiki features [3]. Because people are migrating from Confluence to XWiki and their content is sprinkled with CQL-based macros that we needed to make work.
If you are curious about how a CQL statement can be translated to a Solr query (although querying fields in XWiki, not Confluence), you can look at the tests [4].
I actually find AQL pretty elegant, although I'll admit I haven't used it much.
[1] https://mvnrepository.com/artifact/com.atlassian.querylang
[2] https://github.com/xwiki-contrib/cql/tree/master/aqlparser
[3] https://github.com/xwiki-contrib/cql/tree/master/query
[4] https://github.com/xwiki-contrib/cql/blob/master/query/src/t...
2 replies →
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
It's not LinkedIn style, this is how ChatGPT generates text
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.
This article is part of our launch week and our main distribution channel is LinkedIn.
Our articles are now being read by stakeholders beyond our ICP.
I agree that LinkedIn/ChatGPT style isn't the best route. We cringe on it too. We are experimenting to find a middle ground between what gets more reach, while not giving into the trending writing styles.
Can I please get some more feedback from you?
- would you prefer more technical details in this article? - or just a change in the sentence structure that is more natural (like this response)? - or both?
The technical content is okay, but there's some fluff with a characteristic LLM signature that cheapens the whole thing. Instead of an article hand-crafted by human hands, it screams to the reader that they are currently reading slop.
I would rather not read other people's slop. I could pass your article through an LLM myself, if I wanted that. Here's just one of the most tired snowclones that current LLMs love, everywhere in your content:
>This wasn't a minor limitation; it was a fundamental capability gap
>context-switch not just between data types, but between entirely different mental models of how to query data.
>This wasn't something we asked them to do. They discovered that the query builder could now handle their complex cases, and they preferred it over raw SQL.
>That's not just a technical achievement. That's validation that we finally understood the problem we were trying to solve.
It wasn't just a minor stylistic issue; It was a signal to close the page.
4 replies →
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!)
But you could survey more people as maybe it is just me!
1 reply →
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.
3 replies →
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.
20 replies →
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.)
1 reply →
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).
SQL has CASE statements, if you’d really like to have all branching logic in pure SQL.
What's wrong with string concatenation?
5 replies →
String concatenation
1 reply →
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.
Can’t relate this comment to the article. They can’t just run user sql on DB because they are changing internal db schema between releases. And they can’t implement real sql because it is massive compared to some simple query dsl
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.
> Stop making decisions for users.
yes! please stop making webpages background dark. It's a terrible design for ppl with astigmatism like me...
Thank you for educating me on astigmatism. I wasn't aware of the condition.
We are revamping the design. I'll ensure I understand more about this and make it more accessible.
Devs seem to prefer dark theme across the brand (eg Supabase, Linear). Hence, the current choice.
One of my guiding principles is "avoid indirection unless you add abstraction." If what you're doing is a query language, then keeping that transparent and using existing languages is a good idea, unless you can motivate a new language with a new mental model. E.g. it might be better to write an SQL query validator than a DSL.
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.
You might also like the interactive dashboard feauture that was released in the recent launch week https://www.youtube.com/watch?v=YQTQXq0F5Iw&ab_channel=SigNo...
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
SoQL's lack of joins and subqueries makes it a real pain in my life. `having` and `group by` are not enough when doing a lot of things - like confirming data migration of 40m objects.
Can't do a query builder in one step, or must write Apex.
and here query builder helps by making it earier to do cross-signal joins and subqueries. I see that is upcoming in SigNoz https://signoz.io/blog/query-builder-v5/#what-we-couldnt-shi...
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
> hide complexity to "simplify" the experience
This is a chronic problem in software. What I do instead is either omit the complexity or make it as visible as everything else. If it's not worth making it discoverable, it's not worth having. If you omit it, you get customer feedback about its importance instead of them struggling in silence.