I'm admittedly an ORM apologist [1], but a few of his points articulated as "deal breakers" aren't that bad imo:
- "the pernicious use of foreign keys [...] links between classes are [...] foreign keys" ==> that just sounds like schema normalization, which is usually a good thing?
- "bending over backwards [...] to generate SQL that runs efficiently" ==> the huge majority of ORM-driven queries are "select * from table where id in ..."; for the queries that are more complicated than that, then yes use SQL! That's allowed!
Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction.
- "dual schema dangers" ==> he's exactly right that database should own the schema definition, but then just codegen the entities from the db schema? That's your singular source of truth, no drift. You can do this with Hibernate, ActiveRecord, Joist, many ORMs.
- "Identities" ==> ironically I think ORMs (that use the unit of work pattern) actually have net-better DX here b/c you can hook up a graph of entities with just references.
I.e. hook up a book to its author w/o knowing their ids yet, which explicitly avoids the annoyance he mentions of doing a partial commit/going to the db to figure out "what value should I INSERT into in the book.author_id column?" (but my author is new) in the middle of your business logic that just wants to "create books".
- transactions ==> agreed that "transactions via annotations" ala JPA/Hibernate are terrible, but afaiu all "internet scale" apps these days do reads outside of transactions, and just use op-locking during the singular flush/commit step to the db.
Disclaimer I am sure I won't change anyone's minds
The big problem is that raw SQL has pretty bad type inference and linting support in most editors. A query builder can still give you a lot of type safety benefits.
Which is why one is better off using IDEs, especially those from DB vendors.
Autocomplete is making me lazy. If I don't see what I'm about to type within two or three characters, I feel like the IDE isn't doing its job of helping me. So being able to type `db.Cust` and autocomplete Customers is really nice. I do know SQL, but yes, the language servers usually have a harder time connecting the SQL to my backend code, whatever language it's in, without quite a lot of config fiddling that pretty much obviates any time savings I would have gained from autocomplete.
In my database[0] you get an SDK generated from your schema. Typescript is the default and man, the autocomplete works so well.
I recently added support for SDK generation in Rust and Go, just do `disc codegen —rust` (double dash, my iPad is autocompleting the wrong dash) and you’re good to go.
[0]: https://disc.sh
I think the bigger problem is that SQL is in almost every language a second-class citizen. And even calling it second-class can be seen as a stretch.
The problem is that there is no "SQL" — it's different for every database.
It's not that different. I'd rather have a different way to do UPSERTs or a different window function here and there [1] than figure out every ORM's join syntax or its sneaky ways to SELECT N+1 me into oblivion.
[1] LLMs make these very easy to handle.
For the vast majority of simple use cases the common subset of all popular SQLs is exactly the same. Otherwise… just use Postgres
[dead]
As someone who started their programming journey with SQL, it just feels so odd hearing about learning SQL being presented as an useful option. I get it, it just feels odd. SQL was considered table stakes in the financial IT world - if you said you didn't know SQL, people would look at you funny.
My first job was at a financial services software company. They put everyone through multiple weeks of training on sql. That experience has been paying dividends for 25 years.
Still applies today in data science, one is expected to master SQL alongside Python and Excel.
It's very strange too. You can learn something like ~90% of useful SQL in an afternoon. The remainder is stuff that you only really need for extremely performance sensitive operations
ORMs have their place but they are leaky as hell. RDMSs are very diverse, have different languages, and require different optimisation techniques.
ORMs that try to paper over all the differences fail miserably. They become super complicated and generally produce crap SQL.
ORMs also tend to oversimplify database design. They are just tables with primary keys, right? Who needs indices? Who needs to think about collation? God forbid anyone mentions physical organisation of the data!
Having said this, I do use a very small subset of SQLAlchemy (the bits I understand) in data pipelines.
Use it where it fits, and don't use it where it doesn't.
If you don't use an ORM, you'll end up with more boilerplate from mapping code with DTOs. The reason to use an ORM is dirty checking. It's hard to impose this kind of "state" with a relational database. But fundamentally, relational data doesn't fit well with OOP. In the end, you inevitably have to create a layer that absorbs this mismatch. Both approaches have their pros and cons anyway.
Isn't it just a matter of using it where it fits and not using it where it doesn't? I wonder if we really have to frame it as "never use this" or "always use that."
I feel like ActiveRecord has none of these problems, but I also feel some strong confirmation bias.
Can anyone that has used ActiveRecord share their opinion?
ORMs taught me that relational databases are an operational anti-pattern.
NoSQL for operational data storage is more efficient and cost effective.
ORMs were a regression test that exposed unnecessary complexity.
the N+1 trap and having to incorporate eager loading dictates you need to pretty much understand SQL regardless. applying the object oriented paradigm to relational data created Frankenstein's monster which we unaffectionately refer to as ORMs
What Python taught me: just use C.
These are simply tools. The only wrong opinion is to believe that there’s a strict superiority of one over another. However, the content of this and other blogs can help people make informed decisions on when to reach for each tool.
Just one quick note...
> ...(although things like Postgres’ hstore can help)...
Back when this blog post was written, this advice would have been reasonable. Today, I don't know anyone reaching for hstore since the more featureful json support was added.
I use both SQL and ORMs every day. I've used hibernate since 2004. I've certainly had some difficult times with it; but overall it is a net positive. I find that it generally works well and saves a ton of time as long as I stick to my known patterns.
2014: people respond with indignance that they should have to learn SQL now that there's a shortcut
2026: people respond with indignance that they should have to learn anything now that there's a shortcut
I like SQL. I enjoy writing SQL. I find ORMs produce crap SQL.
But the current shortcut du jour is pretty damn good at writing SQL.
While I do enjoy the Django ORM, for many queries SQL is just better. It's almost as if it was designed for querying database.
Once you hit a certain level of complexity in your queries, you're better of with SQL. It's not that you can't do the query in the ORMs, but you're then looking at learning their special query language and those are never better nor easier to understand than just SQL. Those ORM query languages certainly aren't transferable across ORMs, but SQL frequently is. If you can query MariaDB with SQL, you can query SQLServer and PostgreSQL. The same can't be said for e.g. Django vs. Hibernate.
For the "give me all the entries, with this one property" ORMs a much quicker and easier to work with. Once you start needing to use subselect, multiple joins, weird ranges or constructing object with data from across tables, I'd rather just write the SQL myself.
At yet people (mostly) skip SQL and learn some ORM.
ORMs are a horrible fit for OLAP scenarios. I've got a situation where I need to load ~40 tables with a total of 100k+ rows and I need it to happen at user-interactive speeds (less than 10 seconds).
There is nothing that an ORM can do to help with this sort of problem without reaching for the obvious escape hatch of arbitrary command text execution. The ability to map the tables to objects in my programming environment is a distracting clown show for this specific problem. What really matters is understanding the provider and its techniques for bulk loading records. No ORM will ever be able to touch these provider capabilities on their "happy" paths. At best you'll wind up using the ORM and a bunch of provider-specific SQL anyways.
ORMs for schema management is a stronger argument, but only in cases where the codebase/service has complete ownership over each respective database. Any kind of heterogenous workload says that ORM for schema management is a potential nightmare unless you do something like create a project that is only for migrating the schema, at which point I'd argue you could just maintain a source controlled folder of sql/shell scripts.
Also, NoSQL taught me to love SQL.
Especially Dynamo DB.
One nice thing about the rise of ORMs back in the day was it broke the stranglehold our traditional DBAs had on the data tier. I respected them and their skills, but in a product org it was really difficult to have a separate group that refused to participate in planning and wanted to design everything up front, optimize based on their performance assumptions, and then who would argue with devs when we'd need to do pretty normal things like, say, list users in a webapp.
I'm talking about my experience, not generalizing to all DBAs of course. And of course ORMs introduced performance issues, etc.
Next step is go down one more level to ditch SQL and learn LMDB and/or RocksDB.
> "bending over backwards [...] to generate SQL that runs efficiently" ==> the huge majority of ORM-driven queries are "select * from table where id in ..."; for the queries that are more complicated than that, then yes use SQL! That's allowed!
This is exactly why I hate ORMs. As I always put it "ORMs make the easy stuff slightly easier, and they make the harder stuff way harder".
If you're just using an OEM for the "select * from table where ID in ...", then you're saving practically nothing by using an ORM - just learn to write SQL, because as you put it, you're going to have to use it anyway for places where it falls over. There are lighter weight options that do basic stuff like transaction management and binding result sets to object properties that are much less of a PITA than ORMs.
In practice I've seen people try to use the ORM features first for places that need complicated SQL (which is a reasonable assumption), only to waste a boatload of time before concluding the ORM makes stuff harder.
I have seen many ORM enjoyers argue the point about “you can just use SQL!” but I have never once seen an ORM enjoyer allow it, much less do it themselves in an actual codebase. They will time and time again prefer you write 100 lines of Typescript/Python for what could be achieved with 15 lines of SQL.
To make matters worse, most of the time I've successfully argued a project to just use SQL instead of an ORM, what has happened is that people over time built a home rolled ORM in the development language.
It's like people can't just let go.
This is inevitably what happens every single time so just use an ORM and stop being stubborn.
Worse, that code will be executed on the receiving end, and waste a bunch of network traffic.
The reason given to use raw SQL is for the performance not the perceived code clarity.
If you never used a CTE, maybe… The reason to use SQL is to get what you need out of a database. Performance is orthogonal to that.
I’m not sure why you thought I meant code clarity and not performance? It’s clear in all cases the correct SQL query will be more performant. I thought it was implied that I believe it not only is more performant but also shows intent much more clearly when you just use SQL.
Confused at what you’re evening trying to say here. Are you suggesting that 100 lines of application layer code is easier to understand than 15 lines of SQL?
> the huge majority of ORM-driven queries are "select * from table where id in ..."
From my experience, you are mistaken on that. Those queries mostly come with some joins, either necessary or not to represent the object, and that often could be avoided if the data wasn't mapped into some standard object.
> Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction
my experience is the exact opposite. People who love and advocate the merits of ORM insist that everything be executed through ORM because it introduces too much complexity for them to blend handwritten SQL with the ORM generated queries
I've written/worked on several ORMs from scratch. ORMs are the industry standard. When I see posts like this I simply can't take them seriously. All they are saying is "I won't be a team player" and "I don't actually understand the subject matter". The reality is at a certain scale there's an entire orm team that optimizes everything. But even when there's no team involved there's no way you can write anything more optimized because I'm already at the computational limit of how far something can be optimized.
There's no (good) ORM that doesn't let you simply put your own query in.
What optimizations are you making here when at the end of the day performance is dictated by the schema, the query planner and the network?
Fair point, both "pro ORM" and "anti ORM" camps are prone to extreme stances.
I definitely don't agree with the "all queries must be executed through the ORM", and think that dogmatic stance has done a lot of damage to the ORM brand. :-/
They don't consider the ORM the second class citizen it actually is: an optional simplified alternative to normal queries, that can be used for the easy cases.
> Folks who dislike ORMs seem to have this false dichotomy that "the ORM _must_ be used for all queries", which is a self-imposed/unpractical restriction.
I've always heard a major selling point of ORMs is "You don't have to write the actual SQL anymore"
Because of that, I tend to not trust people who use ORMs to even know how to write queries by hand in the first place
You're right, that has been another "pro ORM" pitch that has gone awry and, taken to the extreme, is wrong imo.
My nuanced articulation is "you don't have to write the _boilerplate_ SQL for the 90% of just-do-some-CRUD endpoints in your enterprise SaaS application, but you 100% need to 'know SQL' for the last 5-10% of ~reporting/analytics queries that the ORM is going to mess up".
Personally I find the 90% boilerplate SQL is easy enough to write that injecting an ORM into the process doesn't make much sense
But that's just me