Testing Apps with Database Access

The Pattern: Shared Identity for Parallel Test Isolation

When testing applications that access multiple data sources (HTTP APIs + databases), the key insight is simple:

Use a single identifier to partition ALL data sources.

┌─────────────────────────────────────────────────────────┐
│                    Test Runner                          │
├─────────────────────────────────────────────────────────┤
│  Test A (x-scenarist-test-id: abc-123)                            │
│     │                                                   │
│     ├─→ HTTP API ──→ Scenarist ──→ Mocks for "abc-123"  │
│     │                                                   │
│     └─→ Database ──→ Your Code ──→ Data for "abc-123"   │
├─────────────────────────────────────────────────────────┤
│  Test B (x-scenarist-test-id: xyz-789)                            │
│     │                                                   │
│     ├─→ HTTP API ──→ Scenarist ──→ Mocks for "xyz-789"  │
│     │                                                   │
│     └─→ Database ──→ Your Code ──→ Data for "xyz-789"   │
└─────────────────────────────────────────────────────────┘

Both tests run in PARALLEL with completely isolated state.

This is the same pattern used in distributed systems for request tracing, multi-tenancy, and session management. The test ID is just another form of correlation ID.

---

What Scenarist Provides vs. What You Implement

Clear Separation of Concerns

Scenarist handles HTTP isolation — it extracts the test ID from request headers and returns scenario-specific mock responses. This is built-in. You get it for free.

You handle database isolation — you implement the mechanism to extract the test ID and partition database queries accordingly. Scenarist doesn’t touch databases.

The connection point is the x-scenarist-test-id header. Both systems read the same header and use it as their partition key.

Responsibility	Tool	What It Does
HTTP API mocking	Scenarist	Intercepts fetch calls, returns mocks based on test ID
Database state isolation	Your code	Partitions database queries/data based on test ID

Scenarist scripts

• Third-party HTTP APIs: Stripe, Auth0, SendGrid
• Other services your server calls over HTTP
• Any outgoing HTTP request from your server code

Scenarist leaves alone

• Database queries: Prisma, Drizzle, raw SQL
• Your own API routes and Server Actions: they’re your code, so let them run for real
• File system operations: fs.readFile and similar

---

Implementing Database Isolation

There are multiple valid approaches to partition database state by test ID. Each has different trade-offs:

Approach A: Repository Pattern with In-Memory Store

Abstract database access behind interfaces, inject test implementations that partition data by test ID.

Best for: Fast parallel tests, clean architecture, any ORM

Trade-offs: Requires refactoring database access; doesn’t test real SQL

Detailed Guide →

Approach B: Test Fixtures with Direct Seeding

Seed database directly in test setup, using test ID to isolate data.

test('premium user sees discounts', async ({ page }) => {
  const testId = generateTestId();

  // Seed directly (if tests have DB access)
  await db.users.create({
    testId,
    tier: 'premium',
    // ...
  });

  // Or seed via HTTP endpoint
  await page.request.post('/test/seed', {
    headers: { 'x-scenarist-test-id': testId },
    data: { scenarioId: 'premiumUser' }
  });

  await page.goto('/products');
  // ...
});

Best for: When tests have direct database access (e.g., same process)

Trade-offs: Tests coupled to database schema; requires cleanup logic

Approach C: Row-Level Security or Test ID Columns

Add test_id column to tables, filter all queries automatically.

-- PostgreSQL RLS policy
CREATE POLICY test_isolation ON users
  USING (test_id = current_setting('app.test_id'));

Best for: When you can’t change application code

Trade-offs: Schema changes in production; RLS overhead

Approach D: Database Snapshots/Migrations

Pre-seed database with known state, reset between tests.

Best for: Testing specific SQL behavior; integration tests

Trade-offs: Slower; harder to parallelize

---

Our Recommendation: Repository Pattern

For teams that want scalable parallel testing of real server-side code, we recommend the repository pattern because:

1. Same isolation model as Scenarist — Test ID partitions both HTTP and database
2. Fast execution — In-memory stores are orders of magnitude faster than real databases
3. Clean architecture — Benefits extend beyond testing (infrastructure flexibility, SOLID principles)
4. ORM agnostic — Works with Prisma, Drizzle, TypeORM, raw SQL

Full Repository Pattern Guide →

The Pattern vs. The Implementation

The pattern is “use test ID to partition database state.”

The repository pattern is one way to implement that pattern. It’s the approach we show in our examples because it provides the cleanest architecture, but it’s not the only way.

If your constraints don’t allow refactoring to repositories, consider the other approaches above.

---

Why Database Testing Is Different

Scenarist intercepts HTTP requests via MSW because every HTTP library eventually calls the same underlying APIs. Databases have no equivalent universal interception point.

Each ORM and driver is a different API surface:

• Prisma: prisma.user.findMany()
• Drizzle: db.select().from(users)
• TypeORM: userRepository.find()
• Raw SQL: pg.query('SELECT * FROM users')

// ❌ Scenarist CANNOT mock this - no HTTP request
export async function fetchProducts() {
  return await db.products.findMany();  // Direct database call
}

That’s why you implement database isolation, using whatever approach fits your architecture. The examples show one way; adapt it to your needs.

---

Quick Decision Guide

Your Situation	Approach
App only calls external HTTP APIs (no database)	Scenarist alone; no database strategy needed
Want scalable parallel tests + clean architecture	Repository Pattern
Tests have direct database access	Test fixtures with direct seeding
Can’t change application code	Row-level security or test ID columns
Need to test actual SQL queries, constraints	Testcontainers
Small test suite, prototyping	Sequential Execution

---

Next Steps

• Repository Pattern Guide — Full implementation walkthrough
• Testcontainers Hybrid — Testing real database behavior
• All Parallelism Options — Detailed comparison of approaches