What ORMs have taught me: just learn SQL

I know quite a few languages, frameworks, what have you and if you can't see the benefits of not adding another damn thing for people to learn on to the pile, then I have no idea how you've missed a lesson that up to this point, it seemed every engineer I'd met has learned.

Not having to specify the mapping between business objects and a SQL database is massive value on it's own. Having not only the ability to avoid doing that, but upon making changes to your objects, being able to migrate the database easily is frankly amazing.

This post should be titled "what ORMs have taught me: world is black and white, if you use ORM, you can't use SQL".

Are ORMs leaky abstraction? Yes. All non-trivial abstractions are leaky.

Can you write 95% of code in ORM and drop to raw SQL for 5%? Yes.

This. In the vast, vast majority of cases I prefer writing my stuff with LINQ. The ease of use and type safety is just brilliant. And still for more complex queries or queries that don't perform well enough I still reach for SQL. The point of ORMs is not to obsolete SQL.

I don't know why but there is this myth that ORMs remove the need to understand what a database is. I've used an ORM that supports both RDBMS and Mongodb and the biggest difference is that the RDBMS lets you write joins in your ORM queries. You still need to understand that the data you are interacting with is in a difference process or even on a different server that you are connected over a network.

ORM is a nice acronym to throw around but people got the impression that it does more than that. Not having to learn SQL... What is that even supposed to mean? You still need to know how to write queries in an ORM. Most ORMs come with very nice query builders for that reason. I personally enjoy writing dynamic queries for extended search functionality. Supporting 3 dozen different fields to filter is much easier when you don't have to concatenate SQL or use boolean flags to toggle individual clauses of a huge static SQL query.

Yeah they do suck at reporting, performance and supporting old organically grown schemas that weren't designed with the limitations of your ORM in mind. Whenever I need to insert thousands of rows, it's much faster without the ORM. I've never thought, oh the last 10% can't be done with the ORM, throw the entire thing away. Just be pragmatic and do what is most effective. I personally am tired of writing raw insert/update/delete queries that only touch a single row. For me there is nothing to be gained by not using an ORM when you can.

My biggest pet peeve with JVM ORMs is that they don't work with GraalVMs native image feature. They create proxies and use reflection. I want a good out of the box experience so I don't use native image very often. It's a shame.

I did learn SQL and used it for years.

Then got on some ORM using projects and hated them. Joins were painful. I had to learn a new syntax which seemed really dumb when I already knew SQL so why the need to add a layer of complication on top?

Over time I got used to it. Recently inherited a project that didn't use an ORM. Tons of stored procedures I have to go look up. Tons of "on the fly" SQL with string concatenation (yes including the potential for SQL injection because author was apparently unaware you can't just take raw user input safely into your SQL strings). And it's just ugly the whole thing. String concatenation with a bunch of "if" statements.

A fan of good ORMs here. But wasn't always, it was a conversion.