34 — Repositories, Transactions and Data Modelling

Level: Intermediate · Time: ~40 min · Prerequisites: Lesson 33 — PostgreSQL with Prisma and Migrations

Why this matters

Lesson 33 ended with a generated Prisma client, and the shortest path from there is to call prisma.productionDay.findMany() straight from a command handler. That is fine for one caller; at the third there is no test that runs without a real PostgreSQL. A repository puts one interface at the domain boundary, so what sits behind it — Prisma, hand-written SQL, an in-memory fake — is a detail, and transactions let one business action touch several tables and still be all-or-nothing. The modelling choices below cost nothing today and a data migration in six months.

Click the diagram to open it full size.

The repository as a domain boundary

The interface belongs to the domain and mentions nothing about Prisma; the implementation is the only file allowed to import @prisma/client.

// src/domain/ports/siteRepository.ts
import type { Site } from "../site.js";

export interface SiteFilter { readonly search?: string; readonly minCapacityKwp?: number; }
export interface SiteSort { readonly field: "name" | "capacityKwp" | "createdAt"; readonly direction: "asc" | "desc"; }
export interface PageRequest { readonly limit: number; readonly offset: number; readonly sort?: SiteSort; }
export interface Page<T> { readonly items: readonly T[]; readonly total: number; }

export interface SiteRepository {
  findById(id: string): Promise<Site | null>;
  list(filter: SiteFilter, page: PageRequest): Promise<Page<Site>>;
  save(site: Site): Promise<void>;
}

A test can now hand the domain an in-memory implementation, and the deletedAt: null rule lives in one file.

Mapping rows, filtering, sorting and pagination

The row and the domain object are different things, and the mapping resolves those differences once: column names, units, sentinels, and rows the domain should not see at all.

// src/infrastructure/prisma/siteRepository.ts
import { Prisma } from "@prisma/client";
import type { Page, PageRequest, Site, SiteFilter, SiteRepository, SiteSort } from "../../domain/ports/siteRepository.js";

interface SiteRow { id: string; name: string; latitude: number; longitude: number; capacityKwp: number; }
const toDomain = (row: SiteRow): Site => ({ id: row.id, name: row.name, latitude: row.latitude, longitude: row.longitude, capacityKwp: row.capacityKwp });

export class PrismaSiteRepository implements SiteRepository {
  constructor(private readonly db: Prisma.TransactionClient) {}

  async findById(id: string): Promise<Site | null> {
    const row = await this.db.site.findFirst({ where: { id, deletedAt: null } });
    return row === null ? null : toDomain(row);
  }

  async list(filter: SiteFilter, page: PageRequest): Promise<Page<Site>> {
    const where: Prisma.SiteWhereInput = { deletedAt: null };
    if (filter.minCapacityKwp !== undefined) where.capacityKwp = { gte: filter.minCapacityKwp };
    const [rows, total] = await Promise.all([
      // An unbounded limit is a denial of service you wrote yourself, hence the clamp:
      this.db.site.findMany({ where, orderBy: orderByFor(page.sort), take: Math.min(page.limit, 100), skip: page.offset }),
      this.db.site.count({ where }),
    ]);
    return { items: rows.map(toDomain), total };
  }
}

Mapping is not ceremony: it is what survives a renamed column, a Decimal that must become a rounded number, and a -999 sentinel meaning "no measurement"; save is a three-line upsert with updatedAt left to @updatedAt. orderByFor is the helper the class calls — an exhaustive switch over the three literals in SiteSort — so a query-string value can never reach ORDER BY. In raw SQL the same rule holds through Prisma.sql and bound parameters.

Transactions and the unit of work

A unit of work runs a function with repositories that share one database transaction, so callers describe a business action and receive atomicity from the plumbing. Repositories bundles the two repositories and UnitOfWork has one method, run<T>(work: (repos: Repositories) => Promise<T>): Promise<T>.

// src/infrastructure/prisma/unitOfWork.ts — Repositories and UnitOfWork are the two interfaces of the port
import { PrismaClient } from "@prisma/client";
import type { Repositories, UnitOfWork } from "../../domain/ports/unitOfWork.js";
import { PrismaProductionRepository } from "./productionRepository.js";
import { PrismaSiteRepository } from "./siteRepository.js";

export class PrismaUnitOfWork implements UnitOfWork {
  constructor(private readonly prisma: PrismaClient) {}

  async run<T>(work: (repos: Repositories) => Promise<T>): Promise<T> {
    return this.prisma.$transaction(
      async (tx) => work({ sites: new PrismaSiteRepository(tx), production: new PrismaProductionRepository(tx) }),
      { timeout: 10_000 },
    );
  }
}

