LayersGENERALSCALE-SPECIFICDATABASE-SPECIFIC

The Repository Layer

A named place for the queries your domain asks, so the definition of "active subscription" lives once instead of in eleven call sites.

What actually happens How to build it

The requirement, the obvious build, and why it breaks

Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.

The question

What does a repository buy you that calling the ORM directly does not?

The requirement

Six parts of the product need "this customer's active orders". Active means: not cancelled, not soft-deleted, placed in the last 90 days, and — since last quarter — belonging to a non-suspended account.

The obvious build

Each place writes the query it needs. It is three lines of ORM, it is right there next to the code that uses it, and an extra file for findActiveOrders feels like architecture for its own sake.

Why it breaks

The six queries were never identical. Two of them forgot cancelled_at IS NULL, so the dashboard count and the billing count disagree by a number nobody can explain.

How it breaks in production

The six queries were never identical. Two of them forgot cancelled_at IS NULL, so the dashboard count and the billing count disagree by a number nobody can explain.
Soft delete is added. deleted_at IS NULL gets appended to the four call sites someone grepped for, and the two written as raw SQL are missed. Deleted orders reappear in one report.
The "non-suspended account" rule arrives. It needs a join, so now five files know the shape of the accounts table.
A query that was fine on 10,000 rows becomes a sequential scan on 4 million. There is no single place to add the index hint, the pagination, or the covering column — there are six (Query Optimization: Finding the Actual Bottleneck).
Nobody can answer "which queries does this service run against orders?" without grepping for a table name across the whole codebase.

Requirement→API Contract→Application Logic→Data Access→External Deps→Concurrency→Failure→Security→Observability→Deployment→Scale

What is actually happening

A repository is an object whose methods are questions the domain asks, not operations the database offers. findActiveOrdersFor(customerId) is a question; findMany(where) is the database's vocabulary with a new spelling.
It is the place where a domain concept becomes a query. "Active" is a business definition; the WHERE clause is its implementation, and keeping them together is the entire value proposition.
It is a seam. Because the caller depends on the method name rather than on SQL, the implementation can change — add an index, denormalise a column, add a read replica, add a cache — without touching callers (Cache-Aside).
It maps storage rows to domain objects. That mapping is where a nullable column becomes an optional field and a NUMERIC becomes a money type instead of a float.
It does *not* own transactions. The caller decides that three writes are one unit; the repository accepts the transaction handle it is given (Where the Transaction Boundary Goes).

The method name is where the domain definition lives

The test for whether a repository is doing anything is simple: does its method name carry information that the ORM call underneath does not? findActiveOrdersFor(customerId) says something a reader could not have derived — that "active" excludes cancelled, soft-deleted and old orders, and now also orders on suspended accounts. findById(id) says nothing the ORM did not already say.

This is why the useful repositories in a codebase tend to be a handful of methods with opinionated names, sitting alongside plenty of direct ORM calls for the trivial lookups. That mix is not a failure of discipline; it is the pattern applied where it pays.

Six call sites, one definition

The definition at every call site

// dashboard.ts, billing.ts, export.ts, admin.ts, digest.ts, api.ts
const orders = await prisma.order.findMany({
  where: {
    customerId,
    cancelledAt: null,
    placedAt: { gte: ninetyDaysAgo() },
    // deletedAt: null   <- added in 4 of 6 files
    // account: { suspended: false }  <- added in 2 of 6
  },
})

The definition in one place

// orders/repository.ts — the only file that knows what "active" means
findActiveOrdersFor(tx, customerId: CustomerId, page: Page) {
  return tx.order.findMany({
    where: {
      customerId,
      cancelledAt: null,
      deletedAt: null,
      placedAt: { gte: ninetyDaysAgo() },
      account: { suspended: false },
    },
    include: { lines: true },   // batched here, so no caller can N+1
    take: page.size, skip: page.offset,
  })
}

When the "suspended account" rule arrived, the second version had one place to change and no way to miss a call site. The first version had six, and the two that were missed produced a billing figure that disagreed with the dashboard — a data-correctness bug, not a tidiness complaint. The include matters for the same reason: one place can guarantee the lines are batched, six places cannot.

What belongs on which side of the seam

