Repository Pattern

The previous lesson showed that the object model and the relational model do not line up at four points, and named the decision that closes each mismatch. Unless these decisions are written down somewhere, they are made again at every call site that writes a query: which strategy brought down the inheritance hierarchy, which side owns a relation, which field is embedded — this information scatters through the code.

This lesson’s subject is the interface that gathers that information in one place. A repository is a boundary that speaks to the caller only in domain concepts and hides how persistence actually happens. Whether the boundary is truly closed is not shown by asserting it, but by testing it: if the business rules run without a database, the boundary is closed.

The Repository’s Promise

A repository is not a tool for writing queries. The caller does not ask it for a table, a column, or a join; it asks for a loan record. The five operations below are the entire set of points where the loan service needs data.

Operation	Meaning
find(loanId)	Returns the loan record with the given identifier, or null
booksOpenRecord(bookId)	Returns the book’s unreturned record, if one exists
membersOpenRecords(memberId)	The list of the member’s unreturned records
add(record)	Stores a new record and returns its identifier
markReturned(loanId, date)	Moves the record into the closed state

There is no SELECT in this list, no connection, no row. This is the repository’s definition: the interface is written in the language of the domain, the implementation in the language of persistence.

The Rules Do Not Depend on the Repository

Issuing a loan carries two rules: a book already on loan cannot be issued again, and a member cannot hold more than three books at once. These rules contain no persistence detail whatsoever.

// loan-service.mjs — business rules; contains no SQL, no driver name
export const MEMBER_LIMIT = 3;

export class LoanService {
  constructor(repository) { this.repository = repository; }

  issue(memberId, bookId, date) {
    if (this.repository.booksOpenRecord(bookId) !== null) {
      return { status: "rejected", reason: "book is already on loan" };
    }
    const open = this.repository.membersOpenRecords(memberId);
    if (open.length >= MEMBER_LIMIT) {
      return { status: "rejected", reason: `member limit exceeded (${MEMBER_LIMIT})` };
    }
    const id = this.repository.add({ bookId, memberId, pickupDate: date, returnDate: null });
    return { status: "issued", loanId: id };
  }

  takeReturn(loanId, date) {
    const record = this.repository.find(loanId);
    if (record === null) return { status: "not found" };
    if (record.returnDate !== null) return { status: "already returned" };
    this.repository.markReturned(loanId, date);
    return { status: "returned" };
  }
}

The service receives its repository through the constructor. It does not build a repository itself and does not know which one will arrive. This detail carries the whole lesson: receiving the repository from the outside makes it possible to hand it a different repository during testing.

Fake Repository

The smallest implementation that satisfies the contract is an array.

// memory-store.mjs — fake implementation of the repository; no database
export class MemoryLoanRepository {
  constructor(initial = []) {
    this.records = initial.map((k) => ({ ...k }));
    this.nextId = this.records.length + 1;
  }
  find(loanId) {
    return this.records.find((k) => k.loanId === loanId) ?? null;
  }
  booksOpenRecord(bookId) {
    return this.records.find((k) => k.bookId === bookId && k.returnDate === null) ?? null;
  }
  membersOpenRecords(memberId) {
    return this.records.filter((k) => k.memberId === memberId && k.returnDate === null);
  }
  add(record) {
    const loanId = this.nextId++;
    this.records.push({ loanId, ...record });
    return loanId;
  }
  markReturned(loanId, date) {
    const k = this.find(loanId);
    if (k !== null) k.returnDate = date;
  }
}

With the fake repository, the business rules can be tested directly. The test file uses the loan-service.mjs and memory-store.mjs modules defined by the previous two blocks.

// service.test.mjs — business rules are tested with the fake repository
import { test } from "node:test";
import assert from "node:assert/strict";
import { LoanService } from "./loan-service.mjs";
import { MemoryLoanRepository } from "./memory-store.mjs";

test("a book with no open loan is issued", () => {
  const service = new LoanService(new MemoryLoanRepository());
  assert.deepEqual(service.issue(1, 5, "2025-07-01"), { status: "issued", loanId: 1 });
});

