OrmHate (2012) | Hacker News Reader

OrmHate (2012)

martinfowler.com

62 pointswilsonfiifi49 comments

I started my career out writing database applications. Every aspect of those applications were schema-driven, and I would generally would try and solve problems at a schema level (e.g. for rendering/presentation, etc.)

In application development, it's gotten very popular to ignore the data storage component of application design, or at least defer it to the last possible moment. React et al take things even further by requiring their own modeling logic.

As many here would agree, relational dbs never went away. But for some reason folks stopped trying to push the envelope with schema-driven application development. I kept expecting to see iterations of schema-driven design and layout system (using inputs derived from table fields, etc). What we got instead is this ORM business. Is there a schema-first tooling community that I'm unaware of?

I'm not sure if this fits your bill, but you can go pretty far into application logic by combining PostgreSQL and PostgREST (https://postgrest.com/).

I think that's a great example!

Yeah, to me SQL Mappers like MyBatis are usually the way to go. I don't want the power of SQL abstracted away from me in most cases.

If I had a nickel for every time I've seen code using an ORM framework fetching a set of ids and then iterating over that set to fetch a single record at a time. And then if I had another nickle for each blank stare I got when I explained that only one SQL query was needed to return the flattened result set of data and that the hierarchical object graph could be built in the app much faster than the N SQL queries they were making.

I really feel like i'm owed a considerable number of nickels for all the times i've seen posts and comments about ORMs which miss the fact that they have powerful query languages built in. JPQL is a genuinely pretty nice way of querying a database - it's basically SQL, plus object property syntax [1]. Here's a little example which concisely examines a many-to-many relationship:

  SELECT DISTINCT p
  FROM Player p
  WHERE p.teams IS NOT EMPTY

Or navigation through a path (i made this example up, might not be quite right):

  SELECT t.name, t.league.sport
  FROM Team t
  WHERE t.city = 'Ul Qoma'

If you're doing reporting where it doesn't make sense to pull out the entities, you can use a constructor expression [2] to materialise results as anything else you like (again, made up, might not be spot on):

  // package hn; public class CountSummary { public CountSummary(String name, int count) {} }
  SELECT NEW hn.CountSummary(l.name, COUNT(l.teams))
  FROM League l

Certainly, there are more sophisticated features of SQL that are useful for analytical queries that aren't in this query language. That's why every ORM has an escape hatch where you can write raw SQL, but still use its machinery for turning results into objects.

[1] https://docs.oracle.com/javaee/6/tutorial/doc/bnbtl.html

[2] https://docs.oracle.com/javaee/6/tutorial/doc/bnbuf.html#bnb...

Many years ago, I wrote a Java ORM that solved that exact problem. But I still have come around 180 degrees to the view that ORMs are terrible.

Even aside from this N+1 problem, to use an ORM well, you have to understand the SQL that you want to be generated, and then instead of writing that SQL, you have the additional problem of having to know exactly what to say to the ORM to get that SQL generated. Also: you don't always want objects returned; sometimes partially instantiated objects are what you want; bulk updates are hard to get right; stored procedures break everything; and on and on.

The thing that really started me on the path to my current view was a conversation with an app developer who was evaluating our project. Just from a career point of view, having Oracle on her resume was a lot more valuable than having OurProprietaryORM on her resume. That, combined with the realization that solving the N+1 problem was really a pretty minor thing, and that all the other problems were even harder to solve.

The approach I have been using for a while is to use a very simple mapping layer that hides some of the ugliness of JDBC, but still lets me write SQL directly. I understand that MyBatis does something like this, although I haven't used it.

>fetching a set of ids and then iterating

The ironic part? The folks who do that tend to be the same folks who swear the ORM will pay for itself by query-optimizing under-the-hood. 9_9

This article focuses on ease of use i.e. avoiding the nastiness of RDBMS entities as in-memory data structure. But the main reason most of us used ORM is because of its portability between different databases. In the on-premise deployment era, each customer was a Oracle/MSSQL/MySQL shop and ORMs magically solved this problem by letting us write once and run on any database. Today, this portability problem still exists between different cloud vendors' NoSQL databases, even if it solves the mapping problem.

