The repository pattern over an ORM

Name each repository method for the question it answers, and let only the service begin and end the transaction its repositories share.

Before you read: predict

ExamHall's submit flow runs on pg. A developer made GradingJobRepository.enqueue atomic on its own, so that the re-grade tool can reuse it:

Attempt a-311 is open, with no grading job and no audit row. submit('a-311') throws, because receipts.issue breaks the NOT NULL on receipts.pdf_path.

Afterwards, what do the tables hold for a-311?

import type { Pool, PoolClient } from 'pg'; export class AttemptRepository {  async markSubmitted(db: PoolClient, attemptId: string) {    await db.query("UPDATE attempts SET status = 'submitted' WHERE id = $1", [attemptId]);  }} export class GradingJobRepository {  async enqueue(db: PoolClient, attemptId: string) {    await db.query('BEGIN');    await db.query('INSERT INTO grading_jobs (attempt_id) VALUES ($1)', [attemptId]);    await db.query('COMMIT');  }} export class AuditRepository {  async record(db: PoolClient, attemptId: string, event: string) {    await db.query('INSERT INTO audit_log (attempt_id, event) VALUES ($1, $2)', [attemptId, event]);  }} export class ReceiptRepository {  async issue(db: PoolClient, attemptId: string) {    await db.query('INSERT INTO receipts (attempt_id) VALUES ($1)', [attemptId]);  }} export class SubmitService {  constructor(private pool: Pool, private attempts: AttemptRepository, private jobs: GradingJobRepository,              private audit: AuditRepository, private receipts: ReceiptRepository) {}   async submit(attemptId: string) {    const db = await this.pool.connect();    try {      await db.query('BEGIN');      await this.attempts.markSubmitted(db, attemptId);      await this.jobs.enqueue(db, attemptId);      await this.audit.record(db, attemptId, 'submitted');      await this.receipts.issue(db, attemptId);      await db.query('COMMIT');    } catch (e) {      await db.query('ROLLBACK');      throw e;    } finally {      db.release();    }  }}

The 30-second version

A repository answers the domain's questions by name, such as findActiveAttempt or lockForSubmit, and holds each query once; a generic CRUD wrapper hands every query back to its callers. The service owns the unit of work: one client, one BEGIN, one COMMIT. Repositories never commit: their BEGIN only warns, and their COMMIT commits the service's work early; later statements autocommit.

Name each repository method for the question it answers, and let only the service begin and end the transaction its repositories share.

How it works

Step 1 of 16

A collection with meaning. A repository gives the domain a collection-like interface; the tables and the queries behind it stay hidden (Fowler).

Analogy: A fireworks display's firing panel (pyrotechnics)

A display has a magazine keeper, several rig crews and one firing director at one firing panel.

At the display	In the code
the keeper hands each crew "the finale set for the river show", already matched to its cues, not "crate 9, tube 4"	a method named for the question, holding its query (steps 2–3)
each crew wires its racks into the director's one panel, and nothing goes up until the director presses Fire	one client, one `COMMIT`, at the service (steps 7–8)
the show is called off: the director disarms the panel, and every wired rack stays unlit	`ROLLBACK` (step 7)
a crew member presses the panel's Fire button: everything wired so far goes up, and racks wired after that are lit one by one, by hand	a repository's `COMMIT`; later statements commit alone (step 10)
a crew's test circuit is reset without disarming the panel	a savepoint (step 11)
a crew that fires from its own hand igniter has fired, whatever the director decides	a repository on a transaction of its own

In real terms: the method's name says which rows; the service's client says which transaction; only the service's COMMIT says when.

Breaks down when: a rack wired after an early Fire still waits for a hand; a statement after an early COMMIT waits for nobody and commits as it runs.

Contrasting case: a single rocket at a garden party needs no panel. One statement is atomic on its own, so a service that writes once needs no BEGIN.

Examples, run for real

4.1 ExamHall EXH-2570: two ways to submit, one generic repository

Attempts reach submitted two ways: the candidate's Submit button, and the deadline sweeper. Both used a GenericRepository<Attempt>:

const attempt = await attempts.findById(db, attemptId);
if (attempt?.status === 'open') {
  await attempts.update(db, attemptId, { status: 'submitted' });
  await jobs.create(db, { attemptId });
}

