Repository: keeping the domain away from the database

  • Lesson 29 of 32
  • 10 min
  • Intermediate
  • 12 steps

A business rule with SQL inside can only be tested with a running database. You will put data access behind an interface you use like a collection, with one version in SQL and another in memory for the tests.

SQL queries in the rule
0
What the test needs
a Map
DomainInfraAPIcloseOrder()OrderServicelistOrders()closeOrder()OrderRepositoryinterfaceOrderRepositoryPgOrderRepositorySpringDataPgOrderRepositoryPostgreSQLInMemoryOrderRepositoryInMemoryOrderRepositoryrule + SQL2 queriescloseOrder() with SQL insideasync function closeOrder(db, id) {const { rows: [o] } = await db.query(▸"SELECT * FROM orders WHERE id=$1", [id]);if (o?.status !== "open") throw Error(id);await db.query(▸"UPDATE orders SET status='closed' " +▸"WHERE id=$1", [id]);}a test for one ifit must be runningTo test that if you needPostgreSQL runningon your machine and in CIthe orders tablecreated with its schemaa test orderinserted beforehanda clean-up at the endso no other test breaksAll that to test a single line.used like a collectionOrderRepositoryinterface OrderRepository {▸findById(id: string):Promise<Order | undefined>;▸save(order: Order): Promise<void>;}0 SQL queriesjust the contractcloseOrder() without SQLasync function closeOrder(+repo: OrderRepository, id: string) {+const o = await repo.findById(id);if (o?.status !== "open") throw Error(id);+await repo.save({ ...o, status: "closed" });}the SQL lives hereSELECT … WHERE idPgOrderRepository: the SQL lives hereclass PgOrderRepository▸implements OrderRepository {async findById(id: string) {const { rows } = await this.db.query(▸"SELECT * FROM orders WHERE id=$1", [id]);return rows[0];}}save() runs the UPDATE; db comes in through the constructor.picks the implementationdoes not know whichmain(): chosen onceconst db = new Pool(config);▸const repo = new PgOrderRepository(db);app.post("/orders/:id/close", (req) =>▸closeOrder(repo, req.params.id));In tests, another line creates the in-memory repository.a Map in memoryInMemoryOrderRepositoryclass InMemoryOrderRepositoryimplements OrderRepository {▸private rows = new Map<string, Order>();async findById(id: string) {return this.rows.get(id); }async save(o: Order) {this.rows.set(o.id, { ...o }); }}status: "closed"passes, no databaseThe test, no databaseconst repo = new InMemoryOrderRepository();await repo.save({ id: "1", total: 30,status: "open" });▸await closeOrder(repo, "1");const o = await repo.findById("1");▸expect(o?.status).toBe("closed");Spring supplies the classgets the interfaceSpring Data: just the interface▸interface OrderRepository▸extends CrudRepository<Order, Long> {}// Spring supplies the implementation:// save, findById, findAll,// count, delete, existsByIdand every implementationN+1 is still thereThe interface grows with every screeninterface OrderRepository {findById(id: string): …;save(order: Order): …;+findByCustomer(customerId: string): …;+findOpenOlderThan(days: number): …;+page(offset: number, limit: number): …;+countByStatus(status: string): …;}Example: every new screen asks for its query.no rules to testDo you need a repository?rules to test without a databaseuse itchanging storageuse itCRUD with no rulesnot neededthe ORM is already testablenot neededonly sees the interfaceRepository at a glanceDomaincloseOrder() and OrderRepositoryInfrastructurePgOrderRepository and the SQLTestsInMemoryOrderRepositoryReal caseSpring Data's CrudRepository
  • request
  • response

Step 1 of 12

Problem

The business rule carries SQL

Your shop closes orders with closeOrder(db, id). There is just one rule: only an open order can be closed. But to apply it, the function builds a SELECT, reads the row and then builds an UPDATE.

The rule and the data access are mixed in the same lines. Changing a table, a column or the database means opening the code that decides what a closed order is.

What changed

  • closeOrder() with 2 SQL queries

Step 2 of 12

Problem

Testing an if needs a database

You want a test for that rule: an open order gets closed and a closed one throws an error. Since the function talks to PostgreSQL, so does the test: you need the database running, the table created and an order inserted before you start.

The test is slower and more fragile than the rule it checks. If the database does not start in CI, a test fails that has nothing to do with the database.

What changed

  • a test that needs PostgreSQL

Step 3 of 12

Concept

A collection of orders

