API and database integration tests

Why this matters

Yesterday's unit tests proved your rules are correct in isolation. They cannot tell you whether the route calls the right rule, whether the SQL column is spelled correctly, or whether the unique constraint you wrote on Day 44 actually fires. Those live at a seam between real components, and only a test that uses the real components can prove them. Today you write tests that send real HTTP requests to your Express app and let them hit a real PostgreSQL database — with data that is wiped clean before every test, so the tests can be run in any order, any number of times.

What integration tests are for

An integration test exercises several real parts together. For your API that means: a real request goes into your Express app, through your middleware, your route, your service, your repository, into PostgreSQL, and back out as a real HTTP response.

You are not re-testing the rules from yesterday. You are testing the things that only exist when the parts are joined:

• The route is registered at the path you think it is, with the method you think it is.
• Authentication middleware actually runs before the handler.
• The SQL matches the schema — right table, right column names, right types.
• Database constraints turn into the HTTP status codes your frontend expects.
• Data written by one request is genuinely still there for the next one.

The test database

The single most important rule today: integration tests must never point at a database with real data in them, because they delete data as part of running. You need a separate database used only by tests.

Create it once. On macOS/Linux with PostgreSQL installed locally:

createdb maintenance_test

Then give your tests their own connection string in a file named .env.test:

DATABASE_URL=postgresql://localhost:5432/maintenance_test
NODE_ENV=test

.env.test holds a local development URL, but it belongs in .gitignore all the same — the habit of never committing an environment file is what stops a production URL leaking later in the week. Run your Day 48 migrations against it so the schema matches production.

Cleaning up between tests

Test isolation means each test starts from a known, empty world and leaves nothing behind. This is what makes tests repeatable and independent — repeatable because run number two sees exactly what run number one saw, and independent because a test cannot be affected by what another test did, or by the order they happened to run in.

Without isolation you get the worst kind of failure: a suite that is green when run whole and red when you run one file, or vice versa. Chasing that costs hours.

Two standard approaches:

• Truncate between tests. Before each test, empty the tables. Simple, obvious, and works no matter how many connections the code uses. Slightly slower.
• Transaction cleanup. Open a database transaction before the test, run everything inside it, and ROLLBACK afterwards so nothing is ever committed. Faster and very clean, but it requires every query in the test to use that same connection — which is fiddly if your app grabs its own connection from a pool.

Start with truncation. It is harder to get subtly wrong, and correctness beats milliseconds here.

HTTP assertions

An HTTP assertion claims something about a real response: its status code, its body, or its headers. The library supertest sends a request straight into your Express app object without starting a network server, which makes it fast and free of port conflicts.

This requires that your app is exported separately from the code that starts it listening — app.js exports the configured Express app, server.js imports it and calls app.listen. If yours calls listen in the same file, split it now; it takes two minutes and is a prerequisite for today.

Assert on the contract, which is what your frontend depends on: the status code, and the fields of the body that callers read. Do not assert on the entire body object — a new field added later should not fail an unrelated test.

Walkthrough: four tests against real components

npm install -D supertest dotenv-cli

dotenv-cli gives you a dotenv command that loads a named environment file and then runs whatever you put after --. That is how the test suite gets .env.test instead of your normal .env.

Create tests/equipment.integration.test.js. The first line switches this file to the Node environment, because yesterday's config set jsdom globally for component tests:

// @vitest-environment node
import { describe, it, expect, beforeAll, beforeEach, afterAll } from "vitest";
import request from "supertest";
import app from "../src/app.js";
import { pool } from "../src/db.js";

let token;

beforeAll(async () => {
  if (!process.env.DATABASE_URL?.endsWith("_test")) {
    throw new Error("Refusing to run: DATABASE_URL is not a _test database");
  }
});

beforeEach(async () => {
  await pool.query("TRUNCATE equipment, maintenance_records, users RESTART IDENTITY CASCADE");
  await request(app).post("/api/auth/register").send({
    email: "[email protected]",
    password: "correct-horse-battery",
  });
  const login = await request(app).post("/api/auth/login").send({
    email: "[email protected]",
    password: "correct-horse-battery",
  });
  token = login.body.token;
});

