Database Access & Integration Testing

The existing todo routes use the shared client in api/src/database.ts. The room routes still use their in-memory array; this chapter moves the room reads to PostgreSQL.

One application pool, separate test data

The shared sql client manages a pool of connections. Query functions reuse it; they do not create or close a pool per request. The listener owns shutdown in the running application. A database test closes its client after all related steps.

When running tests, the DATABASE_URL environment variable points to a separate test database. The test process imports the same application code as development, but the shared client connects to a different database.

Figure 1 shows the configuration boundary. Development and tests use the same application and repository code, but run in separate processes with separately configured clients and databases. Test fixtures still need cleanup within the shared test database.

Set DATABASE_URL before starting the test process. Modules can create their shared client when they are first imported; changing the environment afterward does not reconfigure an existing client.

Keep data values out of SQL syntax

A query with a value parameter has this form:

const rows = await sql`
  SELECT id, name, capacity
  FROM lab_rooms
  WHERE capacity >= ${minimumCapacity}
  ORDER BY id
  LIMIT 100
`;

The syntax sql`...` is a tagged template literal. The embedded minimumCapacity is a value parameter. The database receives the query text and the parameter value separately, so it cannot be interpreted as SQL syntax. This prevents SQL injection attacks.

Do not first build a string using user input and then pass that string to an unsafe execution method.

Use the isolated Compose test database

Use the two-file Compose test setup for running tests. Run tests with compose.yaml and compose.test.yaml under a distinct project name such as wsd-practice-test. The practice application’s development and test projects both use the service hostname database and database name walking_skeleton. Their separate Compose networks and the test project’s temporary database storage keep the data separate.

Keep the test override’s network, port, and storage settings. When migrations change, remove the disposable test project so the next test run recreates it and runs the migrations again. From practice/:

docker compose -p wsd-practice-test -f compose.yaml -f compose.test.yaml down

Use the same project name and both Compose files for the reset and the test run. This keeps the reset scoped to the test project. Tests still need their own fixture setup and cleanup: a separate database protects development data, but does not prevent tests from affecting each other’s records. Run database and browser suites sequentially when they share this test project.

Add a room table to the practice application

Keep the existing 001_create_todos.sql. Add database-migrations/002_rooms.sql in the practice application, using the next unused number if you already added a practice migration:

-- migrate:up
CREATE TABLE lab_rooms (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  capacity INTEGER NOT NULL CHECK (capacity BETWEEN 1 AND 500)
);

-- migrate:down
DROP TABLE lab_rooms;

Run the migration through the practice application’s existing Compose migration service in development. The test project applies the same migrations to its own database. The migration establishes the schema; each test supplies its own records. With the development database running, apply it from practice/:

docker compose run --rm database-migrations

Put room queries in a repository

Create api/src/repositories/room-repository.ts. This is the same persistence responsibility as the existing todo repository, now structured in a folder called repositories.

The module imports the shared client; individual query functions do not create or close pools. A simple read route may call a repository directly. We introduce a service when there is a business decision to express:

import { sql } from "../database.ts";

export type Room = { id: number; name: string; capacity: number };

export const insertRoom = async (name: string, capacity: number) => {
  const [room] = await sql<Room[]>`
    INSERT INTO lab_rooms (name, capacity)
    VALUES (${name}, ${capacity})
    RETURNING id, name, capacity
  `;

  return room;
};

export const roomsForGroup = async (minimumCapacity: number) => {
  return await sql<Room[]>`
    SELECT id, name, capacity FROM lab_rooms
    WHERE capacity >= ${minimumCapacity}
    ORDER BY id LIMIT 100
  `;
};

export const findAll = () => roomsForGroup(1);

export const findById = async (id: number): Promise<Room | null> => {
  const rows = await sql<Room[]>`
    SELECT id, name, capacity FROM lab_rooms WHERE id = ${id}
  `;

  return rows[0] ?? null;
};