The idea of Repository: the domain does not talk to the database, it talks to something you use like a collection of orders. You ask for one by id with findById and hand one over to store with save. How it gets stored is not the domain's business.

Martin Fowler lists it in the catalog of his book Patterns of Enterprise Application Architecture: it mediates between the domain and the layer that maps the data, and he credits it to Edward Hieatt and Rob Mee. It is not a GoF pattern: it is not in the 1994 book.

What changed

  • interface OrderRepository

Step 4 of 12

Solution

The rule only knows the interface

Now closeOrder takes an OrderRepository instead of the connection. It finds the order with findById, applies the rule and stores it with save. Not a single SQL query is left in the function.

The interface lives next to the domain and speaks its terms: orders, not rows. The domain says what it needs, and it is the database code that has to fit that.

What changed

  • SELECT and UPDATE in closeOrder()
  • repo.findById and repo.save

Step 5 of 12

Technique

The SQL moves into a class

The SQL does not vanish: it moves into PgOrderRepository, a class that implements OrderRepository. Its findById runs the SELECT and its save runs the UPDATE.

Look at the diagram: closeOrder() and the interface stay in the domain; the class and PostgreSQL, in infrastructure. If you change database or library tomorrow, you rewrite that class and the rule never notices.

What changed

  • PgOrderRepository implements OrderRepository

Step 6 of 12

Technique

Someone picks which one to use

closeOrder does not create its repository: it receives it. The entry point, main(), builds PgOrderRepository with the connection and passes it to every call.

This is the dependency injection from its own lesson: the choice happens once, at the edge, and the rest of the code only sees the interface.

What changed

  • main() creates PgOrderRepository

Step 7 of 12

Technique

A fake repository for tests

Since closeOrder only knows the interface, you can give it another implementation. InMemoryOrderRepository keeps orders in a Map: findById reads from the map and save writes to it.

It is a fake: it really works, just without a database. It stores a copy with { ...o } so that changing the object outside the repository does not change what was stored, just like with a database.

What changed

  • InMemoryOrderRepository with a Map

Step 8 of 12

Solution

Testing the rule, no database

The test creates the in-memory repository, stores an open order, calls closeOrder and reads the result. Inside closeOrder there are two trips to the repository: findById and save.

Nothing to start and nothing to clean up: each test creates its own Map. And it is the same function that runs in production with PgOrderRepository. What this test does not check is your SQL: for that, a few tests of PgOrderRepository against a real database.

What changed

  • PostgreSQL in the rule's test
  • test with InMemoryOrderRepository

Step 9 of 12

Concept

You already use it: Spring Data

In Java, Spring Data takes the idea further. You declare an interface that extends CrudRepository<Order, Long> and Spring supplies the implementation: you do not write the class with the SQL.

That interface already brings save, findById, findAll, count, delete and existsById. Your service receives OrderRepository, as closeOrder did before, and does not know how the queries are made.

What changed

  • PgOrderRepository → supplied by Spring

Step 10 of 12

Trade-off

The interface grows and the queries stay

Every new screen asks for its own query: a customer's orders, the ones open for more than 30 days, a page of results, the count by status. If each one becomes a method, the interface grows, and so does every implementation, the fake included.

And the repository does not make a slow query fast. If you read 100 orders and then ask for each one's customer, it is still 1 + 100 = 101 queries (the N+1 problem), only now they are hidden behind a method.

What changed

  • 4 query methods
  • the fake grows with them

Step 11 of 12

Trade-off

When you do not need it

If the app is CRUD with no rules, like a listOrders() that just reads rows and returns them, there is no logic to protect: the repository would be a layer that only forwards calls.

You also do not need it if your ORM already gives you an abstraction you can test and are happy with. Use it when there are domain rules you want to test without a database, or when you really will change storage.

What changed

  • OrderRepository in simple CRUD

Step 12 of 12

Summary

The domain asks, infrastructure delivers

Repository puts data access behind an interface you use like a collection: findById, save. The rule is free of SQL, the SQL lives in an infrastructure class and tests use an in-memory fake.

It is not GoF: it comes from Fowler's catalog, and you see it in Spring Data's CrudRepository. It costs one more layer and an interface that grows with the queries; in CRUD with no rules, skip it.

You will learn

  • See why SQL inside a rule makes it hard to test.
  • Put data access behind a collection-like interface.
  • Implement it with SQL and with an in-memory fake for tests.
  • Recognise the pattern in Spring Data's `CrudRepository`.
  • Know when not to use it and what it costs.