[!WARNING] Callers write uow.run(async (repos) => { ... }) and use only the repositories they were handed: the outer prisma client takes a different connection, so a write through it is not part of the transaction, survives the rollback, and can deadlock a long transaction waiting for it.

Data modelling: timestamps, soft delete and audit columns

These columns answer questions you would otherwise be unable to answer: when the row was created, when it last changed, and whether it still exists.

model Site {
  id        String    @id @default(cuid())
  name      String
  createdAt DateTime  @default(now()) @db.Timestamptz(3)
  updatedAt DateTime  @updatedAt @db.Timestamptz(3)
  deletedAt DateTime? @db.Timestamptz(3)
}

Decision	What it buys	What it costs if you skip it
`createdAt` / `updatedAt`	Ordering, debugging, cache invalidation	"When did this change?" has no answer
`deletedAt` instead of `DELETE`	History survives; a wrong delete is reversible	One bad `DELETE` loses a month of totals
Natural key `@@unique([siteId, day])`	Re-importing a week is a no-op	Duplicate rows and doubled totals
Units in the name (`yieldKwh`)	Nobody has to guess kWh, kW or W	A factor-of-1000 bug in a customer report
`@db.Date` for a day, `Timestamptz` for an instant	No drift on a midnight boundary	The first of the month counted twice

Soft delete has one sharp edge worth knowing early: a unique constraint still counts deleted rows, so the same natural key cannot be re-created until the old row is physically removed.

Idempotent imports, N+1 queries and measuring

An import you may run twice must be safe to run twice: upsert on the natural key, batch it, and time it.

// src/infrastructure/prisma/productionRepository.ts (excerpt)
import { performance } from "node:perf_hooks";

async upsertDays(days: readonly ProductionDay[]): Promise<number> {
  const started = performance.now();
  const rows = days.map((day) => ({ siteId: day.siteId, day: new Date(`${day.date}T00:00:00Z`), yieldKwh: day.yieldKwh }));
  const result = await this.db.productionDay.createMany({ data: rows, skipDuplicates: true });
  console.log(`${result.count} of ${rows.length} rows in ${Math.round(performance.now() - started)} ms`);
  return result.count;
}

The opposite pattern — for (const site of sites) await repo.findProduction(site.id) — is the N+1 problem: one query to list forty sites, then forty more. Ask the client to show what it sends with new PrismaClient({ log: [{ emit: "event", level: "query" }] }) and prisma.$on("query", ...); fifty lines of SQL for one report means fifty round trips, where one groupBy with _sum: { yieldKwh: true } means one.

Common mistakes

Mistake	What happens	The fix
Returning Prisma rows from a repository	Domain code imports `@prisma/client`; a schema change breaks every module	Map to a domain type inside the repository
Using the outer client inside `$transaction`	That write escapes the transaction and survives a rollback	Use only the repositories passed into the work function
Forgetting `deletedAt: null` in one query	A deleted site reappears in a report and nobody can explain it	Keep the filter inside the repository
Sorting by a raw query-string value	An attacker orders by any column, or injects through `ORDER BY`	Map a union of allowed literals to column names
`take` with no maximum	A caller asks for a million rows and the database serves them	Clamp with `Math.min(limit, 100)`
Looping with `await` inside a `for`	N+1 queries and a report that takes minutes	`groupBy`, `include`, or one batched write
Re-importing the same CSV	Duplicated days and doubled monthly totals	`upsert`, or `createMany` with `skipDuplicates`

Try it

Add the audit and soft-delete columns to your Lesson 33 schema and migrate with npx prisma migrate dev --name audit_and_soft_delete.

Expected output: a new prisma/migrations/ folder, then Your database is now in sync with your schema.

Implement the repositories and a script importing one week of days for a site, then run it twice:

npx tsc && node dist/scripts/importWeek.js && node dist/scripts/importWeek.js

Expected output: 7 of 7 rows in 45 ms, then 0 of 7 rows in 12 ms — counts matter, timings vary.

Make the script throw between the site write and the day writes, then inspect the tables with npx prisma studio.

Expected output: no site and no days; the transaction rolled back.

Key takeaways

A repository is an interface in the domain's vocabulary; a test can use a fake instead of a database.
Mapping rows to domain objects resolves units, names and sentinels once, inside the repository.
Transactions make several repositories share one connection: anything outside the context you were handed is outside the transaction.
createdAt/updatedAt, soft delete and a natural key are cheap today and expensive to retrofit.
Idempotency belongs to the schema and the write, not the caller.

Check yourself

Why does PrismaSiteRepository accept a Prisma.TransactionClient?
A colleague adds deletedAt but leaves three findMany calls untouched: what does next month's report show?
Your report runs 41 queries for 40 sites. Sketch both shapes of that code and say which is N+1.