[HN Gopher] You probably don't need query builders
___________________________________________________________________
You probably don't need query builders
Author : mattrighetti
Score : 43 points
Date : 2025-01-21 09:47 UTC (4 days ago)
(HTM) web link (mattrighetti.com)
(TXT) w3m dump (mattrighetti.com)
| dgan wrote:
| 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!
| nixpulvis wrote:
| 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.
| hinkley wrote:
| 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 wrote:
| Also once you start pasting the SQL together from multiple
| pieces, risks of SQL injection rise.
| hinkley wrote:
| 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 wrote:
| And ultimately every ORM allows raw SQL if you need to fallback
| agumonkey wrote:
| 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.
| nixpulvis wrote:
| `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.
| catlifeonmars wrote:
| 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?
| scott_w wrote:
| 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.
| orf wrote:
| 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.
| hinkley wrote:
| 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.
| catlifeonmars wrote:
| [delayed]
| mojuba wrote:
| You probably don't. For the same reason you don't need a builder
| for writing Rust programs. You just write Rust programs.
| sebazzz wrote:
| 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.
| hinkley wrote:
| 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 wrote:
| 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.
| hyperpape wrote:
| 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.
| hinkley wrote:
| 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 wrote:
| _> 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.
| hyperpape wrote:
| 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
|
| or select \* from blah where condition1 and
| condition2
|
| but never a query quite like this. Do you know of use cases
| for it?
| throwup238 wrote:
| That sounds like the developer use case. Data scientists
| doing ETL and analyzing messy data with weird rules like
| the ones above are common (although the id is usually a
| contains/in to handle lists of rows that don't fit any of
| the conditions but must be included).
|
| I've had to do some weird things to clean up data from
| vendor databases in several industries.
| janlugt wrote:
| 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 wrote:
| 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 wrote:
| Yeah I'm just going to stick with query builders.
| hn_throwaway_99 wrote:
| 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 wrote:
| 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 wrote:
| 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)?
| from-nibly wrote:
| 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 wrote:
| 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...
| danielheath wrote:
| 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).
___________________________________________________________________
(page generated 2025-01-25 23:00 UTC)