by mattrighetti on 1/21/25, 9:47 AM with 152 comments
by hyperpape on 1/25/25, 9:21 PM
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)
by dagss on 1/26/25, 7:49 AM
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.
by orf on 1/25/25, 9:12 PM
> 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.
by scott_w on 1/25/25, 9:10 PM
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.
by bvrmn on 1/26/25, 8:23 AM
Shameless plug: https://github.com/baverman/sqlbind
by hinkley on 1/25/25, 9:20 PM
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.
by aswerty on 1/26/25, 9:02 AM
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 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.
by lmm on 1/26/25, 1:38 AM
CASE
WHEN $2 BETWEEN 0 AND 100 AND $1 > 0
THEN (($1 - 1) * $2)
ELSE
50
END
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.
by nixpulvis on 1/25/25, 9:03 PM
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.
by dgan on 1/25/25, 9:02 PM
by from-nibly on 1/25/25, 10:23 PM
by maximilianroos on 1/26/25, 12:40 AM
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 50by janlugt on 1/25/25, 9:21 PM
by oksurewhynot on 1/25/25, 11:10 PM
by andybak on 1/25/25, 10:19 PM
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)?
by sebazzz on 1/25/25, 9:13 PM
by nemothekid on 1/26/25, 5:50 AM
let mut builder = Query::select();
builder.and_where(Expr::col("id").eq("A"))
If you are going to treat your QueryBuilder as glorified StringBuilder, then of course you won't see the value of a QueryBuilder.
by mojuba on 1/25/25, 9:13 PM
by davidwparker on 1/26/25, 2:46 AM
by evantbyrne on 1/26/25, 1:32 AM
trance.Query[Account].Filter("foo", "=", trance.Sql("...", trance.Param("bar"))by hn_throwaway_99 on 1/25/25, 10:12 PM
by lukaslalinsky on 1/26/25, 12:32 PM
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.
by econ on 1/26/25, 3:26 AM
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.
by Merad on 1/26/25, 4:12 PM
by andix on 1/25/25, 10:17 PM
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.
by Tainnor on 1/26/25, 7:14 AM
by riiii on 1/26/25, 8:42 AM
Then you'll eventually learn why the road to hell is paved with good intentions.
by hk1337 on 1/26/25, 5:46 PM
by pipeline_peak on 1/26/25, 3:01 PM
Where I work we use Veracode scans regularly. Trusted 3rd party query builders are necessary to prevent them.
by 1270018080 on 1/25/25, 10:06 PM
by PaulHoule on 1/26/25, 7:23 PM
I mean, I get it, structures like
if(X) {
if(Y) {} else {
if(Z) {
return;
} else {}
...
variables = { "age": where_age, "username": where_username, ... }
where_username = (operator, quantity) => where("username", "text", operator, quantity)
{field_name: username, field_type: "text"}
username:gt
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."
by pkstn on 1/26/25, 2:11 PM
by sanderjd on 1/26/25, 2:09 AM
by gaeb69 on 1/27/25, 8:32 PM
by peteforde on 1/26/25, 8:39 AM
by cookiengineer on 1/26/25, 6:59 AM
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.