Comment by slackingoff2017 | Hacker News Reader

Comment by slackingoff2017 | Hacker News Reader

Parent Full thread

slackingoff2017

They're still alive and well, just not sexy anymore. The most popular one is probably still Hibernate which is helped by Java being the top high level language.

I never understood the ORM hate. Every place I worked we intermingled raw SQL when needed. Hibernate has a way to clear query caches so you can use raw SQL when you need to. You can just write raw SQL exclusively if you want within hibernate so I don't get how you could lose anything :) .

Still, my experience is mostly with Hibernate. It's extremely mature, meaning reliable, feature complete, and only 0-30% slower than raw queries in most cases. It makes adding support for things like multitenency and audited tables a breeze without losing database agnostic behavior. It makes database upgrades a no-brainer 95% of the time too. It has a built in memory caches that help enormously with common queries. Probably the biggest thing is it makes the database strongly typed so it's possible to refactor. Code refactoring in Java is easy but raw stringified SQL is nearly impossible to fix in any language.

I think the biggest counterpoint to ORM is shitty ORM. Things like SQLAlchemy generate downright horrific SQL slowing everything to a crawl and causing deadlocks. Another honest counterpoint to ORM is the learning curve. Everyone is taught SQL but the ORM is more abstract and harder to reason about, not a fun thing to learn.

TBH I think most ORM's are just poorly done. Putting an object abstraction on a relational database is hard. The only ones I've enjoyed for completeness and performance are Hibernate and to some extent Entity Framework. EF being the easiest to use but a bit slower with less features.

I have heard good things about Dapper but never used it. I like the idea of injecting a SQL DSL into the language itself, wish it was more prevalent.

I think your experience reflects that basic gist I've gotten from most professionals: ORMs are complicated and convoluted because they provide a solution for a very complicated problem. If you are somewhat new and don't understand things, it's easy for you to see the few problems introduced by ORMs and think that it means that the whole concept is just unnecessary bloat. With more experience and wisdom, you understand that the few quirks of ORMs are a necessary evil for having a software that solves such a complex and flexible task.

That's a good analysis of things, and on top of that, when you're new you'll treat an ORM like a hammer in search of a nail. So someone who's less experienced and doesn't realize that you should supplement the ORM with raw SQL queries might try to evaluate complicated queries in the ORM, with the usual results. Some of that has changed with e.g. LINQ and IQueryable in .NET, but it's no panacea.

>> Things like SQLAlchemy generate downright horrific SQL slowing everything to a crawl and causing deadlocks.

Not my experience... Would you care to explain ? SQLA works pretty well for me, as long as I stay in rather simple queries. For example, data analysis queries are next to impossible to express with SQLA, but that doesn't matter much.

For the simple update queries, it just works fine for me. It also allows for very good control on relationships, etc.

I've always had success with ORMs when I let them write the 95% of trivial queries (SELECT * FROM table, INSERT INTO table, UPDATE table SET ... WHERE id = x). The complex queries I'd always rather write by hand to take advantage of the expressiveness of SQL, and to make sure that queries hit my indexes correctly.

I totally agree. Mr. Fowler's article lays out a good case for ORMs being a good, leaky abstraction with manholes for diving deeper when necessary. That implies that we can't, as application developers, use it as a black box, because underlying knowledge is important. Until someone comes up with a magic algebra for paving over the details SQL, we're stuck mitigating or dealing with its realities.

One of the things I've recently prioritized is the ability to run arbitrary queries (generally read-only) at some sort of REPL prompt, for debugging and investigative purposes. ORMs and their offshoots offer a great deal of support in that area because you can converse with an ORM in a domain-specific way without dropping into raw SQL. That can be a big advantage.

Maybe things have changed, I haven't used it since school and needed an example :-/

Amazing how perception works, everything you said about Hibernate and SQAlchemy is what I think about them, only switching them around. At least for the one time I had to use Hibernate, which was two years ago.

And not just because Java is an odious, ugly language, but the SQL Hibernate generates I just didn't like.

Mind you, this is with complex queries. For 90% of them, I didn't notice any difference between them, as far as generated SQL goes anyway.

Reply on news.ycombinator.com