You probably don't need query builders

https://mattrighetti.com/2025/01/20/you-dont-need-sql-builders

by mattrighetti • 2 years ago

151 152 2 years ago

dgan• 1 year ago

Well. Query builders are composable. You can create a builder with partial query, and reuse in many queries. With sql strings, you either have to copy paste the string, or to define sql functions. It's a trade off!

nixpulvis3• 1 year ago

Good point, even though copying strings isn't hard. Figuring out where in the string to inject new parts isn't always as easy. You end up with `select_part`, `where_part`, etc.

hinkley5• 1 year ago

Making identical updates to copies of the strings when a bug is discovered is hard though. People who act like it isn’t hard create most of the evidence that it is.

jpalomaki• 1 year ago

Also once you start pasting the SQL together from multiple pieces, risks of SQL injection rise.

hinkley5• 1 year ago

I don’t know if it’s still true but some databases used to be able to process prepared statements more efficiently. We ran into a bottleneck with Oracle 9i where it could only execute queries currently in the query cache. Someone fucked up our query builder so a bunch of concurrent requests weren’t using the same prepared statement and should have been.

Which I specifically told him not to do.

jprosevear• 1 year ago

And ultimately every ORM allows raw SQL if you need to fallback

liontwist4• 1 year ago

You need a fallback for anything involving joins or column renaming.

SQL queries do not return graphs of objects, they return arrays of rows.

agumonkey• 1 year ago

Tried to explain ORM composability at work (without praising ORM like a fanatic), most didn't care, someone said to pass context dicts for future open-nes... weird.

nixpulvis3• 1 year ago

Having had the same argument at work in the past, I feel your pain. Trying to migrate away from massive SQL files and random strings here and there to a collection of interdependent composable SQL builders is apparently a tough sell.

camgunz• 1 year ago

My last job had a strong "no query builders or ORMs" policy, but of course then we wanted composability, so we had in-house, half-baked implementations of both that were full of injection bugs and generated incorrect queries with miserable performance.

That's not to say there's never a place for "keep your queries as SQL files and parameterize them", just that I think your point is 100% valid: if you're unaware you're making tradeoffs, you'll at some point experience some downsides of your chosen system, and to alleviate those you might start building the system that would fit your use case better, totally unaware of the fact that you eschewed an existing, off the shelf system that would do what you want.

nixpulvis3• 1 year ago

`push_bind` covers a good deal of the concerns for a query builder, while letting us think in SQL instead of translating.

That said, an ORM like ActiveRecord also handles joins across related tables, and helps avoid N+1 queries, while still writing consistent access to fields.

I find myself missing ActiveRecord frequently. I know SeaORM aims to address this space, but I don't think it's there yet.

catlifeonmars2• 1 year ago

These (avoid N+1, join across related tables) seem like problems that could be solved by writing the SQL by hand. Is it that much of a lift to treat the database like an API and just write a thin library/access layer around it?

ORMs seem like they are a good fit for dynamic queries, where the end user, not the programmer, are developing the models.

Maybe I’m missing the point?

danielheath2• 1 year ago

For established software where performance matters, hand-writing the SQL is reasonable.

Hand-writing SQL for, say, a faceted filtering UI is a tedious job that takes most of a day in even fairly simple cases, or about 20 minutes with a decent ORM.

ActiveRecord (and related libraries like ActiveAdmin) are _amazing_ for rapid prototyping - eg if you don't even know whether you're going to end up keeping the faceted search.

scott_w• 1 year ago

I don’t get the point of this article. Just reading the samples, I strongly dislike this query builder because it looks flaky and difficult to parse by eye. And the examples get worse and worse.

This isn’t an argument against query builders, that just seems like an argument to make your query builder easier to use and understand. I wouldn’t argue against programming languages by picking bad C++ libraries.

orf2• 1 year ago

All of these are simple, almost unrealistic queries. Show me how to handle optional joins in the filter.

My naive-self in the past used to create a fancy custom deserializer function that transformed 11,22,33,44 from a String into a Vec<i64> and that is useless work that could have easily been handled by the database.

