The Data Access Layer

"Any fool can write code that a database understands. Good programmers write code that humans understand — and keep the database's dialect in one small room." — after Martin Fowler

Volume 7 taught you the database: tables, joins, indexes, transactions, the query plan. You can now write SQL that is correct and fast. This chapter is about the other side of that conversation — the part that lives in your application. How does a backend, written in TypeScript, actually reach across to a running database, ask its questions, and turn the answers into objects your business logic can use — without letting the database's concerns bleed into every corner of your codebase?

This is the data access layer: the seam between "my app" and "the store." Get it right and your service is testable, your queries live in one place, and swapping Postgres for something else is a contained change. Get it wrong and SQL strings are smeared through your controllers, every test needs a real database, and a schema rename breaks forty files. This is also a favorite interview area — "why put a repository between your service and the DB?" is a question that separates people who have maintained a backend from people who have only read about one.

The layers: controller → service → repository

A well-structured request flows through three responsibilities, each ignorant of the others' details:

The controller is the subject of Part II (see Routing & Controllers). The service is where your rules live. The repository — sometimes called a DAO, a data-access object — is our subject here: the single, narrow place that speaks database.

Why draw the lines this way? Because each boundary buys you something concrete:

• Testability. A service that talks only to a repository interface can be tested against a fake in-memory repository — no database, no Docker, tests that run in milliseconds. (More on this below; it's the payoff that convinces skeptics.)
• Swappability. If all SQL lives behind UserRepository, moving from raw queries to an ORM, or Postgres to a read-replica, or a table to a different store, touches one implementation — not the whole app.
• One place for queries. When "find active users" is defined once, you optimize it once, add the index once, fix the N+1 once. When it's inlined in six controllers, you fix it six times and miss the seventh.
• A clean vocabulary. The service asks for a User; it does not care that the row came back as snake_case columns with a password_hash you must never leak.

The repository pattern, made concrete

Here is the whole idea in code you can run. We define a domain entity (User), a repository interface (the only shape the service knows), and one implementation backed by an in-memory Map — we simulate the database, including a UNIQUE(email) constraint that raises a domain error instead of a raw DB error. A thin service uses it. Run it and watch create, find, duplicate, and not-found each behave:

Notice what the UserService does not know: whether there's a Map, a Postgres pool, or a network call behind UserRepository. That ignorance is the point. In production you'd pass a real PostgresUserRepository to the constructor; in tests you pass an InMemoryUserRepository (exactly the one above). Same service, no database in the test suite. This is dependency injection, and the repository interface is what makes it possible.

How the repository actually talks to the database

Inside that repository, you reach the database through one of three tools, in ascending order of abstraction. Volume 7's ORMs & Query Builders chapter covered these from the database's side; here's what they mean for your code:

None is "correct." A team optimizing for control and predictable SQL leans on a driver or query builder; a team optimizing for velocity leans on an ORM. Crucially, the choice is hidden behind the repository interface — the service never sees it, so you can even mix them (an ORM for CRUD, a raw driver query for one gnarly report).

Here's a real repository over the pg driver — show-only, because it needs a live database and a network the sandbox doesn't have. Note the parameterized query ($1) and the by-hand row mapping:

// Real driver usage — SHOWN, not run (needs a live Postgres + network).
import { Pool } from "pg";

class PostgresUserRepository implements UserRepository {
  constructor(private pool: Pool) {}

  async findByEmail(email: string): Promise<User | null> {
    // Parameterized — the value goes in as data via $1, NEVER string-concatenated.
    const { rows } = await this.pool.query(
      `SELECT id, email_address, display_name FROM users WHERE email_address = $1`,
      [email],
    );
    return rows.length ? this.toUser(rows[0]) : null;
  }

  private toUser(row: any): User {
    return { id: String(row.id), email: row.email_address, displayName: row.display_name };
  }
}

And the same repository over an ORM (Prisma-flavored) — also show-only. Far less code, at the cost of queries you don't see written:

// Real ORM usage — SHOWN, not run.
class PrismaUserRepository implements UserRepository {
  constructor(private db: PrismaClient) {}

  async findByEmail(email: string): Promise<User | null> {
    const row = await this.db.user.findUnique({ where: { email } });
    return row ? { id: row.id, email: row.email, displayName: row.displayName } : null;
  }
}

The N+1 problem, revisited from the app side

Volume 7 named the ORM's most infamous trap; it's worth recalling because you will cause it in application code without noticing. You fetch a list of 50 orders, then loop over them accessing order.customer — and the ORM lazily fires one more query per order to load each customer. That's 1 query for the orders + N queries for the customers = N+1 round trips to the database, each with its own network latency. At 50 orders it's a slow page; at 5,000 it's an outage.

The fix is eager loading: tell the ORM to fetch the relation up front, so it issues one join (or one batched follow-up) instead of N:

// N+1 (bad): 1 query for orders, then a query per order for its customer.
const orders = await db.order.findMany({ where: { status: "open" } });
for (const o of orders) console.log(o.customer.name); // ← lazy load fires here, N times

// Eager (good): one query, customer joined in.
const orders2 = await db.order.findMany({
  where: { status: "open" },
  include: { customer: true },        // ← eager load the relation
});

The lesson for your data layer: because the repository is the one place queries live, it is also the one place you enforce eager loading. A findOpenOrdersWithCustomer() method that always includes the customer is impossible to accidentally N+1 — the trap is designed out at the seam. Scattered inline queries give the trap a hundred doors.

Connection pooling, from the app's perspective

Opening a database connection is expensive — a TCP handshake, TLS, authentication, session setup — often tens of milliseconds. If your backend opened a fresh connection for every request, that overhead would dwarf the actual query, and under load you'd exhaust the database's connection limit and fall over. Volume 7's Caching, Pooling & the Data Layer chapter explained the mechanism; here's what it means for the code you write.

Your app holds a pool: a fixed set of already-open connections that requests borrow and return. The flow per query is: acquire a connection from the pool → run the query → release it back. The pool is created once at startup, not per request:

// Create the pool ONCE, at startup — shared across all requests. (Show-only.)
const pool = new Pool({
  host: process.env.DB_HOST,
  database: "app",
  max: 20,                       // pool size: at most 20 concurrent DB connections
  idleTimeoutMillis: 30_000,     // reclaim idle connections
  connectionTimeoutMillis: 5_000 // fail fast if none free within 5s
});

Three numbers you must reason about:

• Pool size (max). Too small and requests queue waiting for a free connection under load. Too large and you overwhelm the database — every connection costs the DB memory, and Postgres has its own max_connections. The pool size across all your app instances must fit under the database's ceiling. A common mistake at scale: 10 app servers × a pool of 50 each = 500 connections hammering a database configured for 100.
• Exhaustion. When every connection is checked out and a new request asks for one, it waits. If it waits past connectionTimeoutMillis, the acquire fails. Exhaustion is usually a symptom of something else: slow queries holding connections too long, a leaked connection never released, or a missing index (see Volume 7's indexing chapters) turning a fast query slow.
• Leaks. If you acquire a dedicated connection (for a transaction, say) and forget to release it on an error path, the pool shrinks by one every time — until it's empty and the app hangs. Always release in a finally.

Mapping rows to domain objects: entities vs DTOs

The database hands your driver a row: snake_case columns, database types, and often fields the rest of your app should never touch (a password_hash, an internal tenant_id). Two translations keep this contained:

• Row → entity. A mapper turns the raw row into a clean domain entity in your app's vocabulary (camelCase, real Date objects, sensitive columns dropped). This is the only code that knows column names.
• Entity → DTO. When you send data back over HTTP, you shape it into a DTO (Data Transfer Object) — precisely the fields the client should see, and no more. The entity is internal; the DTO is the public contract.

Run both translations:

Two safety properties fall out for free. Because password_hash is dropped at the mapper, it cannot accidentally reach a controller. Because the DTO is built explicitly, a new sensitive column added to the table later does not silently leak into API responses — you'd have to add it to toPublicDTO on purpose. Compare that to res.json(row), which ships every column, forever, including the ones you add next year.

Don't let database concerns leak up the stack

The whole discipline reduces to one rule: database concerns stop at the repository. Below it, SQL, rows, connection handles, and driver-specific error codes are fine. Above it — in services and controllers — none of that should be visible. Two leaks matter most:

Parameterized queries, always. Never build SQL by concatenating user input into the string. This is the door to SQL injection, and it's serious enough that Part VII (Security) devotes attention to it. The repository passes values as parameters ($1, ?), so the database treats them strictly as data, never as SQL to execute:

// NEVER — string interpolation lets input become SQL. (Anti-pattern, show-only.)
pool.query(`SELECT * FROM users WHERE email = '${email}'`);
//                      email = "'; DROP TABLE users; --"  → catastrophe

// ALWAYS — the value goes in as a bound parameter, never parsed as SQL.
pool.query(`SELECT * FROM users WHERE email = $1`, [email]);

Translate DB errors into domain errors. When the database raises a unique-violation (Postgres error code 23505) or returns no rows, the repository catches it and throws a DuplicateError or NotFoundError — errors your app defined, in your app's vocabulary. Services reason about those; they never see a 23505. The controller then maps the domain error to an HTTP status (this is the through-line to Part II's Error Handling & Status Codes):

The reason to translate rather than let the raw error bubble up: a 23505 bubbling into your HTTP layer couples your API's behavior to Postgres. Swap databases and every error code changes. Translate at the seam, and the layers above are insulated — they know DuplicateError, not Postgres.

Common Mistakes

• SQL in the controller. Inlining pool.query(...) in a route handler collapses all three layers into one, makes the handler untestable without a database, and scatters queries you can never again optimize in one place. Push data access down into a repository.
• String-concatenating queries. The single most dangerous habit in backend code — it is SQL injection waiting to happen. Parameterize everything, no exceptions, even for "internal" values.
• Leaking rows straight to the client. res.json(row) ships password_hash, internal ids, and every future column. Map to an entity, then to an explicit DTO.
• Leaking DB errors upward. Letting a raw 23505 or a driver exception reach the controller couples your API to your database vendor. Translate to domain errors at the repository.
• A pool per request (or per serverless invocation). Creating connections per request destroys performance and exhausts the database. Create the pool once; under serverless, use a pooler.
• The accidental N+1. Looping over a list and touching a lazy relation fires a query per item. Define an eager-loading query method on the repository and use it.
• A repository that returns ORM entities. If findById hands back a live, DB-attached ORM object, "database concerns" have escaped — the service can lazily trigger queries or mutate rows by accident. Return plain domain objects.

Summary

• The data access layer is the seam between your app and the store. Structure a request as controller → service → repository, each ignorant of the others' concerns: HTTP up top, business rules in the middle, SQL only at the bottom.
• The repository pattern hides all database knowledge behind an interface, buying testability (fake it in unit tests), swappability, one place for queries, and a clean domain vocabulary.
• Reach the database through a driver, query builder, or ORM — the choice hides behind the repository. ORMs bring the N+1 trap; kill it with eager loading defined once on the repository (recall Volume 7).
• Hold a connection pool created once at startup; reason about pool size (fit under the DB's limit across all instances), exhaustion, and leaks (release in finally). Serverless needs a pooler.
• Map rows → entities → DTOs so snake_case, DB types, and sensitive columns never leak upward. Build DTOs explicitly.
• Parameterize every query (never concatenate — SQL injection, Part VII), and translate DB errors into domain errors (NotFoundError → 404, DuplicateError → 409) so the layers above never see a vendor error code.

With the data layer in place, the natural next questions are about correctness under concurrent writes and speed under load — transactions crossing the repository boundary, and caching the reads that don't need the database at all. Those are the chapters ahead in Part V.