Type-Safe Database Patterns

The Repository Boundary

A repository is an interface owned by the domain, implemented by the ORM adapter. Callers depend on the interface, so swapping Prisma for SQL or a test double never leaks into business logic:

interface UserRepository {
  findByEmail(email: string): Promise<User | null>;
  save(user: NewUser): Promise<User>;
}

class PrismaUserRepo implements UserRepository {
  constructor(private db: PrismaClient) {}
  findByEmail(email: string) {
    return this.db.user.findUnique({ where: { email } });
  }
}

Return domain-shaped values, not ORM payloads: mapping once at the boundary keeps include structures and scalar aliases out of service code.

Typed Filters and Query Helpers

Generic helpers remove repeated, error-prone mapping while staying specific:

type Filter<T> = {
  [K in keyof T]?: T[K] | { equals?: T[K]; contains?: string };
};

async function search<T>(
  repo: { findMany(f: Filter<T>): Promise<T[]> },
  filter: Filter<T>,
) {
  return repo.findMany(filter);
}

type Page<T> = { items: T[]; nextCursor: string | null };

Mapped filters autocomplete valid columns, and a Page<T> wrapper gives every list endpoint one predictable shape. For SQL you control yourself, a typed builder (Kysely, Drizzle) gives the same guarantees: table types come from one schema definition shared by every query.

Pitfalls and Interview Angles

Transactions do not span objects. Two repositories sharing no transaction client commit independently and can half-apply a change. Fix it with a unit of work: start the transaction, construct repositories over that client, commit or roll back once.

Abstraction tax. A repository that only wraps findUnique adds a layer without value; extract interfaces where substitution, testing, or dialect switching actually happens.

N+1 hides behind helpers. A generic findById called in a loop still queries per row — batch with where: { id: { in } }.

Interview: design a repository over a real table, then extend it to participate in one transaction across aggregates.