Most confusion about repositories is about which decisions live above the seam and which live below it. The dividing line is whether the decision needs to know about the use case. Transaction boundaries do; index choice does not.

Getting this wrong in either direction is expensive. A repository that opens its own transactions makes atomic multi-step operations impossible. A service that constructs WHERE clauses has re-imported the schema into the application layer.

Decision	Where it belongs	Why	Symptom when it is on the wrong side
What "active" means	Repository	It is one definition many callers share	Two reports disagree and both queries look correct
Which index / join strategy	Repository	Callers cannot act on it and should not see it	A tuning change touches nine files
These three writes are atomic	Service	Only the use case knows they belong together	Partial writes after a mid-sequence failure
Retry on serialization failure	Service	It must replay the whole unit, not one statement	A retried statement inside a dead transaction
Page size limits	Repository (enforced) + API (chosen)	The cap protects the process; the value is a contract	A client asks for 100,000 rows and gets them (Pagination That Survives a Large Table)
Tenant scoping	Repository (mandatory parameter)	It must be impossible to omit	One forgotten predicate leaks another tenant's rows
May this user see this row	Service / handler	It needs the actor, not just the tenant	Any authenticated user reads any id (Object-Level Authorization)
Cache this result	Above the repository, usually	Invalidation is a use-case concern	Stale reads with no obvious owner (Cache Invalidation)

The seam is worth more for observability than for swappability

DATABASE-SPECIFICPostgres: pg_stat_statements normalises literals but keeps comments, so the tag survives. MySQL's performance schema digests strip comments by default unless comments is included in the digest configuration, so the same trick needs different setup; some managed proxies also strip comments in transit.

The classic argument for repositories — "we could change database" — is the weakest one, and believing it leads to the anti-pattern in the next lesson. The arguments that hold up in production are duller and much more useful: every query has a name, every query has one owner, and every query can be instrumented without touching a caller.

A named query surfaces in three places you actually look at: the slow-query log, the trace waterfall, and the per-request query count. Anonymous ORM calls scattered through handlers surface in none of them without work.

Tagging queries so production can name them

1-- every repository method emits its own name into the SQL text
2SELECT /* repo=orders.findActiveOrdersFor */ o.*
3FROM orders o
4JOIN accounts a ON a.id = o.account_id
5WHERE o.customer_id = $1
6  AND o.cancelled_at IS NULL
7  AND o.deleted_at IS NULL
8  AND o.placed_at >= $2
9  AND a.suspended = false
10ORDER BY o.placed_at DESC
11LIMIT $3 OFFSET $4;
12 
13-- then the slow-query view points at a method, not at an anonymous SELECT
14SELECT substring(query from 'repo=([a-zA-Z.]+)') AS method,
15       calls, mean_exec_time, rows
16FROM pg_stat_statements
17ORDER BY mean_exec_time * calls DESC
18LIMIT 20;

The comment is the point, not the query. Without it, pg_stat_statements gives you normalised SQL and you get to guess which of the four similar selects in the codebase it is. With it, the top-cost row names the function to open.

How to build it

Most important first.

Name methods after the question, with the domain's words. findOverdueInvoices() beats findByStatusAndDate(status, date) — the second one pushes the definition of "overdue" back onto every caller.
Keep the table name inside. If orders appears outside the orders repository, the seam has a hole in it.
Accept an optional unit-of-work handle as an explicit first parameter, not an optional trailing one, so a caller cannot forget it silently.
Return domain types where the mapping does real work (money, enums, dates with timezones) and be honest when it does not — a repository that returns the ORM entity unchanged is fine, and is not an abstraction (When the Repository Is Just Indirection).
Let read-heavy paths bypass it deliberately. A reporting query with fifteen joins and a window function does not belong behind a collection-shaped interface (Raw SQL in Application Code).
Put the N+1 fix here. Batch loading is a data-access decision, and the repository is where "load these orders with their lines in one query" can be made true for everyone (Eager Loading and Batching).

What can go wrong

Failure modes

Method explosion: findByCustomer, findByCustomerAndStatus, findByCustomerAndStatusAndDateRange. The interface is now a worse query language than the one it wraps.
The generic escape hatch — find(criteria) — where criteria is the ORM's own filter type. Every caller is coupled to the ORM again, through a layer that pretends they are not (When the Repository Is Just Indirection).
Repositories calling repositories, so one "simple" lookup issues four queries and nobody can see it from the call site (The N+1 Query Problem).
The transaction handle is optional and half the callers omit it, so writes that were meant to be atomic are not.
Caching added inside a repository method without invalidation, turning a correctness-neutral seam into a stale-data source (Cache Invalidation).

