Active Record Pattern

Overview

The Active Record Pattern wraps a database table in a class where each instance is a single row. Each object holds its data and its persistence methods in one place — user.save() goes straight to the database, nothing in between.

Martin Fowler catalogued it in Patterns of Enterprise Application Architecture, and it became the default data-mapping style for a generation of frameworks: Ruby on Rails, Django ORM, Laravel Eloquent, and Sequelize all build on it. The appeal is obvious — the least possible code between “I have a user” and “it’s in the database.” The cost is that your domain model now knows SQL exists, and that coupling shapes everything downstream: how you test, how you migrate, and how much the model is allowed to grow.

When to Use

• The domain model maps closely to database tables — mostly one class per table.
• You want CRUD with the least possible boilerplate: prototypes, MVPs, admin panels.
• The team already uses a framework where it’s the convention (Rails, Django, Laravel) — fighting the convention costs more than the pattern does.
• Business logic is thin and lives naturally on the record itself: validation, formatting, simple state transitions.

When NOT to Use

• Rich domain logic that shouldn’t know persistence exists — use Data Mapper or Repository to keep the model pure.
• The same entity persists to several data sources or maps to a schema you don’t control.
• You need heavy unit testing without a database — Active Record objects drag a connection everywhere they go.
• Transactions regularly span two or three entities — Active Record has no natural home for cross-object units of work.

Solution

Python

import sqlite3
from typing import Optional, List

class User:
    _db_path = "app.db"

    def __init__(self, id: int = None, name: str = "", email: str = ""):
        self.id = id
        self.name = name
        self.email = email

    @classmethod
    def _connect(cls):
        return sqlite3.connect(cls._db_path)

    def save(self):
        with self._connect() as conn:
            if self.id is None:
                cursor = conn.execute(
                    "INSERT INTO users (name, email) VALUES (?, ?)",
                    (self.name, self.email)
                )
                self.id = cursor.lastrowid
            else:
                conn.execute(
                    "UPDATE users SET name = ?, email = ? WHERE id = ?",
                    (self.name, self.email, self.id)
                )
            conn.commit()
        return self

    def delete(self):
        with self._connect() as conn:
            conn.execute("DELETE FROM users WHERE id = ?", (self.id,))
            conn.commit()

    @classmethod
    def find_by_id(cls, user_id: int) -> Optional["User"]:
        with cls._connect() as conn:
            row = conn.execute(
                "SELECT id, name, email FROM users WHERE id = ?", (user_id,)
            ).fetchone()
            return cls(*row) if row else None

    @classmethod
    def find_all(cls) -> List["User"]:
        with cls._connect() as conn:
            rows = conn.execute("SELECT id, name, email FROM users").fetchall()
            return [cls(*row) for row in rows]


# Usage
user = User(name="Alice", email="alice@example.com")
user.save()
print(User.find_by_id(user.id))

Java

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

public class User {
    private static String dbUrl = "jdbc:sqlite:app.db";

    private int id;
    private String name;
    private String email;

    public User() {}

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

    public void save() {
        try (Connection conn = DriverManager.getConnection(dbUrl)) {
            if (id == 0) {
                PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO users (name, email) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, name);
                ps.setString(2, email);
                ps.executeUpdate();
                ResultSet keys = ps.getGeneratedKeys();
                keys.next();
                id = keys.getInt(1);
            } else {
                PreparedStatement ps = conn.prepareStatement(
                    "UPDATE users SET name = ?, email = ? WHERE id = ?");
                ps.setString(1, name);
                ps.setString(2, email);
                ps.setInt(3, id);
                ps.executeUpdate();
            }
        } catch (SQLException e) { throw new RuntimeException(e); }
    }

    public void delete() {
        try (Connection conn = DriverManager.getConnection(dbUrl)) {
            PreparedStatement ps = conn.prepareStatement("DELETE FROM users WHERE id = ?");
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) { throw new RuntimeException(e); }
    }

    public static User findById(int id) {
        try (Connection conn = DriverManager.getConnection(dbUrl)) {
            PreparedStatement ps = conn.prepareStatement("SELECT id, name, email FROM users WHERE id = ?");
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) return new User(rs.getInt(1), rs.getString(2), rs.getString(3));
        } catch (SQLException e) { throw new RuntimeException(e); }
        return null;
    }

    public int getId() { return id; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }

    @Override
    public String toString() {
        return "User{id=" + id + ", name='" + name + "', email='" + email + "'}";
    }
}

// Usage
User user = new User();
user.setName("Alice");
user.save();
System.out.println(User.findById(user.getId()));

JavaScript

// Requires an ESM context (top-level await) or wrap the usage in an async function.
class User {
  static db = null; // Injected database connection

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

  async save() {
    if (this.id === null) {
      const result = await User.db.run(
        'INSERT INTO users (name, email) VALUES (?, ?)',
        this.name, this.email
      );
      this.id = result.lastID;
    } else {
      await User.db.run(
        'UPDATE users SET name = ?, email = ? WHERE id = ?',
        this.name, this.email, this.id
      );
    }
    return this;
  }

  async delete() {
    await User.db.run('DELETE FROM users WHERE id = ?', this.id);
  }