The downside of using ORM this way is that it doesn't really solve the interesting cases, or more subtle differences in behaviour.

By "interesting cases" I mean applications that leverage the more unique features in different database systems (like the full-text search implementations, that tend to be very database specific). By subtle differences I mean e.g. specific issues like Oracle handling NULL vs. empty string unlike other databases, or more serious differences related e.g. to locking (so an application that works fine on one database fails spectacularly on another one).

Obviously, if you treat the advanced database system as a dumb data store, this may work. But it kinda tends to reduce any database to the worst non-existent database, missing any of the nice features. Because all the advanced stuff is "forbidden" for portability reasons. I've seen so many cases of performance issues where the right solutions were rejected because it would impact portabiliy ...

It's a tradeoff, though. For 99% of LOB applications the odd details of specific database implementations are irrelevant and can be ignored by even a team of mediocre developers. Most businesses are mostly interested in selling 5-10x the number of licenses for 1.1x the cost. (Or, like the sibling comment pointed out for 0.9x the cost because you can usually ignore much of the annoying boilerplate) Performance frequently is more than "good enough" and in the 1% case where it isn't, most ORMs will let you write custom SQL (or, you can always write it outside the ORM). Sometimes the best and cheapest solution to performance problems is to simply throw more hardware at it. IME, most developers are terrible at writing queries with or without an ORM.

There are certainly use cases where ORMs are not appropriate at all but the ability to evaluate your situation and recognize the tools that are appropriate and those that are not is an important skill in the software industry in general and is not limited to ORMs.

>> By "interesting cases" I mean applications that leverage the more unique features in different database systems

Actually, most ORMs like Hibernate allow you to write native SQL (or even stored procedure) to take advantage of native db features. In that case, you have to trade-off portability, if that feature is important to you.

>> or more serious differences related e.g. to locking (so an application that works fine on one database fails spectacularly on another one)

Locking (default - READ_COMMITTED) also works consistently across databases. The problem happens only when you introduce distributed ORM cache where data is not updated synchronously across the cluster. It is a reasonable trade-off for performance.

Something like jOOQ allows you to use the fancy SQL features while still keeping portability.

"Most of us"?

I've never worried much about portability, but i've really appreciated an ORM saving me writing all the tedious boilerplate for turning ResultSets into objects, and objects back into parameters to update statements, particularly when changing collection properties.

A data mapper can do that just fine. You only need an ORM if you’re trying to model the relations.

The biggest advantage I see is maintennability. After years of ORM, I have to say that result is less effort to keep as not complete mess then the older way - which tended to end in much more horrible state.

No reason to hate ORM unless you're letting the ORM tool generate your schema, in which case, you might get really lucky... or you might find out the hard way that there's a fine distinction between DBAs and software engineers.

ORMs make 95% of queries/tasks simpler, but when you encounter the other 5% the lack of "practice" regarding how the database works under the hood will bite you in the ass.

Somehow I became the "database guy" of my workplace (small startup without a dedicated DBA) and everybody thinks I really dislike our ORM because all they hear me say about it is pretty negative. However, that is because nobody comes to me with the 95% of easy questions so every question I get is one of the 5% edge cases where the ORM is really not a good fit :|

That's been my experience. My team (webservices at a large enterprise) has come to rely on ORM as a crutch—the rule is, if we use an ORM, we don't need to run our schemas past a DBA. This probably came out of necessity (most of the DBAs at my org tend to be more sysadmins responsible for installing Oracle, rather than real DBAs), but the results—bloated schemas that are difficult to query efficiently, various tricky performance gotchas, etc.—should not have been surprising.

The problem I have with every ORM I've used is it feels tacked on. The only framework that I know that treats queries like a first class citizen is .Net. The beauty of Linq -> Expression Trees -> data store can't be overestated.