Great, now the database has no idea what the cardinality of the IN clause is and has to generate a sub-optimal plan, because it could be 1 or it could be 10000.

The same for a lot of the other examples.

hinkley5• 1 year ago

This article just gives me the impression that the Rust query builder has terrible DevEx.

Why is adding the clause and binding variables two calls and not one? The lack of variadic functions makes this clunky but you could limit people to 3 binds per clause and that would cover 95% of people. Formatting and cognitive load both crap out around 3 anyway.

catlifeonmars2• 1 year ago

While clunky to implement, variadic calls is mostly a solved problem in rust by way of tuples and macro rules. It’s ugly to implement as a library author (generate K different declarations using macro expansions, where K is the limit of the variadic expansion), but IMO that’s secondary to the library user experience, which is fine with this approach.

tucnak• 1 year ago

Laterel. Show me correlated subqueries, & I'll take it seriously.

The blog reads like exercise in Rust macros writing.

MSM• 1 year ago

Depends what you mean by optional join- if you mean you want the flexibility to build up a list of columns you actually want data from on the fly, you would probably have good results from doing left joins to the tables that contain those columns while ensuring you have a unique column (PK, Unique constraint) you're joining to. Your query engine should be smart enough to realize that it can avoid joining that table.

The logic is that you've ensured the query engine that it'll never return more than one row from the optional table so if you're not returning any actual columns from that table there's no need to think about it. Without the unique constraints the query engine has no idea how many rows may be returned (even if you aren't selecting the data) so it still needs to go through the work.

orf2• 1 year ago

I mean a series of joins that are conditional on a user filter. For example, users with orders with invoices that are paid in the last 10 days.

Anyone can string-concat simple one table queries. That’s fine. The moment you add any non-trivial joins it gets exponentially more complex.

mojuba• 1 year ago

You probably don't. For the same reason you don't need a builder for writing Rust programs. You just write Rust programs.

sebazzz• 1 year ago

Using the OR approach can actually cause some headaches. It can cause SQL Server to make an suboptimal plan for the other queries which have the same query text but due to the parameters behave completely different.

hinkley5• 1 year ago

Eventually people will have enough of Little Bobby Tables and url spoofing and then query engines won’t allow string concatenation at all.

The only alternative I know of is to make a query engine that exactly emulates the String Interpolation syntax of the host language and can detect string concatenation in the inputs.

But the problem with non-builders is always going to be GraphQL and advanced search boxes, where there are any of a couple dozen possible parameters and you either build one query that returns * for every unused clause or you have a factorial number of possible queries. If you don’t use a builder then Bobby always shows up. He even shows up sometimes with a builder.

electronvolt• 1 year ago

I mean, in C++ (17? 20? Whenever constexpr was introduced) it's totally possible to create a library that allows you to build a SQL query via the language's string concatenation libraries/etc., but only allows you to do it with static strings unless you use ~shenanigans. (C++ unfortunately always allows ~shenanigans...)

I guess you do wind up needing to potentially re-implement some basic things (or I guess more complex, if you want format string support too). But for basic string concatenation & interpolation, it's reasonable.

That's a pretty useful way to get basic string concatenation while also preventing it from creating opportunities for SQL injection.

For example, you have a class that requires a constexpr input & can be appended to/concatenated/etc.:

SqlStringPart(constexpr ...)

operator+(SqlStringPart ...)

(so on)

And you have a Query API that only takes SQL string expressions that are built out of compile time constants + parameters:

SqlQuery(SqlStringPart ..., Parameters ...);

This doesn't solve the problem mentioned in the article around pagination & memory usage, but at least it avoids letting someone run arbitrary SQL on your database.

deergomoo2• 1 year ago

He even shows up sometimes with a builder

Something I’ve ran into a lot over the years is people not realising that (at least in MySQL) prepared statement placeholders can only be used for values, not identifiers like column names.

Because many query builders abstract away the creation of a prepared statement, people pass variables directly into column fields and introduce injection vulns.

Number one place I see this is data tables: you have some fancy table component where the user can control which columns to see and which to sort by. If you’re not checking these against a known good allow list, you’re gonna have a bad time.

RadiozRadioz• 1 year ago

Yes, it varies by database whether placeholders can be used for table names.

Personally I find table names sufficiently non-dynamic that an enum of accepted values & string concatenation works. Whenever I've wanted to template user input into table names, I've realised that I should probably refactor my schema or add some views.

kevingadd• 1 year ago

Your post made me realize that custom string interpolation handlers from C# could probably be used to do injection-safe SQL querying (https://learn.microsoft.com/en-us/dotnet/csharp/advanced-top...). Makes me wonder whether scenarios like that were considered when they built that feature or if it's just a fun coincidence from making string interpolation extensible.

neonsunset• 1 year ago

This is already a thing: https://learn.microsoft.com/en-us/ef/core/querying/sql-queri... although it uses an older API - https://learn.microsoft.com/en-us/dotnet/api/system.formatta...

Interpolated string handlers are a newer API made primarily with customization and efficiency of interpolation in mind. They are more complicated to implement and use but enable zero-cost and/or zero-allocation use cases and performing custom behavior on appending literals or data. They are also used to implement the default string interpolation handler and a few auxiliary ones like Append handler for a string builder which writes directly into it, bypassing intermediate string construction.

It's quite a mouthful to say though but very useful for advanced scenarios. Haven't seen any other language provide anything comparable.

wredcoll3• 1 year ago

Javascript added 'tagged templates' a while ago that allows you write a function that gets passed the 'parse tree' of a template string, so js libs do a lot of 'interpolation magic' for sql queries

ericvsmith• 1 year ago

And for a similar idea in Python see https://peps.python.org/pep-0750/

hyperpape3• 1 year ago

The recommended approach is to generate SQL that looks like:

    SELECT \* FROM users
    WHERE id = $1
        AND ($2 IS NULL OR username = $2)
        AND ($3 IS NULL OR age > $3)
        AND ($4 IS NULL OR age < $4)

It's worth noting that this approach has significant dangers for execution performance--it creates a significant chance that you'll get a query plan that doesn't match your actual query. See: https://use-the-index-luke.com/sql/where-clause/obfuscation/... for some related material.

hinkley5• 1 year ago

This may be a situation where you classify the parameters and create a handful of queries that include or exclude each class.

The same way we always have a distinct query for selecting by ID, you have one with just username, one with demographics, one with account age or activity ranges, and then C(3,2) combinations of categories.

jasode• 1 year ago

WHERE id = $1
It's worth noting that this approach has significant dangers for execution performance

This extra WHERE id=$1 clause makes it behave different from the slow examples you cited from the Markus Winand blog. The query planner should notice that id column is a selective index with high cardinality (and may even be the unique ids primary key). The query optimizer can filter on id to return a single row before the dynamic NULL checks of $2,$3,$4 to avoid a full-table scan.

The crucial difference is the id clause doesn't have an extra "OR id IS NULL" or "$1 IS NULL" -- like the blog examples.

hyperpape3• 1 year ago

You are right. If that’s the query you need to write, you’ll be ok.

That said, I don’t think I’ve ever had occasion to write a query quite like that. I’ve written

  select * from blah where id in (1,2,3…) and condition

  select * from blah where condition1 and condition2

but never a query quite like this. Do you know of use cases for it?

Given that most queries don't look like that, I think my criticism is reasonable. For most use cases, this query will have performance downsides, even if it doesn't for some very narrow use-cases.

cwillu• 1 year ago

Note that null is being used here in the “unknown value” sense in order to combine several possible queries into one query plan. Which is a bad idea for a query you need to be performant: $1 can be null (which is possible in the original stackoverflow question that this blog post is a reaction to), and if the parameters are passed in via a bound argument in a prepared statement (not uncommon), then the query plan won't necessarily be taking the passed parameters into account when deciding on the plan.

nycdotnet3• 1 year ago

Agree. With patterns like this you are leaning on your db server’s CPU - among your most scarce resources - versus doing this work on the client on a relatively cheap app server. At query time your app server knows if $2 or $3 or $4 is null and can elide those query args. Feels bad to use a fast language like Rust on your app servers and then your perf still sucks because your single DB server is asked to contemplate all possibilities on queries like this instead of doing such simple work on your plentiful and cheap app servers.

pocketarc• 1 year ago

This is actually an incredible way of articulating something that's been on my mind for quite a while. Thank you for this, I will use this.

The received wisdom is, of course, to lean on the DB as much as possible, put all the business logic in SQL because of course the DB is much more efficient. I myself have always been a big proponent of it.

But, as you rightly point out, you're using up one of your infrastructure's most scarce and hard-to-scale resources - the DB's CPU.

BeefWellington3• 1 year ago

I've seen many devs extrapolate this thinking too far into sending only the most simple queries and doing all of the record filtering on the application end. This isn't what I think you're saying -- just piggybacking to try and explain further.

The key thing here is to understand that you want the minimal correct query for what you need, not to avoid "making the database work".

The given example is silly because there's additional parameters that must be either NULL or have a value before the query is sent to the DB. You shouldn't send queries like:

    SELECT \* FROM users
    WHERE id = 1234
        AND (NULL IS NULL OR username = NULL)
        AND (NULL IS NULL OR age > NULL)
        AND (NULL IS NULL OR age < NULL)

But you should absolutely send:

    SELECT \* FROM users
    WHERE id = 1234
        AND age > 18
        AND age < 35

kamma44342• 1 year ago

While this is no excuse for sending sloppy queries to the database server, my rule of thumb with databases - as I was told by my elders - is ”if it can be reasonably done in the database, it should be done by the database”. Data base engines are meant to be quite performant at what they do, possibly more than your own code.

BeefWellington3• 1 year ago

If your column named simply `id` isn't a unique index you've gone very wrong.

The rest of the query plan probably won't need much power.

hyperpape3• 1 year ago

https://news.ycombinator.com/item?id=42825171

hobs2• 1 year ago

It really depends on your query engine, this would be considered a "catch all query" in SQL Server, and you're going to have really bad parameter sniffing blowing you up, you do not want to do this usually.

crazygringo4• 1 year ago

AFAIK, that's only a risk if you're using prepared statements.

If you're just running each query individually, the parser should be smart and ignore the Boolean clauses that can be skipped, and use the appropriate indexes each time.

But yes, if you're trying to optimize performance with prepared statements, you almost certainly do not want to follow this approach if any of columns are indexed (which of course you will often want them to be).

rw-access• 1 year ago

I have hundreds of queries like this in production (Go + Postgres + pgx), and don't have issues leveraging the right indexes. Make sure when using prepared statements, you have custom query plans per query for this via `SET plan_cache_mode = force_custom_plan`.

These optimizations are trivial for Postgres to make optimization/plan time, so there's no runtime hit. But as always, profile and double check. You're definitely right that assuming can get you in trouble.

I don't have experience with the other databases to speak to their quirks. But for my specific setup, I haven't had issues.

janlugt• 1 year ago

Shameless plug, you can use something like pg_named_args[0] to at least have named instead of numbered arguments in your queries.

[0] https://github.com/tandemdrive/pg_named_args

MathMonkeyMan• 1 year ago

I will jump on the plug train with my [namedsql][0] for use with the Go standard library.

[0] https://github.com/dgoffredo/namedsql/

1270018080• 1 year ago

Yeah I'm just going to stick with query builders.

Supermancho• 1 year ago

Avoid pushing business logic into SQL. Broadly, if a solution is "be more sophisticated or nuanced", you're raising the bar for entry. That's one of the worst things to do for development time across a set of collaborators.

hn_throwaway_99• 1 year ago

There was an essay a couple years ago that really convinced me to not use query builders, https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41... , and from that I switched to using Slonik (by the author of that blog post). There were some growing pains as the API was updated over the years, especially to support strong typing in the response, but now the API is quite stable and I love that project.

andix• 1 year ago

I completely disagree. I love .NET Entity Framework Core. It's possible to build queries in code with a SQL-like syntax and a lot of helpers. But it's also possible to provide raw SQL to the query builder. And the top notch feature: You can combine both methods into a single query.

Everything has it's place though. Query builders and ORMs require some effort to keep in sync with the database schema. Sometimes it's worth the effort, sometimes not.

andybak• 1 year ago

I assumed this meant "graphical query builders" (and who exactly is defending those!)

Is this term Rust specific or have I slept through another change in terminology (like the day I woke up to find developers were suddenly "SWE"s)?

Macha• 1 year ago

It's definitely not Rust specific, or even that new. Certainly I was hearing things (e.g. SQLAlchemy's lower level API) referred to as query builders 15 years ago.