  static async findById(userId) {
    const row = await User.db.get('SELECT id, name, email FROM users WHERE id = ?', userId);
    return row ? new User(row) : null;
  }

  static async findAll() {
    const rows = await User.db.all('SELECT id, name, email FROM users');
    return rows.map(row => new User(row));
  }
}

// Usage (ESM)
const user = new User({ name: 'Alice', email: 'alice@example.com' });
await user.save();
const found = await User.findById(user.id);
console.log(found);

How It Works

Think of an Active Record object as a database row that also does work. Three things live in one place:

• Domain data: fields that map one-to-one to columns.
• Persistence methods: save(), delete(), find() — they generate and run the SQL.
• Domain rules: validation and small behavior that fits on a single record.

The consequence nobody warns you about: the moment user.save() exists, every layer of the app can touch the database. There’s no boundary telling you where persistence stops and business logic starts. That’s the trade you make for having no boilerplate — it scales beautifully for CRUD apps and becomes a liability when the domain gets complicated.

Active Record vs Data Mapper vs Repository — the three patterns that solve the same problem:

	Active Record	Data Mapper	Repository
Where persistence lives	On the object	Separate mapper class	Collection-like abstraction
Domain knows SQL exists	Yes	No	No
Unit-test without DB	Hard	Easy	Easy
Boilerplate	Minimal	High	Medium
Fits	CRUD apps, prototypes	Complex domains	DDD, aggregates
Frameworks	Rails, Django, Eloquent	Hibernate, SQLAlchemy, Prisma	Spring Data, Doctrine

A two-minute contrast — the same operation in each style:

# Active Record: the object knows how to persist itself
user = User(name="Alice")
user.save()

# Data Mapper: persistence is a service you hand the object to
user = User(name="Alice")
mapper.save(user)

# Repository: you think in collections, not rows
users.add(user)
users.commit()

Variants

Variant	Description	Use Case
Classic Active Record	Object owns its persistence	Rails, Django ORM
Data Mapper	Separate mapper class handles persistence	Hibernate, SQLAlchemy
Repository	Collection-like abstraction over persistence	DDD aggregates
Table Data Gateway	Static methods on a class, not instances	Simple CRUD utilities

What Works

• Keep validations in the model. Check constraints before saving and raise meaningful errors.
• Use callbacks sparingly. before_save and after_create hooks create invisible control flow.
• Prefer scoped queries over raw SQL — User.where(active=True) keeps business logic readable and injection-safe.
• Lazy load associations. Load related records only when accessed, not on every fetch.
• Avoid business logic in the database. Stored procedures couple your code to the DB vendor.

Common Mistakes

• Fat models that hit 500 lines — the fix is service objects, not more model.
• N+1 queries inside association loops. Eager-load them (select_related, includes).
• Database access in unit tests. Active Record makes this hard. Use in-memory SQLite or mocks.
• Validation in controllers instead of the model. The model owns the rules — put them there.
• Mutating during iteration. Add to or remove from a collection mid-loop and the results get unpredictable.

Real-World Examples

Ruby on Rails

User.create(name: "Alice") creates a record, validates it, and persists in one call. Associations like user.posts are lazily loaded — the framework that made the pattern famous and still its most complete implementation.

Django ORM

With user.save() and User.objects.get(id=1) you’re seeing Active Record at work. Django adds managers (objects) for collection queries — a half step toward Repository that keeps the record itself in charge of writes.

Laravel Eloquent

Eloquent keeps the same shape — User::find(1), $user->save() — plus relationships, scopes, and a query builder on top.

Sequelize and friends (JavaScript)

Sequelize models are close to classic Active Record (user.save(), User.findAll()). TypeORM ships both modes — ActiveRecord and DataMapper base classes — and Prisma leans Data Mapper. Worth knowing which side your ORM sits on before you design around it.

Troubleshooting

• N+1 query storms in production: the code iterates user.posts in a loop and each access fires a query — log the query count per request and eager-load the association (includes, select_related, preload).
• Unit tests that need a real database: the model can’t be instantiated without a connection — run the suite against in-memory SQLite, or move the logic under test off the record and onto a plain service object.
• Fat model nobody wants to touch: hundreds of lines of callbacks, scopes, and business rules on one class — extract domain behavior into service objects and keep the record for persistence and validation only.
• save() silently skips fields: a column was added to the table but never to the model’s attributes — check the model’s field list or schema definition before assuming the ORM picks up new columns automatically.
• Transaction spanning several records: user.save() then order.save() and the second fails — wrap multi-record operations in an explicit transaction block; if that pattern repeats, it’s a sign the domain wants Unit of Work.
• Objects leaking the connection lifecycle: a record created inside a request keeps living (and querying) after the request ends — scope record usage to the request or unit-of-work boundary.

Further Reading

• Martin Fowler — Active Record (PoEAA catalog) — the original pattern definition.
• Rails Guides: Active Record Basics — the reference implementation.
• Django docs: Models — how a second framework shaped the same pattern.
• Patterns: Data Mapper and Repository — the escape hatches when Active Record stops fitting.

The runnable versions of these examples live in the companion repository: stack-practices-resources/resources/patterns/design/active-record-pattern.