afterAll(async () => {
  await pool.end();
});

beforeEach gives every test the same starting world: empty tables, one known user, a fresh token. Now the four behaviours from your Day 71 plan:

describe("POST /api/equipment", () => {
  it("creates equipment and returns 201 with an id", async () => {
    const response = await request(app)
      .post("/api/equipment")
      .set("Authorization", `Bearer ${token}`)
      .send({ name: "Pump 3", serial: "SN-1001" });

    expect(response.status).toBe(201);
    expect(response.body.id).toBeDefined();
    expect(response.body.name).toBe("Pump 3");
  });

  it("rejects a duplicate serial with 409", async () => {
    const payload = { name: "Pump 3", serial: "SN-1001" };
    await request(app).post("/api/equipment").set("Authorization", `Bearer ${token}`).send(payload);

    const second = await request(app)
      .post("/api/equipment")
      .set("Authorization", `Bearer ${token}`)
      .send({ ...payload, name: "Pump 3 spare" });

    expect(second.status).toBe(409);
  });

  it("rejects an unauthenticated request with 401", async () => {
    const response = await request(app).post("/api/equipment").send({ name: "Pump 3" });
    expect(response.status).toBe(401);
  });

  it("persists the row in the database", async () => {
    await request(app)
      .post("/api/equipment")
      .set("Authorization", `Bearer ${token}`)
      .send({ name: "Pump 3", serial: "SN-1001" });

    const { rows } = await pool.query("SELECT name FROM equipment WHERE serial = $1", ["SN-1001"]);
    expect(rows).toHaveLength(1);
    expect(rows[0].name).toBe("Pump 3");
  });
});

Run them, loading the test environment file:

npx dotenv -e .env.test -- npx vitest run tests/equipment.integration.test.js

Put that behind a script so nobody ever runs it against the wrong database by accident:

"scripts": {
  "test:integration": "dotenv -e .env.test -- vitest run tests/*.integration.test.js"
}

If the duplicate-serial route is missing its error handling, the failure is unmistakable:

 FAIL  tests/equipment.integration.test.js > POST /api/equipment > rejects a duplicate serial with 409
AssertionError: expected 500 to be 409 // Object.is equality

- Expected
+ Received

- 409
+ 500

A 500 means PostgreSQL raised unique-violation error 23505 and nobody caught it. That is exactly the class of bug a unit test with a fake database can never find.

Your turn

1. Create maintenance_test, write .env.test, add it to .gitignore, and run your migrations against it. Confirm with psql maintenance_test -c "\dt" that the tables exist.
2. Split app.js from server.js if you have not already, and add the _test guard shown above.
3. Write the four tests from the walkthrough against your own routes: created, duplicate conflict, unauthorized, persisted.
4. Add a forbidden case: a second registered user must not be able to modify the first user's equipment. Expect 403 (or 404, if your app hides other users' records — assert whichever your Day 52 design chose, and say why in a comment).
5. Run the file three times in a row, and then run it with --sequence.shuffle so Vitest reorders the tests. Green all four times is your evidence of isolation.
6. Record the run in docs/test-plan.md, marking those rows covered.

Common pitfalls

• Reusing one record across tests. Creating shared fixtures in beforeAll reintroduces the coupling you were avoiding. Create per-test data in beforeEach.
• The process never exits. Vitest hangs after a green run because a database pool is still open. await pool.end() in afterAll fixes it.
• Asserting the whole response body. toEqual on the entire object breaks the day someone adds a createdAt field. Assert the fields the caller reads.
• Truncating a table with a foreign key and no CASCADE. PostgreSQL refuses. Name every table and keep RESTART IDENTITY CASCADE.

Verify it yourself

Open today's reference, the Vitest Getting started guide.

1. Find the difference between beforeAll and beforeEach. Write one sentence on why today's truncation belongs in beforeEach and the safety guard belongs in beforeAll.
2. Find how Vitest decides whether test files run in parallel. Then say what would happen if two files truncated the same test database at the same moment — and what the documented option is for preventing it.