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

2025-09-1013:009587signoz.io

Query Builder v5 brings familiar SQL like syntax to observability data with expression-based querying that works across logs, metrics, and traces. Write complex queries using the syntax you already…

Show article

In 2022, we had three different query interfaces. Logs had a custom search syntax with no autocomplete. Traces only had predefined filters - no query builder at all. Metrics had a raw PromQL input box where you'd paste queries from somewhere else and hope they worked.

Each system spoke a different language. An engineer debugging a production issue had to context-switch not just between data types, but between entirely different mental models of how to query data.

When we built v3 in 2022, we thought we were solving this. We created a unified query builder - essentially a UI abstraction over SQL. Count, group by, filter, limit. It worked well enough to carry us from 2022 to 2024.

But we were building with the wrong assumptions.

The v3/v4 Design Flaw That Took Two Years to Understand

We designed v3 around traces and metrics. In these data types, you rarely need complex boolean logic. A simple AND between conditions is usually enough.

But logs are different. When you're searching logs during an incident, you need expressions like:

(node_name contains 'management' OR pod_name contains 'test')
AND NOT (status_code >= 500)

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.

The Support Calls That Changed Our Philosophy

Over four years of support calls, a pattern emerged that challenged everything we thought we knew about UI design.

Senior engineers - people with 5-10 years of experience - couldn't find features that seemed obvious to us. The most telling example: chronological ordering in logs. The feature existed in v3 and v4, hidden three clicks deep in the UI. Users didn't just struggle to use it; they assumed we didn't support it at all.

During these calls, we'd watch them search for features, see their frustration, and realize: if you built it and know exactly where it is, everything seems obvious. But if senior engineers can't discover your features, those features effectively don't exist.

This led to a fundamental principle for v5: Stop making decisions for users.

In v3/v4, we tried to be clever. We'd make assumptions about what users wanted, hide complexity to "simplify" the experience. These assumptions were often wrong and led to surprising behavior that broke trust.

For v5, we established a new rule: if we must make a decision, it should be the least surprising one possible. And wherever possible, don't make the decision at all - let users control their experience.

The Architectural Reality: You Can't Ship a Query Builder in Isolation

When we started building v5, we quickly discovered that the query builder isn't just one component. It's the foundation of how users interact with data across the entire product.

Think about the typical workflow: You write a query in the explorer to investigate an issue. Then you either:

Save it as a dashboard panel to monitor the pattern
Create an alert to catch it next time
Switch between logs, traces, and metrics to correlate data

This interconnection meant we couldn't ship v5 for just the explorer. A query written in the new format had to work everywhere. This forced us to simultaneously rebuild:

All three explorers (logs, traces, metrics)
Dashboard panel creation (including value panels that only exist in dashboards)
Alert creation flows
The underlying query API that powers all of these

What started as "let's add OR support to the query builder" became a complete architectural overhaul.

The Technical Implementation

The most common use case during an incident: a user sends you an error message. In v3, you'd need to construct a query with the correct syntax. In v5, you just paste and search:

"connection timeout in payment service"

Behind the scenes, we parse this into the appropriate query structure. But the user doesn't need to know that. They're debugging a problem, not learning a query language.

Complex Boolean Logic with Proper Precedence

The feature that was impossible in v3/v4 and forced users to write ClickHouse queries:

(service_name = 'api' AND status_code >= 500)
OR
(service_name = 'worker' AND error_message contains 'timeout')

This seems basic, but implementing it required rethinking our entire query structure. We needed to support arbitrary nesting, maintain precedence rules, and still provide autocomplete and suggestions at every level.

Cross-Source Query Portability

One of the most powerful features that users don't initially notice: queries are portable across data types.

Write a query filtering for service_name = 'api' in logs. Copy it. Paste it in traces explorer. It works.

This seems simple, but the implementation is complex. Logs, traces, and metrics have:

Different underlying table schemas
Different column names for similar concepts
Different valid operations

We built an abstraction layer that translates queries between these contexts automatically. Users think in terms of their data, not our storage schema.

Performance at Scale: Instant Suggestions

When you're typing a query, you need suggestions immediately. But we're dealing with:

Millions of unique field values
Multiple data sources
Complex hierarchical data structures

We implemented:

Smart caching that predicts what fields you'll query next
Progressive loading that shows the most relevant suggestions first
Query optimization that happens before we send anything to ClickHouse

