9 ms·

You probably don't need query builders

• deleted 2y ago
  [deleted]
• dgan 2y 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!
  • nixpulvis 2y 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.
    • hinkley 2y 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.
  • jprosevear 2y ago
    And ultimately every ORM allows raw SQL if you need to fallback
    • liontwist 2y 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.
  • jpalomaki 2y ago
    Also once you start pasting the SQL together from multiple pieces, risks of SQL injection rise.
    • hinkley 2y 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.
  • agumonkey 2y 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.
    • nixpulvis 2y 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.
      • wredcoll 2y ago
        My experience is this sort of thing is cyclical. You start with one approach, you use it a lot, you start noticing the flaws, oh here's a brand new approach that solves all these flaws... and the cycle repeats.
      • aswerty 2y ago
        From experience, this goes from many little piles of hell to sprawling piles of hell. Obviously the current situation isn't good, but the "collection of interdependent composable SQL builders" will turn into an insane system if you roll things on 5 years. Everybody will yolo whatever they want into the collection, things in the collection will almost match a use cases but not quite and you'll get 90% similar components. Obviously that is just one persons experience. But I'd take a single crazy sql file any day of the year because it's insanity is scoped to that file (hopefully). But I'd agree the random string are no good. Maybe refactoring them into an enum either in the code or in the DB would be a good step forward.
  • camgunz 2y 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.
• nixpulvis 2y 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.
  • catlifeonmars 2y 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?
    • danielheath 2y 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.
      • vips7L 2y ago
        > For established software where performance matters, hand-writing the SQL is reasonable. These things aren’t mutually exclusive though. Every ORM I know gives you an escape hatch to write whatever sql you want. ORMs are great for 90% of things and as a reviewer I don’t need to scrutinize their queries too much. It’s much easier to for me to review an ORM builder query because I know it’s going to do the correct joins on the correct columns. For example in the ORM I use id rather see: query() .where() .eq(“parent”, parent); Instead of: “select * from table join parent on parent.id = table.parent_id where parent.id = :parent”
• scott_w 2y 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.
• orf 2y 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.
  • hinkley 2y 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.
    • catlifeonmars 2y 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 2y ago
    Laterel. Show me correlated subqueries, & I'll take it seriously. The blog reads like exercise in Rust macros writing.
  • MSM 2y 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.
• mojuba 2y 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 2y 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.
• hinkley 2y 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 2y 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.
  • deergomoo 2y 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.
• hyperpape 2y 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/smart-logic https://use-the-index-luke.com/sql/where-clause/obfuscation/... for some related material.
  • hinkley 2y 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 2y 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.
    • hyperpape 2y 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 or 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.
• janlugt 2y 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 https://github.com/tandemdrive/pg_named_args
  • MathMonkeyMan 2y 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/ https://github.com/dgoffredo/namedsql/
• 1270018080 2y ago
  Yeah I'm just going to stick with query builders.
  • Supermancho 2y 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.
• deleted 2y ago
  [deleted]
• hn_throwaway_99 2y 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-bf410349856c 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 2y 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 2y 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)?
  • deleted 2y ago
    [deleted]
  • Macha 2y 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 2y ago
    It’s a commonly used term. https://www.prisma.io/dataguide/types/relational/comparing-sql-query-builders-and-orms https://www.prisma.io/dataguide/types/relational/comparing-s...
• from-nibly 2y 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 2y 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-feature-youre-not-using/ https://www.craigkerstiens.com/2013/11/18/best-postgres-feat...
  • danielheath 2y 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 2y 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.
  • liontwist 2y ago
    views are query composition
• deleted 2y ago
  [deleted]
• oksurewhynot 2y 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.
  • deergomoo 2y 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 2y 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 2y 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.
    • bvrmn 2y 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 2y ago
    I do sometimes miss RQL with RethinkDb. It was a cool database.
• evantbyrne 2y 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"))
  • wredcoll 2y ago
    So now instead of writing sql I have to manually write the AST?
    • evantbyrne 2y 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.
    • bvrmn 2y ago
      Writing AST or an abstract plan/execution tree is not a bad idea. mongodb and elasticsearch are extremely friendly for programmatic query builders.
