The Case for SQL over ORMs: Lessons Learned (2014)

After significant experience, I have reached a definitive conclusion: Object-Relational Mappers (ORMs) often do more harm than good.

While they can serve as a helpful supplement to SQL within a program, they should never be viewed as a total replacement for it.

🛠️ Context & Background

For the last 30 months, my work has centered on interfacing with Postgres and SQLite. My experience is split across two major tools:

Tool	Sentiment	Note
`SQLAlchemy`	👍 Positive	Generally well-regarded
`Hibernate`	👎 Negative	Often problematic

My work involved both legacy codebases and fresh designs, primarily focusing on event-based storage (timelines) with a heavy requirement for generating complex reports.

The "Impedance Mismatch"

The industry often discusses the Object/Relational Impedance Mismatch. As noted in the influential essay by Neward, ORMs frequently devolve into "quagmires." In my own tenure, I've collided with several specific manifestations of this:

Entity Identity Issues
The Dual-Schema Problem
Data Retrieval Concerns
The Partial-Object Problem

Below is a conceptual look at the mismatch:

📉 Attribute Creep and the "Wide Table" Trap

One of the most insidious issues is "attribute creep." This happens when tables continuously accumulate new columns. While tools like Postgres' hstore can mitigate this, sometimes it's unavoidable—especially when clients provide vast amounts of business logic data that you are simply "schlepping" around without deep insight.

The problem manifests when you use the entity to build a query. Consider this early-stage Hibernate query:

// Simple and clean when Foo has 5 attributes
query(Foo.class).add(Restriction.eq("x", value))

~~This is efficient.~~ This is a disaster once Foo grows to 100 attributes. This approach is functionally equivalent to SELECT *, which retrieves far more data than necessary.

Performance Note: I once optimized a query by replacing the entity-based fetch with a precise projection. The runtime dropped from minutes to seconds. The bottleneck wasn't the DB; it was the overhead of translating massive database rows into Java objects.

The Foreign Key Nightmare

ORMs represent class links as foreign keys. Without meticulous configuration, this leads to an explosion of joins. In one instance, a single object retrieval triggered:

600+ attributes
14 joins

This proves that to use an ORM effectively, you must be proficient in SQL. My logic is simple: $\text{If } (\text{SQL Knowledge} = \text{Required}) \rightarrow \text{Just use SQL}$ . This removes the guesswork of how non-SQL code is being translated.

🔍 The Struggle of Data Retrieval

Unless your data model is trivial (i.e., zero joins), you will spend an exorbitant amount of time fighting the ORM to produce efficient SQL.

The Trade-off:

Keep the query simple $\rightarrow$ Do heavy lifting in application code (Slow).
Use advanced SQL (e.g., Window Functions) $\rightarrow$ Painful to implement via ORM.

If you avoid writing advanced SQL in the query, you end up transferring massive amounts of unnecessary data. My solution? Use a templating system for the raw SQL while using the ORM solely to describe the table structures.

⚠️ The Dual-Schema Danger

Redundancy is almost inevitable. You end up defining your data in two places: the database and the application.

App-Centric: Writing DDL (Data Definition Language) via the ORM is as cumbersome as writing complex queries.
DB-Centric: Keeping definitions in the DB is better, but you still need an app-side representation to avoid "string typing."

I prefer the DB-centric approach, though I've found reflection techniques too tedious to be worth the effort. However, migrations remain a nightmare. Application changes are easy; database changes are persistent and painful. ORMs offer little help here.

My Philosophy: Database definitions should not be manipulated within the application. I prefer thinking in terms of $\text{Functions} \rightarrow \text{Return Types}$ rather than $\text{Objects}$ .

🆔 The Identity Crisis

Managing entity identities requires constant vigilance. You are essentially writing for two different systems using the expressivity of only one.

Database Identity: A unique identifier (Primary Key).
Application Identity: A memory location (Pointer).

These two concepts are fundamentally at odds, and the friction between them is a constant source of complexity when using an ORM.