Hacker News Reader

Comment by blakesley | Hacker News Reader

I'm still pretty new to dev, but what's wrong with ORMs? And, importantly, what would you recommend instead?

SQL is a transferable skill. ORMs are not. If you already know SQL and have to use an ORM on top of that, then it's a net loss.

It's trivial to use SQL to build objects from individual rows in a database. Which is all an ORM is really good for. Once you start doing reporting or aggregates, then ORMs fall apart. I've seen developers who, because they have a library built up around their flavor of ORM, go and do reporting with that ORM. What happens is this ORM report consumes all the RAM in the system and takes minutes to run. Or crashes.

ORM code hits performance issues because so many objects have to be built (RAM usage) and the SQL queries underneath the framework are not at all efficient. You can use OOP on top of SQL and get decent performance. But you need shallow objects built on top of complex SQL. ORM goes the opposite: large hierarchies of nested objects built from many dumb SQL queries.

This also ties into GraphQL. Think careful about the hierarchies you design. A flat API call that does one thing well is often better than some deeply-nested monster query that has to fetch the entire universe before it can reach that one field you need.

GraphQL is not an ORM. An individual GraphQL server implementation can act as an ORM for a specific use case, but GraphQL can do much more than that.

You should not ever need to implement your own GraphQL server. There are plenty to choose from.

I am personally fond of:

1. query builder APIs, which can only generate valid queries, but you can control exactly what that query will be, 2. APIs that return basic data structures from the database, like maps or tuples.

Query languages like SQL are very powerful and easy to learn. And in my opinion, preferable to ORM approach of "what method calls do I need to make to trick the engine into executing the SQL I know would make this work?" ORMs add complexity and limitations that, in my opinion, are not worth the benefits.

Personally I don't think there's anything wrong with them. They're just tools.

but like any abstraction, if you don't know what's going on behind the curtain, they can turn into foot guns quick.

People will have different opinions, but for me, there's nothing wrong with ORMs themselves, they are a significant productivity boost for 80% of the database interactions in your app. The tricky part is recognizing the 20% where ORMs are a bad idea, which ends up meaning that an ORM is best used not as a replacement for knowing SQL, but as a tool to make you more productive when you already know SQL.

ORM's are fine for the majority of simple use cases. When things get complicated you end up either fighting with the ORM or just overriding it and writing the sql yourself anyway.

Yes, there definitely need to be escape hatches for situations where you need to write sql.

But that should be rare. If you're commonly bailing to raw sql, I'd say there's something wrong, probably a poor fit of the orm to the problem.

I'll use raw SQL (maybe not as an entire query, but something like a computed column) pretty often, for situations where I want to query things like "give me all foos with a count of bars related to them", or "give me a list of foos with the name of the latest related baz". Most ORMs would want to hydrate the graph of related objects to do that, or at least have multiple round trips to the DB server.

That doesn't sound like a good ORM. They should be lazy until you access the relevant data.

That said.

This is where the knowing what's going on behind the scenes matters.

Oh they would be lazy, it's just that expressing something like that efficiently (i.e. something like "SELECT foo.*, (SELECT count(1) FROM bar where foo_id = foo.id") is usually really hard to do. Most ORMs I've seen would N+1 on that with a naive approach, and even the "optimized" approach will want to fetch all bars vs. just the counts.

Reply on news.ycombinator.com