The result: autocomplete that feels instant, even at scale.

The UX Debt We Finally Paid

Because we were touching every part of the query experience, we could finally address years of accumulated UX issues.

Chronological ordering in logs: Moved from a hidden dropdown to a prominent toggle. Same capability, completely different discoverability.

Time aggregation controls: Previously buried in advanced settings, now directly visible. Users can switch from 1-minute to 5-second granularity with one click.

Interval selection: Direct control over data granularity from 5 seconds to 1 hour. Why does this matter? During an incident, 30-second aggregation might smooth out the spike that's causing your problem. 5-second aggregation shows you exactly when things went wrong.

These weren't query builder features, but fixing them was essential to delivering a coherent experience. When engineers are debugging production issues at 2 AM, they shouldn't hunt for basic controls.

The Validation: Users Replacing ClickHouse Queries

We shipped v5 with a single changelog entry. No marketing campaign. No push to adopt it.

Within three weeks, the feedback started coming in. The one that stood out: a user telling us they'd replaced all their ClickHouse queries with Query Builder queries.

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.

Why? Because with Query Builder:

They don't need to learn ClickHouse SQL syntax
They don't need to update queries when we change schemas
They get autocomplete and validation
They can copy queries between different data types
They can share queries with team members who don't know SQL

When users actively choose your abstraction over direct database access, you know you've built the right thing.

What We Couldn't Ship Yet: The Future of Cross-Signal Correlation

Imagine investigating an incident where you see 500 errors. Your hypothesis: high CPU usage caused the failures. Today, you check traces for errors, then separately check metrics for CPU usage, then try to mentally correlate the timings.

With subqueries (currently in development), you'll write:

Show traces where:
 status_code >= 500
 AND subquery(metrics: CPU_usage > 80% for same service)

This requires real-time joining of traces and metrics data. The architecture is designed, the UI patterns are established. Implementation is next.

Cross-Source Joins: Unified Debugging Experience

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.

With joins (in design phase), you'll write:

Show logs where:
 JOIN traces ON trace_id
 WHERE traces.duration > 500ms

This unlocks debugging workflows that are impossible today. Find all logs related to slow traces. Show logs where the parent span had an error. Correlate log patterns with trace characteristics.

The Engineering Lesson: Technical Elegance Without Discoverability Is Worthless

After four years working on this product, countless support calls, and watching experienced engineers struggle with features I thought were obvious, the lesson is clear:

Doesn't matter how elegant your technical solution is. Doesn't matter how powerful your features are. If users can't discover and use them, they don't exist.

We could have the most sophisticated query engine in the world. But if an engineer investigating a production incident can't immediately figure out how to use it, we've failed.

Query Builder v5 isn't just about adding OR support or fixing bugs. It's about recognizing that during an incident, engineers shouldn't have to think about query syntax. They should think about their problem.

Where We Go From Here

We closed 80 issues with v5. We have 50+ more in the backlog.

But we're not planning a v6 mega-release. We designed v5's architecture to be extensible. The abstractions are correct. The patterns are established. Now we can ship incremental improvements without breaking changes.

Subqueries, joins, and the remaining enhancements will roll out as they're ready. No more two-year gaps between major improvements.

The query builder is no longer just a UI component. It's the foundation of how engineers interact with their observability data. And for the first time, it's powerful enough that users are choosing it over writing raw SQL.

That's not just a technical achievement. That's validation that we finally understood the problem we were trying to solve.

Query Builder v5 is live in the latest release. Check the documentation for detailed examples and capabilities.

Read the original article

Comments

By jerf 2025-09-142:184 reply

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.

By sublinear 2025-09-148:102 reply

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.

By jerf 2025-09-1414:311 reply

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.

By sublinear 2025-09-1416:06

It doesn't matter how many. Jira should be on the same level as the other tools it's commonly used alongside.

GitLab has GLQL[1]. ServiceNow has CMDB[2]. Both offer more flexible queries on any data the users can see and can return arbitrary tables. The dashboards, reports, APIs, etc. are actually useful because of that. On Jira, all you get are lists of issues and dashboard widgets that are barely more helpful that the list of issues they obscure.