The fact that you can use the same Linq expressions in your code and they can be translated to in memory Lists, Sql, or even MongoQuery at runtime is beautiful.

Agreed. I used to be on the .NET hate bandwagon because I didn't understand it and conforming to MS was more of a business-suit idea for me. However, EF + LINQ + expression trees are amazing.

I don't know that a better ORM than ActiveRecord is imaginable. It's seriously good, and getting better every year. I don't think a better DSL for database wrangling exists, and dropping down to SQL or the intermediate representation (arel) couldn't be easier, you can take any ActiveRelation object and call .to_sql on it to see what it's doing.

I've used DataMapper and Sequel, and read through the ROM docs, I don't think any of them really bring something better to the table than good, old, solid Active Record. Linq is expressive and fun, compared to .NET. Nothing compared to Ruby.

As abstraction layers go, I can't ask for a better one.

While I agree that ActiveRecord is really good in a lot of ways, the entire ecosystem around it is a giant clusterf* waiting to happen, and it always does. Fat models, callbacks, concerns, scopes... changing a model reliably without 100% test coverage is damn near impossible for any relatively complex application and knowing what your model is going to do on any given call is a nightmare when you have a dozen+ callbacks/concerns/scopes/whatever. That 'oh crap I need to deploy a big change, is it going to work right?' feeling never, ever goes away.

I've used both ActiveRecord and Entity Framework extensively. And while Entity Framework definitely has it's drawbacks, it doesn't get any better than Linq in combination with static compilation for a large and complex code base with a lot of moving parts.

And yes, "don't do that" is all well and good when it comes to the 'right' way to do things in Rails/ActiveRecord, but when DHH himself is driving the callback train at full speed and you take a project over from someone who jumped on the bandwagon and didn't actually know how to properly apply these tools to the code, it's going to be a pretty nasty wreck.

You can be damn sure I'd rather take over and refactor a large, messy C#/Linq/Entity Framework project than a large, messy Rails/ActiveRecord project.

Also I could never get over the fact that Rails developers for some reason treat foreign keys like it's ebola. "It's just not the Rails way" is not a good answer for not using foreign keys.

I agree, ActiveRecord is pretty good. My main gripe with it is that bulk inserts are _still_ not a first class operation, which can easily lead to some horrendous N+1 queries if you are not careful. There are multiple great gems to retrofit that though.

It is also a bit overzealous in being database agnostic. For example, MySQL allows for LIMIT clauses on UPDATE and DELETE statements, which is super useful for making sure you don't accidentally create super long running transactions because there were many more rows on the other side of that relation than you expected. However, if you want to use that from ActiveRecord you need to drop down to custom SQL, because not all databases support that SQL pattern.

> I don't know that a better ORM than ActiveRecord is imaginable.

SQL Alchemy https://www.sqlalchemy.org/

That's a fair suggestion, as I don't use Python. My feeling is that it would be the same as Linq, really amazing compared to the other Python alternatives, but ultimately hampered by Python when you're trying to compare it across languages / stacks. My distaste for Python runs somewhat deep, I certainly wouldn't involve myself on any web project that uses it. Maybe for games.

sqlalchemy is hands-down the best ORM I've ever seen. It only gives you exactly as much magic as you want (imho an annoying problem with anything related to Django) and instead of generating SQL more or less directly it operates on an SQL DSL, which can be extended at just the right points to use pretty much any DB feature, including those sqlalchemy does not know or understand.

I don't know Linq personally but I know people liking it very much -- your comparison is probably accurate.

I have always found ActiveRecord to be extremely limiting, and Sequel to be much better. Some random limitations I have had to deal with over the years (in no particular order):

* No support for "OR" statements, without dropping down to raw SQL (I think this was added in either Rails 4, or Rails 5)

