Using a modern database? You might not need that ORM anymore

Object Relational Mapping (ORM) frameworks translate programming objects to SQL statements. ORM implementations are found in most programming languages, and frameworks like Hibernate and Prisma are extremely popular among developers for their ease of use and abstraction of common SQL queries.

I’ve used ORMs in many production-grade systems over the years, and I will likely continue to do so. However, sometimes ORMs won’t be my first choice for data access and management.

Where do ORMs come up short?

While ORMs deliver a powerful abstraction layer, every solution has its pros/cons, and ORMs are no different. Issues with ORMs generally fall into the following categories:

Performance Issues

N+1 Query Problem: ORMs may generate one query per row instead of a single join, leading to huge performance costs (N+1 queries instead of 1).
Lazy vs. Eager Loading Confusion: Fetch strategies may load too much or too little data upfront, increasing the number of database round-trips.
Inefficient SQL: ORM-generated queries may include unnecessary columns or joins, slowing execution compared to hand-tuned SQL.

Dev/Ops Flexibility

Evolving Schemas: Database schemas change over time, and handling schema upgrades or migrations with ORMs is tough. Versioned SQL files or something like Liquibase is often the way to go.
Stale Data / Caching Issues: ORM caches can serve outdated data if not carefully managed. Cache inconsistencies often appear in production environments!
Boilerplate on Classes: This is a personal annoyance of mine, but some ORMs require excessive class/field annotations, to the point that it greatly exceeds what you’d need to write for an equivalent table definition (DDL). I find these annotations more difficult to maintain and “get right” than a SQL DDL file.
Transactions and unit-of-work: Understanding how the ORM manages transactions, sessions, and persistence contexts can trip up developers. You may accidentally use more transactions than necessary, or find yourself using abstractions like @Transactional without fully understanding their scope.

Leaky Abstractions

Opaque Query Behavior: Developers may not understand the SQL an ORM is producing, and be surprised by production changes.
Debugging: ORM Debugging usually requires inspecting generated SQL and execution plans, which can be non-intuitive if you expected “no SQL”.
SQL Still Matters: Every time I’ve used an ORM for non-trivial applications, I find myself writing raw SQL anyway – whether it’s for complex queries, performance tuning, database-specific features, or getting table/index definitions just right. ORMs can’t cover all of SQL, and don’t intend to.

OK, what’s a viable alternative?

SQL statements can always be hand-written – It’s what developers did before ORMs came along. Before you balk, it’s regaining popularity. Articles like Why We Dropped the ORM and Went Back to Manual SQL argue for returning to plain SQL.

But, can we leverage database features to make this easier?

JSON Relational Duality Views take a different approach: rather than abstracting your database with a client-side framework, just send relational JSON documents directly to the database. The database transparently handles composition of JSON to/from relevant tables and columns.

An infographic illustrating the process of converting JSON data to and from relational data, featuring three steps: writing a JSON document, converting JSON for table inserts/updates, and loading table data as JSON.

New to JSON Relational Duality Views? Check out my article, 7 Reasons to try JSON Relational Duality Views, with samples.

Duality views are backed by relational schemas. If I have one-to-many, many-to-many, or more complex relationships, it can be represented in one or more JSON documents.

A visual representation of JSON documents mapping to relational data, showing a JSON structure for a student named Alice Smith with course enrollments, alongside a database schema illustrating the relationships between Students, Enrollments, and Courses.

Duality views also aren’t just for Oracle Database. MySQL has supported them since the 9.4 release – it would not be surprising if other multi-model databases follow suit.

Code snippet demonstrating the creation of a JSON relational duality view for weather sensor data in a database, incorporating attributes like relativeHumidity, temperature, and station details.

Your apps send JSON data directly, either through SQL statements or with a programmatic database client. Joins and the like are not required, and to insert or update relational data, I only need to serialize programmatic objects to JSON and send them to the database. All types of relationships (one-to-many, many-to-many, etc.) can be modeled with duality views.

Column-level data access is easily implemented through the view-per-service pattern, where each persona/app is accesses data through a duality view with the minimum set of fields and permissions.

Importantly, document reads, writes, and updates are always done as one database round trip, regardless of the relational data those documents contain. Concurrent writes can be handled through optimistic, lock-free concurrency, allowing multiple apps/threads manage the same data without corruption/loss.

Final Thoughts

ORMs vs. SQL vs. JSON Relational Duality Views is not a zero-sum, one-size-fits-all approach. If you’re working across multiple databases and need portability, or if your team is comfortable navigating the potential pitfalls or performance issues of ORMs: you’re probably going to want to use an ORM.

ORMs cover a vast middle ground. But they can’t – and don’t intend to – cover the feature set of a modern database engine. If you find yourself coming up against the pitfalls of ORMs often, it’s time to consider an alternative approach – like hand-written SQL or JSON Relational Duality Views.

andersswanson.dev