I will never understand why more developers don't just learn effective SQL. In many cases, the database is both the most expensive and most powerful component of the system - may as well put it to use!
The N+1 query used as an example in the post could have just been a single query.
ORMs, like SQL, C++, Python, and Ruby are just a tool. You can be shit at using that tool.
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.
There is good value in having ORM for all those boring 90% data fetch tasks. It removes a whole lot of boilerplate and you can guarantee that your code representation equals database representation.
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…
> 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 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.
> But many apps don't have to care, and those are the ones where AR isn't such an albatross.
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.
You're preaching to the choir here: I much prefer datamapper ORMs when I even want an ORM at all (I'm not quite the OOP astronaut I once was). I'm saying AR's poor design isn't a noticeable drag in small scopes, especially for short-lived models whose only purpose is to populate a view. It's like having really fancy DTOs, and keeping them in the DTO bin is what keeps you from getting stuck with all the sharp spikes. Life can be comfy in a minefield when you have a map and don't wander too much :)
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.
I’m increasingly cold on ORMs existing to abstract away the specifics of your DBMS. In my entire career I think I’ve seen one migration between different databases (MySQL to Postgres), and it was an undertaking despite using an ORM.
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’m increasingly cold on ORMs existing to abstract away the specifics of your DBMS. In my entire career I think I’ve seen one migration between different databases (MySQL to Postgres), and it was an undertaking despite using an ORM.
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.
While this feels like common knowledge, I’m willing to bet you’ve learned extensively why you should profile everything when it comes to performance.
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.
I worked in a Rails app in ~2018 & hunting down N+1s by sprinkling `includes` in just the right places was tedious, despite being a regular occurrence.
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:
Any ORM built around implicit lazy loading is doomed. The idea in itself is simply stupid. The ideal usecase for lazy loading is that you have a single object, e.g. a user page and then you never load a collection of objects. The fact that you end up doing a few more queries doesn't matter.
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.
I will never understand why more developers don't just learn effective SQL. In many cases, the database is both the most expensive and most powerful component of the system - may as well put it to use!
The N+1 query used as an example in the post could have just been a single query.
ORMs, like SQL, C++, Python, and Ruby are just a tool. You can be shit at using that tool.
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.
Former ActiveRecord & Django ORM enjoyer here.
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.
These are rust tools, right? (edit: Perhaps Go?)
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!!
There is good value in having ORM for all those boring 90% data fetch tasks. It removes a whole lot of boilerplate and you can guarantee that your code representation equals database representation.
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…
> 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 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.
> But many apps don't have to care, and those are the ones where AR isn't such an albatross.
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.
You're preaching to the choir here: I much prefer datamapper ORMs when I even want an ORM at all (I'm not quite the OOP astronaut I once was). I'm saying AR's poor design isn't a noticeable drag in small scopes, especially for short-lived models whose only purpose is to populate a view. It's like having really fancy DTOs, and keeping them in the DTO bin is what keeps you from getting stuck with all the sharp spikes. Life can be comfy in a minefield when you have a map and don't wander too much :)
Ex-Rails here.
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.
I’m increasingly cold on ORMs existing to abstract away the specifics of your DBMS. In my entire career I think I’ve seen one migration between different databases (MySQL to Postgres), and it was an undertaking despite using an ORM.
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’m increasingly cold on ORMs existing to abstract away the specifics of your DBMS. In my entire career I think I’ve seen one migration between different databases (MySQL to Postgres), and it was an undertaking despite using an ORM.
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.
While this feels like common knowledge, I’m willing to bet you’ve learned extensively why you should profile everything when it comes to performance.
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.
I worked in a Rails app in ~2018 & hunting down N+1s by sprinkling `includes` in just the right places was tedious, despite being a regular occurrence.
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:
https://joist-orm.io/docs/goals/avoiding-n-plus-1s
Any ORM built around implicit lazy loading is doomed. The idea in itself is simply stupid. The ideal usecase for lazy loading is that you have a single object, e.g. a user page and then you never load a collection of objects. The fact that you end up doing a few more queries doesn't matter.
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.