This seems to be the dataloader pattern. There are implementations in many languages, but the idea is that you have a bunch of threads which declare their I/O needs, and then you 1) debounce and merge the requests (uniform access) and 2) cache the results so that later in the graph of calls you don’t need to fetch already loaded data.
Yup! It plays quite nicely with graphql, where you are "resolving" rather than "rendering". It's also a nice place to add any caching logic, because a data loader is essentially saying "give me IDs for as many objects of type X, and I will batch load them for you".
I see all these comments stating 'oh ORMs are bad' and 'just write some SQL'. Yes, you should probably not be afraid of SQL, and yes, using an ORM for everything is probably not great, and no, ORMs aren't a full replacement for writing SQL occasionally, but taking an extremist pro-SQL point-of-view is not doing any favors to this debate.
There are very real reasons why writing raw SQL is a pain. You can make arguments about typesafety of queries, maintainability, mapping into application level data structures/types yadayada. Imho the primary argument is that you cannot efficiently write a query that doesn't suffer from duplication of data. If you have an entity A that has many B's, and that same entity A also has many C's, then if you join on both you now are loading A * B * C rows. This is going to be slow too, and is difficult to detangle.
ORMs in their current form are terrible at tackling this problem, and people are right to call them out for that. "Just" lazy loading or "just" eager loading both do not scale, nor does doing firing event listeners on a per-entity basis. But rawdogging SQL is not a viable solution either, and I wish people advocating that would stop this attitude of 'oh you prissy dev using your puny ORM, not willing to write a line of SQL, look at me with my glorious hair on my chest and superior intellect writing pristine queries'.
This will return A number of (author) rows, where each author row as a `_b` column that is a JSON array of that author's books. And if you asked for C comment rows, then one of the "columns"/indices in each of those book arrays will itself be a nested array of the book's comments.
So all the data gets returned, but with no repetition/Cartesian product.
Hat tip to PostGraphile where I first saw the "cross join later" + "json_agg" technique used. Disclaimer we're not actually running this Joist feature in production yet--just haven't had time to run it through the test suite.
I wasn't aware of Joist until I read a comment of yours somewhere else in this comment section, interesting stuff! I think Joist is the closest thing I've seen to an actual fix to this problem. The way you use the JS event loop as your 'scope' is pretty clever, and satisfies most use-cases I think.
It aligns pretty closely to what we do internally. We don't use classes or annotations to do entity definitions, instead we'll have something like this:
Then the way to query and save is something like this:
datasource.inTransaction(async (orm) => {
const users = await orm.findMany('User', { where: sql`...`, orderBy: sql`email asc` });
// Await in this loop might look bad, but only blocks for the first user
for (const user of users) {
console.log(user.email, (await user.role).name);
}
const adminRole = await orm.findOne('Role', 1); // by ID, throws if not found
return orm.saveMany('User', users.map(u => ({ ...u, role: adminrole }));
});
Which looks different from what Joist is doing, but the fetching mechanics under the hood are very similar I think. Our 'scope' is just somewhat more explicit, since we don't use the event loop mechanics of JS but rather use the inTransaction callback as the boundary.
(The reason we haven't open sourced this yet is that I'd like to iron out some of the ugly parts first, like the string entity identifiers, and the forced usage of an id column, and the lack of type safe where clauses right now)
The fundamental problem is with the wire protocol. It’s inherently tabular, but should actually be more like a binary JSON format, with hierarchical representation for joins to avoid repeating the data.
Better yet, the wire formats should have efficient representations for things like column store compression.
Well, looking at something like the PostgreSQL protocol, it's decently efficient already. You could probably run a layer of compression on top of it, like HTTP gzip or so, but I doubt that will give a significant performance gain.
If you look at the end-to-end problem of 'what is the minimum amount of data I need during this request' vs 'how much data do I fetch, and what is my total latency / number of roundtrips to the db doing so?' I think for most ORM patterns that use lazy loading your primary target is reducing roundtrips, and for most hand rolled queries or ORMs tweaked to do eager loading, the primary target is deduplicating the results.
My take on this is that a decent approximation is a query per relation you're fetching, so if you have 10 entities A in a transaction, and each has 20 entities B attached, ideally you want 2 queries: one for the 10 entities A, and one for the 200 entities B. Lazy loading will give you 1 query for A + 10 queries for B, and eager loading will duplicate the 10 A entities data 20 times each (and that problem gets worse as your graph gets bigger with more one-to-many relations).
Once you run into the raw data transfer between database and backend being the limit, trying to optimize that protocol comes into play, but at least in the use cases I tend to have this is not usually a bottleneck. Besides, I'll typically serialize the data fetched to send out over HTTP again, which essentially has the same challenges if you're not using protobuf or so.
> I'll typically serialize the data fetched to send out over HTTP again
That's a whole other can of worms, so let's just ignore it for a second.
I've found that in a sense, the converse is the case, where back-end protocols significantly pre-date HTTP and "haven't learned the hard lessons" around things like cookies, caching, load-balancing, etc...
Ask yourself this: How many SQL database platforms can provide a redirect to clients? Or handle "client steering" at the load-balancer layer when hosted as a cluster with a partition key? Can any database platforms work with an in-line cache equivalent to a HTTP proxy? Digitally sign responses so that even un-trusted caches can be safely used? Etc...
Sure, some database platforms have solutions for some of those issues, but it's hit & miss at best.
And then, you have the problem that fundamentally all of them return tabular data, or go through some legacy thing like ODBC that expects row-oriented tabular data. If the source data isn't really tabular, it'll get expanded into a tabular form anyway.
At risk of sounding like the Dropbox-guy: it's not that hard to implement these yourself. I've had to write a service mimicking a PostgreSQL database for... reasons, and expanding the Postgres connection protocol to have functionalities for load balancing/caching/cookies would definitely be possible and not even crazy hard, especially if you at some point in your connection can just start proxying to an actual PostgreSQL server so you only have to intercept the handshake.
The bigger question is: what would that solve? Caching is mostly relevant if you are repeatedly executing the same, relatively heavy, query. Load balancing is mostly interesting if you have read-only queries that you can easily execute against any of X replicas, in which case you might as well do that application side instead of having some transparent layer in between. And I don't get why you'd need cookies for something that is a persistent connection instead of individual requests?
The tabular data thing is tricky though, you're right. The impedance mismatch between tables and nested object structures isn't easy to solve.
The reality of the situation is that ODBC isn't going away any time soon, and proposing a breaking change on top of protocols like that is not realistically going to see lots of adoption unless you come up with a really great alternative AND some good marketing around that.
I've come to the conclusion that the best way to spend my efforts is to work with the ecosystem and build tools that try and fix the impedance mismatch is easier than hoping for something better to come along. Making it easier to write performant code is yielding more results for me than trying to adapt SQL-protocols to horizontally scale. A single box with some well placed indexes and well written queries is ridiculously fast.
A lot of ORMs default eager loading to select-in loading with multiple queries because it performs best. First A is loaded and then the B query has a `WHERE B.foreign_key in (...all A id's loaded in the first step..)`.
Still has overhead, but usually less than duplicating data or using another serialization format like returning hierarchical json objects
Depending on what you are doing there might be some duplication that you could remove by creating hash lookups as in this post, but I would reach for Jet first.
They seem quite comparable in terms of features. The way they do relationships is a little different though. Sqlboiler is a little more ORM in its approach with relationships- it adds methods to retrieve relationships and it adds optional fields to its models.
Jet on the other hand expects you to write out a type-safe join and then will load relationships into your struct automatically if you define a struct with the appropriate fields.
I prefer the Jet approach- there's not ORM, just SQL and something that loads SQL results into structs. There is down-side that the loading into structs is not fully type-safe once you write custom structs rather than use generated models although I am not sure if that is even possible in sqlboiler.
sqlboiler shows a lot of examples of using strings for queries which is odd because they seem to support type-safe queries as well.
Some of us just aren't smart enough for sql. I'm perpetually running into the situation where I want to join one table with another that has multiple rows. Like a blog post with tags. Exactly like this: https://stackoverflow.com/questions/8201462/join-with-anothe...
For which the answer is oh, just use GROUP_CONCAT, which isn't even SQL. And I've still got to fix it up by running split when I get it back. Nor does it work particularly well if you're joining against something that isn't a single string column.
So I just bang the rocks together like a caveman and write a second query to go back and fetch all the tags, then loop around assign them to the matching posts.
This seems fundamentally unfixable with current ORMs. You either have to pick between lazy loading per entity, or eager load and carthesian product everything, which breaks badly if you are dealing with multiple one-to-many relations.
Our solution was to write our own ORM-like system that “remembers” which entities you’ve loaded within the context of the transaction, and then will fetch the relation for all of them. So if you access a1.b then it will also fetch a2.b and cache it if you loaded a2 within that same transaction. The call to a2.b will then resolve instantly. So instead of n queries with n being the number of entities loaded in lazy loading, you’re doing n queries with n being the number of relations touched.
The one bad failure case is if you update entities one-by-one but also accessing their relations in a loop since you invalidate the cache for that entity type on every write.
Django (python) does lazy loading by default, cartesian product with "select_related()", and has a third option called "prefetch_related()" where it does only 2 queries then does the join programmatically for you instead of in the database. It's kind of like your custom system except you do have to specify ahead of time which fields to prefetch.
It's also had this for over a decade so I have to wonder how rare it actually is in ORMs in general...
Interesting. Does it do this separate query for all entities in scope, or per entiry like Hibernates SELECT FetchMode? I find a separate SELECT is usually possible, but doesn’t quite solve this in the general case.
Perhaps I missed Django, but as far as I could tell, Hibernate and jooq can’t do this, Ecto can’t do it, ActiveRecord can’t, and the entirety of ORMs for JS can’t (TypeORM, Prisma, Drizzle, Sequelize and a bunch more).
I'm not sure I understand the question. If I'm reading the Hibernate examples right, I already answered that in my first comment: Lazy loading (separate query for each row, 1+N problem) is the default, one additional query per each field with prefetch_related() (1+1 if you only need 1 relationship, 1+2 for 2 relationships, etc).
Django also specifies foreign keys on the model, which are used by name when doing queries. From their examples in prefetch_related() [0], this query would involve 2 levels of lazy loading, except prefetch_related() tells Django to do 3 total queries and join them together in code:
jOOQ doesn't get involved in any such prefetch/eager fetch shenanigans, which are often wrong. They work for some cases and do too little/too much for others. So, specifying the exact data to fetch in every query is a much better approach, ideally with nested collections:
https://blog.jooq.org/jooq-3-15s-new-multiset-operator-will-...
Not really true. You can have separate queries to avoid cartesian explosions. EF Core implements it and its standard practice to use - but not enabled by default, because if you don't order by something unique you'll have a bad time.
I think that's exactly correct. You either do split queries (with more latency) or you do a join (and risk Cartesian explosion). Most ORMs should do this for you.
SQL is easy to understand from the perspective of syntax, but quickly becomes challenging to understand depending on the data model how to actually get what you want out of a query. I have spent many years working in SQL across different server products (PostgreSQL, MySQL, Microsoft SQL Server, and others), and I still sometimes find myself struggling to build the most efficient query for task. Most of the time, it's not SQL that's the problem, it's a broken data model that preceded me (often created by an ORM) that I am forced to work with.
All that said, while it may be painful sometimes, learning it can at least let you poke your ORM more towards the right direction, or give you an opportunity to identify the hot spots where it's worth investing the effort to write a more efficient query directly, much like where sometimes it makes sense to write some subset of an application in assembly for performance, while most of the time you're better off just letting the compiler optimize for you.
For the problem you're talking about above, you're trying to avoid a Cartesian explosion, which is probably best handled through query splitting as you mentioned, but it depends on the database engine how best to approach that. For all its warts, Microsoft SQL Server is pretty good about things like the ability to use query splitting within the context of a single transaction to make it serializable and ensure data consistency (although that can come with its own challenges for performance).
The example you provided is nearly exactly the case where I would expect an ORM to produce a bad query, where-as a direct query build would be significantly more performant because you can incorporate a clear understanding of the data model and use limits and ordering to minimize the size of the result set you need to deal with.
I've heard this all my career. And I went through several phases with SQL including the banging rocks phase.
No, some of us just haven't had time to improve. I wrote my first sql in the early 2000s. I wasn't aware of window functions and lateral joins until 10 years later. Took me another couple of years to start using CTEs.
I don't even write SQL that much. I know enough SQL to make ORMs just do what I want.
Or else, if the post is talking about a "public-facing API resource", can someone tell me why the API wouldn't implement querying for multiple of the same record type at once? It just seems to me that choosing between getting 1 owner, and "get ALL owners" (as TFA puts it), is like a law of the excluded middle
It's tricky because in some cases you might be able to batch all the queries up front, but in others you will only know the IDs you need to fetch after you get one or more intermediate results back and apply business logic to arrive at a decision.
As of today there's no silver bullet beyond having a solid and principles-first understanding of your database and related infrastructure.
This might be true for some APIs but if you're letting the caller control what they fetch, you don't really have that luxury. An extreme example is Graphql-based APIs but even Rest-based APIs might allow some flexibility to decide what they fetch.
That was my first thought as well. Devs will do anything to avoid learning SQL.
You’re spending 20x the code and probably 1000x the time coming up with this nonsense, and for what – so you can say there isn’t any raw SQL in your code base? It’s as ridiculous as denouncing someone for occasionally dropping into Assembly for a specific purpose, or writing a bit of C to call from Python, etc.
(I don't know him personally, but have been following his blog for years.)
What these "just write SQL" rants are missing is encapsulation--let's say you've got a business logic, like "if the account is disabled, render the account name as 'Foo (disabled)'".
You want to write this logic in your preferred backend language, Go/TS/C/etc.
This works fine, in the /account/X endpoint (just load the account, and apply the logic).
But now what about the /accounts/client:Y endpoint (load all accounts, all the logic for all of their accounts)
As time goes by, you end up having 10-20 endpoints that all "return some part of account" as their payload, and you want the same "Foo (disabled)" business logic.
Your options are:
1. Build a single, giant SQL statement that strings together every snippet of business logic applicable to this endpoint (bulk friendly b/c the db is doing all the cross-entity work w/joins)
2. Push the business logic down into the db layer (simple for this, just string concate with an if, but what about anything that is a loop? doable in SQL but tedious)
3. Use your language's abstractions, like functions, to organize the business logic (what Brandur is attempting to do).
Nearly everyone wants to do 3, because 1 doesn't scale as your business logic becomes more & more sophisticated (copy/pasting it around every endpoint's single-giant SQL statements, or pushing it down into the db as views/stored procedures).
> because 1 doesn't scale as your business logic becomes more & more sophisticated (copy/pasting it around every endpoint's single-giant SQL statements, or pushing it down into the db as views/stored procedures).
I think the idea that doing business logic inside the DB is an anti-pattern is cargo culting. You can quite easily store schema definitions, stored procedures, etc. inside version control. You can document it just as you would any other code. You don’t have to see what precisely the call is doing at all times, provided you understand its signature.
Letting the DB do more than just be a dumb data store is a great idea, IMO, and one that is too often eschewed in favor of overly-complicated schemes.
I know putting business logic in the DB has been used very successfully, but it also has some large downsides.
It's harder to express some things as SQL and your team will be less familiar with SQL than your preferred language. SQL language tooling (code autocomplete, testing libs, etc) is also far less mature than tooling for most languages.
It's harder to debug when things go wrong. Where do you put a breakpoint?
It's likely your team ends up splitting your business logic between your app and your DB, and then you have to figure out which part is writing incorrect data when there's a problem.
For my apps, I'm trying to set up the database to reject incorrect information (with types and constraints and sometimes triggers), but have the app code do all the business logic and writing data.
Honestly, it's too soon to tell whether my apps will be successful with this approach. They haven't yet gone through years of real world usage and requirements changing. But so far it's gone well!
This is true, but then, it's also harder to write Rust than Python. There are tradeoffs which are made for every choice.
> and your team will be less familiar with SQL than your preferred language.
Also true, but given how SQL is practically everywhere and is not likely to go away any time soon, I strongly feel that nearly every dev could benefit from learning it.
> SQL language tooling (code autocomplete, testing libs, etc) is also far less mature than tooling for most languages.
Again, true. Personally I dislike autocomplete functions (they break my flow, needing to parse the suggestion and accepting it, rather than just typing from muscle memory), but I get that others like them. Re: testing, I have an unpopular opinion: this matters very little compared to most other languages. Declarative languages like SQL or Terraform generally do not produce surprises. If you get an unexpected output, it's probably because you have incorrect logic. There are some testing frameworks that exist for various flavors of SQL should you wish, but IMO as long as you have some rigorous E2E tests, and your dev/staging environments are accurate and representative of prod, you'll be fine.
> For my apps, I'm trying to set up the database to reject incorrect information (with types and constraints and sometimes triggers), but have the app code do all the business logic and writing data.
Hey, you're doing better than most! I've never understood people's unwillingness to use things like length constraints. Sure, your app should ideally catch basic issues so they never have to make it to the DB, but I'd rather have the guarantee. The last thing you want is for someone to find a way to inject the entirety of Moby Dick into a `name` field.
for 3, you could write a stored proc to handle the various situations, and call that stored proc appropriately, letting the DB engine optimize appending "(disabled)".
however, I do wish Go had a map function ala python map() as opposed to just verbosely writing more for loops or a complicated thread-safe goroutine. Seems almost strange that Google, who popularized the mapreduce model, doesn't want it in Go.
Stored procedures are also screeched at as being anti-patterns, somehow. I don’t get it; you can store them in version control just like anything else, and add comments where necessary in the code base indicating what the DB is doing.
PostgREST for instance avoids the alleged problem by generating a single SQL statement. So does Hasura, PostGraphile, and probably Prisma for that matter.
For detecting rather than preventing duplicates, I'm fond of this pattern in Go:
At "entry" to your code, add a mutable "X was called" tracker to your context. Anywhere you want to track duplicates, insert/increment something in that tracker in the context. And then when you exit, log the duplicates (at the place where you put the tracker in).
It's reasonably implicit, works for both tracking and implicitly deduplicating (turn it into a cache rather than a counter and voila, lazy deduplication*), and it's the sort of thing that all your middle layers of code don't need to know anything about. As long as they forward contexts correctly, which you REALLY want to do all the time anyway, it Just Works™.
*: obviously you can go overboard with this, there are read-after-write concerns in many cases, etc which this article's "prevent by design" structure generally handles better by making the phases of behavior explicit. but when it works, it's quite easy.
If you're using Go, sqlboiler can do this for you in most common cases (e.g., fetch all the Users matching this filter, and for each User, fetch the related Company)
I've always been sort of fond of 1 + 1. It's too often the case that there's a popular query that doesn't even need the child data to function, and unless you have some elaborate caching mechanism it would be a shame to pay the full cost of the join or however you want to implement it.
Making one query that returns the base data and a second that pulls all of the associated data works often enough.
Then it's only when you need to pull M individual records and the associated data that might put you into M + 1 queries, if you can't work out client side grouping for some esoteric reason. But you've reduced the exponent of the fanout by 1, which can hold you for a long time. Years even.
To work around N+1 is to write all your database layer functions in form of getFoosByIds(id[]) instead of getFooById(id). This allows you to easily compose the loads when you have subresources. It's similar to what the author is doing, but does not tear apart the subresources from the parent object.
Pushing the subresource fetching down to the database requires using JOINs and fails badly when you have multiple one-to-many relations in one fetch.
This works even in adhoc loops, i.e. if you have a lifecycle hook** of "after an author changes, do x/y/z logic", and you update 100 authors, every SQL operation invoked by those ~100 individual hooks is auto-batched.
We've been running this in production for ~4 years at this point, and haven't had an N+1 since then (although we didn't initially support auto-batch find queries; that came later).
Of course kudos to dataloader.
*everything --> any queries our "find" API supports, which doesn't do aggregates, sums, havings, etc.
**lifecycle hooks --> yes, a blessing and a curse; we're always attempting to find better/higher-level abstractions for declaring the intent of business logic, than raw/imperative hooks.
Besides pitching Joist, going through OP, I'm not following how verbose the nested examples would get, i.e. ProductLoadBundle is loading "products & widgets".
But what if I need "products & widgets & widget orders". And like sometimes I want "just products & widgets" (2 levels) and sometimes I want "products & widgets & widget orders (3 levels)"?
Would these be the same "ProductLoadBundle" with some conditionals to the Product.LoadBundle method? Would the result of those conditionals be seen in the type-safe as, i.e. sometimes the 3rd level is available, sometimes it is not?
Or would it be two separate bundles, a 2-level Product.LoadTwoLevelBundle and a 3-level Product.LoadWidgetsAndOrdersBundle, which has the pro of better type-safety, but a con of repeating the bundle boilerplate for each unique shape/tree of data your app needs to load.
My guess/hope is that it's the 2nd, if only because I assume brandur also values type-safety over verbosity.
It took me awhile to find again, but this OG scala library in theory handled these adhoc shapes (called "enrichments" in their readme) somewhat elegantly:
Mea culpa another Joist pitch, but TypeScript makes this "give me an adhoc type based on the call-site specific tree of data" super-pleasant, i.e. Loaded<Product, "widgets"> vs. Loaded<Product, { widgets: "orders" }> gets you the two different 2-level vs. 3-level types.
...granted, you just have to accept non-Go/non-native levels of performance., but tradeoffs. :-)
I'm very grateful to this post for introducing me to sliceutils to create a map from a slice. I think that's a very elegant way to create nested models given a parent and child struct.
The problem is not in Go's endless verbosity. The problem is the basic concept of ORMs - that the model you need for efficiently rendering data is the same model that you need for efficiently storing data. ORMs map them 1:1 and that's what results in N+1 queries and all the other problems.
Go's endless verbosity and lack of dynamic features is a blessing, not a curse. Because you have to write your own data access layer, you can break this blatantly false assumption that what you need in the UI is the same as what you need in the database.
To break the N+1 problem, you can do funky stuff like pull back sub-components as array fields in the main component, or pull multiple result sets, or concatenate the sub-component data into a single text field, whatever your UI requires. Because you're not forcing the databases query to map 1:1 with whatever structs you've got inside your application, you can get creative with how you structure the database storage, and how you query that storage. You can create funcs in the database that do crazy shit with the data to return exactly what the UI requires in a custom struct. Because it all runs on the database itself it's fast and painless. Because you have to manually map all the structs in your application yourself (thanks Go!) then you are not constrained to mirror the database structure in your code. Your UI code does what it needs, your database does what it needs, and you can map between them however you want.
ActiveRecord is easy to use, and very concise, which is what it optimises for. Go is not optimising for the same thing. The author is trying to recreate ActiveRecord in Go without realising that ActiveRecord is a straightjacket, and obviously struggling. If you free yourself from those constraints, the world becomes a simpler, better, place.
I think the N+1 problem is overblown. The number of database calls will scale with the volume of data retrieved, but the volume is data retrieved should always be small.
I think it’s underblown. If more people were properly horrified about how much more needless work it causes the DB server, the network, and the client to do, they’d burn it with fire.
In my experience it’s the biggest culprit behind random system fallovers that “work on my machine”. Well sure. Your test page load only resulted in 8 round trips to the server with a hidden O(n^2) in-process merge. The same page on the fully populated prod server required 137 round trips, and it still fails under load even though you can’t physically fit more RAM in the thing.
Agree the DB side is overstated. These particularly suck though in ruby on multithreaded servers (eg puma), where N+1s are constantly giving control of the GVL to another potentially greedy thread. I’m sure python multithreaded servers will suffer similarly.
Oh it definitely isn't. Data volume may be small, but latency will kill you, especially if your database server isn't on the same machine as your application. If you fetch an entity A with a one-to-many to entity B, with a typical size of 100 B's for every A, you are looking at 101 separate queries if you access any relations on B, which assuming 1ms of network latency give you a lowerbound of 100ms for an operation that may have taken well below 10ms in total had you fetched in one go.
That's also 101 entities. That's a half a thousand values if A and B have ~5 attributes. That's too much data. No human can make use of it and no application should offer it. Good ones won't. They'll impose limits and when they do they'll naturally also be imposing a limit on the size of the N+1 problem. That's why I say that in my opinion, it's overblown. You're welcome to form your own opinion, of course.
Happy to name a concrete use case: showing a manifest of containers to load/unload from a ship. Search needs to happen client-side for many reasons, ballpark figures may be 1000 loads, 1000 unloads, the info about the shipvisit, and a stowage plan of 1000 records loading/unloading (separate from the manifest). Give you 4001 individual entities, clocking in at at least 20 attributes per entity (container number, type, port of loading/discharge, cargo description, weight etc)
N+1 just isn't cutting it for that use case, because while a manifest may be paginated, you pretty much just need your entire stowage plan in memory to do any useful operation on it.
If I'm reading you correctly you're saying there's an industrial grade application with a UI that routinely shows up to 80000 attributes to a user, or at least needs that information for client-side processing. Setting aside whether that's justified for this particular use case, do you think that's a common use case in let's say consumer grade web and mobile applications?
No, probably not, but SQL and ORMs aren’t used just for consumer grade applications ;)
I’m sure N+1 is not a big problem in those use cases, but saying that thereforr the problem doesn’t exist is overgeneralizing a bit. B2B is not a small market.
I don't believe I said the N+1 problem doesn't exist. I said that I believe it's "overblown". I'll grant that's vague, and I can be more precise, but one thing I mean by "overblown" is that it's not necessarily a big problem in all cases, even if it's sometimes a problem in some cases. I definitely do not mean that it's never a big problem in any cases.
> The number of database calls will scale with the volume of data retrieved, but the volume is data retrieved should always be small.
Isn't that the point? Repeated network calls will cause latency even if the data is minimal; the total amount of data returned will always be the same, so the "N" extra network calls are pure overhead. For applications where the amount of data is usually small, network calls will likely be the bottleneck.
Again, the number of network calls will scale with the total data payload of the overall request, but the requested total data payload should never be large. It's effectively capped. Therefore, the number of network calls is effectively capped. The N+1 SQL call multiplying factor may be 2, or 10, but it shouldn't be 100 or 1000. That's what I mean when I say I think the problem is overblown. It's not that it isn't a problem--for some people--but that it's not a problem that can grow without bounds.
Put another way, while the N+1 problem scales the number of SQL calls exponentially, it also scales the data payload the same way. Large payloads shouldn't occur and won't occur if we take steps to ensure that they don't. If we do, then we're also pretty much guaranteeing large batches of SQL statements will likewise be limited.
ORMs are such a toxic idea, we'd be better off if we could un-invent them. Look how thoroughly this poor author's mind has been poisoned by long-term exposure.
Please get out there and write some SQL. I promise it won't hurt you.
You absolutely 1000% do not need an ORM to prevent SQL injection. You just need to Not Be Flagrantly Incompetent. As the psycopg2 docs say: never use string concatenation, not even at gunpoint.
You and I may not be, but, not to besmirch coding bootcamps, there are some great employees coming out of them, but not all of them are great. Some of them don't even know SQL, and training them up on it would take an inordinate amount of time, given their background. So it's a lot of trust to put in a team of building the hottest new CRUD app, when "use an ORM" is an easy enough answer.
For queries in the hot path, you can apply a senior dev to optimize, but their time is limited and wants to be used most effectively. From the pushback I've received, this isn't a universally held belief, but the right tool in the right place, there are places for it. You're absolutely right that you don't need one to prevent SQL injection, but just like using Rust simply avoids a class of memory access bugs compared to C++, ORMs just avoid the problem from happening in the first place.
Here’s one impl: https://github.com/graphql/dataloader