This is expected to be all available out-of-the-box without plugins or other gotchas. Nobody should have to rewrite their scripts to run the same basic recurring jobs on another project or part of the company just because they're on a different instance and don't want to buy a plugin that is seldom used. The only reason anyone tracks projects with Jira is its familiarity. Hardly anyone uses the newer features that Atlassian has shifted focus towards. All anyone wants is the core UI, for that UI to not be so bloated and slow (and it's only getting worse), and a better JQL.

[1]: https://docs.gitlab.com/user/glql/

[2]: https://www.servicenow.com/products/servicenow-platform/conf...

By jraph 2025-09-149:481 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...

By pas 2025-09-1413:011 reply

thanks for the links, and ... uhh, mad respect on the xwiki CQL feature! :)

a bit tangential, but isn't Solr (and ElasticSearch and probably others) all use Lucerne "indexes" (as in Lucene is a library like LevelDB)?

what's strange is that most JQL that people want would translate to the most boring Hibernate query (or whatever Jira uses for relational DB access)

By jraph 2025-09-1413:10

You are totally right, I thought Solr was a Lucene fork, but it's actually built on top of Lucene, thanks for correcting me. Both are Apache projects.

By crabmusket 2025-09-142:515 reply

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.

By klysm 2025-09-149:331 reply

We do this with DuckDB, and leave the connection in read only mode, exposing the database over HTTP. SQL injection by design?

By crabmusket 2025-09-1411:341 reply

Cool to hear I'm not off the deep end. Have you written anything up on this, or were you inspired by anything else? How has it worked out for you?

By klysm 2025-09-1417:24

haven’t written anything up on it, but I wouldn’t use my anecdote as an indication we aren’t off the deep end together!

At a high level, we use DuckDB as an in-memory OLAP cache that’s invalidated via Postgres logical replication.

We compute a lot of derived data to offer a read-heavy workload as a product.

Possibly the most dubious choice I tried out recently was letting the front end just execute SQL directly against DuckDB with a thin serialization layer in the backend.

The best apart about it is the hot reload feedback loop. The backend doesn’t have to rebuild to iterate quickly.

By sixdimensional 2025-09-143:581 reply

If you're writing out data for analytical read only use - go with DuckDb all the way, over SQLite.

By crabmusket 2025-09-1411:33

Thanks, I will look into it!

By m3047 2025-09-1417:54

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/)

By mschuster91 2025-09-1412:051 reply

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.

By crabmusket 2025-09-150:461 reply

I think my gut would feel safer having a serialisation boundary between the customer's queries and the primary data store. But yes in principle I don't see why this approach wouldn't work just as well. Food for thought.

By mschuster91 2025-09-159:11

The boundary is the read-replica, it cannot write back to the primary DB cluster. But yeah if you require absolute isolation of tenant data, aka even a compromise / local privilege escalation on the read-replica node shouldn't allow an attacker to read other tenants' data, no way around.

Or... depending if your database layout allows, you might be able to achieve that with a per-tenant read replica server and MySQL replication filters [1] or Postgres row filters [2].

A sqlite db is effectively the safest option because there is no way to bypass an export step... but it might also end up seriously corrupting your data (e.g. columns with native timezones) or lack features like postgre's spatial stuff.

[1] https://dev.mysql.com/doc/refman/8.4/en/change-replication-f...

[2] https://www.postgresql.org/docs/current/logical-replication-...

By nijave 2025-09-1412:05

Zuora (billing and payment SaaS) has a user facing SQL interface backed by Trino that works pretty well

By perching_aix 2025-09-142:59

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.

By giveita 2025-09-142:44

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.

By giveita 2025-09-142:412 reply

> v3 couldn't do this. No OR support. No complex boolean expressions. No parentheses for precedence.

What is it with the LinkedIn style?

No X

No Y

No Z

Isn't A its B

By outlier99 2025-09-144:191 reply

It's not LinkedIn style, this is how ChatGPT generates text

By jjani 2025-09-145:33

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.

By ak_builds 2025-09-145:352 reply

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?

By tux3 2025-09-148:393 reply

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.

By porker 2025-09-1412:081 reply

Isn't that though the narrative arc being shaped? We see it everywhere now, but just because LLMs like to output it doesn't make the structure you're highlighting bad.

Overall I found it a decent piece, a few too many "<term>: <explanation>" blocks for my taste but better than what I can write - and than most of the tech-industry blogging I come across.

By giveita 2025-09-1422:18

Not sure why you need an "ain't just water, its a two element molecule!" type rubbish to tell a story.

