Hacker News

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.

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.

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.

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.

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.

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.

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.

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.

ORM is a great tool for data input. Complex output I always write the old and good raw SQL query.

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.

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

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.

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.

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.

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.

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.

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.

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.

> 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.."

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?

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 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.

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] :-)

[1] https://joist-orm.io/

[2] https://joist-orm.io/modeling/why-entities/

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.

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

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.

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.

Also, NoSQL taught me to love SQL.

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

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.

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.