What ORMs have taught me: just learn SQL (opens in new tab)

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.

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.

I think if you are using an ORM you should know SQL anyway. If you are running into trouble with the mappings it means the the database layout isn't how you are using it in your application.

And that is find when you have more than one application using the database, but, if you are the only one using it, then likely you should change it until it does.

In our team we typically use noSql for databases with single applications, and SQL for ones where we have a lot of applications hanging off the same database.

Just because it means we can match what the application is doing and the database closer.

The ORM vs SQL debate is a false dichotomy, at least if you take Hibernate as an example.

You can use a combination of JPQL, native SQL, and object mapping. There's nothing stopping you from mixing and matching.

You can use an ORM to save entities, and using native SQL to retrieve projections, Spring Data makes this easy, as it will do the mapping, if you provide an interface.

"In these cases, I’ve elected to write queries using a templating system and describe the tables using the ORM. I get the convenience of an application level description of the table with direct use of SQL. It’s a lot less trouble than anything else I’ve used so far."

This has been the best that I've encountered myself.

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.

(2014)

One case where I still think ORMs like SQLAlchemy make a great sense is in open-source software than can be configured to work with different databases (lime SQLite/Postgres/Maria).

If you own the entire stack and can control what FB the software is used with, fine, but it can be worth trading some performance to flexibility.

The two big issues I always had with ORMs are automatic joins and inheritance.

I‘m probably just not knowledgable enough on how to use e.g. Hibernate „correctly“ but modeling any entity with inheritance was a recipe for disaster at some point. Also Collections and Hibernate were an incredible pain.

DB stuff is not the thing I‘m very good at (as probably gleaned from my comment) but even pretty basic stuff kind of fell apart pretty fast at any sort of non-hobby scale. At the same time I dread handling transactions and de/serialization more manually…

A number of specialists in the field of databases got together and designed a DSL specifically for working with databases. It's actually a pretty good fit for its domain and is reasonably well designed. It's also pretty simple (it's even right in the name).

Then a bunch of non-specialists said "we don't want to use more than one language" and came up with some hacks to write DB queries in languages that were never designed for that. The hacky stuff actually ends up generating the proper query language behind the scenes, poorly.

One would wonder why would anyone prefer the hacks, but the appeal of using one language for everything is strong. This is also why DSLs never took off in general despite promising to significantly lower the cognitive load due to being a better fit for the problem domain. At least with most DSLs there are usually additional reasons not to use them: the implementation is usually not as good as the popular languages. This is not true for SQL, and yet is suffers from the same problem.