By ak_builds 2025-09-149:21

Feedback well taken! I'll update the articles soon and do better henceforth.

By huflungdung 2025-09-1411:45

…”for me”

Everyone else managed to read it fine.

By giveita 2025-09-149:011 reply

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!

By giveita 2025-09-1422:16

Agree with tux3 too

By ryoshu 2025-09-141:206 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.

By t-writescode 2025-09-142:162 reply

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.

By webstrand 2025-09-143:462 reply

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.

By prmph 2025-09-1410:371 reply

If one can update the underlying Db, I've developed an ORM pattern that I use in my projects that works very well.

They 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.

I also have capable but easy to use filtering/sorting/paging operators and combinators in the ORM that are translated into SQL where/sort/limit clauses.

Because the heavy lifting is already done by the db functions (which you have full control of to use whatever SQL you need), the pattern is actually quite powerful but easy to use.

You can define virtual read only tables, several virtual tables that access the same actual table in different way, custom operators that transcend SQL, etc

By webstrand 2025-09-1614:481 reply

Doesn't this break the object-relation mapping the ORM would be trying to do? Since the views would be producing resultsets that do not map to one single object? Or do you just define a new object type with virtual relations?

By prmph 2025-09-1615:33

Exactly. I define a pair of DTOs for each virtual table: an input type (the shape of a record that can be inserted) and an output one (the shape of each record returned when you select from the virtual table, which might include generated, computed, and foreign columns, etc).

These types do not necessarily have to map to the underlying table types.

By andoando 2025-09-145:091 reply

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.

By monkeyelite 2025-09-142:382 reply

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.

By kaoD 2025-09-148:472 reply

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.

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.

[0] https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41...

By esafak 2025-09-1415:501 reply

That thin wrapper is a query builder.

By kaoD 2025-09-1415:511 reply

> antipattern (what we traditionally think of when we say query builders)

By monkeyelite 2025-09-150:59

I'm with you.

By ameliaquining 2025-09-142:592 reply

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.

By monkeyelite 2025-09-143:092 reply

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.

By ameliaquining 2025-09-143:152 reply

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.

By branko_d 2025-09-144:361 reply

> 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.

By ameliaquining 2025-09-144:492 reply

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.

I think Postgres sort of supports this but it's really clunky, and also I think you'd have to run the function-creation code on every connection; I don't know whether that would create any performance problems.

What does Visual Studio do?

By snuxoll 2025-09-146:12

You just mentioned PostgreSQL, which, like pretty much every RDMBS sans MySQL, DB2/400, and maybe DB2/ZOS (never worked with the ZOS variant) supports schemas. If you need to worry about this, keep your tables that actually contain your data in one schema, then keep views, functions/stored procedures, etc. in separate schemas every time you make incompatible changes.

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. "Oh, but we're doing microservices and everything only ever talks over HTTP or gRPC" - yeah, sure, and I don't have a backlog of tech debt a mile long.

By branko_d 2025-09-1413:58

> 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

I don't really see a "problem" here. Having everything in the same repo is probably the easiest way to ensure that the client cannot go out of sync with the database. When making a release, create a Git tag and deploy both client and database from there.

But you must make sure you know which tag is deployed at each customer. We use naming conventions for that (the name of the customer is part of the Git tag name), or you could hold that mapping externally if necessary.

Once you have that, making a client-level hotfix for a specific customer is (relatively) easy - just branch from the customer's release tag, do the changes you need, run the tests, and there is high probability everything will work properly. Once you release the hotfix, you create another tag and remember that this tag is now installed at the customer, and so on...

If you make changes to the database, then of course you still need to have an upgrade procedure from one version of the database to another, but you can be confident that the client will never query the "wrong" database version. And since both old and new database structure are just SQL files under different Git tags, you can discover exactly what changed by just by diffing.

> What does Visual Studio do?

Visual Studio has a special SQL project type, where you can keep you base table definitions, as well as all the SQL code (stored procedures, functions, views, user-defined types, indexes etc.). You can group the SQL project together with client projects, tools, automated tests and benchmarks in the same "solution" (kind of a project-of-projects). When working on the product, you load that solution, and you have all dependencies in one place, right in the Solution Explorer. This eases navigation and minimizes dependencies whose source code you cannot (easily) see.

