Integration Testing with Claude Code: API Tests and DB Testing Automation

#claudecode #testing #node #typescript

Why Unit Tests Aren't Enough

Unit tests can pass perfectly while integration bugs hide in plain sight.

A common failure pattern:

UserService.createUser() unit tests: all green
POST /api/users handler unit tests: all green
But sending an actual request returns 500 instead of 400 for validation errors
Root cause: the middleware error handler and service layer exception types don't match

Unit tests isolate with mocks, so they miss bugs at the "seams" between modules. Integration tests verify the whole system using real HTTP requests and a real database.

When you ask Claude Code to generate integration tests without explicit rules in CLAUDE.md, it tends to produce mock-heavy tests that aren't real integration tests at all.

Step 1: Write Integration Test Rules in CLAUDE.md

Add this to your project's CLAUDE.md:

## Integration Test Rules

### Frameworks
- HTTP testing: supertest + vitest
- DB access: Prisma (real DB required, no mocks)
- Test DB: Docker (docker-compose.test.yml)

### Test File Naming
- Unit tests: `*.unit.test.ts` — mocks allowed
- Integration tests: `*.integration.test.ts` — real DB + real HTTP required

### Rules
1. `beforeAll`: connect to DB. `afterAll`: disconnect.
2. `beforeEach`: reset DB to clean state.
3. Test data must be created with seeding functions (no inline SQL or hardcoded IDs).
4. HTTP tests use supertest's `request(app)` (no actual server startup).
5. Validate: HTTP status code + response body + DB state.

### Test DB Setup
- Env var: DATABASE_URL_TEST
- Start: `docker-compose -f docker-compose.test.yml up -d`
- Migrate: `DATABASE_URL=$DATABASE_URL_TEST npx prisma migrate deploy`

With this in CLAUDE.md, Claude Code reads and follows these rules automatically when generating integration tests.

Step 2: Generate API Endpoint Integration Tests

Prompt

Following the integration test rules in CLAUDE.md, generate integration tests
for the following API endpoint.

Target: POST /api/users (user creation)
Spec:
- Validation: email required + format check, name required + max 50 chars
- Success: 201 + created user object (passwordHash excluded)
- Validation error: 400 + error details
- Duplicate email: 409 + error message

Stack: supertest, vitest, Prisma
File: src/__tests__/users.integration.test.ts

Generated Integration Test

// src/__tests__/users.integration.test.ts
import { describe, it, expect, beforeAll, afterAll, beforeEach } from "vitest";
import request from "supertest";
import { app } from "../../app";
import { prisma } from "../../lib/prisma";

beforeAll(async () => {
  await prisma.$connect();
});

afterAll(async () => {
  await prisma.$disconnect();
});

beforeEach(async () => {
  // Reset DB between tests (delete in FK order)
  await prisma.post.deleteMany();
  await prisma.user.deleteMany();
});

describe("POST /api/users", () => {
  it("creates a valid user successfully", async () => {
    const res = await request(app)
      .post("/api/users")
      .send({ name: "Myouga", email: "myouga@example.com" })
      .expect(201);

    expect(res.body).toMatchObject({
      id: expect.any(String),
      name: "Myouga",
      email: "myouga@example.com",
    });
    // Confirm passwordHash is not exposed
    expect(res.body.passwordHash).toBeUndefined();

    // Verify the user was actually persisted in DB
    const user = await prisma.user.findUnique({
      where: { email: "myouga@example.com" },
    });
    expect(user).not.toBeNull();
    expect(user!.name).toBe("Myouga");
  });

  it("returns 400 for invalid email format", async () => {
    const res = await request(app)
      .post("/api/users")
      .send({ name: "Myouga", email: "not-an-email" })
      .expect(400);

    expect(res.body.error).toContain("email");
  });

  it("returns 409 for duplicate email", async () => {
    await prisma.user.create({
      data: { name: "Existing User", email: "myouga@example.com" },
    });

    const res = await request(app)
      .post("/api/users")
      .send({ name: "Myouga", email: "myouga@example.com" })
      .expect(409);

    expect(res.body.error).toContain("already exists");
  });

  it("returns 400 when name exceeds 50 characters", async () => {
    await request(app)
      .post("/api/users")
      .send({ name: "a".repeat(51), email: "myouga@example.com" })
      .expect(400);
  });
});

supertest takes the app object and sends HTTP requests without actually starting a server — no port conflicts.

Step 3: Generate Test Data Seeding Functions

Sharing test data between tests causes flaky tests that depend on execution order. Use seeding functions to manage test data explicitly.

Prompt

Following the integration test rules in CLAUDE.md, generate test seeding
functions.

Requirements:
- File: src/__tests__/helpers/seed.ts
- Functions callable from any test
- Use Prisma to insert test data
- Return the inserted record(s) including IDs

Functions needed:
1. seedUser(overrides?) — create one user
2. seedUserWithPosts(postCount) — create user + N posts
3. seedAdminUser() — create an admin user

Generated Seeding Functions

// src/__tests__/helpers/seed.ts
import { prisma } from "../../lib/prisma";
import type { User, Post } from "@prisma/client";