test("a book already on loan is rejected", () => {
  const repository = new MemoryLoanRepository([
    { loanId: 1, bookId: 5, memberId: 2, pickupDate: "2025-06-01", returnDate: null },
  ]);
  const result = new LoanService(repository).issue(1, 5, "2025-07-01");
  assert.equal(result.status, "rejected");
  assert.match(result.reason, /already on loan/);
});

test("exceeding the member limit is rejected", () => {
  const repository = new MemoryLoanRepository([
    { loanId: 1, bookId: 1, memberId: 4, pickupDate: "2025-06-01", returnDate: null },
    { loanId: 2, bookId: 2, memberId: 4, pickupDate: "2025-06-02", returnDate: null },
    { loanId: 3, bookId: 3, memberId: 4, pickupDate: "2025-06-03", returnDate: null },
  ]);
  const result = new LoanService(repository).issue(4, 7, "2025-07-01");
  assert.equal(result.status, "rejected");
  assert.equal(result.reason, "member limit exceeded (3)");
});

test("a returned book can be issued again", () => {
  const repository = new MemoryLoanRepository([
    { loanId: 1, bookId: 5, memberId: 2, pickupDate: "2025-06-01", returnDate: null },
  ]);
  const service = new LoanService(repository);
  assert.equal(service.takeReturn(1, "2025-06-20").status, "returned");
  assert.equal(service.issue(1, 5, "2025-07-01").status, "issued");
});

node --test service.test.mjs

✔ a book with no open loan is issued (0.905208ms)
✔ a book already on loan is rejected (0.105625ms)
✔ exceeding the member limit is rejected (0.050667ms)
✔ a returned book can be issued again (0.059709ms)
ℹ tests 4
ℹ suites 0
ℹ pass 4
ℹ fail 0
ℹ cancelled 0
ℹ skipped 0
ℹ todo 0
ℹ duration_ms 37.269625

The durations in parentheses and the duration_ms field change on every run; what matters is the pass 4 and fail 0 lines. Four rules were tested without a database file, without setting up a schema, and without opening a single connection. The test’s setup step comes down to writing an array of records; the starting state that feeds the rules can be read directly.

The Fake’s Fidelity

A fake repository carries a risk: if it behaves differently from the real implementation, the tests pass but the application does not work. The method that closes this risk is applying the same test suite to both implementations. The real implementation comes first.

// sqlite-store.mjs — real implementation of the repository; all SQL is gathered here
const TO_RECORD = (s) => (s === undefined ? null : {
  loanId: s.loan_id, bookId: s.book_id, memberId: s.member_id,
  pickupDate: s.pickup_date, returnDate: s.return_date,
});

export class SqliteLoanRepository {
  constructor(db) { this.db = db; }
  find(loanId) {
    return TO_RECORD(this.db.prepare("SELECT * FROM loan WHERE loan_id = ?").get(loanId));
  }
  booksOpenRecord(bookId) {
    return TO_RECORD(this.db.prepare(
      "SELECT * FROM loan WHERE book_id = ? AND return_date IS NULL").get(bookId));
  }
  membersOpenRecords(memberId) {
    return this.db.prepare(
      "SELECT * FROM loan WHERE member_id = ? AND return_date IS NULL ORDER BY loan_id")
      .all(memberId).map(TO_RECORD);
  }
  add(record) {
    const result = this.db.prepare(
      "INSERT INTO loan (book_id, member_id, pickup_date, return_date) VALUES (?,?,?,?)")
      .run(record.bookId, record.memberId, record.pickupDate, record.returnDate);
    return Number(result.lastInsertRowid);
  }
  markReturned(loanId, date) {
    this.db.prepare("UPDATE loan SET return_date = ? WHERE loan_id = ?").run(date, loanId);
  }
}

The implementation also receives its connection from the outside. If it kept the file name inside itself, it could not be pointed at an in-memory database during testing.

// contract.test.mjs — the same test suite is applied to both implementations
import { test, describe } from "node:test";
import assert from "node:assert/strict";
import { DatabaseSync } from "node:sqlite";
import { MemoryLoanRepository } from "./memory-store.mjs";
import { SqliteLoanRepository } from "./sqlite-store.mjs";

