Notesdata-models🌱 seedling

Object-Relational Mismatch

Also: Impedance Mismatch · Object-Relational Impedance Mismatch · ORM · N+1 Query Problem

Application code holds nested object graphs; relational databases hold flat rows. The translation layer between them, usually an ORM, saves boilerplate but leaks, most famously as the N+1 query problem.

Why it exists

A User with a list of Jobs, each pointing to a Company, is one object in memory but rows in three tables. Something must split it on write and reassemble it with joins on read.

How it works

  • ORMs (Hibernate, ActiveRecord, Django ORM, Prisma) map classes to tables and fields to columns, and load related objects on access.
  • Lazy loading makes a database look like memory: touching user.jobs silently runs a query.
  • N+1 problem: one query loads N parents, then each lazy access fires one more query: N+1 round trips, growing with the data.
  • Fixes: eager loading (Rails includes, Django select_related / prefetch_related), one join, SQL that builds the nested result (Postgres json_agg), or storing the aggregate as one Document Model record.

Trade-offs

Gains Costs
less mapping boilerplate, type-safe access hidden round trips and unexpected queries
database portability generated SQL can be inefficient
objects stay idiomatic the relational model's strengths (set operations, joins) get hidden

In practice

  • Log SQL in development and assert query counts per request in tests; N+1 is invisible in code review.

Pitfalls

  • Each lazy query is fast, so N+1 never shows up in a profile of a single query, only in page latency at scale.
  • Fetching whole object graphs when a page needs two columns.

Recall

These cards are also in your review deck, scheduled with spaced repetition.