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.
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.
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.
jorl17|1 year ago
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.
jflwyasdf|1 year ago
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.
DarkNova6|1 year ago
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…
chuckadams|1 year ago
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.
banish-m4|1 year ago
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.
jon-wood|1 year ago
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.
unknown|1 year ago
[deleted]
hahahacorn|1 year ago
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.