What can race

Read-then-write across two repository calls is a check-then-act gap: findActive() then markCancelled() can be interleaved by another request. Atomicity comes from the transaction and the constraint, not from the repository (Backend Races, Database Constraints).
Two calls in one request may run at different snapshots unless they share a transaction handle, so a total and its detail list can disagree (Isolation Levels).

Security

The repository is the natural chokepoint for tenant scoping: if every method requires a tenant id and every query includes it, a forgotten WHERE tenant_id = ? becomes a compile error rather than a data leak (Tenant Isolation).
It is also the natural chokepoint for parameterisation. String-built ORDER BY clauses from user input are the classic injection route that parameterised values do not cover — allowlist the column names (SQL Injection).
A repository is not authorization. Scoping by tenant is coarse; "may *this user* see *this* order" still needs an object-level check (Object-Level Authorization).

Misreads

"A repository lets us swap the database." Almost never true in practice, and the wrong reason to build one. Migrations, transaction semantics, isolation behaviour, types and the query dialect do not survive the swap (When the Repository Is Just Indirection).
"A repository is an ORM replacement." It sits above whatever data access you chose — ORM, query builder or raw SQL are still a separate decision (Choosing a Data Access Layer).
"Every table needs a repository." Repositories follow domain concepts. A join table usually has no questions of its own.
"Repository means one row at a time." A method returning a page, an aggregate or a projection is still a repository method; insisting on entity-shaped returns is how N+1 gets built in.

Operating it

How you see it in production

Tag every query with the repository method name (a SQL comment or an ORM query tag). Then pg_stat_statements and slow-query logs point at a method rather than at an anonymous SELECT (The Slow Query Workflow).
Count queries per request. A repository makes the count controllable; a per-request query counter in development makes an N+1 fail the test instead of the p99 (The Comb: N+1 as a Visible Shape).
Histogram the row counts returned. A method that used to return 20 rows and now returns 20,000 is a memory incident waiting for a traffic spike (Pagination That Survives a Large Table).

What changes at 10x and 100x

At 10x rows the queries behind the method change — index, pagination, covering columns — and callers do not. That is the concrete payoff, and it is the only one that is about scale rather than about people.
At 100x you may split reads to a replica for some methods and not others. A repository gives you a per-method place to make that choice, and a per-method place to be wrong about replication lag (Eventual Consistency in Practice).
None of this makes the service faster today. A repository is neutral on throughput; it changes where a future change lands.

What this costs

Every method is a file to open and a name to agree on. In a small codebase the indirection costs more than the drift it prevents.
The interface is a lowest common denominator. Partial selects, RETURNING, streaming cursors, ON CONFLICT and database-specific features either leak through or are unavailable (What an ORM Buys and What It Costs).
Mapping rows to domain objects costs allocation and CPU on large result sets — usually irrelevant, occasionally the whole problem on a 50,000-row export (What Serialization Costs).

Where this applies

Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.

GENERALThe idea of naming a query after the domain question it answers holds anywhere data is queried.
SCALE-SPECIFICFlips on shared definitions, not rows or requests. Below roughly three call sites for the same concept a repository is a rename; above about ten, or once a definition has changed twice, it is the only place a change can land completely. It also flips with team size: with two engineers the definition lives in their heads, with twenty it has to live in a file.
DATABASE-SPECIFICWhat can hide behind the interface depends on the engine: Postgres lets a method use ON CONFLICT, partial indexes and RETURNING invisibly to callers; MySQL's ON DUPLICATE KEY UPDATE differs in what it reports as affected; SQLite has no RETURNING before 3.35. A repository interface written against one engine's capabilities is not portable just because it is an interface.

Where the depth lives

This domain teaches the application-side mechanism and hands the rest off.

Databasequery-tuning reading-explain joins sql-basics

Architecturelayered-architecture hexagonal-architecture

Securitymulti-tenant-isolation

Performanceslow-query-workflow n-plus-one-traces