Skip to content
Tutorials Center
My learning

Topics

Lesson 94 of 147 ยท Databases

ORM Concepts: Active Record vs Data Mapper

Understand what an ORM does, compare the Active Record and Data Mapper patterns by building tiny versions in PHP, and learn to spot and fix the N+1 query problem.

With PDO you write SQL and get arrays back. That is flexible and fast, but a large application ends up with the same chores everywhere: build an INSERT from an array, turn rows into objects, remember which column holds which property, load an author's books with a second query. An ORM (Object-Relational Mapper) automates those chores. It maps tables to classes, rows to objects and columns to properties, so you can write $book->title = 'Dune'; $book->save(); instead of assembling SQL by hand.

Laravel's Eloquent, Doctrine, Cycle ORM and RedBean are all ORMs. They differ mainly in which pattern they follow, and those patterns shape how your whole application is organized. This lesson explains the two main ones, Active Record and Data Mapper, by building a miniature of each on top of PDO, and then tackles the most common ORM performance trap, the N+1 query problem. Everything here runs on in-memory SQLite, so you can experiment freely.

The mismatch an ORM bridges

Relational databases think in tables, rows, foreign keys and joins. PHP thinks in objects, references and collections. Moving data between the two worlds (sometimes called the impedance mismatch) is tedious and repetitive:

flowchart LR
  A["PHP objects<br/>Book, Author"] <-->|"ORM"| B["SQL tables<br/>books, authors"]
  B <--> C[("Database")]

An ORM translates between objects in your code and rows in tables.

