Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 37 ยท Performance and Patterns

The Repository and Unit of Work Patterns

Keep SQLAlchemy out of your business logic: build a repository, wrap sessions in a unit of work, swap in a fake for fast tests and know when the patterns are overkill.

SQLAlchemy's Session is already an implementation of the unit of work pattern, and its select() API is already an expressive query layer. For many applications, using the session directly in your service functions is exactly right. But as a codebase grows, two problems appear: queries are scattered across dozens of files, and testing business rules requires a database.

The two classic patterns that address this are the repository (one place that knows how to fetch and store a kind of object) and the unit of work (one object that defines the boundary of a transaction). This lesson builds both in a few lines, then shows what they buy you and when to skip them.

The domain code we want to protect

Suppose a shop has orders, and the rule is "an order cannot be confirmed if it has no lines". That rule is business logic. It should not care whether the data lives in PostgreSQL, SQLite or a Python list.

flowchart LR
    S["Service function<br/>confirm_order()"] --> UoW["Unit of Work<br/>commit / rollback"]
    UoW --> R["Repository<br/>get / add / list"]
    R --> DB[("SQLAlchemy Session")]

The service talks to a repository and a unit of work. Only those two know about the session.

A repository

A repository exposes methods named in the language of your domain (get, add, open_orders), and hides select(), joins and loading options inside.

Python Repository and unit of work

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice what confirm_order does not contain: no select, no Session, no loading options. If you later change how orders are loaded (add an index, switch eager loading), only OrderRepository changes.

Testing without a database

Because the service only uses the methods of the repository, you can hand it a fake that stores objects in a dictionary. Tests that exercise business rules then run in microseconds with no setup.

Python A fake repository for fast tests

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

You still want a few tests against a real (SQLite or PostgreSQL) database to prove that the repository's queries work. Those are integration tests; the many small rule tests use the fake.

What goes where

Responsibility Where
Business rules (confirm, validation, state changes) Methods on the model or domain functions
How to find and store objects Repository
Where a transaction starts and ends (commit or rollback) Unit of work
Wiring a request to a use case Controller / API route / CLI command

Rules of thumb for repositories

  • Return domain objects, not Row or Select objects. The point is that callers do not need to know SQLAlchemy.
  • Let callers pass plain arguments (with_status("open")), not SQLAlchemy expressions.
  • Do not call commit() inside a repository. Committing belongs to the unit of work, so one use case equals one transaction.
  • Do not build a generic Repository[T] with filter(**kwargs) that just re-implements the query API; it adds code without adding protection.

When not to bother

If your app is a thin CRUD layer over a database, with almost no logic between the request and the table, extra layers add indirection without benefit. Start with a session per request (as in Session Patterns) and queries in small functions. Introduce a repository when the same query appears in several places, when business rules grow, or when tests become painfully slow. Patterns should answer a problem you actually have.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main purpose of a repository?

    The repository encapsulates queries and persistence for an aggregate.
  2. 2. Where should commit() be called in this design?

    A use case is one transaction, so the unit of work owns commit and rollback.
  3. 3. Why can service tests run without a database in this design?

    Swapping a fake repository for the real one needs no changes to the service code.
  4. 4. When is a repository probably overkill?

    Extra layers cost code; add them when duplication, complexity or slow tests justify it.

Summary

  • The Session already implements a unit of work; the patterns here wrap it so business code does not depend on it.
  • A repository offers domain-named methods (get, add, with_status) and hides queries and loading options.
  • A unit of work defines the transaction boundary: one use case, one commit.
  • Fakes make business-rule tests fast; keep a few integration tests for the real queries.
  • Do not add the layers until a real problem asks for them.

Next: Hybrid Properties, Custom Types and Mixins