* Terrible support (if any) for more advanced PostgreSQL features, such as CTEs, bigserial (Rails 5 finally supports this if I'm not mistaken), no proper support for composite primary keys, no support for PostgreSQL CHECK constraints, no support for index expressions (e.g. an index on `lower(foo)`), etc. For GitLab we had to add many hacks to work around this.

* A rather messy model API, where models are used as both repositories and objects representing single rows. Sequel makes the same mistake with its ORM, but at least you can use the query toolkit separately. ROM supposedly does this better, but I have not tried it.

* Too many methods are injected into your classes, and you'll probably never use most of these (this is a more philosophical issue, and certainly not a deal breaker).

There are plenty more, but these are the ones that come to mind. My biggest issue in recent times was caused by ActiveRecord/Arel silently throwing away CTEs used in UPDATEs. Basically in GitLab we did something like this (I don't fully remember what the query was):

    WITH foo AS ( ... )
    UPDATE some_table 
    SET a = b
    WHERE x = foo.y

In other words: we'd grab some data using a (recursive) CTE, then update a bunch of rows based on that data. Unfortunately, ActiveRecord/Arel would throw away the CTE in certain cases. This would result in us effectively running the following:

    UPDATE some_table
    SET a = b

You can imagine the fun of having to deal with all rows being updated unexpectedly. More details on this particular bug can be found here: https://gitlab.com/gitlab-org/gitlab-ce/issues/37916

Long story short, I'm fairly convinced that using Sequel would have made GitLab's database code much more solid/less buggy than it is today, simply because it supports so much more (and better) out of the box.

.NET’s entity framework (combined with Linq in C#) is pretty amazing — if it’s not better than ActiveRecord, it’s at least a strong contender.

When I last used it (maybe 5 years ago), Entity Framework would generate positively awful, bloated SQL queries for complex nested includes. Maybe that's improved now?

ActiveRecord always did the right thing, and whenever I looked at the generated SQL I was pleased.

It generally does that if you tell it to. Meaning, you can't completely ignore the fact that the linq query you are writing will be translated into SQL and the closer your linq query looks to the target SQL you are trying to generate the better EF will do (and it does a great job for a wide range of queries from simple to complex in the systems I have worked on).

ActiveRecord is really good, but Sequel[1] is much more flexible. I think it starts from a better base, where as ActiveRecord wraps and mostly hides arel.

That said, they're both enormously productive.

1: https://github.com/jeremyevans/sequel

I've used both somewhat extensively, and while I would have agreed with you a few years ago, nowadays I see my earlier ardor for Sequel as mostly just youthful exuberance.

Recently I went looking for a schema generation tool, found one that went and looked at an existing database, and generated scaffolding commands to create models and if necessary, controllers and basic CRUD. Only thing it didn't do was has_many associations, of course you could just add those in before running the generated commands. You just can't find this level of refinement anywhere else except maybe Java, of course then you'd have to use Java.

One thing I don't like in the rails/ar mindset is that they hide behind ruby. Example, some ruby methods are provided by active record (projections) but others may not (.pluck). I prefer my queries predictable. Of course if you are a rails senior you already know all the gotchas and can be efficient.

And, in my experience, an ActiveRecord model backed by a database view will solve pretty much every problem you run into where ActiveRecord alone just doesn't cut it.

My problem with ORMs is that they feel like overkill for small apps, and they merely get in your way with large apps. And I have yet to be on a project with Hibernate where they were using the caching and really understood what they were doing.

Well, I once worked on a 1 MLOC (as far as that’s relevant) high school SaaS administration system providing services for a large chunk of the Dutch market. Hibernate, EHcache and cache synchronization across applications (with an Electronic Learning Environment) all worked well. The team of 12 developers with a small minority had expert knowledge in Hibernate and cache management.

The biggest problem I often see in large DB systems is the lack of proper normalization, leading to very wide tables and subsequently causing performance problems (both due to reduced scanning speed and ORMs tendency to just pull in all attributes of selected rows).

Another problem with Hibernate is the proper demarcation of transactional boundaries, causing DB inconsistencies. Very important to get right.

I could go on, but then this would become a blog post. In any case, I feel that a functional mapping over the SQL AST and result sets using Scala and Slick (for example) leads to much higher versatility, places computation closer to storage and provides better abstraction possibilities. This, in turn, reduces the complexity of the middle layer.

> a small minority had expert knowledge in Hibernate and cache management.

How does Hibernate as a project (or the community around it) develop people who have expert knowledge in it and/or general cache management?

It doesn’t! Most was self taught by reading sources and some O’Reilly books. Community support was rather marginal at the time when true technicalities were discussed. Same as Stack Overflow now: loads of people with superficial knowledge, but very few with in-depth or architectural technical prowess. Unfortunately, those ppl are often too occupied to scan the SO posts and a simple answer skimming the true ideas often wins the accepted answer instead.

Love this. What I've seen lately, as a contractor at several different companies, is inexperienced devs getting a very long way building apps without knowing what's going on with their database under the hood. That's a testament to the value of ORMS.

It's common, then, that the low hanging fruit for a contractor coming in to improve performance is to optimize queries and/or introduce triggers, views, and stored procedures. I've found that devs are scared of plain old SQL but the ones who know their ORM well can learn SQL easily with a little nudge and some guidance.

It is futile to try to hide the relational model. Now days, I think it needs to be embraced.

'futile' is a false wall.. maybe common rational talk has been polarized by entrenched interests in a technology .. BUT single column and/or schema-less has shown its raw power, without a doubt

> In the 90's many of us (yes including me) thought that object databases would solve the problem by eliminating relations on the disk. We all know how that worked out.

What am I supposed to know about this? I've only used an object database for one serious (>100KLOC) project, but it was by far the best part of that system. I always wondered why that architecture never caught on.

The only reason I'm not using an ODBMS on my own projects today is that there's no good open-source one, but that seems like something a company could do something about, and a lousy reason to condemn an entire architecture.

OrientDB is a good open-source database that exposes a primarily object data model (the document and graph abstractions are built on top of it).

Lack of a good query language and scalability issues doomed those. Mike Stonebraker (https://blog.grakn.ai/what-goes-around-comes-around-52d38ee1...) had a nice summary in that article.

There are 4 reasons proposed for the failure of OODBs, in that paper, but scalability is not among them.

Interestingly, the issues are largely C++-specific (and I know lots of people using an ORM today but none via C++), and largely driven by historical accident and market forces ("It is interesting to conjecture about the marketplace chances of O2 if they had started initially in the USA with sophisticated US venture capital backing").

I still don't think these sound like good reasons to dismiss the architecture.

I agree that a pure ODBMS makes no much sense today, but OrientDB is a Multi-Model where the Object Model is one of the supported models. You can mix objects, graphs, schema-less documents and much more + using SQL as the query language. Boom!

(Disclaimer: I am the founder of OrientDB)

Good article. I’m currently using Entity Framework in .net and I’ve designed the latest bit of software I’m developing by using the balanced approach advocated here; the modelling and the RDBMS are both part of the system and have their own constraints one has to bear in mind. After working with EF there’s only one thing I couldn’t express that I can do in SQL and that’s have multiple parent keys in the parent table to represent relationships with multiple children. It took me a while to work out and I believe it is possible in .net core.

> deal with the object/relational mapping problem by writing their own framework

Now, instead, everybody deals with the object/ORM mapping problem by writing their own framework.

I'm moving my ORM into a real database, anyone interested to join me? Need anyone with major in DBMS, able to write SQL parser and database storage/organization. We'll be selling data model instead of database when completed. Check us out at: http://shujutech.mywire.org/corporation?goto=ormj

I don't get it. Firstly, is this being cited in sincerity, or to point out "Look, even Martin Fowler advocated for NoSQL back in 2012"?

If it is entirely in sincerity, I don't understand why he thinks queries isn't the de-facto solution. Perhaps his implicit assumption is that the database should be structured to reflect the code, when really the code should be structured to reflect the database?

Reply on news.ycombinator.com