Data Mapper Pattern: Keep Domain Models DB-Free

Overview

The Data Mapper Pattern separates in-memory domain objects from the database by delegating all persistence logic to a dedicated mapper layer. The domain model knows nothing about the database — no SQL, no ORM decorators, no save() methods. A separate mapper object does all the translating: objects into rows on write, rows into objects on read.

This is the pattern behind Hibernate (JPA), SQLAlchemy’s classical mapping, and the Repository pattern when used with ORMs. It gives you the cleanest separation of concerns for complex domains — at the cost of more boilerplate than Active Record.

When to Use

Use the Data Mapper Pattern when:

• The domain model is rich and should remain independent of the database
• You need to map the same domain object onto two or more database schemas
• Testing domain logic without hitting the database is important
• The persistence mechanism may change (SQL now, NoSQL later)

When to Avoid

• Simple CRUD applications where Active Record is sufficient
• When the overhead of an extra mapping layer isn’t justified
• Prototypes or internal tools where speed of development matters more than purity

Solution

Python

from dataclasses import dataclass
from typing import Optional, List

# Domain Model — knows nothing about the database
@dataclass
class User:
    id: Optional[int] = None
    name: str = ""
    email: str = ""

    def update_email(self, new_email: str):
        if "@" not in new_email:
            raise ValueError("Invalid email")
        self.email = new_email


# Data Mapper — handles all persistence logic
class UserMapper:
    def __init__(self, connection):
        self._conn = connection

    def find_by_id(self, user_id: int) -> Optional[User]:
        row = self._conn.execute(
            "SELECT id, name, email FROM users WHERE id = ?", (user_id,)
        ).fetchone()
        if row:
            return User(id=row["id"], name=row["name"], email=row["email"])
        return None

    def find_all(self) -> List[User]:
        rows = self._conn.execute("SELECT id, name, email FROM users").fetchall()
        return [User(id=r["id"], name=r["name"], email=r["email"]) for r in rows]

    def insert(self, user: User):
        cursor = self._conn.execute(
            "INSERT INTO users (name, email) VALUES (?, ?)",
            (user.name, user.email)
        )
        user.id = cursor.lastrowid

    def update(self, user: User):
        self._conn.execute(
            "UPDATE users SET name = ?, email = ? WHERE id = ?",
            (user.name, user.email, user.id)
        )

    def delete(self, user_id: int):
        self._conn.execute("DELETE FROM users WHERE id = ?", (user_id,))


# Usage
import sqlite3
conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row  # rows support row["column"] access
conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)")

mapper = UserMapper(conn)
user = User(name="Alice", email="alice@example.com")
mapper.insert(user)
print(f"Inserted user with ID: {user.id}")

loaded = mapper.find_by_id(user.id)
print(loaded.name, loaded.email)

Java

import java.sql.*;
import java.util.*;

public class User {
    private Integer id;
    private String name;
    private String email;

    public User() {}
    public User(String name, String email) {
        this.name = name;
        this.email = email;
    }

    public Integer getId() { return id; }
    public void setId(Integer id) { this.id = id; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }

    public void updateEmail(String newEmail) {
        if (!newEmail.contains("@"))
            throw new IllegalArgumentException("Invalid email");
        this.email = newEmail;
    }
}

class UserMapper {
    private final Connection conn;

    public UserMapper(Connection conn) { this.conn = conn; }

    public User findById(int id) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(
                "SELECT id, name, email FROM users WHERE id = ?")) {
            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setName(rs.getString("name"));
                    user.setEmail(rs.getString("email"));
                    return user;
                }
            }
        }
        return null;
    }

    public List<User> findAll() throws SQLException {
        List<User> users = new ArrayList<>();
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(
                     "SELECT id, name, email FROM users")) {
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setEmail(rs.getString("email"));
                users.add(user);
            }
        }
        return users;
    }

    public void insert(User user) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(
                "INSERT INTO users (name, email) VALUES (?, ?)",
                Statement.RETURN_GENERATED_KEYS)) {
            stmt.setString(1, user.getName());
            stmt.setString(2, user.getEmail());
            stmt.executeUpdate();
            try (ResultSet keys = stmt.getGeneratedKeys()) {
                if (keys.next()) user.setId(keys.getInt(1));
            }
        }
    }

    public void update(User user) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(
                "UPDATE users SET name = ?, email = ? WHERE id = ?")) {
            stmt.setString(1, user.getName());
            stmt.setString(2, user.getEmail());
            stmt.setInt(3, user.getId());
            stmt.executeUpdate();
        }
    }

    public void delete(int id) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(
                "DELETE FROM users WHERE id = ?")) {
            stmt.setInt(1, id);
            stmt.executeUpdate();
        }
    }
}

// Usage
public class Main {
    public static void main(String[] args) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:");
        conn.createStatement().execute(
                "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)");

        UserMapper mapper = new UserMapper(conn);
        User user = new User("Alice", "alice@example.com");
        mapper.insert(user);
        System.out.println("Inserted user with ID: " + user.getId());
    }
}

JavaScript

class User {
  constructor(name, email) {
    this.id = null;
    this.name = name;
    this.email = email;
  }