4.1 EXH-2570 GenericRepository<Attempt> on both paths, both reads before either write: a-77 submitted · 2 grading jobs
4.1 MR !2870 lockForSubmit on both paths: the sweeper waited for the lock (pg_blocking_pids) · a-77 submitted · 1 grading job

Each path read open before the other wrote, so each queued a grading job (step 4). MR !2870 adds AttemptRepository.lockForSubmit, which locks the row and refuses an attempt that is not open; both paths call it, and the sweeper waits for the lock, then finds submitted (step 5). The lock itself is db-lost-update-strategies' teaching.

4.2 ExamHall EXH-2571: repositories with a pool of their own

The submit flow's repositories were built with the pool and ran each statement through this.pool.query, so each statement was its own transaction:

export class GradingJobRepository {
  constructor(private pool: Pool) {}
  async enqueue(attemptId: string) {
    await this.pool.query('INSERT INTO grading_jobs (attempt_id) VALUES ($1)', [attemptId]);
  }
}

4.2 EXH-2571 repositories on the pool: a-78 (receipt insert fails) throws 23502: submitted · 1 job · 0 receipts | a-79 (receipt insert succeeds) returns: submitted · 1 job · 1 receipt
4.2 MR !2871 one client, BEGIN at the service: a-78 (receipt insert fails) throws 23502: open · 0 jobs · 0 receipts | a-79 (receipt insert succeeds) returns: submitted · 1 job · 1 receipt

The failed receipt left the attempt submitted with its job queued and no receipt. MR !2871 makes the service check out one client, send BEGIN, and pass that client to each repository method (steps 7–8).

Try it

Answer first, then check. Nothing is saved.

Question 1 of 5

A payments ledger's billing service runs on pg. Three callers each list a customer's open invoices with their own SQL:

Finance's rule: an invoice is open while it is issued and not voided, until 90 days after its due date, when it goes to collections. For customer c-4 on 2026-10-01 the three callers list different invoices. The team wants two things: all three list the same invoices for c-4, the ones the rule gives, and the 90-day rule written in one file only.

Which change gets both?

// billing/customer-page.tsimport type { Pool } from 'pg';export async function pageInvoices(pool: Pool, customerId: string, today: string) {  const { rows } = await pool.query(    "SELECT id FROM invoices WHERE customer_id = $1 AND status = 'issued' AND NOT voided ORDER BY id", [customerId]);  return rows.map((r) => String(r.id));} // billing/dunning.tsimport type { Pool } from 'pg';export async function dunningInvoices(pool: Pool, customerId: string, today: string) {  const { rows } = await pool.query(    "SELECT id FROM invoices WHERE customer_id = $1 AND status = 'issued' AND due_on >= $2::date - 90 ORDER BY id", [customerId, today]);  return rows.map((r) => String(r.id));} // billing/export.tsimport type { Pool } from 'pg';export async function exportInvoices(pool: Pool, customerId: string, today: string) {  const { rows } = await pool.query(    "SELECT id FROM invoices WHERE customer_id = $1 AND status = 'issued' AND NOT voided AND due_on >= $2::date - 90 ORDER BY id", [customerId, today]);  return rows.map((r) => String(r.id));}

Question 2 of 5

A shop's catalog import runs on pg:

The prices table has CHECK (cents > 0). A batch of three rows, mug 1200, lid -50 and flask 2400, returns { products: 3, priced: 1, skipped: 2 }, and afterwards the catalog holds none of the three products. The team's rule: every product in a batch is stored, with every price the database accepts, and a batch with a product the database refuses stores nothing.

Which is the bug?

