What ORMs have taught me: just learn SQL (2014)

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.

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.

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.

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.

PS: I still exported the table before deploying this fyi.

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.

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

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

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.

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

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.

Every time I tried to do a project without an ORM, using only raw SQL, I inevitably ran into:

- serialization/deserialization boilerplate. Like, having to manually map values returned by the DB library to object (or named tuple, or structure) properties

- poor code reuse, having multiple very similar queries that have just one small difference

- extra pain in changing DB schema. Adding a field requires to go and manually edit many queries

Anti-ORM crowd never gives a good answer to these issues.

Instead, they push strawman attacks like "oh, you only use ORM, because you can't write raw SQL". I can absolutely assure you that this is not the case. Every time I use an ORM (SQLAlchemy mostly, the one mentioned in the article) I am 100% sure what SQL do I want it to produce and what SQL will a particular ORM invocation produce.

Can the OP expand on why this is? Just curious.

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

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

Can anyone that has used ActiveRecord share their opinion?

A now defunct site discussing why ORM is a poor map.

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.

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

2026: people respond with indignance that they should have to learn anything now that there's a shortcut

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.

We do programmers always need a library?

Program the damn thing.

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.

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.

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.

I'm talking about my experience, not generalizing to all DBAs of course. And of course ORMs introduced performance issues, etc.

So I think the ORM debate could be over

postgresql is a beast

NoSQL for operational data storage is more efficient and cost effective.

ORMs were a regression test that exposed unnecessary complexity.