Hacker News Reader

Comment by pizza234 | Hacker News Reader

I'm a "hardcore" SQL developer, but still deem ORMs [commonly] necessary. Importantly:

> Their alleged benefit is they cut down development time

> Let's dispel with the myth that ORMs make code cleaner

This is very narrow view of what ORMs do; without them, access to the DB layer becomes a black box.

Actually, not even the black box definition is appropriate - APIs to access the DB layer are needed in one way or another, so one ends up writing their own ORM.

ORM APIs do a lot more than just executing queries. The first thing that pop into my mind is persistence/state management, but also, composing queries is much more than bashing together a series of strings.

EDIT: elaborated on ORM APIs role.

View on HN

The underlying problem as I see it is that SQL is itself a black box, in the sense that the database execution plan does not always trivially follow from the SQL as written. That is a very powerful property for an ad-hoc query language because it means that the user describes what they want, and the database itself takes care of the best way to get it to them. For programmers though, who usually want something predictable and repeatable to happen, it is not always ideal.

ORMs try to make SQL seem more programmer-oriented by adding an extra layer of indirection over SQL. Unfortunately, what they actually tend to do is to add an additional black box enclosing SQL's black box, and that usually makes everything worse because it is now almost impossible to reason about how the database is actually going to execute a particular query.

There doesn't seem to be an ideal solution to this problem. I think it is why so many databases tend to tweak SQL into something that better fits their implementation detail, and we end up with the explosion of almost-SQL languages that the author is complaining about. Personally, the least-bad solution I have found is the one you mention in your post: write your own SQL abstraction layer every time. At least that way, you can poke your screwdriver into the box and reason about what is going on in there.

There's been times where I've had to spend quite a bit of time trying to change the SQL in subtle ways to get an index to be used and do the join algorithm type I wanted; when it didn't want to do so. Usually improved performance by a massive factor for the datasets I was working with (e.g. 10x). The predictable performance as well was a big factor - I would prefer predictable and adequate performance over peak performance but high variability every time.

In the end after all this frustration I wished I could have written the query plan directly, especially when I used Postgres with no query hints. And yes I'm aware of Postgres and all the tricks that you can do to make it do certain types of joins and such and I employed many of them (adding statistics, loose index scans, all the index types and others). IMO the potential this could open up is quite large given many databases all have the indexes/algorithms and many data structure types these days. Gluing them together in a performant way where you use the appropriate algorithm/data structure index for the data on tables/JSON blobs/etc seems to be the hard part right now that requires a lot of trial and error and learnings of the SQL optimizer to get right.

or maybe the ORM of the day doesn't support all the database features you need and you end up using leacky abstractions and less seniors colleagues end up with an OOP-ORMed database mess that would make cry anyone who did in fact learn the DB layout.

I think that's where the majority of ORM criticism comes from ... namely the idea (that some may hold) that you don't need to have a good mental model of SQL if you just use ORMs. I can see some people who are intimidated by SQL use ORM as a crutch to work with relational databases.

OOP and SQL are different paradigms. An OOP abstraction over SQL is never going to be perfect so to properly and responsibly use an ORM framework you have to understand what's going on under the hood. If you don't, then yes, you will create a mess for anything other than the most trivial use-cases.

Reply on news.ycombinator.com