These are ordinary exported functions. They use the same shared client as the existing todo repository. The result type describes the selected columns to TypeScript; the database still enforces its own constraints.

An awaited query returns an array of rows, even when it selects one ID. const [room] takes the first returned row; this insert returns one because RETURNING follows one successful insertion. A lookup can return no rows, so findById converts rows[0] from undefined to the service’s explicit null result. A collection query returns an empty array when nothing matches.

sql<Room[]> describes selected columns to the type checker; it does not convert or validate database values at runtime. INTEGER columns arrive as JavaScript numbers.

CREATE TABLE lab_equipment (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  stock INTEGER NOT NULL CHECK (stock >= 0)
);

Use the repository in the existing room application

In api/src/routes/room-routes.ts, remove the fixed rooms array and import the repository:

import * as roomRepository from "@src/repositories/room-repository.ts";

Replace the two route handlers with the database-backed versions. Keep the exported app, logging middleware, and unknown-route handler. Also keep schema imports and validateRoomId middleware:

app.get("/api/rooms", async (c) => {
  const rooms = await roomRepository.findAll();
  return c.json(rooms);
});

app.get("/api/rooms/:id", validateRoomId, async (c) => {
  const { id } = c.req.valid("param");
  const room = await roomRepository.findById(id);
  if (!room) {
    return c.json({ error: { code: "NOT_FOUND", message: "Room not found" } }, 404);
  }

  return c.json(room);
});

The request paths and JSON fields stay the same; the records now come from PostgreSQL. To expose these routes in the running practice API, import the example as roomsApp in its main api/src/app.ts and mount it once with app.route('/', roomsApp).

Keep the existing mount if you already added it in Chapter 3.2; do not register it twice.

The room router already includes /api, so its collection is available at /api/rooms both here and in direct application tests. The separate example instance is also what the following test imports.

Test the application against persisted rows

A fixture is the starting state prepared for a test. Here, it is one inserted room; the migration has already created the table. The test uses that room to check that a request reads the persisted values.

Replace api/tests/room-routes_test.ts with this focused database test:

import { assertEquals } from "@std/assert";
import { app } from "@src/routes/room-routes.ts";
import { sql } from "@src/database.ts";

Deno.test("a room request reads a persisted record", async () => {
  let id: number | undefined;
  try {
    const [room] = await sql`
      INSERT INTO lab_rooms (name, capacity)
      VALUES (${"Reader's room"}, ${6}) RETURNING id, name, capacity
    `;
    id = room.id;
    const response = await app.request(`/api/rooms/${id}`);
    assertEquals(response.status, 200);
    assertEquals(await response.json(), room);
  } finally {
    try {
      if (id !== undefined) await sql`DELETE FROM lab_rooms WHERE id = ${id}`;
    } finally {
      await sql.end();
    }
  }
});

The test owns one inserted row and the imported pool. It uses the returned ID and cleans up even when an assertion fails. The nested finally closes the pool even if deleting the row fails.

Run this from practice/ with the isolated Compose test project:

docker compose -p wsd-practice-test -f compose.yaml -f compose.test.yaml run --build --rm api deno test --cached-only --allow-env --allow-net tests/room-routes_test.ts

From this chapter onward, the room app imports the database repository, so use the above database-enabled command instead of the no-dependency command from earlier. The test removes its own row: a test database is separate from development, but records still need isolation between tests and suites.

The request passes through the real room route and repository. Changing that route incorrectly can now make this test fail; no route implementation is copied into the test.

Deno.test("an equipment request reads a persisted record", async () => {
  await withEquipmentFixture(async (app, equipment) => {
    await assertEquipmentResponse(app, equipment);
  });
});

Check Your Understanding

1. Why is a tagged parameterized query different from building a raw SQL string?
2. Who should own and close a database client in a test?
3. Why should a database integration test use IDs returned by its fixture inserts instead of assuming fixed IDs?
4. How do migrations and fixtures serve different purposes?
5. Why does a separate test database not automatically make every test independent?