An ORM typically handles four jobs:

  1. Mapping. Which class belongs to which table, which property to which column.
  2. Persistence. Creating, updating and deleting rows from object changes.
  3. Querying. Building SQL from method calls (Book::where('year', '>', 1950)), always with prepared statements.
  4. Relations. Loading related objects (an author's books) lazily or eagerly.

It is not magic and not always the right tool: reporting queries with complex joins and bulk updates are often clearer as plain SQL. Most projects use both.

Pattern 1: Active Record

In Active Record, each object wraps one row and knows how to save itself. The class holds data and database behavior together: $book->save(), Book::find(3). It is simple and productive, which is why Laravel's Eloquent and Ruby on Rails use it.

Here is a working miniature. A Model base class does the mapping by convention (the table name comes from a static property), and subclasses stay tiny:

PHP A tiny Active Record base class

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

That is the heart of Active Record: find() builds an object from a row, property writes update an in-memory attribute array and save() decides between INSERT and UPDATE. A real ORM adds dirty tracking (only update the changed columns), type casts, timestamps, soft deletes, query builders, events and much more.

Advantages: very little code, easy to learn, everything about a table lives in one class. Drawbacks: the domain object is tied to the database (you cannot create a Book without a connection being configured), every model inherits many methods, and unit-testing business rules often means touching a database.

Pattern 2: Data Mapper

Data Mapper separates the two responsibilities. An entity is a plain PHP object that knows nothing about SQL. A separate mapper (or repository) moves data between entities and rows. Doctrine ORM works this way.

classDiagram
  class Book {
    +int id
    +string title
    +int year
    +isClassic() bool
  }
  class BookMapper {
    +find(int id) Book
    +save(Book book) void
    +delete(Book book) void
  }
  class PDO
  BookMapper ..> Book : creates and reads
  BookMapper --> PDO : uses

The entity holds only data and business rules; the mapper owns all SQL.

PHP A tiny Data Mapper

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Book can be created, tested and passed around without a database. Swapping SQLite for MySQL, or the mapper for an in-memory fake in tests, changes nothing in the entity. The cost is more classes and more ceremony, and real Data Mapper ORMs need clever machinery (a unit of work that tracks all changes and an identity map that makes sure one row gives exactly one object).

Active Record Data Mapper
Who saves? The object ($book->save()) A mapper/repository ($mapper->save($book))
Domain objects know SQL? Yes, they inherit it No, they are plain objects
Learning curve Gentle Steeper
Best for CRUD-heavy apps, fast development Rich domain logic, long-lived systems
Examples Eloquent, Yii AR Doctrine, Cycle ORM

Neither is "right". A blog or admin panel is happy with Active Record; a system with intricate business rules benefits from keeping them in database-free entities. You can also pair them: use Eloquent models but keep complex rules in separate service classes. The CRUD with PDO lesson showed the repository style by hand; a Data Mapper ORM generates that layer for you.

Relations and the N+1 problem

Relationships map to foreign keys: an author has many books, a book belongs to one author. An ORM lets you write $book->author->name, and lazy loading runs a query the first time you touch author. That convenience hides a trap.

Suppose you list 100 books with their authors. Loading the books costs 1 query. Then touching each book's author lazily costs 100 more, one per book: N+1 queries in total. Each query is fast, but a hundred round trips to the database add up, and the number grows with your data.

sequenceDiagram
  participant A as App
  participant D as Database
  A->>D: SELECT * FROM books (1 query)
  D-->>A: 3 books
  A->>D: SELECT * FROM authors WHERE id = 1
  A->>D: SELECT * FROM authors WHERE id = 2
  A->>D: SELECT * FROM authors WHERE id = 1
  Note over A,D: One extra query per book: N+1

Lazy loading inside a loop sends one query for the list and one more for every row.

The fix is eager loading: ask for the related rows up front in a constant number of queries, typically with a JOIN or a second query using WHERE id IN (...). This demo counts queries so you can see the difference:

PHP Counting queries: N+1 versus eager loading

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

With 6 books, lazy loading used 7 queries; eager loading used 2 regardless of size. Real ORMs offer this directly: Eloquent has Book::with('author')->get(), Doctrine has fetch joins and EXTRA_LAZY/EAGER modes. Make these habits:

  • Count queries during development. Eloquent can throw on lazy loading (Model::preventLazyLoading()), and debug bars list every query.
  • Eager load what a page will definitely use, and lazy load what it might not.
  • Fetch only the columns you need on big tables, and add pagination so lists stay small.
  • Index foreign key columns (author_id) so lookups stay fast.
  • Use plain SQL for reports. A single grouped query often beats loading thousands of objects to add them up in PHP.

Security and transactions still apply

An ORM does not change the rules. It should use prepared statements for values, but identifiers (table and column names) cannot be bound as parameters. Note how the Model::all($orderBy) demo interpolated $orderBy: that is only safe because the caller is trusted code. Never pass user input there without checking it against a fixed allow-list. Related writes that must succeed or fail together still belong in a transaction; ORMs wrap them as DB::transaction(...) or $entityManager->wrapInTransaction(...).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the defining trait of the Active Record pattern?

    In Active Record, data and persistence behavior live together in one class, with methods such as save() and find().
  2. 2. Which statement describes Data Mapper correctly?

    Data Mapper separates domain objects from persistence so that business rules can be tested without a database.
  3. 3. You load 50 posts and then access $post->author inside a loop with lazy loading. About how many queries run?

    One query loads the posts and one more query per post loads its author, which is the N+1 problem.
  4. 4. Which approach fixes an N+1 problem?

    Eager loading fetches the related data in a constant number of queries instead of one per row.
  5. 5. Why can't a table name from user input be protected with a prepared-statement placeholder?

    Identifiers are part of the SQL structure. Validate them against an allow-list instead of binding them.

Summary

  • An ORM maps classes to tables and objects to rows, and handles persistence, query building and relations.
  • Active Record objects save themselves ($model->save()); it is quick to use but couples domain objects to the database.
  • Data Mapper keeps entities free of SQL and puts persistence in mappers or repositories; it takes more setup but suits rich domain logic.
  • Lazy loading in a loop causes the N+1 query problem; fix it with eager loading (with(), JOIN, WHERE IN) and monitor query counts.
  • Prepared statements protect values, not identifiers; allow-list column and table names.
  • ORMs and plain SQL work well together: use each where it is clearer.

Next: Web Security Basics