lobsters score: 1 · tags: databases
I think there’s a sweet spot that’s not yet explored in designing query builders. (I scoured the internet to find one a year ago or so, would be happy to be wrong now.)
The query builder should be generated from each target SQL grammar. Yes it doesn’t provide types and many guarantees that usual query builders provide, but it’s better than writing stringly typed queries, has full power of the underlying SQL system and sits in an unoccupied spot in the raw SQL to ORM spectrum for power users.
score 1 · canonical
Something that struck me reading the article is in fact how similar the different examples of query builders are to plain SQL. Which begs the question, why use them in the first place? I mean, on the one hand those DSLs actually require us to be knowledgeable in SQL. On the other hand they prevent using more advanced features of SQL such as CTEs, window functions, RETURNING clauses, etc. Those abstractions are quite leaky. The way those WHERE clauses are put together in some of the examples. Sure, SQL has some warts, but finally what is the value of all those DSLs, apart from letting you put the SELECT clause at the end?
I wrote more of my thoughts on the subject here: https://noteflakes.com/articles/2026-09-05-beyond-orms
score 1 · canonical
I think that the DSLs that simply map to the existing SQL structure (like ActiveRecord) are less useful than ones that create a pipeline abstraction (like FunSQL). IMO the latter is much easier to compose programmatically or build extra helper functions, as each step is just a transform from one schema to another.
There are advantages of having your “main” or “real” language be aware of the queries so you can do cross-references, etc. Doing less string concatenation to build SQL also discourages people from accidentally/unknowningly creating an SQL injection vulnerability.
score 1 · canonical
Yeah the data-oriented (“pipeline”) abstractions are more composable. You can create pre-filtered objects (as in, “this represents the records this user has access to”) and perform queries on those, rather than to ad-hoc filter the output of a full query.
My own experience with this is that it’s difficult to make a truly reusable and generic approach perform well, though! In the end you still have to know the SQL it maps to.
score 1 · canonical
Having to look at what is going on from time to time (but I can ask the generator what is generated, of course), while having better ergonomics of reusing blocks (and avoiding forgetting parts of the blocks I reuse!) sounds like a win to me.
score 1 · canonical
Although a pipeline can be a bit closer to how the database will do the work, and it’s easier to make explicit optimisations (moving filters up and selecting fewer fields early) compared to regular SQL. You’ve probably got to be ready to look at a query plan or profile at some point either way.
score 1 · canonical
That’s what I’ve done for the past few years. I don’t see the point in writing SQL keywords as methods. The only thing that’s tricky is when you need dynamic queries (eg. appending a WHERE filter if a variable is present) but I just write them as static queries instead (
WHERE $1 IS NULL OR foo = $1). Writing raw SQL has made me wayyy better at SQL, I can easily copy paste queries if I want to try them in a DB query tool, and I don’t have surprise regarding the final query since it’s already written.
score 1 · canonical
The only thing that’s tricky is when you need dynamic queries
So what you’re saying is, it’s easy to not use a query builder, so long as you don’t need to do the exact thing query builders are designed to make easier and safer.
Who would have possibly guessed that?
score 1 · canonical
To be fair, the vast majority of queries are not that dynamic in most programs. But it depends heavily on exactly what you’re doing, of course.
You also have to consider the complexity of query builders. The useful ones are typically a huge pile of code, and yet another dependency to manage, often with a big security impact.
score 1 · canonical
To be fair, the vast majority of queries are not that dynamic in most programs.
I think we must work on very different types of program.
Give the history of the web and databases, removing the risk of SQLi by using a query builder in place of ad hoc dynamic query likely negates way more risks than the query builder introduces.
score 1 · canonical
True, the risk historically has been very high. I don’t know why - I blame the ease of string interpolation combined with shitty tutorials and books that teach it that way. But I do know that passing in user-supplied data as separate parameters solves the problem for 90%. The remaining 10% are rare enough that solving them ad-hoc can be done quite safely, as long as you’re in a competent team. With code reviews, those won’t slip through easily.
Also, query builders are not immune to SQLi either. There have been several CVEs in common query builders, and in some cases columns are spliced in unsafely and it’s unaddressed(!). As a concrete example, I remember having to convince ~the Laravel team~Taylor Otwell to add that warning after the first paragraph. He just didn’t take this very seriously, but it meant that anywhere a user could supply a column name (e.g. in an interface that allows sorting on arbitrary columns), it would be injected unsafely. I would be extremely surprised if there were no codebases in the wild affected by this because the authors are unaware, missed that the note (which isn’t present in most tutorials) or wrote the code before the note was added.
score 2 · canonical
But I do know that passing in user-supplied data as separate parameters solves the problem for 90%.
Parameterisation does indeed solve the majority - the problem is that without the query builder, the developer inevitably has to resort to “building” at least some queries themselves - which is arguably more likely to result in issues again.
score 1 · canonical
The useful ones are typically a huge pile of code, and yet another dependency to manage, often with a big security impact.
The good ones are separately a huge pile of code, and separately a small subpile of code with a big security impact.
score 1 · canonical
That’s not what I’m saying at all. It’s tricky because we’re not used to write real SQL when using query builders, but as soon as you learn how to do what you did with a builder with pure SQL, well it becomes trivial.
And no, builders are not safer than writing SQL short circuits or CTEs
score 1 · canonical
It’s tricky because we’re not used to write real SQL when using query builders
Speak for yourself. I quite frequently write ad-hoc queries (yes writing the queries, not asking a fucking robot in 8 iterations of increasingly verbose detail to write it for me). That works perfectly well because I know what I want at the time I’m writing the query.
That isn’t at all the same thing as using a query builder to allow the application to be able to filter on arbitrary columns, or complex value types, or have conditional conditions, or conditional columns returned, or any number of other dynamic aspects that are literally the reason for query builders to exist.
as soon as you learn how to do what you did with a builder with pure SQL, well it becomes trivial.
Ok fuck it, I’ll bite. What’s the “trivial, pure SQL” to take a variable length array/list/set/tuple/<whatever structure your language uses for a bunch of values>, and check if a column is equal to one of them.
i.e convert
['foo', 'bar', 'baz']intoWHERE col IN ('foo', 'bar', 'baz');.
score 1 · canonical
convert [‘foo’, ‘bar’, ‘baz’] into … WHERE col IN (‘foo’, ‘bar’, ‘baz’);.
I do it with
col = ANY($1), and $1is bound as a single valueARRAY[]on the app side (in Go I usepq.Arrayfromlib/pqwhich does this)ANYis easier to handle thanINin this kind of queries
score 1 · canonical
I guess that’s great if your DB supports an array type, and supports it for ANY(), and the language bindings/driver have support for binding arrays natively.
score 1 · canonical
I’m not entirely convinced any of those query builders do anything you couldn’t express in a cleaner way with SQL. Generally speaking, I’d say the Django style of query building is the best one out there in that it actually feels like it takes advantage of using a programming language to express your SQL queries; to express the same sample query in Django, it’d be something like this:
Person.objects.filter(gender_concept_id=8507).order_by('year_of_birth').values_list('person_id', flat=True)[:100]The main advantages I find this has are that filter is just expressed with keyword arguments (making it really easy to create dynamic queries without needing to violate DRY in annoying ways) rather than a messy OR/AND system to express the where clause, that the select equivalent (values_list) is technically optional (it’s usually more of a specific optimization to use it, as you just get a model without it) and that to limit the list, you can use python slices rather than more function stacking.
Not to say Django is perfect though; there’s definitely some warts on it’s ORM. Double underscores for processing filter components are an acquired taste for example. Similarly, annotating queries that are more complex than Sum/Count/Min/Max quickly becomes a headache. But at that point you might as well drop down to SQL anyways, as every query builder and ORM I’ve seen just absolutely shits the bed on clean code for that subject. Same with tricks like CTEs. For almost every CRUD operation (which are tbf most operations on a database), it works fine though.
Oh one final pain points about the Django ORM is how stacking filter/exclude clauses work vs. adding keyword arguments to a single call. Stacking filter clauses works more or less the way you expect; every filter clause filters the query down further, and usually the difference between keyword arguments and more function calls is meaningless. For exclude calls, combining keyword arguments instead creates an AND clause on every exclude keyword argument, so usually you end up needing to stack the function calls. It’s a bit weird but you get used to it.
score 1 · canonical
Agreed that Django ORM is one of the better ones, and agree with most limitations you mention.
Oh one final pain points about the Django ORM is how stacking filter/exclude clauses work vs. adding keyword arguments to a single call.
Q objects make this non-awkward and composable. When I was doing Django, I’d typically use
filterexclusively and use negated Q objects for exclusions.
score 1 · canonical
Person.objects.filter(gender_concept_id=8507).order_by(‘year_of_birth’).values_list(‘person_id’, flat=True)[:100]
The main advantages I find this has are that filter is just expressed with keyword arguments
I don’t use Django (and vary little Python) so I had to look this up, but the “advantage” you’re referring to kind of goes down the toilet once you need anything other than the default “equals” operator, IMO.
This is the first example I found from the Django docs, for the
filtermethod:Entry.objects.filter(pub_date_year=2006)Oh wait, no sorry it was actually this:
Entry.objects.filter(pub_date__year=2006)Which kind of demonstrates my point. The two are not particularly distinctive visually, but they are obviously very different in terms of the SQL that is generated.
Using variadic arguments to define the column of a predicate is interesting, but the part where you chain together a bunch of double underscores and append them to the column name, honestly sounds like the kind of weird shit Wordpress or some other early-2000’s PHP monstrosity would do (and I say that as someone who makes a significant amount of my income from PHP-related projects).
score 1 · canonical