  updateEmail(newEmail) {
    if (!newEmail.includes('@')) throw new Error('Invalid email');
    this.email = newEmail;
  }
}

class UserMapper {
  constructor(db) {
    this.db = db;
  }

  findById(id) {
    const row = this.db
      .prepare('SELECT id, name, email FROM users WHERE id = ?')
      .get(id);
    if (!row) return null;
    const user = new User(row.name, row.email);
    user.id = row.id;
    return user;
  }

  findAll() {
    return this.db
      .prepare('SELECT id, name, email FROM users')
      .all()
      .map(row => {
        const user = new User(row.name, row.email);
        user.id = row.id;
        return user;
      });
  }

  insert(user) {
    const result = this.db
      .prepare('INSERT INTO users (name, email) VALUES (?, ?)')
      .run(user.name, user.email);
    user.id = result.lastInsertRowid;
  }

  update(user) {
    this.db
      .prepare('UPDATE users SET name = ?, email = ? WHERE id = ?')
      .run(user.name, user.email, user.id);
  }

  delete(id) {
    this.db.prepare('DELETE FROM users WHERE id = ?').run(id);
  }
}

// Usage — Node's built-in node:sqlite, zero dependencies
// (on Node 22.x, run with --experimental-sqlite)
const { DatabaseSync } = require('node:sqlite');
const db = new DatabaseSync(':memory:');
db.exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)');

const mapper = new UserMapper(db);
const user = new User('Alice', 'alice@example.com');
mapper.insert(user);
console.log(`Inserted user with ID: ${user.id}`);

const loaded = mapper.findById(user.id);
console.log(loaded.name, loaded.email);

Explanation

Data Mapper splits persistence into three layers that only talk through the mapper:

• Domain model — plain objects with business rules (update_email validates the address). Nothing in it imports a driver, carries an annotation, or knows what save() means.
• Data mapper — the class that owns the SQL. It turns objects into rows on the way out and rows into objects on the way back.
• Database — the domain model can’t see it at all.

Reads and writes follow the same path. On insert, the mapper pulls the object’s fields, runs the SQL, and writes the generated ID back. On find_by_id, it runs the query and rehydrates a domain object from the row. The domain object never sees a connection, a cursor, or a column name.

All that indirection isn’t free. Each query hits the database once per mapper call, so a loop calling find_by_id produces an N+1 problem that Active Record hides behind includes/joins helpers. The usual fixes live in the variants below: an Identity Map deduplicates loaded rows, lazy loading defers related objects, and a Unit of Work batches inserts and updates into one transaction.

Transactions are the other sharp edge. mapper.insert(user) commits on its own unless your connection wraps several calls — which is why real implementations pair the mapper with Unit of Work instead of letting it own transaction boundaries.

The cost is boilerplate: every new domain field means touching the mapper’s INSERT, UPDATE, and SELECT. Worth it when the schema and the object model evolve independently. Not worth it when they’re a 1:1 mirror — that asymmetry is exactly what Active Record is for.

Variants

Variant	Approach	Use Case
Identity Map	Keeps one object instance per row	Stops the same row becoming two objects
Unit of Work	Collects changes, commits them together	Transactional consistency
Query Object	Wraps a query in an object	Composable database queries
Repository	Sits between domain and data layer	Hides mapper details from the app

What Works

• Keep the domain model pure. No framework dependencies, no persistence methods.
• One mapper per domain class. Once a mapper starts covering a dozen types, nobody can navigate it.
• Be careful with lazy loading. It feels free until the N+1 queries show up.
• Identity Map avoids duplicate instances. Once it has built an object from a row, it hands back that same object instead of building a second copy.
• Hand out immutable snapshots so callers can’t mutate domain state behind the mapper’s back.

Common Mistakes

• Leaking SQL into the domain model. If the model knows about the database, it isn’t Data Mapper.
• Mapper as a God class. One class handling 20 domain types becomes unmaintainable.
• Ignoring transactions. Individual mapper operations need to be composable into transactions.
• Deep object graphs without lazy loading. Eagerly loading a tree can pull the entire database.
• Confusing mappers with repositories. A mapper moves objects in and out of storage; a repository pretends your domain objects are just a collection.

Real-World Examples

Hibernate (JPA)

Hibernate maps entities to tables through XML or annotations, so the entity classes stay free of persistence code. Its Session plays the mapper role: it tracks object state and turns it into SQL and back.

SQLAlchemy Classical Mapping

SQLAlchemy supports declarative (Active Record-like) and classical (Data Mapper) styles. With classical mapping you call mapper(User, user_table), so the class never touches the table definition.

Doctrine ORM

PHP’s Doctrine ORM is another Data Mapper implementation — entities stay plain, and the XML/YAML/annotation mappings carry all the persistence details.

See Also

• Data Mapper — Fowler’s PoEAA catalog — the canonical definition of the pattern.
• SQLAlchemy mapping styles — declarative vs. classical (Data Mapper) mapping.
• Hibernate ORM documentation — the Session as a data mapper.
• Doctrine ORM — Data Mapper in PHP.
• Companion examples — runnable mappers in Python, Java, and JavaScript.