ORM — Object-Relational Mapper: what the mapping problem is

Beginner5 min read·eng-15-001

interviewsql

Concept

ORM (Object-Relational Mapper) — a layer of software that maps between the relational world (tables, rows, columns, SQL) and the object-oriented world (classes, instances, properties).

The mapping problem: Relational databases organize data in rows and columns with relationships via foreign keys. Object-oriented code organizes data as objects with properties and references. These two paradigms don't fit naturally — this mismatch is called the "Object-Relational Impedance Mismatch."

What an ORM does:

Maps SQL table → PHP class.
Maps table row → PHP object instance.
Maps column → object property.
Maps foreign key relationship → object reference or collection.
Generates SQL for CRUD operations so you write PHP instead of SQL.

Problems an ORM solves:

You don't write SELECT * FROM users WHERE id = ? everywhere — you write User::find($id).
Related data feels natural: $order->user->name instead of a JOIN query.
Schema changes propagate via migrations, not manual SQL.
Type safety: ORM can cast columns to PHP types.

Problems an ORM introduces:

Hides SQL: Developers don't know what SQL is being generated → accidental N+1 queries.
Over-fetching: SELECT * when you need 2 columns.
Complex queries: Some SQL is hard to express via ORM — drop to raw SQL.
Performance: ORM overhead for simple queries. Not suitable for bulk operations.

PHP ORMs:

Eloquent (Laravel): ActiveRecord pattern. Model = table + query methods + relations.
Doctrine (Symfony): DataMapper pattern. Model is a pure PHP class, Doctrine handles persistence separately.

Code Example

php

<?php
// WITHOUT ORM — raw PDO
$pdo = new \PDO('mysql:host=localhost;dbname=myapp', 'user', 'pass');

// Finding a user by ID
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ? AND deleted_at IS NULL');
$stmt->execute([$id]);
$row = $stmt->fetch(\PDO::FETCH_ASSOC);
// $row is an array: ['id' => 1, 'name' => 'Alice', 'active' => '1', ...]
$name   = $row['name'];
$active = (bool) $row['active'];  // manual type cast!

// WITH ORM (Eloquent)
$user   = User::findOrFail($id); // generates: SELECT * FROM users WHERE id = 1 AND deleted_at IS NULL
$name   = $user->name;           // $name is a string
$active = $user->active;         // $active is a bool (via cast)

// THE MAPPING:
// Table 'users'                 → class User extends Model
// Column 'id'                   → $user->id
// Column 'name'                 → $user->name
// Column 'active' (TINYINT)     → $user->active (bool, via cast)
// FK 'orders.user_id'           → $user->orders() relation → $user->orders Collection

// Queries
User::where('active', true)->get();
// → SELECT * FROM users WHERE active = 1 AND deleted_at IS NULL

User::with('orders')->find($id);
// → SELECT * FROM users WHERE id = ?
//   SELECT * FROM orders WHERE user_id IN (?)

// IMPEDANCE MISMATCH examples:
// 1. Inheritance: How do you map class hierarchy to tables? (TPH vs TPC vs CTI)
// 2. Collections: A user has MANY orders — array in PHP, multiple rows in DB
// 3. Identity: Two PHP objects with same id — are they the same? ORM identity map says yes.
// 4. Lazy vs eager: When do you load related data?

PreviousBootstrapper vs Service Provider — two different boot phasesVocabulary — Laravel & Framework NextActiveRecord vs DataMapper — two ORM patterns, different trade-offsVocabulary — Database & SQL