What ORMs have taught me: just learn SQL (2014)

I guess my experience is limited, but I’ve not seen much of this despite working in an ORM environment with around 75 entities for the last few years (my recent experience anyway, the rest goes back 16 years). Maybe that is small potatoes, I don’t know, but I’ve found that anyone who understands JPA well enough can work to avoid any pitfalls of using ORM. It seems to me that having a good mix of understanding SQL and ORM is a good thing; and especially understanding exactly what the ORM system is doing for you and how it is doing it. Dropping ORM altogether sounds like a bad idea since it provides a number of built-in security features as well as an abstract modeling paradigm that is fairly easy to conceive and maintain; provided, of course, that you learn to say “No” to protect the integrity of the model (such as rejecting the attribute creep the article warns about).

I have found, in my experience, that people who tend to want to write SQL over ORM usually want to do so because they simply know SQL better. That’s okay, there is nothing wrong with that. But that doesn’t immediately mean ORM systems are bad. No need to be tribal about it.

The problem I see is that many new software developers these days sometimes can’t see the forest for the trees because they dwell too much on what they think is better instead of simply seeing the software and abstractions as nothing more than tools in the tool belt. It happens everywhere — PC vs Mac, iOS vs Android, Scala vs Java, SQL vs ORM. It’s fine to have opinions, I have many, but as I’ve aged I’ve become acutely aware that my biases are almost solely rooted in the limitations of my understanding.

`create proc if not exists get_customer_v1243(params)`

.. I've seen this.

I mean you could, but it’d need to be through some kind of migration system to update it.

Yeah EF is great for CRUD. The way I distinguish whether EF is going to be used or not is simply whether the workload is OLTP or OLAP. At OLTP EF excels. It's terrible at OLAP (ORMs generally are) so I'll drop to raw ADO.NET and (if lots of data has to go in to SQL Server) table valued parameters.

Use the right tool for the job.

"What ORMs have taught me: just learn SQL"

Could be retitled:

"I should have learned SQL before dealing with ORMs."

Every single point the author brings up comes seems to come down to simple database design, lazy development, or not understanding their tools. They really don't seem to have anything to with ORMs or query languages.

Any screwdriver can make for a bad hammer and some screws may go in with a large enough mallet.

> Perhaps the most subversive issue I've had with ORMs is "attribute creep" or "wide tables"

Normalization of data is required whether you're using a query language or an ORM to access it. The fact that ORMs make it easy to "hide" the fact that you've added 500 columns to a table isn't the ORM's fault.

> Knowing how to write SQL becomes even more important when you attempt to actually write queries using an ORM. This is especially important when efficiency is a concern.

What do they think the ORMs are doing? Magical incantations over the disks? The ORMs are just using queries too. You can write really horrifically bad queries in a query language and also abuse ORMs, but that doesn't make either one bad. Most ORMs can let you see precisely the SQL they are creating. If not, the database will surely log the queries for you and let you know what's going on.

> The problem is that you end up having a data definition in two places: the database and your application.

Welcome to the fact that we have multi-layered technology? There's always going to be discrepancies between the layers that have to be ironed out because no data designs are perfect or future-proof. The author then attempts to bring migrations into the picture as if database migrations are somehow just not a problem if you aren't using ORMs (hint: database migrations have always been tough even in very well-design systems).

> Dealing with entity identities is one of those things that you have to keep in mind at all times when working with ORMs, forcing you to write for two systems while only have the expressivity of one. What this results in is having to manipulate the ORM to get a database identifier by manually flushing the cache or doing a partial commit to get the actual database identifier.

Sounds like a pretty frustrating example, but I've worked with at least 10 different ORMs I can think of off of the top of my head and not a single of them required "manually flushing a cache" or a "partial commit" to "get the actual database identifier." I wouldn't write this up as being an issue with ORMs or that this problem would be magically fixed by only writing SQL either.

> Transactions. Something that Neward alludes to is the need for developers to handle transactions. Transactions are dynamically scoped, which is a powerful but mostly neglected concept in programming languages due to the confusion they cause if overused.

Transactions are pretty straightforward and I cannot agree with: "The concept of a transaction translates poorly to applications due to their reliance on context based on time."

Transactions don't care about time at all. They care about order and making sure that things are completed in a certain series of steps. This actually translates very well to applications, especially when you have processes that take a long time, where you don't want something to happen unless another thing happens first.

While a decently-written article, this comes across as someone who learned about ORMs more deeply than databases, discovered the flaws that ORMs have, and decided that query languages must be the only way forward.

This ignores the fact that we created and adopted ORMs after struggling through years of rigid queries smattered throughout code.

Writing bare queries has a time and a place, but ORMs have saved countless hours of development time, and allowed for vastly improved longevity of code.

Don't throw the baby out with the bathwater.