import type { Pool, PoolClient } from 'pg'; export class ProductRepository {  async upsert(db: PoolClient, sku: string, title: string) {    await db.query('INSERT INTO products (sku, title) VALUES ($1, $2) ON CONFLICT (sku) DO UPDATE SET title = $2', [sku, title]);  }} export class PriceRepository {  async set(db: PoolClient, sku: string, cents: number): Promise<boolean> {    try {      await db.query('INSERT INTO prices (sku, cents) VALUES ($1, $2)', [sku, cents]);      return true;    } catch {      return false; // a refused price is skipped    }  }} export class ImportService {  constructor(private pool: Pool, private products: ProductRepository, private prices: PriceRepository) {}   async importBatch(rows: { sku: string; title: string; cents: number }[]) {    const db = await this.pool.connect();    try {      await db.query('BEGIN');      for (const r of rows) await this.products.upsert(db, r.sku, r.title);      let priced = 0;      for (const r of rows) if (await this.prices.set(db, r.sku, r.cents)) priced++;      await db.query('COMMIT');      return { products: rows.length, priced, skipped: rows.length - priced };    } catch (e) {      await db.query('ROLLBACK');      throw e;    } finally {      db.release();    }  }}

Question 3 of 5

A chat app runs on Prisma ORM 8. Posting a message also stores an unread marker for each room member:

The merge request says: "bump is atomic on its own, and postMessage calls it inside its transaction, so if the notification insert fails, the unread markers roll back with the message." Which review reply holds up?

// chat/unread.repository.tsimport { db } from '../prisma/db.js'; export class UnreadRepository {  bump(roomId: string, memberIds: string[]) {    return db.transaction(async (tx) => {      for (const userId of memberIds) await tx.orm.public.Unread.create({ roomId, userId });    });  }} // chat/post-message.tsimport { db } from '../prisma/db.js';import type { UnreadRepository } from './unread.repository.js'; export function postMessage(unread: UnreadRepository, roomId: string, authorId: string, body: string, memberIds: string[]) {  return db.transaction(async (tx) => {    const message = await tx.orm.public.Message.create({ roomId, authorId, body });    await unread.bump(roomId, memberIds);    await tx.orm.public.Notification.create({ messageId: message.id, roomId });    return message;  });}

Question 4 of 5

A box office runs on pg. Releasing a seat takes two statements, and two callers release seats:

The box office wants three things: the night job releases an expired hold and logs it; when the night job's seat_events insert fails, the seat stays held; and when a refund's ledger insert fails, the refund leaves nothing behind and its seat stays held.

Which change gets all three?

// seats/seat.repository.tsimport type { PoolClient } from 'pg';export class SeatRepository {  async release(db: PoolClient, seatId: string) {    await db.query('UPDATE seats SET held_by = NULL WHERE id = $1', [seatId]);    await db.query("INSERT INTO seat_events (seat_id, event) VALUES ($1, 'released')", [seatId]);  }} // refunds/refund.service.tsimport type { Pool } from 'pg';import type { SeatRepository } from '../seats/seat.repository.js';export class RefundService {  constructor(private pool: Pool, private seats: SeatRepository) {}  async refund(orderId: string, seatId: string, cents: number) {    const db = await this.pool.connect();    try {      await db.query('BEGIN');      await db.query('INSERT INTO refunds (order_id, cents) VALUES ($1, $2)', [orderId, cents]);      await this.seats.release(db, seatId);      await db.query('INSERT INTO ledger (order_id, cents) VALUES ($1, $2)', [orderId, -cents]);      await db.query('COMMIT');    } catch (e) {      await db.query('ROLLBACK');      throw e;    } finally {      db.release();    }  }} // jobs/expire-hold.tsimport type { Pool } from 'pg';import type { SeatRepository } from '../seats/seat.repository.js';export async function expireHold(pool: Pool, seats: SeatRepository, seatId: string) {  const db = await pool.connect();  try {    await seats.release(db, seatId);  } finally {    db.release();  }}

Question 5 of 5

A video service cancels subscriptions on pg and asks each leaver for a reason:

cancel_reasons has CHECK (char_length(reason) <= 280), and credits has CHECK (cents >= 0). A leaver who types a 300-character reason cannot cancel: cancel throws, and nothing is kept. The team wants two things: when a reason is refused, the plan is still cancelled, the credit issued and the mandate stopped, with no reason stored; and when a credit is refused, nothing is kept.

Which change to cancel gets both?

import type { Pool, PoolClient } from 'pg'; export class PlanRepository {  async cancel(db: PoolClient, subscriptionId: string) {    await db.query("UPDATE subscriptions SET plan = 'cancelled' WHERE id = $1", [subscriptionId]);  }} export class CreditRepository {  async issue(db: PoolClient, subscriptionId: string, cents: number) {    await db.query('INSERT INTO credits (subscription_id, cents) VALUES ($1, $2)', [subscriptionId, cents]);  }} export class ReasonRepository {  async record(db: PoolClient, subscriptionId: string, reason: string) {    await db.query('INSERT INTO cancel_reasons (subscription_id, reason) VALUES ($1, $2)', [subscriptionId, reason]);  }} export class MandateRepository {  async stop(db: PoolClient, subscriptionId: string) {    await db.query('UPDATE mandates SET active = false WHERE subscription_id = $1', [subscriptionId]);  }} export class CancelService {  constructor(private pool: Pool, private plans: PlanRepository, private credits: CreditRepository,              private reasons: ReasonRepository, private mandates: MandateRepository) {}   async cancel(subscriptionId: string, refundCents: number, reason: string) {    const db = await this.pool.connect();    try {      await db.query('BEGIN');      await this.plans.cancel(db, subscriptionId);      await this.credits.issue(db, subscriptionId, refundCents);      await this.reasons.record(db, subscriptionId, reason);      await this.mandates.stop(db, subscriptionId);      await db.query('COMMIT');    } catch (e) {      await db.query('ROLLBACK');      throw e;    } finally {      db.release();    }  }}

Go deeper

Pro tip

In an integration test of a service method, select the xmin of every row the call wrote: rows written in one transaction share one xmin, so a count above one shows the unit split. When: reviewing a service that calls several repositories (steps 7–8; xmin is db-transactions-wal-mvcc's step 3). Verify:
```
tip 1 §4.2 as written (repositories on the pool), a-79 submitted: 3 rows written · 3 distinct xmin
tip 1 after MR !2871 (one client, BEGIN at the service), a-79 submitted: 3 rows written · 1 distinct xmin
```

Source

Prisma's documentation changes with releases, so its claims are labelled as of 2026-10-02; Prisma's behaviour is cited only, never executed here.

Martin Fowler, Repository, Patterns of Enterprise Application Architecture catalog (pattern by Edward Hieatt and Rob Mee), the curriculum row's first source: https://martinfowler.com/eaaCatalog/repository.html (fowler-repository.txt). Teaching for steps 1–4 and 6: a repository "Mediates between the domain and data mapping layers using a collection-like interface for accessing domain objects", and "In these cases particularly, adding this layer helps minimize duplicate query logic."
Martin Fowler, Unit of Work, the same catalog: https://martinfowler.com/eaaCatalog/unitOfWork.html (fowler-unit-of-work.txt). Teaching for step 7: a unit of work "Maintains a list of objects affected by a business transaction and coordinates the writing out of changes and the resolution of concurrency problems."
Prisma documentation, Transactions (Prisma ORM 8, as of 2026-10-02), the page the row's link (prisma.io/docs/orm/prisma-client/queries/transactions) now redirects to, read as its markdown: https://www.prisma.io/docs/orm/fundamentals/transactions.md (prisma-8-transactions.txt).
Prisma documentation, Transactions and batch queries (Prisma ORM 7, the row's title, as of 2026-10-02): https://www.prisma.io/docs/orm/v7/prisma-client/queries/transactions.md (prisma-7-transactions.txt). Background for steps 15–16; it keys nothing.
PostgreSQL 17 documentation, BEGIN, COMMIT, ROLLBACK, SAVEPOINT and ROLLBACK TO SAVEPOINT: https://www.postgresql.org/docs/17/sql-begin.html, sql-commit.html, sql-rollback.html, sql-savepoint.html and sql-rollback-to.html (pg17-sql-*.txt). Teaching for steps 9–13; every item executes what they state. BEGIN: "Issuing BEGIN when already inside a transaction block will provoke a warning message. The state of the transaction is not affected. To nest transactions within a transaction block, use savepoints". SAVEPOINT: "Savepoints can only be established when inside a transaction block." ROLLBACK TO SAVEPOINT: "Roll back all commands that were executed after the savepoint was established and then start a new subtransaction at the same transaction level." ROLLBACK: "Issuing ROLLBACK outside of a transaction block emits a warning and otherwise has no effect."

Checked against the tools and docs as of 2026-10-02.

Predict first