reshlo• 1 year ago

It’s a commonly used term. https://www.prisma.io/dataguide/types/relational/comparing-s...

from-nibly• 1 year ago

SQL isn't composable. It would be great if it was, but it isn't. So we can use query builders or write our own, but we're going to have to compose queries at some point.

rented_mule• 1 year ago

Common Table Expressions enable a lot of composability. Using them can look like you're asking the DB to repeat a lot of work, but decent query optimizers eliminate much of that.

https://www.craigkerstiens.com/2013/11/18/best-postgres-feat...

danielheath2• 1 year ago

Common table expressions do exist, and they compose ~alright (with the caveats that you're limited to unique names and they're kinda clunky and most ORMs don't play nice with them).

somat• 1 year ago

How composable do you want it?

When I want to make a complicated intermediate query that other queries can reference I create it as a view.

I will admit that future me hates this sometimes when I need to dismantle several layers of views to change something.

And some people hate to have logic in the database, Personaly I tolerate a little logic, but understand them who don't like it. any way, common table expressions (with subquery as ()...) are almost as composable as views. and have the benefit of being self contained in a single query.

liontwist4• 1 year ago

views are query composition

oksurewhynot• 1 year ago

I use SQlAlchemy and just generate a pydantic model that specifies which fields are allowed and what kind of filtering or sorting is allowed on them. Bonus is the resulting generated typescript client and use of the same pydantic model on the endpoint basically make this a validation issue instead of a query building issue.

deergomoo2• 1 year ago

I recently used SQLAlchemy for the first time and was delighted it has something I’ve always wanted in Laravel’s Eloquent: columns are referenced by identifiers on the models rather than plain strings.

Seeing `.where(Foo.id == Bar.foo_id)` was a little jarring coming from a language where `==` cannot be anything but a plain Boolean comparison, but it’s nice to know that if I make a typo or rename a field, it can be picked up statically before runtime.

maximilianroos• 1 year ago

SQL is terrible at allowing this sort of transformation.

One benefit of PRQL [disclaimer: maintainer] is that it's simple to add additional logic — just add a line filtering the result:

  from users
  derive [full_name = name || ' ' || surname]
  filter id == 42           # conditionally added only if needed
  filter username == param  # again, only if the param is present
  take 50

anonzzzies• 1 year ago

I never looked into prql; does the ordering matter? As if not , that would be great; aka, is this the same:

  from users
  take 50
  filter id == 42           # conditionally added only if needed
  filter username == param  # again, only if the param is present
  derive [full_name = name || ' ' || surname]

? As that's more how I tend to think and write code, but in sql, I always jump around in the query as I don't work in the order sql works.

I usually use knex or EF or such where ordering doesn't matter; it's a joy however, I prefer writing queries directly as it's easier.

bvrmn8• 1 year ago

`take 50` of all `users` records in random order and after filter the result with username and id? I hope it's the right answer and prql authors are sane.

withinboredom• 1 year ago

I do sometimes miss RQL with RethinkDb. It was a cool database.

evantbyrne2• 1 year ago

The lack of expressiveness in query builders that the author refers to in their first post as a motivation for ditching them is an easily solvable problem. It seems like most ORMs have easily solvable design issues though, and I would definitely agree that you should ditch tools that get in your way. What I've been doing is sporadically working on an _experimental_ Golang ORM called Trance, which solved this by allowing parameterized SQL anywhere in the builder through the use of interfaces. e.g.,

    trance.Query[Account].Filter("foo", "=", trance.Sql("...", trance.Param("bar"))

wredcoll3• 1 year ago

So now instead of writing sql I have to manually write the AST?

evantbyrne2• 1 year ago

The whole query builder is an optional abstraction built onto the ORM. It has many benefits, but will also never completely support every SQL variant in existence, as is true with all query builders. so I feel as though the only responsible way to build ORMs is with escape hatches, and this is one of them.

bvrmn8• 1 year ago

Writing AST or an abstract plan/execution tree is not a bad idea. mongodb and elasticsearch are extremely friendly for programmatic query builders.

lmm2• 1 year ago

    CASE
        WHEN $2 BETWEEN 0 AND 100 AND $1 > 0
            THEN (($1 - 1) * $2)
        ELSE
            50
    END

What a wonderful, maintainable language for expressing logic in /s. Perfect for my COBOL on Cogs application.

The problem with SQL has never been that it's impossible to put logic in it. The problem is that it's a classic Turing Tarpit.

lelanthran2• 1 year ago

Why the `/s`?

That's neither more nor less comprehensible than what I often see in python's built-in DSL within list comprehensions.

At least the SQL variant has the excuse of being designed back when language design was still in its infancy. The madness in Python's list comprehensions and the ad hoc DSL in ruby has no such excuse.

liontwist4• 1 year ago

That’s not what a Turing tarpit is. It’s the opposite - a language tailored to a specific useful task.

lmm2• 1 year ago

"Everything is possible but nothing of interest is easy" describes SQL perfectly in my experience.

sanderjd• 1 year ago

Yeah of course you don't need query builders. But maybe you want them?

davidwparker• 1 year ago

Meta - anyone else not seeing a scrollbar on the blog? Chrome on OSX.

butz• 1 year ago

scrollbar-width: none; - looks like decision to hide scrollbar was intended by author. Shame.

defanor2• 1 year ago

Indeed. I was about to write about it to the author by mail, but noticed that he posted this link, and probably will read these comments, so can as well provide the feedback by joining this subthread: at least a few readers are not happy about our scrollbars being hidden.

econ• 1 year ago

With only 4 optional Params you can just have 15 queries. Heh

I remember back when everything was someone's idea and others would both compliment it and improve it. Now it is like things are unchangable holy scripture. Just let `Null < 42 or Null > 42 or name = Null` all be true. What is the big deal? I can barely wrap my head around joins, the extra luggage really isn't welcome. Just have some ugly pollyfills for a decade or so. All will be fine.

nemothekid• 1 year ago

The use of `push_bind` here is strange to me. The idomatic way would be do something like:

    let mut builder = Query::select();

then you could (optionally) add clauses like so:

    builder.and_where(Expr::col("id").eq("A"))

it shouldn't matter if a where clause exists or not, the builder should figure that out for you.

If you are going to treat your QueryBuilder as glorified StringBuilder, then of course you won't see the value of a QueryBuilder.

cookiengineer2• 1 year ago

Ah yes, the SQL injection cycle begins anew. A solved vulnerability for decades, only for the new generation of junior devs to ignore wisdom of the old generation again and introduce it anew.

Don't ever do this. Query builders exist to sanitize inputs in a failsafe manner. SQL has so many pitfalls that tools like sqlmap [1] exist for a reason. You will never be able to catch all encoding schemes in a regex approach to filter unsanitized input.

The examples in the blog can be exploited with a simple id set to "1 or 1=1;--" and is literally the very first web exploitation technique that is taught in highschool-level CTFs.

sqlx can mitigate a lot of problems at compile time, but sanitization is completely ignored in the post, and should at least be mentioned. If you recommend to juniors that they don't need a query builder, tell them at least why they existed in the first place.

[1] https://github.com/sqlmapproject/sqlmap

bvrmn8• 1 year ago

but sanitization is completely ignored in the post, and should at least be mentioned

Why do you need a sanitization for bind parameters?

cookiengineer2• 1 year ago

Because type correctness does not imply branch correctness. SQL has side effects of interpretation, and any string/query builder that is not aware of grammatical implications should be avoided in my opinion.

Check the query builder of sqlx [1]

[1] https://github.com/launchbadge/sqlx/blob/main/sqlx-core/src/...

Tainnor• 1 year ago

I don't know Rust well, is this what's known as a query builder in Rust? That's weird to me, because in other typed languages that I know, query builders are typically typesafe and don't just concatenate strings (see e.g. jOOQ for the JVM).

dagss3• 1 year ago

At least for MSSQL: Never do this (before learning about query caches). Or at least, if you do, add (option recompile) to the query.

For each combination of parameters to search for you may want to use a different index.

But... the query plans are cached by query string lookup!

So it is imperative that your search string looks different for each query plan/index being used.

The code suggested here will pick a more or less random index (the one optimized for the parameters of the first execution) and stick with it for remaining executions, leading to bad queries for combinations of non-null that doesn't match the first query.

You could just add a comment inside the string that was different depending on what parameters are null, but that is no less complex than just generating the query.

PS: Of course there are situations where it fits, like if your strategy is to always use the same index to do the main scan and then filter away results from it based on postprocessing filters. Just make sure to understand this issue.

gulikoza• 1 year ago

This ^

I debugged an app a couple of years ago that from time to time brought entire MSSQL down. The server had to be physically restarted. Nobody could figure out for years what was going on, all the queries had been analyzed and checked for missing indexes, everything was profiled... Except when an app generated a query like this which did not go fine through the cached plan.

bvrmn8• 1 year ago

It seems article shows the opposite argument. SQL builders are useful not to write fragile raw sql ridden with noisy filter patterns with repeated numbered placeholders which could be easily broken on refactoring. Also it's impossible to compose queries with abstracted parts.

Shameless plug: https://github.com/baverman/sqlbind

murkt4• 1 year ago

That is a really neat library! Can see myself using it quite a lot.

bvrmn8• 1 year ago

Yep, it's quite handy for complex reporting. An original motivation was to disentangle a 1.5k lines (SQL + corresponding python code to assemble the query) of almost identical clickhouse queries. There were two big almost similar data tables with own dictionary relations. And there is a bunch of reporting with complex grouping and filtering. Around of 20 report instances. Each report was a separate monstrous SQL.

Schema changes were quite often and reports started to rot up to the point of showing wrong data.

After refactoring it became 200 lines and allows to query more reports due to increased generality.

peteforde• 1 year ago

A strong reminder that you'd have to yank ActiveRecord from my cold, dead hands.

yxhuvud• 1 year ago

Even then, if you actually need a good query builder for Ruby that manages more complex cases than AR does, then Sequel is there and is so, so powerful.

riiii• 1 year ago

You don't need them until you do. And when you do, you might first think that you can just hack your way around this minor inconvenience.

Then you'll eventually learn why the road to hell is paved with good intentions.

aswerty2• 1 year ago

I see a lot of push back against this approach. And since it is something I've been experimenting with recently, this is pretty interesting stuff. Clearly it has issues with query planning getting messed up, which is not something I had been aware of since my DB size I've been experimenting with is still only in the 10s of thousands of rows. But...

Using raw SQL file addresses:

  1. Very difficult for devs to expose SQL injection vulnerabilities because you need to use parameters.

  2. Having all available filtering dimensions on a query makes it very clear what the type of filtering is for that particular query.

  3. Easy debugging where you can just throw your query into an SQL client and play around with the parameters.

  4. Very clear what the total query footprint of you application is (e.g. files all neatly listed in a dir).

  5. Super readable and editable.

  6. Code for running the SQL is pretty much: here is my query, here are my params, execute.

  7. Etc?

So the amount of good you can get our of this approach is very high IMO.

So an open question to anybody who is more familiar with DBs (and postgres in particular) than myself. Is there a reliable way to address the issue with this approach to querying that you all are flagging as problematic here. Because beyond the query planning issues, raw SQL files (with no building/templating) just seems to me like such a better approach to developing a db access layer.

lelanthran2• 1 year ago

That's basically what I did. No problems on even complex queries when I can use either CTEs or stored procedures if a single statement is sufficient.

mattrighetti2• 1 year ago

Thanks for summing this up! I'm also in the thousands of rows space at the moment and that's probably why I've fallen in the query planning trap that many pointed out.

shkkmo• 1 year ago

This is the kind of anti-pattern that can work on toy or small projects but doesn't scale well to larger projects or groups.

1. Very difficult for devs to expose SQL injection vulnerabilities because you need to use parameters.

You should use parameters either way.

2. Having all available filtering dimensions on a query makes it very clear what the type of filtering is for that particular query.

Code is easier to document well than a SQL query

3. Easy debugging where you can just throw your query into an SQL client and play around with the parameters.

Query builders will give you a query you can do the same thing with.

4. Very clear what the total query footprint of you application is (e.g. files all neatly listed in a dir).

This seems like a design/organization choice that is separate from whether those files are query or code.

5. Super readable and editable.

Doesn't scale as a project grows, you end up with massive unwieldy queries or a bunch of duplicated code across a bunch of files.

6. Code for running the SQL is pretty much: here is my query, here are my params, execute.

It is pretty much the same with a query builder, in either case the 'execute' is calling a library where all the actual stuff happens.

If you know your project is gonna stay small with simple queries and your scope won't creep, raw SQL files might the right choice, but they will create technical debt as the project grows. It's worth the time in the long run to get comfortable with a query builder.

lukaslalinsky• 1 year ago

It's pretty much impossible not to end up with a lot of repeated spaghetti code, if you are doing anything beyond a really trivial single user app.

Even for simple stuff, like each user only having permission to see parts of the database, it's essential to have a systematic way of filtering that is composable.

I'm not a fan of ORMs and I actually like SQL and yet have been using sqlalchemy expression language (the low level part of sqlalchemy) for many many years and i wouldn't really go to SQL strings.

liontwist4• 1 year ago

each user only having permission to see parts of the database

This is easily accomplished with a view, or RLS.

pkstn• 1 year ago

Definitely not, if you use modern db like MongoDB :D

pipeline_peak• 1 year ago

With code like this, there’s nothing in place to prevent injections.

Where I work we use Veracode scans regularly. Trusted 3rd party query builders are necessary to prevent them.

Merad• 1 year ago

It seems to me a big part of the problem is that the "query builder" in TFA is little more than a string builder. In the .Net world I've used SqlKata [0] and been very pleased with it. It allows you to easily dynamically build and compose queries.

0: https://sqlkata.com/

hk1337• 1 year ago

This is weird. When you say “query builder” I’m thinking of something associated with an ORM so it already knows the table specifics and you don’t have to initialize it with “SELECT * FROM table”.

PaulHoule• 1 year ago

The carping about if statements really gets to me.

I mean, I get it, structures like

  if(X) {
    if(Y) {} else {
      if(Z) {
        return;
      } else {}
  ...

will drive anybody crazy. For a query builder though, you should write something table driven where for instance you have a hash that maps query names to either functions or objects

   variables = { "age": where_age, "username": where_username, ... }

these could be parameterized functions, e.g.

   where_username = (operator, quantity) => where("username", "text", operator, quantity)

or you could have some object like

   {field_name: username, field_type: "text"}

and then, say loop over the get variables so,

   username:gt

gets broken into "username" and "gt" functions, and the where_username function gets these as arguments in the operator and quantity fields. Easy-peasy, wins at code golf if that's what you're after. Your "field" can be a subselect statement if you want to ask questions like "how pictures are in this photo gallery?"

This is the kind of code that Lisp wizards wrote in the 1980s, and there's no reason you can't write it now in the many languages which contain "Lisp, the good parts."

gaeb69• 1 year ago

Beautifully designed blog.

You probably don't need query builders

Related Stories