The idea: where does the SQL live?
Most business applications keep their data in a relational database, and somewhere in the code a class writes the SQL. Martin Fowler's Patterns of Enterprise Application Architecture (2002) names four ways to place that code, the data source architectural patterns: Table Data Gateway, Row Data Gateway, Active Record and Data Mapper. On top of a Data Mapper it adds a Query Object and a Repository.
The page does one small job in every tab: load person 1 from the people table, add a dependent (2 → 3), and save. The business rule is the book's: exemption = 1500 + 750 × numberOfDependents, so 3000 becomes 3750. Every tab sends the same two SQL statements, a SELECT … WHERE id = ? and an UPDATE people SET … WHERE id = ?. What changes is which class writes them (the lavender SQL chip), which object holds the data (the In-memory data column), and where the business rule lives (the green logic chip). Demo 1: same flow, four ways fills in the scoreboard under the drawing.
Table Data Gateway: one object for the table
A Table Data Gateway is one object per table that holds all the SQL for it: finders such as find(id) and findWithLastName(name), and insert, update and delete. It keeps no state; it just moves data back and forth. A finder returns the rows in a generic structure, here a RecordSet (rows and columns, as in ADO.NET or JDBC), and the caller reads fields by column name.
It pairs with Transaction Scripts, procedures that hold the business logic, so on the canvas the rule 1500 + 750 × n runs in AddDependentScript. The SQL is all in one place, easy for a DBA to find and tune. The cost: the script works with columns, not with an object, so it knows the schema. The Java world calls this a DAO; with Spring it is often a class on JdbcTemplate that returns Map<String, Object> rows.
Row Data Gateway: one object per row
A Row Data Gateway is one object per row: a field per column, plus its own insert, update and delete. A separate finder (PersonFinder) runs the SELECT and makes one gateway per row. The gateway has no business logic; the rule still sits in the script. Row gateways are tedious to write, so they are usually generated from the schema. jOOQ's generated records are a real example: store() writes back only the fields that were set.
Move the business rule into the row object and it is no longer a gateway: it is an Active Record. Demo 3: a row gateway grows into an Active Record.
Active Record: the row with business logic
An Active Record is a domain object that wraps one row: its fields match the columns one to one, it has static finders (Person.find(id)), it saves itself (person.update()), and it holds the business logic (addDependent(), getExemption()). It is simple and easy to understand, and it works well while the domain classes match the tables.
It breaks down as the domain grows. Inheritance, collections and small cooperating classes don't map onto one-row-one-object, and the domain class depends on the database, so you can't test it without one. Rails' ActiveRecord, Laravel's Eloquent and Django models follow this pattern. A Rails model doesn't even declare its fields: it reads them from the table when it starts.
Data Mapper: a layer neither side knows about
A Data Mapper is a separate layer that moves data between objects and tables and keeps them independent of each other. PersonMapper runs the SELECT, creates the Person and fills it. On save it pulls the fields out of the Person and writes the UPDATE. Person has no SQL, no save() and no import of anything from the database. The domain model and the schema can then change separately, and the domain can be tested with no database.
It is the most complex of the four, and Fowler advises buying one rather than writing it: Hibernate and every JPA provider are Data Mappers. With JPA the mapping metadata sits on the domain class as annotations (@Entity, @Column), but the class still sends no SQL. What a Hibernate Session adds on top (an identity map, dirty checking, flush order, lazy loading) is on the Hibernate Session page.
The four compared
| Table Data Gateway | Row Data Gateway | Active Record | Data Mapper | |
|---|---|---|---|---|
| builds the SQL | the gateway (one per table) | the finder and each row object | the domain class | the mapper |
| holds the data | a RecordSet | a row object | the domain object | the domain object |
| business logic in | transaction scripts | transaction scripts | the domain object | the domain object |
| domain → database dependency | no domain object | no domain object | yes | no |
| rename a column: edits | 2 classes, 4 lines (caller too) | 2 classes, 2 lines | 1 class, the domain class | 1 class, the mapper |
| unit test, no database | ~ with a fake gateway | ✗ | ✗ | ✓ |
| pairs with | Transaction Script, Table Module | Transaction Script | a simple Domain Model | a rich Domain Model |
The arrows are the important difference (Demo 2: who depends on whom). With Active Record the arrow goes from Person to the table. With Data Mapper the mapper depends on Person and on the table, and Person depends on nothing. This is the same dependency inversion that Hexagonal Architecture uses: the domain is at the centre and the persistence code points at it.
Renaming a column
A DBA renames number_of_dependents to dependents (Demo 4: rename a column). Every class whose code names the column must change. With a Table Data Gateway that is the gateway and the script, because the script reads the RecordSet by column name. With a Row Data Gateway it is the finder and the gateway class, and the script is untouched. With Active Record it is a single class, but that class is the domain class, so a schema change edits business code. With a Data Mapper only the mapper changes. Behind a Query Object with mapping metadata it is one line of metadata and no class at all. See Coupling and Cohesion for counting a change's ripple in general.
Testing the domain without a database
The unit test (Demo 5: unit test without a database) runs "add a dependent" with no database at all:
- Table Data Gateway: passes with a fake gateway, but the fake has to build RecordSets keyed by column names, so the test knows the schema.
- Row Data Gateway: fails at the first SELECT. The script creates its finder itself, and the row object saves itself.
- Active Record: fails the same way.
Person.findis static andupdate()runs SQL. OnlygetExemption()on its own can be tested withnew Person(…). - Data Mapper: passes. The service depends only on the mapper's interface, the test plugs in an in-memory mapper, and
Personis a plain object.
A row gateway with a finder passed in from outside could be partly rescued, and an in-memory database can stand in for the real one. But that is an integration test, not a unit test. The kinds of doubles are on Test Doubles.
Query Object: field names in, SQL out
A Query Object is an object that represents a database query, an instance of the Interpreter pattern. The caller builds it from criteria written in the language of the domain: greaterThan("numberOfDependents", 0), matches("lastName", "f%"). The Query Object asks the mapping metadata (DataMap) which column holds each field, then writes the WHERE clause: number_of_dependents > ? AND UPPER(lastname) LIKE UPPER(?). The book's small example pastes the values into the SQL. The page binds them as parameters, as real code must, to avoid SQL injection. Demo 6: Query Object, criteria to SQL.
Because the query is written in field names, only the metadata knows the schema. A variant, query by example, takes a half-filled Person as the criteria. JPA's Criteria API and Querydsl are Query Objects.
Repository: a collection in front of the mapper
A Repository (written for the book by Edward Hieatt and Rob Mee) sits between the domain and the mapping layer and looks like a collection of domain objects: repository.matching(criteria) returns the Persons that satisfy the criteria, and soleMatch or byObjectId return one. The client never runs a query. It asks the collection, and the repository decides how to answer.
Inside, the repository delegates to a strategy (see Strategy and Factory). A RelationalStrategy builds a Query Object and goes to the database. An InMemoryStrategy checks each object in a set with criteria.isSatisfiedBy(person) and sends no SQL. The caller can't tell them apart, which makes fast tests easy (Demo 7: swap the repository's strategy). Spring Data's repositories and its Specification criteria are today's best-known form.
Which one when
The book's guidance follows the domain logic. With Transaction Scripts, use a gateway: a Table Data Gateway if your platform works with record sets (and with a Table Module), a Row Data Gateway otherwise. With a simple Domain Model whose classes match the tables one to one, Active Record is the simple choice. With a rich Domain Model, use a Data Mapper, usually one you buy, plus Repositories when there are many queries. Pick one primary mechanism and don't mix them. A gateway can still wrap an external table or service under a Data Mapper.
In real frameworks
Switch names: to framework to see the same flow with real tools (Demo 8: Rails, Hibernate, Spring Data):
| Pattern | Real tool | What the SQL looks like |
|---|---|---|
| Table Data Gateway | a DAO on Spring's JdbcTemplate | the DAO's own SQL; rows as Map<String, Object> |
| Row Data Gateway | jOOQ generated UpdatableRecord | store() sets only the changed columns |
| Active Record | Rails ActiveRecord, Laravel Eloquent, Django models | SELECT "people".* FROM "people" WHERE "people"."id" = ? LIMIT ?; save runs BEGIN, an UPDATE of the changed attributes, COMMIT |
| Data Mapper | Hibernate / JPA EntityManager | Hibernate 6 aliases (p1_0); by default the UPDATE sets every column |
| Repository | Spring Data JpaRepository | generated from method names or a Specification |
| Query Object | JPA Criteria API, Querydsl, Spring Data Specification | predicates on fields, turned into SQL by the provider |
A one-row lookup through any of them is a single query. Looping over associations is where an ORM sends one query per object: see N+1 Queries.
What the page leaves out
The page has one table, explicit update() calls and no transactions (except Rails' BEGIN/COMMIT). It leaves out the behaviour a real mapping layer needs: a Unit of Work that tracks changes, an Identity Map that keeps one object per row, and Lazy Load (all on the Hibernate Session page). It also skips inserts and deletes, the structural mappings (foreign keys, associations, inheritance, embedded values), how metadata mapping works inside, Record Set and Table Module as patterns of their own, stored procedures and views, connection handling and concurrency control.