Hacker News Reader

Comment by sqldba | Hacker News Reader

SQL is easy. ORM is not - except through a framework which will make you forget about the SQL at all.

ORMs are a trap. They are like some sugary drink which makes you fat and you only realize it when it's too late.

They prevent harnessing the power of SQL by providing seemingly convenient shortcuts. Only later (when it's too late and you already invested heavily in it) it becomes clear that one would have been better off not using an ORM but using SQL directly.

That's a great analogy, I'm totally with you on this one.

ORMs are painful, I only use solutions which help me reducing the amount of boilerplate code to map data to domain objects (Dapper being a great example in .NET land), but I really prefer writing my queries and seeing them at a glance (however ugly they may look when inlined) without having to actually run the app. Plus, SQL produced by things like Entity Framework is not really readable with all those "extent1", "extent2" and so on.

In addition, ORMs tie you to the lowest common denominator so if you want to take full advantage of, say, jsonb in PostgreSQL you're out of luck.

I mean, in principle they're great, and they will theoretically allow you to switch database engines but, according to my limited experience, that is not such a frequent occurrence.

The thing is, they come with too many hidden costs, and I never bought the "no need to know any SQL" argument to begin with, as I really think a decent command of it is part of the fundamental abilities of a developer.

"if you want to take full advantage of, say, jsonb in PostgreSQL you're out of luck"

I don't know if you can take 'full advantage' but with the Django ORM, you get special postgreSQL fields to take advantage of postgres jsonb. There is nothing stopping someone from developing more features specific to other RDBMS either... and if you need to write some queries in SQL, the Django ORM lets you do that easily alongside more traditional ORM queries.

I agree, in a twisted way. On the one hand, I like the idea of ORMs in general. But on the other hand, I never really trust them and tend to write my own SQL, unless a framework like Django pretty much makes me use its ORM (to be fair, the few projects I built on Django had a sufficiently simple data model to make the ORM rather painless).

And even then, Django allows you to perform sql queries if you think you need to :)

https://docs.djangoproject.com/en/1.9/topics/db/sql/#perform...

https://docs.djangoproject.com/en/1.9/topics/db/sql/#executi...

Indeed. As far as those all-inclusive frameworks go, I really like Django, it is - for my needs, at least - fairly comprehensive without being overwhelming.

ORM are bad if you look at things from a DB perspective.

If you're an application developer you've seen what happens what the code dissolves to if developers are rolling their own queries all over the place and doing manual model to object mapping. ORMs have appeal in environments they provide tooling on top and where developers are expected to know how to use them (Entity Framework, Django admin). In other situations like Clojure I would just use something like yesql.

One trap I've seen with big ORMs is some coders stop "thinking database", which can lead to performance problems.

The "N+1 selects" problem ( http://use-the-index-luke.com/sql/join/nested-loops-join-n1-... -- running for loops against nested selects) is probably the most notorious case of this. When you learn SQL, you pretty much learn from the get go that loops are bad (eg, you avoid cursors, and process things in batch, as much as possible). I think it's a lot less obvious that this also is the case in ORM land, that for loop doesn't instinctively look too dangerous when you first start programming using an ORM. (Because in your regular code, it really wouldn't be.)

Personally, I do quite like micro-ORMs like Dapper. Dapper's great at mapping query results to objects, but still allow you to take advantage of SQL performance.

The thing with ORMs is that they buy you a lot of fancy stuff as well - scaffolding/crud templates, they integrate in to your development workflow (under source control, in the same language as the rest of your code base).

I'm not really a fan of ORMs - I think it's fundamentally an inferior approach and I think clojure/yesql or (presumably) Dapper like micro ORMs are much better where you treat query results like values instead of objects - but at the same time an enormous ammount of work went in to ORMs and a lot of developers are familiar with them to the point where they can do their job in 90% of the cases - and for that 10% case you have the senior dev or a DBA can step in - this IMO is a big value proposition that shouldn't be ignored.

Every widely used ORM allows direct SQL. Like many other things knowing when and where to use it is key. An ORM will be great for 90% of an app. When it is not a good fit, go direct to the DB/datastore. The point of enlightenment is not rejection of the ORM but rejection of the idea that the ORM is good for everything.

They do, but that is not the problem. The problem is that they usually try to impose a database design based on the class structure of the application, which will be something that you never would design by hand, and not is optimized for taking advantage of the relational model. Now you have a convoluted database structure and queries filling several screens, and becomes hard to reason about. Now it does not help that you can handcraft queries.

In addition, your data is usually longer lived than the class structure of application, so making a database design based on the object structure means that you can't change your class structure, because you have several terabytes of data you want to keep, following its structure.

If you create your database schema straight from your class structure, then you are probably going to have problems, I think that's pretty much a given.

But if you want to query from an ORM to a reasonably normalized database, then with a bit of thought, you can almost certainly dodge many of the traps that an ORM can lead the unwary.

Then don't use bad ORMs. What you are describing is caused by devs with poor understanding using ORMS as a crutch. That same dev would probably make a crappy schema without the ORM.

ORM are good writing simple queries and prevent navie mistakes, like misspelling columns. And also narrow the chance of certain attacks, like SQL-injection.

And Django has backdoor for you to use raw SQL.

It is not a bad thing if not being abused.

Reply on news.ycombinator.com