After you make your changes, you deploy to the local database (we use the free SQL Server Developer Edition, other databases have their own free editions), run/debug tests, run/debug clients as needed. You can even start debugging a C# test or client application and step into T-SQL stored procedure seamlessly, which can be a very powerful tool for ferreting-out difficult bugs. When you are done with your changes, commit to Git, let the CI double-check the tests and make the build for the the last-row-of-defense manual QA testing. Then deploy, associate the new Git tag to the customer, rinse-and-repeat...

Basically, we treat SQL like every other code, and manage SQL dependencies not fundamentally unlike any other dependencies.

By monkeyelite 2025-09-143:311 reply

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.

By ameliaquining 2025-09-144:532 reply

The textbooks all say that, but is it really true in practice? I suspect it's a bit of holdover conventional wisdom from an earlier era of software. Usually, when I've created a database for a particular app, it was never used by anything else besides that app (and manual queries, for which versioning is mostly not relevant).

You might choose to have a set of views and functions that are versioned separately from your app, for the same reasons you might choose to set up a microservice. But as with microservices, it should be a deliberate decision that you make because you're getting something out of it that outweighs the complexity costs and version-skew headaches; it should not be the default way to do composition.

By baq 2025-09-1411:57

It’s absolutely true. Nobody should be building a microservice architecture for a product without a proven market fit, monolithic development is just faster at this stage, and when you get around to scaling it once you outgrow it (which should be much later than conventional microservice wisdom agrees) the database isn’t the bottleneck anyway and hence stays monolithic (don’t confuse with adding shards).

By monkeyelite 2025-09-151:02

Making two views and deprecating one is actually the simplest possible compatibility strategy - just like adding a new version of a function to a header.

By branko_d 2025-09-144:261 reply

> Only for parameterization over scalar values.

ADO.NET has full support for table-valued parameters.

By ameliaquining 2025-09-145:001 reply

That's part of .NET Framework and therefore legacy, right? Do the database libraries from recent versions of .NET do this?

In any case, it's just one framework; previous comment said "all major languages". And it's useful to be able to abstract and compose over expressions and predicates and such, not just data values, which this still doesn't help with.

By branko_d 2025-09-1414:15

> That's part of .NET Framework and therefore legacy, right? Do the database libraries from recent versions of .NET do this?

ADO.NET is available both in the legacy Windows-only .NET Framework and in the new cross-platform .NET (previously known as .NET Core).

> In any case, it's just one framework; previous comment said "all major languages".

Well, you are not implementing a piece of code in "all major languages" - you can pick the one that fits the problem best.

> And it's useful to be able to abstract and compose over expressions and predicates and such, not just data values, which this still doesn't help with.

You can do that via LINQ - there is even special query-like syntax built right into C# for that that looks like this:

    var companyNameQuery =
        from cust in nw.Customers
        where cust.City == "London"
        select cust.CompanyName;

This does NOT load the entire table in memory just to filter on City. It actually transpiles to SQL which does the filtering on the server.

But anything non-trivial is much better done in SQL proper, IMO. Most of the time, at least for OLTP, you'll be using static SQL - that is you will not need to change the text of the SQL query, just parameters. But dynamic SQL is a thing and can be very useful on occasion - which is string concatenation with all the problems that might bring.

By never_inline 2025-09-141:585 reply

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.

By minitech 2025-09-142:061 reply

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.)

By yencabulator 2025-09-1516:17

I 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/ and convert to string..

By foobazgt 2025-09-143:15

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).

By crazygringo 2025-09-142:142 reply

What's wrong with string concatenation?

By whatevaa 2025-09-149:491 reply

Guaranteed source of bugs in complex cases.

By crazygringo 2025-09-1412:59

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.

By t-writescode 2025-09-142:171 reply

Simpler SQL injection risk and more testing to make sure all potential branching paths don’t result in invalid SQL.

By webstrand 2025-09-143:251 reply

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.

By crazygringo 2025-09-143:45

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.

By sgarland 2025-09-1411:44

SQL has CASE statements, if you’d really like to have all branching logic in pure SQL.

By paulddraper 2025-09-142:011 reply

String concatenation

By Xss3 2025-09-142:08

No, we must build 16 more layers of pointless abstraction in a new DSL.

By chillfox 2025-09-142:12

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.

By dec0dedab0de 2025-09-141:49

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.

By ozgrakkurt 2025-09-1414:33

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

By lmm 2025-09-1411:59

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.