ORM Concepts: Entities, Repositories, and Query Builders
Understand the core building blocks behind any Object-Relational Mapper -- entities, repositories, relationships, lazy vs eager loading, the identity map, unit of work, dirty checking, and query builders -- by implementing simplified versions of each in plain PHP.
An ORM (Object-Relational Mapper) is a layer that translates between two very different worlds: rows and foreign keys in a relational database, and objects and references in your PHP code. Frameworks like Doctrine or Eloquent hide most of this machinery behind a friendly API, but understanding what's actually happening underneath -- how an object gets mapped to a row, how relationships get loaded, how changes get tracked and eventually saved -- makes you far more effective at using (and debugging) any ORM, and lets you reason clearly about performance problems like the notorious N+1 query. This lesson builds each core ORM concept from plain PHP so the mechanics are visible rather than hidden behind framework magic.
Entities and the Repository Pattern
An entity is a plain PHP object that represents a single row of a table, identified by a unique key (almost always an id). Unlike a raw associative array pulled from a PDOStatement, an entity has typed properties, can carry behavior (methods), and gives you IDE autocompletion and static analysis instead of magic string keys. Mapping is the process of turning a database row into an entity object (hydration) and turning an entity's current state back into a row to persist (usually an INSERT or UPDATE).
A repository is an object responsible for retrieving and persisting entities of one type, hiding the actual SQL behind method names that read like intent: findById(), findActiveUsers(), save(). The point of a repository is to give the rest of the application a collection-like interface to entities -- 'give me the user with this id' -- without every caller needing to know or care what SQL makes that happen. This also gives you a single place to swap implementations (e.g., add caching, or point at a different data source) without touching calling code.
The repository pattern deliberately separates two concerns that raw SQL scattered through the codebase tends to tangle together: what data you want (expressed by calling a repository method) and how it's actually fetched (the SQL/query-builder logic living inside the repository). This separation is most of what makes a codebase's data-access layer testable and maintainable as it grows.
• Entity: a typed PHP object mapped to a single database row, identified by a unique key. • Hydration: turning a raw DB row into an entity object. • Repository: an object exposing intent-based methods (findById, save) that hide the SQL behind them. • Repositories decouple 'what data is needed' (calling code) from 'how it's fetched' (SQL/query builder).
Relationships and Loading Strategies
Relational databases connect tables through foreign keys, and ORMs mirror this as relationships between entities. A one-to-many relationship -- one Author has many Book rows -- is represented as the Author entity exposing a collection of related Book entities. A many-to-many relationship -- many Student rows relate to many Course rows -- requires a join/pivot table (student_course) under the hood, even though at the object level it just looks like $student->courses and $course->students collections pointing at each other.
The critical design decision for any relationship is when the related data actually gets fetched. Lazy loading defers the query until the related property is actually accessed -- $author->books triggers a SELECT * FROM books WHERE author_id = ? only the moment you touch ->books, not when you loaded the Author. This keeps simple reads cheap, but is the classic cause of the N+1 query problem: looping over 50 authors and touching ->books inside the loop fires 50 separate queries, one per author, instead of one.
Eager loading fetches the related data up front, typically via a JOIN or a second batched query (WHERE author_id IN (...) for all authors at once), so that accessing ->books later costs nothing extra. Eager loading is the fix for N+1 in list/report scenarios where you know in advance you'll need the relationship for every row, but it can waste work (and memory) if you eager-load a relationship you end up not using on most requests. The right call depends on the access pattern: lazy for 'usually not needed', eager for 'needed for every row in this specific query'.
• One-to-many: one entity's collection of related entities (Author -> many Books). • Many-to-many: needs a join/pivot table under the hood (Student <-> Course via student_course). • Lazy loading: fetches related data only when accessed -- simple, but causes N+1 in loops. • Eager loading: fetches related data up front (JOIN or batched IN query) -- fixes N+1 for known access patterns. • Choose per query: eager when you know you'll need the relationship for every row; lazy otherwise.
Identity Map and Unit of Work
The identity map pattern guarantees that within a single request (or unit of work), fetching the same row twice returns the exact same object instance, not two separate objects with duplicate copies of the same data. A repository backed by an identity map checks an in-memory cache keyed by entity class + id before hitting the database; if the object is already loaded, it returns the cached instance. This matters because without it, mutating 'the user' in one part of the code wouldn't be visible to another part of the code holding a different object instance for the same row -- a subtle and confusing class of bug.
A unit of work tracks every entity that's been created, modified, or marked for deletion during a request, and defers actually writing any of it to the database until you explicitly call something like flush(). Instead of issuing an UPDATE the instant you call $user->setEmail(...), the unit of work just notes the change; when flush() runs, it computes the full set of necessary INSERTs, UPDATEs, and DELETEs and issues them (ideally inside a single transaction), often in dependency order.
This batching has real benefits: it avoids issuing dozens of tiny UPDATE statements when you're really making one logical change involving several entities, and it lets the ORM wrap the whole batch in a transaction so it either all succeeds or all rolls back together, rather than leaving the database in a half-updated state if something fails partway through.
• Identity map: same row fetched twice within a request returns the same object instance. • Prevents divergent, out-of-sync copies of 'the same' entity existing simultaneously in memory. • Unit of work: tracks pending inserts/updates/deletes and defers writing until flush(). • flush() computes and issues the batched SQL, typically wrapped in a single transaction.
Dirty Checking and Query Builders
Dirty checking is how a unit of work knows which properties of an already-loaded entity actually changed, so it only updates the columns that need updating instead of rewriting every column on every save. The typical mechanism: when an entity is first hydrated from a row, the ORM snapshots its original property values; at flush time, it compares the entity's current values against that snapshot, and any property that differs goes into the UPDATE statement's SET clause. An entity whose properties never changed generates no UPDATE at all.
This is why calling a setter doesn't necessarily mean a query will fire -- $user->setEmail($user->getEmail()) sets the property to the value it already had, so a correctly implemented dirty-check sees no actual difference and skips the UPDATE. Real ORMs implement this with snapshot-and-compare logic much like the manual version shown in the examples below.
A query builder is a fluent, object-oriented API for constructing SQL programmatically -- $qb->select('*')->from('users')->where('active = ?', [true])->orderBy('created_at', 'DESC') -- instead of concatenating raw SQL strings by hand. Its advantages over raw strings are composability (you can conditionally add ->where() clauses based on runtime logic without fragile string concatenation), safety (bound parameters are handled for you, reducing injection risk), and portability (many query builders can target more than one database dialect). The tradeoff is an extra layer of abstraction between you and the SQL that actually runs, which is why most query builders also let you drop down to raw SQL for genuinely complex queries.
• Dirty checking: compares current property values against a snapshot taken at load time. • Only properties that actually changed are included in the UPDATE's SET clause. • No changes detected -> no UPDATE is issued at all, saving a needless query. • Query builder: fluent API for building SQL programmatically -- composable, parameter-bound, easier to reason about conditionally than raw string concatenation.
A Minimal Entity + Repository Shape
The entity knows nothing about SQL -- it's a plain object with data and behavior. The repository is the only place that knows how a User maps to and from the users table.
final class User
{
public function __construct(
public readonly int $id,
private string $email,
) {}
public function getEmail(): string { return $this->email; }
public function setEmail(string $email): void { $this->email = $email; }
}
final class UserRepository
{
public function __construct(private PDO $db) {}
public function findById(int $id): ?User
{
$stmt = $this->db->prepare('SELECT id, email FROM users WHERE id = ?');
$stmt->execute([$id]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
return $row ? new User((int) $row['id'], $row['email']) : null;
}
}Code Examples
The repository keeps a small in-memory cache keyed by id. The second findById(42) call returns the exact same User instance without touching the database, which is exactly what an identity map guarantees within a single request/unit of work. Expected result: bool(true) if (isset($this->identityMap[$id])) { return $this->identityMap[$id]; }
The lazy version is simple but fires one SELECT per author the moment getBooks() is called inside the loop -- classic N+1. The eager version fetches every author's books in a single batched IN (...) query, then groups the results in PHP, giving O(1) queries instead of O(n).
Common mistake: Calling a lazy-loading relationship method inside a foreach loop over many parent entities without realizing each call is a fresh query -- 50 authors silently becomes 51 round trips to the database.
track() snapshots the entity's original state. Changes made through the object's setters aren't written immediately; flush() compares each tracked entity's current state against its snapshot and only issues an UPDATE for entities that actually changed, all inside a single transaction. This is the same idea real ORMs use for dirty checking -- compare current vs original, update only what differs, batch and transact the whole set at flush time.
if ($current !== $entry['snapshot']) { $update->execute([$current, $id]); }
Each method returns $this (or static), letting calls chain fluently. WHERE conditions and their parameters are added independently and safely bound, so conditional query construction (adding a where() only when a filter is actually active) stays clean instead of turning into fragile string concatenation.
public function where(string $condition, mixed $param): static { ...; return $this; }
<?php
declare(strict_types=1);
final class User
{
public function __construct(
public readonly int $id,
private string $email,
) {}
public function getEmail(): string { return $this->email; }
public function setEmail(string $email): void { $this->email = $email; }
}
final class UserRepository
{
/** @var array<int, User> id => already-loaded instance */
private array $identityMap = [];
public function __construct(private PDO $db) {}
public function findById(int $id): ?User
{
if (isset($this->identityMap[$id])) {
return $this->identityMap[$id]; // same instance, no query
}
$stmt = $this->db->prepare('SELECT id, email FROM users WHERE id = ?');
$stmt->execute([$id]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row === false) {
return null;
}
$user = new User((int) $row['id'], $row['email']);
$this->identityMap[$id] = $user;
return $user;
}
}
$repo = new UserRepository($pdo);
$a = $repo->findById(42);
$b = $repo->findById(42);
var_dump($a === $b); // true -- identical object instance, second call hit no DB<?php
// --- Lazy: touching ->books fires one query PER author inside the loop ---
final class Author
{
private ?array $books = null;
public function __construct(public readonly int $id, private PDO $db) {}
public function getBooks(): array
{
if ($this->books === null) {
$stmt = $this->db->prepare('SELECT title FROM books WHERE author_id = ?');
$stmt->execute([$this->id]);
$this->books = $stmt->fetchAll(PDO::FETCH_COLUMN);
}
return $this->books;
}
}
// 50 authors -> 1 query to fetch authors + 50 more inside the loop = 51 queries (N+1).
foreach ($authors as $author) {
echo count($author->getBooks()) . " books\n";
}
// --- Eager: one batched query for every author's books up front ---
function loadBooksForAuthors(PDO $db, array $authorIds): array
{
$placeholders = implode(',', array_fill(0, count($authorIds), '?'));
$stmt = $db->prepare(
"SELECT author_id, title FROM books WHERE author_id IN ($placeholders)"
);
$stmt->execute($authorIds);
$byAuthor = [];
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
$byAuthor[(int) $row['author_id']][] = $row['title
…<?php
declare(strict_types=1);
final class User
{
public function __construct(
public readonly int $id,
private string $email,
) {}
public function getEmail(): string { return $this->email; }
public function setEmail(string $email): void { $this->email = $email; }
}
final class UnitOfWork
{
/** @var array<int, array{entity: User, snapshot: string}> */
private array $tracked = [];
public function __construct(private PDO $db) {}
public function track(User $user): void
{
$this->tracked[$user->id] = [
'entity' => $user,
'snapshot' => $user->getEmail(), // original value at load time
];
}
public function flush(): void
{
$this->db->beginTransaction();
try {
$update = $this->db->prepare('UPDATE users SET email = ? WHERE id = ?');
foreach ($this->tracked as $id => $entry) {
$current = $entry['entity']->getEmail();
if ($current !== $entry['snapshot']) { // dirty checking
$update->execute([$current, $id]);
$this->tracked[$id]['snapshot'] = $current; // refresh snapshot
…<?php
declare(strict_types=1);
final class QueryBuilder
{
private string $table = '';
private array $wheres = [];
private array $params = [];
private ?string $orderBy = null;
public function __construct(private PDO $db) {}
public function from(string $table): static
{
$this->table = $table;
return $this;
}
public function where(string $condition, mixed $param): static
{
$this->wheres[] = $condition;
$this->params[] = $param;
return $this;
}
public function orderBy(string $column, string $direction = 'ASC'): static
{
$this->orderBy = "$column $direction";
return $this;
}
public function get(): array
{
$sql = "SELECT * FROM {$this->table}";
if ($this->wheres !== []) {
$sql .= ' WHERE ' . implode(' AND ', $this->wheres);
}
if ($this->orderBy !== null) {
$sql .= " ORDER BY {$this->orderBy}";
}
$stmt = $this->db->prepare($sql);
$stmt->execute($this->params);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
}
$rows = (new QueryBuilder($pdo))
->from('users')
->where('ac
…Unit of Work: Track -> Mutate -> Flush
Changes accumulate in memory; only flush() talks to the database, once, in a transaction.
load User(7) setEmail('new@x.com') flush()
| | |
v v v
snapshot: old@x.com entity.email = new@x.com compare snapshot vs current
(tracked in UoW) (no query fired yet) -> differs -> UPDATE issued
-> same -> no queryImportant Rules
Entities hold data and behavior; repositories hold the SQL/query logic that maps entities to rows.
Use eager loading when you know a query's results will all need a relationship; lazy loading otherwise.
An identity map must be scoped to one request/unit of work, never shared globally across requests.
Dirty checking should compare against the snapshot taken at load time, not against the database on every check.
Prefer a query builder's parameter binding over concatenating variables into a SQL string.
Why This Matters in Production
N+1 query bugs from careless lazy loading are one of the single most common performance issues in ORM-backed applications -- a page that feels instant with 10 test rows can grind to a crawl with 10,000 production rows purely because a relationship access sits inside a loop. Understanding identity maps, unit of work, and dirty checking also demystifies why 'just call save()' sometimes issues zero queries, why the same object reference can show up twice, and why batching writes inside a transaction is safer than firing them one at a time as you go.
foreach ($authors as $author) { echo count($author->getBooks()); } // lazy relationship touched inside a loop over 50 authors
$booksByAuthor = loadBooksForAuthors($db, array_map(fn($a) => $a->id, $authors)); foreach ($authors as $author) { echo count($booksByAuthor[$author->id] ?? []); }
Touching a lazily-loaded relationship inside a loop fires one query per iteration (N+1). Batching the related data into a single eager query ahead of the loop turns 51 queries into 2.
$stmt = $db->prepare('UPDATE users SET email = ? WHERE id = ?'); $stmt->execute([$user->getEmail(), $user->id]); // runs on every save() call regardless of whether email actually changed if ($currentEmail !== $snapshotEmail) { $stmt->execute([$currentEmail, $user->id]); } // only issue the UPDATE when dirty checking finds an actual difference
Writing an UPDATE unconditionally on every save() wastes a write (and, at scale, real database load) for entities whose data never actually changed -- dirty checking exists specifically to avoid this.
[multiple-choice] What problem does eager loading solve that lazy loading tends to cause? A. Eager loading reduces the total amount of data transferred from the database B. Eager loading fetches related rows up front in one batched query, avoiding the N+1 problem of firing one query per parent entity inside a loop C. Eager loading makes entities immutable D. Eager loading removes the need for a repository
B. Eager loading fetches related rows up front in one batched query, avoiding the N+1 problem of firing one query per parent entity inside a loop — Lazy loading defers each relationship's query until access, which inside a loop over many parent entities means one query per entity (N+1). Eager loading batches all the related data into a single query up front.
[multiple-choice] What guarantee does the identity map pattern provide? A. That every query result is cached forever across all requests B. That fetching the same row twice within a request/unit of work returns the exact same object instance, not two separate copies C. That all entities are read-only D. That SQL injection is impossible
B. That fetching the same row twice within a request/unit of work returns the exact same object instance, not two separate copies — The identity map keeps a per-request/unit-of-work cache keyed by entity type and id, so repeated lookups for the same row return the same in-memory instance instead of separate, potentially divergent copies.
[multiple-choice] How does dirty checking decide which columns to include in an UPDATE? A. It always updates every column on the entity, regardless of whether it changed B. It compares the entity's current property values against a snapshot taken when it was loaded, and includes only the properties that differ C. It re-queries the database before every save to see what's different D. It relies on the developer manually calling markDirty() on each property
B. It compares the entity's current property values against a snapshot taken when it was loaded, and includes only the properties that differ — A snapshot of the entity's original values is taken at load time; at flush, current values are compared against that snapshot, and only the properties that actually changed are written -- entities with no changes generate no UPDATE at all.
You have a Product entity and an Order entity, where one Order has many OrderLine rows, each referencing a Product. Design (in words or pseudocode) whether Order -> OrderLines and OrderLine -> Product should be eager or lazy loaded for an order-listing page that displays every line item and product name for 50 orders per page, and explain why.
Order -> OrderLines should be eager loaded (batched IN query on order_id) since every order on the listing page needs its lines rendered. OrderLine -> Product should also be eager loaded (batched IN query on product_id across all line items) since every line displays its product name -- lazy loading either relationship here would cause an N+1 (50+ extra queries) exactly like the classic author/books example.
Think about what data every row on the page definitely needs versus what might only occasionally be needed.