alt Hacker NewsWhat ORMs have taught me: just learn SQL (2014)
Comments
Why not both? ORMs for the simpler CRUD operations, SQL when it gets a little hectic.
The author basically says this in the first paragraph, but the title (and some of the language the author uses) implies that people should just use SQL.
It's a reasonable article pointing out some of the annoyances and problems of ORMs (especially in the Java world, where they tend to be overengineered) but there are still a lot of advantages to them if you are in an OO language and they used in a reasonable way.
The problem with ORMs is that they look kludgy without language support - which is why Hibernate in Java looks painful, while DotNet's EF looks like magic. I wrote something similar called TinqerJS - https://tinqerjs.org, which is like Entity Framework but for TypeScript.
There's immense value in everything being typed from the API down to the DB queries.
// EF-inspired type-safe API in TypeScript
const query = (q) =>
q
.from("users")
.where((u) => u.age >= 18 && u.email.includes("@company.com"))
.orderBy((u) => u.name)
.select((u) => ({ id: u.id, name: u.name, email: u.email }));
I generally like ORMs but recognize that they have a lot of problems. The most common problem that I've seen is when an ORM makes it easy to select records in a way that looks efficient but really is not. Strictly speaking, this isn't a failure of the ORM itself -- it's the fault of the developer who is using the ORM and also the developer that didn't catch it in code review. But it's a case where the ORM is making work for everyone and obscuring legibility into the code instead of saving time and providing clarity.
I've written complicated stuff where an ORM isn't appropriate, but if I'm honest, a large fraction of what I've done in my career is just making boring software to automate menial clerical work, and ORMs are good enough for those kinds of projects.
I used to love ORMs so much that I built one for Java, in the early 90s, and it was one of the main offerings of a startup that I joined. I have come around 180 degrees. My rethink started when a developer at a Wall Street bank said: having Oracle on my resume is valuable. Having your ORM on my resume is not.
And then there’s the “now you have two problems” dynamic. You not only have to write high-performing queries, but you have to get the ORM to generate that query for you. And sometimes you don’t want objects. And the schema mapping has to track schema changes.
Just write the damned SQL, it’s not that difficult.
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.
The argument that really hits home for me, after 30+ years in this industry, is stored procedures. The “Stored Procedures are Evil” argument to me is an artifact of an industry that promotes treating engineers and infrastructure as entirely interchangeable and anything that gets in the way of that is Evil(tm). But what working at Salesforce in the 2000’s taught me is that you can do really amazing things if you’re willing to invest heavily in understanding your infrastructure and specializing the hell out of it. Of course that created Oracle lock-in for Salesforce, but that lock-in was the result of Oracle having capabilities that simply didn’t exist elsewhere that Salesforce needed to scale. I would argue Google took that same idea and 100X’d it by building the capabilities they needed when they needed them. In the case of stored procedures, I think if you find yourself fetching huge amounts of data and then doing complex manipulation to it that you can’t do with SQL, consider doing it with stored procedures in the engine and greatly simplifying your application. It may just work out!
There are simple "ORM"s that just map classes to tables and columns to attributes. Basically focused on serialization instead of query generation. I find those to be a good balance.
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.
I was against ORMs until I used EF Core in .NET which I really loved. A good ORM is amazing for productivity and when needed you can always write raw SQL.
I don't use .NET anymore but lately I've been happy with Drizzle for TS. It's very performant and expressive. After years it seems that they're finally going to release v1.0 soon.
Personally I would never go back to writing all my queries with SQL, manually mapping the results, etc.
What's the problem with using ORMs for 95% of the cases and using raw SQL only for the remaining 5% where ORM isn't sufficient? One important benefit (aside from writing less code) of ORMs is type checking which is important for maintainability in large complex projects.
In my opinion Elixir Ecto is ORM done right:
1. the functional/immutable nature of Elixir makes read and writes much more explicit and there is no need to magically track deep mutations of nested objects to translate them back into UPDATE/INSERT queries
2. Elixirs support for lisp-like macros allows for an ergonomic embedded query languages that is syntax and schema checked, mirrors raw SQL really well and, frees you from string-oriented query building
3. the query builder DSL addresses one of the main weaknesses of SQL query statements not being composable
4. The automatic conversion between JOINed tables (on the DB side) and nested structs (on the Elixir side) is done on the right abstraction level to work reliable and and being explicit enough to generate predictable queries.
I really enjoy using Rel8 (https://rel8.readthedocs.io/), so much so that I reimplemented it in Rust (https://github.com/simmsb/rust-rel8).
For me I find it's an excellent step up from a plain SQL query builder (with an API such as `select(Foo).join(bar)`) as it lets me both effortlessly perform projections (one can write `(\e -> (e.foo, e.bar) <$> someQuery` to take a query producing rows of `E` and turn it into rows of 2-tuples built from two projected fields.
I wrote a bit about my Rust rewrite here: https://bensimms.moe/postgres-lateral-makes-quite-a-good-dsl...
I agree that "learn SQL" is a necessity, but I'm not sure the article makes a good argument against using ORMs.
ORMs are just a layer of abstraction. Like any abstraction, they make some tradeoffs that can get you into some sticky situations like inefficient queries mentioned in the article.
But, if you understand the tradeoffs, you can use them for what they're good for (standardization & simplification & in-codebase schema definitions & so on) and usually drop down to SQL whenever there's a particularly necessary case.
I've been using ORMs since the late-90s with WebObjects (I still have a running product on the internet that uses WebObjects). I've used I don't even know how many other orms. But it's always been a mix of orm and raw sql, so yes learn sql. Especially useful for reporting.
I'm not sure why people have not hit on the following hybrid architecture that works so well for me.
I make use of table-valued db functions (IMO the most underrated feature of relational DBs) to define virtual relations/tables. I implement a set of CRUD db functions per entity. Then, on the app side, I define (or generate) DTO types representing these virtual relations. Finally, I use a custom ORM I wrote myself, which defines a general and consistent storage API, to talk to the db functions, using the DTO types.
The advantages of this approach are numerous, some include:
- I have full control of the SQL that goes into constructing the virtual table, I can leverage all the goodness of SQL here. I can even define multiple virtual relations per physical table, or read-only relations, etc, all by implementing the appropriate sets of CRUD db functions
- On the ORM side, I have all the goodness of static typing, a consistent API for all CRUD methods, a full fluent query DSL, etc
- Since, unlike tables or views, db functions can be passed arguments, i am able to layey all kinds of goodness on top of the basic CRUD actions, like audit info passing, custom upsert strategies, some level of record-based authorization, etc
But this architecture does require you to know and write SQL. IMO the value of ORMs do not lie in avoiding SQL; it lies in the capability to express consistent SQL at a higher level of abstraction, but you still need to understand your SQL.
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.
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.
The purpose of an orm is not to "stop writing SQL". In order to effectively use a layer abstraction, you must be able to use the layer below the abstraction.
My point of view (after 18 years of programming): DO use frameworks (compile-time checked queries if you can) but skip ORMs that hide/obfuscate SQL completely as it will result in slow queries, extra round-trips, etc
Oh no, this meme again. Of course you should learn SQL. But also, you can use a library to help generate SQL based on classes and objects that you change, so you don't have to repeat yourself. Why don't you use both?
I always disliked ActiveRecord, but I figured ORMs don't have to be ActiveRecord. I created this library 14(!) years ago not too long before this article was written https://github.com/iaindooley/PluSQL
The idea is that you like SQL, but it gets repetitive writing joins and accessor code. I had always hoped it would catch on as a pattern: no boilerplate, automatic mapping to objects in your code of any query (whether generated by the ORM or passed in as a raw query) and easy to override/dynamically build bits of the query as you pass the object around.
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
I wonder if the real problem isn't being able to write efficient queries, but that developers struggle to add (yet another) programming language. Just use AWK, just use SQL, just use jq, just use xyz. It's a lot of overhead. I would be OK to lose whatever fractional speed difference to be able to write my queries in a different scripting language. If I ever scaled so much that I needed to shave microseconds off my queries, there are already tons of DBs available, maybe just using a different tool or, even better, compile the DB with(out) different scripting support.
It's a bit aside, but what i love about ORM frameworks is that they try to find the universal interface to multiple database backends. For basic CRUD it's nice: test on sqlite deploy wherever.
If you use Java and like to write SQL, check out https://pyranid.com
I stopped using ORMs around 2008 because they made the easy problems easier and the hard problems harder. I wanted to just write SQL and exploit all the power the DBMS has to offer instead of fighting with an abstraction layer, so I created Pyranid in 2015 and keep it actively updated.
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 thought this was well put. https://web.archive.org/web/20160301022121/http://www.revisi...
A now defunct site discussing why ORM is a poor map.
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?
ORM is a great tool for data input. Complex output I always write the old and good raw SQL query.
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 :-)
Edit: in the HN comments, we're debating "the best way to generate SQL", which is fine, but imo it overlooks the biggest value for ORMs: enforcing business invariants.
I.e. yes a simple INSERT is trivial is write, "why have the ORM to that!", but are you going to enforce the same business logic in the 10 places you do `INSERT authors` in your codebase? And if the answer is "I write an single `insertAuthor` abstraction to enforce this" then you're half-way to writing an adhoc half-specified, bug-riddled version of what a reactive ORM like Joist will do for you. [2] :-)
I never use ORMs. But slightly before 2014, there was still kind of a reason to use them, getting/setting a whole nested bag of fields at once that you don't care about individually. Json/jsonb now handles that better.
> August 3, 2014
That's important. Because now days it's trivial for LLMs to translate ORM to SQL and vice-versa with ~100% accuracy. I haven't written any raw SQL (only Active Record) in about two years, and the odd time I blunder with AR and create an n+1 I find out about it via error tracking (e.g. Sentry) a few minutes later and fix it. No biggie.
There's also an additional layer of protection in that using AI on the codebase can spot SQL blunders incidentally (i.e. you ask about X, and the AI does X but also says "Not asked, but flagging for your attention: problem with SQL on line 256 etc.."
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.
I am no SQL God by any means, but I am quite proficient. Despite my SQL skills, I cannot give up EF Core.
Even when using other languages, I just pine for LINQ/EF Core. It's truly the best ORM in my opinion. Also, even if one does not want to use the LINQ or the Query syntax (I forgot what it was called), the ability to execute SQL is also still a game changer.
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 have the same response every time I hear this: like 95% of application CRUD plumbing is much better served by an ORM. It gives your application typed versions of your data, lets you work with objects rather than rows, which are almost always more useful, is much easier to read, etc. Then for the 5% of critical/complicated queries: just use SQL there. In fact your ORM almost certainly has an escape hatch for you to do that.
ORMs may be convenient, but only as long as you stay within their limitations. One you surpass those, things get much more complicated and messy. SQL does not have that artificial breaking point.
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.
>just learn SQL
Implying I use an ORM because I don't know SQL... I've reverse engineered embedded databases and written directly to the .dat files on production systems that deal with HIPAA data. I'm pretty sure I know SQL better than most people on HN. I still prefer an ORM.
Why? Because with my ORM, I can code gen faster than you can vibe code. I can build on top of the abstraction layer. The data model in the ORM is the M in MVC. The backend could be a SQL database, a file system, a REST service, that part is irrelevant. The M is the same, regardless of the backing store. View and Controller code still works.
I find most people who are anti-ORM are kinda junior and trying to flex their power to write SQL scripts as if it is impressive. That's why there's always this weird implying that ORM users don't know SQL.
SQL is awesome and you’ll never get the best out of your database unless you learn to program the damn thing and bit hide behind some abstraction.
We do programmers always need a library?
Program the damn thing.
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."
Actually, on second thought, I take it back. "Right tool for the right place" is harder. If you're on a team, it's probably better to just pick one: either don't use it at all, or use it everywhere. Because either way, friction is going to happen. My earlier thinking was too shallow.
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.
[flagged]
Next step is go down one more level to ditch SQL and learn LMDB and/or RocksDB.
LLMs are better at writing raw queries now and knowing the consequences of how it fits in your architecture (if you ask)
So I think the ORM debate could be over
postgresql is a beast
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.
I don't disagree with any of the major gripes people have with orms and I find SQL to be much cleaner in a lot of circumstances.
That being said, if orms didn't force you to explicitly define your domain models about 60% of developers would simply never do it. And you would see differently structured, ad-hoc interfaces defined all over the code base completely entangled with whatever action they are trying to perform.
ORMs being a forcing function for domain modeling is enough benefit for me that it outweighs all of their obvious limitations.