Oh look, it's another ORM good/bad thread
Tags: dotnet, sql
Published: 2020-03-19 23:47:48

{% twitter 1239951039694024704 %}

That tweet inspired this post, but I’m not trying to call @rickasaurus out. This debate is probably endless.

At this point the use of an ORM hardly needs defending, and there's plenty of room on the field for opposing viewpoints. But as someone who switched sides, I wanted to write this for the benefit of anyone who is today where I used to be. (I'm primarily a .NET developer; my choice of tools obviously affects my opinions.)

I once viewed ORMs with suspicion. The idea of building an application on top of a [Rube Goldberg machine](https://en.wikipedia.org/wiki/Rube_Goldberg_machine) of questionable abstractions seemed unwise. I have never agreed with the idea that the database is just an implementation detail; it is a critical part of your system and should be treated that way. And I was comfortable with SQL, so why bother?

*(If you want a well-written argument that sums up my original feelings about ORMs, look at [ORM is an anti-pattern](https://seldo.com/posts/orm_is_an_antipattern). Although I'm walking away from its premise in this post, it's full of valid concerns, and worth reading for anybody who cares about the subject. I also recommend the related [In defence of SQL](https://seldo.com/posts/in_defence_of_sql).)*

I made plenty of use of [Dapper](https://github.com/StackExchange/Dapper), which is often called a "micro-ORM". But I don't think that's an accurate description. Tools like Dapper aren't trying to solve the [object-relational impedance mismatch](https://en.wikipedia.org/wiki/Object-relational_impedance_mismatch) so much as they just help you use SQL with a minimum of boilerplate and risk. (I'd call Dapper a *SQL Facilitator*, but "SQLF" hasn't really taken off.) I'm not about to criticize Dapper -- I still use it quite a bit.

But I finally gave myself time to learn Entity Framework Core, and I have been **kicking myself ever since for not having tried it earlier**.

### A good ORM is a wonderful tool

My code is **so** much easier to change. Major domain changes have actually been possible without headaches! Users have not noticed the small performance hits! And -- the value of this cannot be overstated -- I spend an *order of magnitude* less time on the SQL that I would otherwise constantly be tweaking and fixing.

Most of the SQL in an application of decent size is not complicated. It’s straightforward queries and writes, with one or two joins, a few parameters ([I hope](https://en.wikipedia.org/wiki/SQL_injection)), and that’s about it. Things can get complicated in a few places but the bulk will be just... normal. The problem with this “normal” SQL is that you’re on the hook for updating and testing it every time you change your domain model. In this way the plain old SQL is actually coupled to your model, but without any assistance from static typing. Keeping all that SQL up to date quickly gets tedious. An ORM takes this pain almost entirely away. The developer efficiency gains are enormous.

My last big project owes its completion timeline at least partly to the [magic unicorns](https://twitter.com/efmagicunicorns) that are Entity Framework Core. I have actually considered retrofitting an earlier project with it just to make the project easier to work on in the future. The future benefits would outweigh the cost of migration. It's that useful.

### It doesn't have to hurt your design

The ORM is tightly coupled to your data model, that's true, but the reverse is not true. Your model is firmly in the driver's seat.

There's no debating the fact that even the cleanest ORM will exert some influence on your domain design. Hopefully more indirect than direct, but either way it's manageable, if you're thoughtful about it. Generally I'm tweaking the ORM to fit the entities rather than the other way around. The biggest adjustment I might need to make is if my preferred design just isn't supported, but that's rare.

The fact that I'm using a relational database is far more of an influence. That's to be expected and is not a problem if an RDBMS was the right choice to begin with. Every time I make significant changes I have to consider the design from several angles, one of which is the database itself. How the ORM handles the translation is one of those angles too, but usually not a distracting one.

### You don't have to give up SQL

I still write SQL. I definitely use it in tiny services that don't need an ORM. When I've only got a couple of reads and writes, dipping into good old SQL is the fastest and easiest approach. I generally favor Dapper for this because I'm used to it, and its design strongly encourages practices like parameterization, but that's personal taste.

I also write SQL in larger applications that use Entity Framework. Hey, not every query is tied to the domain. Sometimes you have a simple question to ask the database, and the query operates at a higher level that is only tangentially related to the domain model. And sometimes the ORM just isn't going to figure out the right query; they're designed for common cases, not fancy ones.

If the operation fits into the domain then it's ORM all the way. But if not then I may write it myself. I could certainly use EF's native SQL support for this -- and I should try that, given all the recent improvements -- but up to this point I've almost always reached for Dapper. Old habits die hard.

The last decently sized application I set up used Entity Framework Core for (very roughly) about 90-95% of database operations, Dapper for the rest.

I'm still happy to be proficient with SQL. But I now consider every bit of it that I write myself to be a potential liability in the same way that [code is a liability](https://saintgimp.org/2009/03/11/source-code-is-a-liability-not-an-asset/): something that needs upkeep, and may turn out to be the thing that breaks down the road. It's fine as long as there's not too much of it. Organization is important.

---------------

ORMs have unavoidable limitations and efficiency problems. Valid criticisms can be made. But I'm here to say: 

**The flexibility, simplicity, and convenience provided by proper use of an ORM is absolutely worth the learning curve and the cost.**

---------------

One last thing. If you want to set things up right, it is helpful, if not critical, to understand what your relational database is doing and why. Getting into an ORM was a lot less risky for me than it could have been because by the time I tried it I had years of experience working directly with the database. Understanding RDBMS concepts is an important and productive use of your time, one that will pay dividends even after you switch to another RDBMS, another ORM, or move away from them both entirely.