Part 4.5 — Database Reset, Transactions & Running Tests in CI

Flaky tests destroy trust.
In backend systems, flakiness almost always comes from shared database state, leaky transactions, or misconfigured CI environments.

In this post, you’ll harden your testing setup so that:

• every test starts from a clean database state

• parallel tests don’t collide

• Prisma behaves predictably

• CI runs mirror local behavior

• failures are reproducible

This is the difference between “tests that sometimes pass” and “tests you rely on”.

1. Why Database Isolation Matters

Consider two tests:

• Test A creates a user

• Test B assumes the database is empty

If Test A runs first, Test B fails.
If the order flips, everything passes.

That’s not testing — that’s luck.

Isolation guarantees:

• order doesn’t matter

• tests can run in parallel

• CI behaves like local runs

2. Strategy 1: Full Database Reset (Simple & Safe)

For small to medium projects, the simplest strategy wins.

You already created a helper:

export async function resetDb() {
  await prisma.post.deleteMany();
  await prisma.user.deleteMany();
}

Use it consistently:

beforeEach(async () => {
  await resetDb();
});

Pros:

• easy to understand

• no hidden state

• works everywhere

Cons:

• slower as schema grows

For most teams, this is good enough.

3. Order Matters When Deleting Relational Data

With relations:

User → Post

You must delete children first:

await prisma.post.deleteMany();
await prisma.user.deleteMany();

If you reverse the order, PostgreSQL will throw a foreign key error.

Be explicit and deterministic.

4. Strategy 2: Transaction-Based Test Isolation (Advanced)

For larger test suites, full resets get slow.
Transactions are faster.

Core idea

• Start a DB transaction

• Run the test inside it

• Roll it back afterward

No data ever persists.

5. Implement Transactional Tests with Prisma

Create a helper:

// test/utils/transaction.ts
import { PrismaClient } from '@prisma/client';

export async function withTransaction<T>(
  prisma: PrismaClient,
  fn: (tx: PrismaClient) => Promise<T>,
) {
  return prisma.$transaction(async (tx) => {
    return fn(tx as PrismaClient);
  });
}

Use it in tests:

it('creates a user inside a transaction', async () => {
  await withTransaction(prisma, async (tx) => {
    const user = await tx.user.create({
      data: { name: 'Alice', email: 'a@example.com' },
    });

    expect(user.id).toBeDefined();
  });
});

Once the test completes, Prisma rolls back automatically.

6. When Transactions Do NOT Work

Transactions do not isolate:

• HTTP requests via Supertest

• multiple Prisma clients

• async work outside the transaction scope

That means:

• use transactions for unit tests

• use resets for integration tests

This split is intentional and practical.

7. Choosing the Right Strategy

Be explicit. Don’t mix strategies randomly.

8. Preparing CI for Database Tests

Your CI must provide:

• PostgreSQL

• environment variables

• Prisma migrations

• deterministic execution order

GitHub Actions example:

name: Backend Tests

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: password
          POSTGRES_DB: fullstack_test_db
        ports:
          - 5432:5432
        options: >-
          --health-cmd="pg_isready"
          --health-interval=10s
          --health-timeout=5s
          --health-retries=5

    steps:
      - uses: actions/checkout@v4

      - uses: pnpm/action-setup@v4
        with:
          version: 8

      - uses: actions/setup-node@v4
        with:
          node-version: 20
          cache: 'pnpm'

      - run: pnpm install

      - run: pnpm prisma migrate deploy
        env:
          DATABASE_URL: postgresql://postgres:password@localhost:5432/fullstack_test_db

      - run: pnpm test:ci
        env:
          DATABASE_URL: postgresql://postgres:password@localhost:5432/fullstack_test_db
          NODE_ENV: test

This mirrors your local test environment exactly.

9. Run Tests Serially in CI

In CI, prefer:

pnpm test:ci

Which maps to:

"test:ci": "jest --runInBand"

Why?

• avoids connection pool exhaustion

• prevents race conditions

• makes failures deterministic

Speed matters less than reliability in CI.

10. Handling Prisma Connection Pooling

In CI, Prisma can open too many connections.

Best practices:

• use a single PrismaClient instance

• disconnect after tests

• run tests serially

• avoid parallel test workers

Always close Prisma:

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

This prevents hanging CI jobs.

11. Test Data Factories (Cleaner Tests)

Instead of repeating setup code:

await prisma.user.create({ ... });

Create factories:

export async function createUser(prisma, overrides = {}) {
  return prisma.user.create({
    data: {
      name: 'Test User',
      email: `user${Date.now()}@test.com`,
      ...overrides,
    },
  });
}

Cleaner tests, less duplication.

12. Common CI Failure Causes

If CI fails but local passes, check:

• missing DATABASE_URL

• migrations not applied

• wrong Node version

• Prisma client not generated

• tests running in parallel

• ports mismatched

Most CI bugs are environment bugs.

13. Summary

You now know how to:

• isolate PostgreSQL state across tests

• reset databases safely

• use transactions where appropriate

• choose the right isolation strategy

• configure GitHub Actions for backend tests

• run Prisma migrations in CI

• prevent flaky tests and hanging jobs