const INITIAL = [
  { loanId: 1, bookId: 5, memberId: 2, pickupDate: "2025-06-01", returnDate: null },
  { loanId: 2, bookId: 6, memberId: 2, pickupDate: "2025-06-02", returnDate: "2025-06-25" },
];

const setUpMemory = () => new MemoryLoanRepository(INITIAL);

function setUpSqlite() {
  const db = new DatabaseSync(":memory:");
  db.exec(`CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
           member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT)`);
  for (const k of INITIAL) {
    db.prepare("INSERT INTO loan VALUES (?,?,?,?,?)")
      .run(k.loanId, k.bookId, k.memberId, k.pickupDate, k.returnDate);
  }
  return new SqliteLoanRepository(db);
}

for (const [name, setUp] of [["memory", setUpMemory], ["sqlite", setUpSqlite]]) {
  describe(`repository contract: ${name}`, () => {
    test("an open record is found, a closed record is not", () => {
      const d = setUp();
      assert.equal(d.booksOpenRecord(5).loanId, 1);
      assert.equal(d.booksOpenRecord(6), null);
    });
    test("a member's open records are only the open ones", () => {
      assert.deepEqual(setUp().membersOpenRecords(2).map((k) => k.loanId), [1]);
    });
    test("an added record gets a new identifier and can be found", () => {
      const d = setUp();
      const id = d.add({ bookId: 7, memberId: 3, pickupDate: "2025-07-01", returnDate: null });
      assert.equal(d.find(id).bookId, 7);
    });
    test("marking a return closes the open record", () => {
      const d = setUp();
      d.markReturned(1, "2025-07-02");
      assert.equal(d.booksOpenRecord(5), null);
    });
    test("a nonexistent identifier returns null", () => {
      assert.equal(setUp().find(999), null);
    });
  });
}

node --test contract.test.mjs

▶ repository contract: memory
  ✔ an open record is found, a closed record is not (0.665792ms)
  ✔ a member's open records are only the open ones (0.293875ms)
  ✔ an added record gets a new identifier and can be found (0.079417ms)
  ✔ marking a return closes the open record (0.080666ms)
  ✔ a nonexistent identifier returns null (0.049959ms)
✔ repository contract: memory (1.656583ms)
▶ repository contract: sqlite
  ✔ an open record is found, a closed record is not (0.361625ms)
  ✔ a member's open records are only the open ones (0.122083ms)
  ✔ an added record gets a new identifier and can be found (0.135166ms)
  ✔ marking a return closes the open record (0.08875ms)
  ✔ a nonexistent identifier returns null (0.093833ms)
✔ repository contract: sqlite (0.925875ms)
ℹ tests 10
ℹ suites 2
ℹ pass 10
ℹ fail 0
ℹ cancelled 0
ℹ skipped 0
ℹ todo 0
ℹ duration_ms 38.271041

Ten tests, two implementations, zero failures. This test suite is the repository’s contract; it reaffirms on every run that the fake stays faithful to the real thing. When a new implementation is added — another data store, a caching layer, a version that keeps records in a file — the same suite is applied to it too.

The contract test has a limit. Isolation level, locking behavior, and concurrent update conflicts cannot be produced inside an in-memory array; these are tested only against the real implementation. The fake repository’s scope is the business rules, not persistence behavior.

Measuring the Leak

How closed the boundary is can be counted. How many times, and in which module, do persistence traces — SQL keywords, the driver type, the prepared-statement call — show up? For comparison, a version that does the same job without a repository is also worth writing.

// leaky-service.mjs — version that uses a direct connection instead of the repository
export class LeakyLoanService {
  constructor(db) { this.db = db; }
  issue(memberId, bookId, date) {
    const openBook = this.db.prepare(
      "SELECT 1 FROM loan WHERE book_id = ? AND return_date IS NULL").get(bookId);
    if (openBook !== undefined) return { status: "rejected", reason: "book is already on loan" };
    const count = this.db.prepare(
      "SELECT count(*) AS n FROM loan WHERE member_id = ? AND return_date IS NULL").get(memberId).n;
    if (count >= 3) return { status: "rejected", reason: "member limit exceeded (3)" };
    const result = this.db.prepare(
      "INSERT INTO loan (book_id, member_id, pickup_date, return_date) VALUES (?,?,?,NULL)")
      .run(bookId, memberId, date);
    return { status: "issued", loanId: Number(result.lastInsertRowid) };
  }
}