let userCounter = 0;

export async function seedUser(
  overrides: Partial<{ name: string; email: string; role: string }> = {}
): Promise<User> {
  userCounter++;
  return prisma.user.create({
    data: {
      name: overrides.name ?? `Test User ${userCounter}`,
      email: overrides.email ?? `test${userCounter}@example.com`,
      role: overrides.role ?? "user",
    },
  });
}

export async function seedUserWithPosts(
  postCount: number
): Promise<{ user: User; posts: Post[] }> {
  const user = await seedUser();
  const posts = await Promise.all(
    Array.from({ length: postCount }, (_, i) =>
      prisma.post.create({
        data: {
          title: `Test Post ${i + 1}`,
          content: `Content ${i + 1}`,
          authorId: user.id,
          published: true,
        },
      })
    )
  );
  return { user, posts };
}

export async function seedAdminUser(): Promise<User> {
  return seedUser({ name: "Admin", email: "admin@example.com", role: "admin" });
}

Using Seeding Functions in Tests

describe("GET /api/users/:id", () => {
  it("retrieves an existing user", async () => {
    const user = await seedUser({ name: "Myouga" });

    const res = await request(app)
      .get(`/api/users/${user.id}`)
      .expect(200);

    expect(res.body.name).toBe("Myouga");
  });

  it("returns user with post count when include=posts", async () => {
    const { user } = await seedUserWithPosts(3);

    const res = await request(app)
      .get(`/api/users/${user.id}?include=posts`)
      .expect(200);

    expect(res.body.posts).toHaveLength(3);
  });
});

Step 4: Generate DB Transaction Tests

Transaction and rollback logic can't be accurately verified with unit tests. You need to hit the real DB.

Prompt

Following the integration test rules in CLAUDE.md, generate integration tests
for the service layer.

Target: UserService.transferPosts(fromUserId, toUserId)
Spec:
- Transfers all posts from fromUser to toUser
- If fromUser doesn't exist: throw NotFoundError + rollback
- If toUser doesn't exist: throw NotFoundError + rollback
- All-or-nothing transfer (transaction)

Test cases:
1. Successful transfer: verify DB state directly
2. fromUser missing: verify DB unchanged after error
3. After transfer: verify fromUser has 0 posts

Generated DB Transaction Test

// src/__tests__/user-service.integration.test.ts
import { describe, it, expect, beforeAll, afterAll, beforeEach } from "vitest";
import { UserService } from "../../services/UserService";
import { prisma } from "../../lib/prisma";
import { NotFoundError } from "../../errors";
import { seedUser, seedUserWithPosts } from "./helpers/seed";

const userService = new UserService(prisma);

beforeAll(async () => { await prisma.$connect(); });
afterAll(async () => { await prisma.$disconnect(); });
beforeEach(async () => {
  await prisma.post.deleteMany();
  await prisma.user.deleteMany();
});

describe("UserService.transferPosts", () => {
  it("transfers all posts to the target user", async () => {
    const { user: from } = await seedUserWithPosts(3);
    const to = await seedUser();

    await userService.transferPosts(from.id, to.id);

    // Verify DB directly — don't trust the return value alone
    const fromPostCount = await prisma.post.count({
      where: { authorId: from.id },
    });
    expect(fromPostCount).toBe(0);

    const toPostCount = await prisma.post.count({
      where: { authorId: to.id },
    });
    expect(toPostCount).toBe(3);
  });

  it("rolls back when fromUser does not exist", async () => {
    const to = await seedUser();
    const nonExistentId = "00000000-0000-0000-0000-000000000000";

    await expect(
      userService.transferPosts(nonExistentId, to.id)
    ).rejects.toThrow(NotFoundError);

    // Verify toUser's post count is still 0 (rollback confirmed)
    const toPostCount = await prisma.post.count({
      where: { authorId: to.id },
    });
    expect(toPostCount).toBe(0);
  });

  it("does not transfer when toUser does not exist", async () => {
    const { user: from } = await seedUserWithPosts(2);
    const nonExistentId = "00000000-0000-0000-0000-000000000000";

    await expect(
      userService.transferPosts(from.id, nonExistentId)
    ).rejects.toThrow(NotFoundError);

    // Verify fromUser's posts are unchanged
    const fromPostCount = await prisma.post.count({
      where: { authorId: from.id },
    });
    expect(fromPostCount).toBe(2);
  });
});

The key pattern: after an error, query the DB directly to confirm state is unchanged. This is the only way to verify that rollback actually worked.

Summary

Three principles for generating high-quality integration tests with Claude Code:

Principle	Practice
Explicit rules in CLAUDE.md	Specify real DB required, no mocks, cleanup method
Seeding functions for isolation	`beforeEach` reset + create data per test
Verify DB state, not just HTTP	Query Prisma directly after each operation

Writing these three rules in CLAUDE.md significantly improves the quality of integration tests Claude Code generates — without needing to repeat them in every prompt.

Want to automatically detect missing test coverage and edge cases? The Code Review Pack (Claude Code custom skill) runs /code-review to report test coverage gaps, missing boundary conditions, and untested error paths across 5 axes.

Available at prompt-works.jp