Lesson 2 / 35
The Object-Relational Mismatch
Name the gaps between objects and tables (identity, relations, inheritance, granularity) and predict where an ORM has to make a choice for you.
By Rahul Agarwal · 15 years in web development
Why mapping is harder than it looks
Objects and tables model data differently, and this gap is called the object-relational impedance mismatch. Objects hold references: user.posts points straight to an array. Tables hold foreign keys: a posts.author_id column stores a number, and you must join to follow it. Objects have identity by memory address; rows have identity by primary key. Objects support inheritance; SQL has no direct equivalent, so the ORM must pick a strategy such as one table per hierarchy. Objects form graphs you can walk forever, while a SQL query returns a flat, finite set of rows. Each gap forces the ORM to make a decision on your behalf: when to load related rows, how to represent a many-to-many link, what to do when two objects describe the same row. Knowing these decisions is what separates using an ORM from fighting it.
The same data, two shapes
On the left is what your code wants; on the right is what the database stores. The ORM converts between them.
// What application code wants: nested objects with references
const user = {
id: 1,
name: 'Asha',
posts: [
{ id: 10, title: 'Hello SQL' },
{ id: 11, title: 'Joins 101' },
],
};
// What the database stores: flat rows linked by a foreign key
// users: (id=1, name='Asha')
// posts: (id=10, title='Hello SQL', author_id=1)
// (id=11, title='Joins 101', author_id=1)Loading is never free
Because objects look like they always contain their related data, students assume user.posts costs nothing. In reality something must decide to run a second query or a join. In Prisma you ask with include, in TypeORM with relations, in Sequelize with include. If you forget, you either get no data or, worse, a hidden query per row.
Quick check: In a relational database, how does a post usually record which user wrote it?
- By storing a copy of the whole user object inside the post row
- By storing a memory reference to the user
- By storing an array of post IDs inside the user row
- By storing the user's primary key in a foreign key column such as author_id
Answer
By storing the user's primary key in a foreign key column such as author_id — Relational tables link rows through foreign keys. The ORM turns that key into an object reference when you ask it to load the relation.