// leak-measure.mjs — how many persistence traces are in which module
import { readFileSync } from "node:fs";

const TRACES = /\b(SELECT|INSERT|UPDATE|DELETE|prepare|DatabaseSync)\b/g;

for (const file of ["loan-service.mjs", "leaky-service.mjs", "sqlite-store.mjs", "memory-store.mjs"]) {
  const trace = readFileSync(file, "utf8").match(TRACES) ?? [];
  console.log(`${file.padEnd(22)} persistence trace = ${trace.length}`);
}

node leak-measure.mjs

loan-service.mjs       persistence trace = 0
leaky-service.mjs      persistence trace = 6
sqlite-store.mjs       persistence trace = 10
memory-store.mjs       persistence trace = 0

The numbers show the distribution. In the service that uses the repository, the trace count is zero; all of persistence is gathered into a single module, as ten traces. In the leaky service, six traces are mixed in among the business rules. These six traces are the reason that service cannot be tested with a fake repository: the prepare call asks for a capability an array has no counterpart for.

The measurement is a blunt instrument, not conclusive proof. A module can bind itself to persistence without writing SQL; a find call relying on the database’s default ordering for the order it returns rows in is a dependency too. The number’s job is to make where the boundary is drawn visible to the eye.

The Repository’s Boundary and Query Proliferation

Running the same service with the real repository asks for no further code change.

// run-with-real.mjs — the same service, this time with a repository connected to the database
import { DatabaseSync } from "node:sqlite";
import { LoanService } from "./loan-service.mjs";
import { SqliteLoanRepository } from "./sqlite-store.mjs";

const repository = new SqliteLoanRepository(new DatabaseSync("library.db"));
const service = new LoanService(repository);

console.log("book 1 (has an open record):", JSON.stringify(service.issue(6, 1, "2025-07-15")));
console.log("book 7 (free):              ", JSON.stringify(service.issue(6, 7, "2025-07-15")));
console.log("member 4 open record count: ", repository.membersOpenRecords(4).length);

This block expects the library.db file carrying the library schema from the SQL Fundamentals course. The command below sets up the schema and the rows this lesson runs against.

rm -f library.db
sqlite3 library.db <<'SQL'
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
                    member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT);
INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'),
  (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'),
  (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'),
  (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'),
  (12,4,4,'2025-06-20','2025-07-04');
SQL
node run-with-real.mjs

book 1 (has an open record): {"status":"rejected","reason":"book is already on loan"}
book 7 (free):               {"status":"issued","loanId":13}
member 4 open record count:  1

Book 1’s request was rejected because it has an open record; the free book 7 was issued and got the identifier thirteen. Not a single line changed in the service code.

The repository pattern’s cost shows up here. Because the interface is written in the domain’s language, every new question asks for a new method: overdue records, open records at a given branch, records issued within a date range, both overdue and at a given branch. Because criteria can be combined, the method count grows with the combination of criteria. Past a certain point, the repository interface starts to resemble the query language it was trying to hide.

Summary

• A repository is an interface that speaks to the caller in domain concepts; it keeps the query, the connection, and the row-to-object conversion inside itself.
• The service receiving its repository from the outside makes it possible to hand it an in-memory fake repository during testing; four business rules were tested without a database.
• The same contract test was applied to both implementations, and all ten tests passed; this is the continuous verification that the fake stays faithful to the real thing.
• Counting persistence traces gave zero in the service that uses the repository and six in the leaky service; this is how the boundary’s closure is made visible.
• Isolation and concurrency behavior cannot be produced in the fake repository; the fake’s scope is the business rules.

Next Step

As the repository’s interface closed off, the number of questions to ask grew, and every question asked for a new method. Overdue records, records filtered by branch, records limited to a date range, and their combinations bloat the repository when they are each written out separately. The problem is not in the criteria themselves; it is in the criteria being embedded into fixed method names on the interface. The next lesson turns a criterion into an object: each criterion produces its own SQL fragment and bound parameters, criteria are combined with “and”, “or”, and “not”, and the repository meets every combination through a single method.