• lmm 2y 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.
  • lelanthran 2y 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.
  • liontwist 2y ago
    That’s not what a Turing tarpit is. It’s the opposite - a language tailored to a specific useful task.
    • lmm 2y ago
      "Everything is possible but nothing of interest is easy" describes SQL perfectly in my experience.
• sanderjd 2y ago
  Yeah of course you don't need query builders. But maybe you want them?
• davidwparker 2y ago
  Meta - anyone else not seeing a scrollbar on the blog? Chrome on OSX.
  • butz 2y ago
    scrollbar-width: none; - looks like decision to hide scrollbar was intended by author. Shame.
    • defanor 2y 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.
      • mattrighetti 2y ago
        I've hidden the sidebar because I did not like the push-to-the-left it causes when going from certain pages to others. Am I breaking accessibility features for some?
        • defanor 2y ago
          Not exactly accessibility features, but a more general usability, even for people without disabilities. I personally noticed the lack of a scrollbar when I wanted to check how far through the article I am, but another common use case for those is to actually scroll through the document (particularly for skimming of larger documents, for which mouse wheel, space bar, or Page Down key are too slow, or in more rare situations when they are not easily available). Unfortunately tinkering with visual presentation tends to conflict with the principle of least surprise, user settings, or even basic functionality.
        • lewiscollard 2y ago
          The push-to-left doesn't matter. Most people will read one thing on the site so they're not navigating between pages anyway, most of the remainder aren't clicking through pages fast enough to even notice the jump, most of those that do notice it know how scrollbars work, and the remainder is you :) I guarantee nobody will complain about the jump, but they will (did) complain about disabling basic browser functionality. If the jump really bothers you, you can replace your rule with html { overflow-y: scroll } which should force scrollbars to appear on every page whether they're needed or not. But you don't need it.
• econ 2y 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.
• deleted 2y ago
  [deleted]
• deleted 2y ago
  [deleted]
• nemothekid 2y 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.
• cookiengineer 2y 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 https://github.com/sqlmapproject/sqlmap
  • bvrmn 2y ago
    > but sanitization is completely ignored in the post, and should at least be mentioned Why do you need a sanitization for bind parameters?
    • cookiengineer 2y 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/query_builder.rs https://github.com/launchbadge/sqlx/blob/main/sqlx-core/src/...
      • bvrmn 2y ago
        I clearly don't understand something about implications. Could you please elaborate or give a link to read about it? What is branch correctness? How could it be exploited? How does sanitization prevent it? sqlx looks like a usual builder, I don't see nothing criminal about it.
      • c00kien1gg3r 2y ago
• Tainnor 2y 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).
• dagss 2y 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 2y 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.
• bvrmn 2y 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 https://github.com/baverman/sqlbind
  • murkt 2y ago
    That is a really neat library! Can see myself using it quite a lot.
    • bvrmn 2y 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.
      • murkt 2y ago
        Haven’t seen a mention in readme, but I’ve found in the sources some nicety for INSERT/VALUES too!
        • bvrmn 2y ago
          BTW, if you are interested there is an application of sqlbind in a non reporting context (https://github.com/baverman/wadwise/blob/main/wadwise/model.py https://github.com/baverman/wadwise/blob/main/wadwise/model....) It's a small app with an attempt (and a goal) to model persistence without ORM. I think it suits quite well, could be fully type hinted (with some raw force though) and somewhat less verbose in this particular context. But also I see how it could be a maintenance hell for a medium/large scale apps.
• deleted 2y ago
  [deleted]
• peteforde 2y ago
  A strong reminder that you'd have to yank ActiveRecord from my cold, dead hands.
  • yxhuvud 2y 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 2y 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.
• aswerty 2y 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.
  • lelanthran 2y 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.
  • mattrighetti 2y 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 2y 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 2y 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.
  • liontwist 2y ago
    > each user only having permission to see parts of the database This is easily accomplished with a view, or RLS.
• pkstn 2y ago
  Definitely not, if you use modern db like MongoDB :D
• deleted 2y ago
  [deleted]
• pipeline_peak 2y 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 2y 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/ https://sqlkata.com/
• hk1337 2y 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 2y 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 2y ago
  Beautifully designed blog.