N+1 in Ruby on Rails
visuality.pl
visuality.pl
The N+1 query used as an example in the post could have just been a single query.
Rails can optionally use SQL migrations, UUIDs for pks, enforce referential integrity with additional options, and create additional indexes easily. The value of ORMs comes in separating domain data modeling from the peculiarities of a DBMS'es SQL flavor.
An ORM should be used to automate the commonplace rather than as a substitute for understanding how to manage and operate a DBMS.
SQL knowledge is often needed for cleanup and ETL work outside of the monolith. There is no substitute for understanding how things deeper in the stack operate.
Where I do appreciate ORMs is in being able to bridge between the relational world of SQL and the object oriented or functional world of the application. Occasionally I’ll decide an application is stupidly simple and it’s not worth the overhead of an ORM, I’ll just write SQL directly. I regret it every single time, because inevitably I end up implementing a half-baked ORM to make the results usable in the application layer.
And yes, as you say, regardless of an ORM you need to understand the database underneath and the SQL being generated. I’ve seen so many developers who are completely baffled by why their code is running slowly when a quick EXPLAIN will surface the fact they’re doing a sequential scan over several million rows of data.
I've always viewed it more as "we can start fresh with another database with ease" and not "we can migrate from a database to another quickly".
And when I say "start fresh", I don't literally mean starting fresh. It can just mean I write my packages (e.g. Django's "Applications") in a database-independent way and whoever ends up using it can use it with whatever DB they have.
Perhaps your vision is indeed what is often sold, though I never saw it that way.
I remember my engineering manager was freaking out over me using strings instead of symbols because they’re less performant, meanwhile he didn’t know to add indexes to fks lmao.
He clearly cares about performance! Just never came to understand relational databases, which was my day 1 obsession. And doesn’t know how to profile the entire thing to make sure he’s stressing over the things with the largest effect size.
Anyway he got fired and works as a systems guy now. Happy for him lol.
ORMs also usually allow you to drop down to SQL when you have to. They are there to help you, and they expect you to know what you're doing. Like other tools that make so much easy, they do make it easy to shoot yourself in the foot. I won't argue that the average ORM-user is more likely to create shitty-performing code than the average SQL-only-user. However, this speaks to the average population of the tools, not to the tools themselves.
When building a backend, I mostly work with Python and Django, and when interviewing candidates I place heavy value on them knowing what select_related and prefetch_related are. These are ways to void N+1 (akin to includes, I gather).
You can also write shitty queries, subqueries and just terrible stuff overall in SQL. And create shitty data models. Just the other day I had to comb through a PHP codebase that used raw SQL. Do you want to know what it had? N+1 problems.
If you use the ORM with the knowledge that you, as a competent developer, should have, then you most often will get a lot of bang for your buck. You will easily avoid the N+1 problem (and others), while benefitting immensely from what the ORM gives you, such as migrations, (somewhat of a) database independence, easy index creation and manipulation, much faster development times and iteration, an (arguably) much better syntax for creating and documenting the data model, and others.
I will never understand why so many people constantly criticise ORMs while turning a blind-eye to the fact that _most_ of their criticisms are actually criticisms of junior/amateur developers doing junior/amateur developer things. (I don't think you are one of these people)
The right tool for the right job. Sticking just to the ORM and using it blindly is a terrible idea. And I'm sure that extremely large codebases can be made all out of SQL — I'm not bashing it. I'm merely actively defending ORMs (and other tools) because they have their use and they do have very clear advantages — if used correctly.
I've been using sqlc, sqlx, pgtyped, & kysely and I found they have ORM-like productivity with full type-safety but they don't bring the baggage of leaky abstractions.
Unfortunately I don't know if there are Ruby or Python equivalents yet.
Could you kindly provide some examples of having type-safety and patterns that "feel like" ORM-like productivity, without the baggage?
I'm very interested! I'm a heavy Django user, but I hate dogma, so I'm very willing to try other stuff!!
The actual problem here lies in the Active Record (anti) Pattern. It becomes impossible to distinguish in-memory access from db access. I’m not surprised to find this being particularly prevalent in dynamically typed scripting languages…
I'm not a big fan of Active Record either, but conflating the two is pretty much the point. I think the biggest antipatterns around AR are in trying to treat it as a transparent abstraction rather than always being aware that it's more or less an "immediate mode" DB interface. Yes, you can treat your model objects as plain old domain objects here and there, but they don't stop being intimately tied to the database at all times, and you need proper DTOs and/or extra interfaces if such separation ever becomes important. But many apps don't have to care, and those are the ones where AR isn't such an albatross.
My main problem with AR is crappy implementations like Eloquent. No identity cache, so many methods and props on the Model base God Class that prevent using a column with that name, magic methods everywhere... Still, it serves all right as long as you treat it as a DB interface and not your canonical business object model.
I am super defensive on this one. Apps can keep not caring about it... until they have to.
Is it that much more effort to have a clear boundary between domain and db-access? I think it is only effort if you don't know what you are doing... which is _precisely_ when you need it the most.
> Still, it serves all right as long as you treat it as a DB interface and not your canonical business object model.
I still don't get the benefits. If you decouple db-model from domain model you lose all "advantages" of AR. It's like having plain DTOs but with its sharp spikes sticking out if you ever do something wrong.
Since then I've been building Joist, which is written in TypeScript, and has dataloader integrated into every operation (even `find`s) to basically never N+1:
ORM Framework developers have optimized their framework for rare cases. You can see this most clearly with Hibernate. If you want to initialize entities, you must either use an extremely convoluted JOIN FETCH statement, which doesn't really work the way you think it should work and can't be composed, or you choose to use entity graphs, which are a horrific contraption of Java annotations. The Rails style include method is a reasonably sane solution, but again, why can you load that association via lazy loading in the first place? The unpredictable performance of ORMs emerges from the fact that you write a query, which then gets you a hollow shell of an object, containing landmines which can blow up and trigger additional queries, long after the original query. The call to the framework and it's actual communication with the database are temporally disconnected.