Backend and API testing

How to generate database state setup and teardown scripts with AI?

Database state management is one of the most painful parts of backend testing at scale. Tests that depend on real data are brittle, hard to parallelize, slow to run, and nearly impossible to make deterministic. The standard solution — fixture files and seed scripts — requires significant maintenance as schemas evolve. AI dramatically accelerates the creation, maintenance, and extension of database test fixtures.

The core challenge: fixtures rot

Schema changes that aren't reflected in fixtures silently corrupt tests. A column added in production without a corresponding fixture update causes tests to fail with confusing errors — or worse, pass incorrectly against stale data. The problem compounds with foreign keys, cascades, and multi-tenant data models.

AI can regenerate fixtures from the current schema on demand, eliminating fixture drift.

Generating seed data from schema definitions

Give AI your schema and ask for realistic, boundary-aware seed data:

I'm building test fixtures for our PostgreSQL database. Generate INSERT statements 
that create a realistic test dataset for these tables. Requirements:
- Include at least 3 records per table
- Cover boundary conditions (max-length strings, null optionals, minimum values)
- Ensure foreign key relationships are valid and consistent
- Include at least one "edge case" record per table (e.g., user with no orders, 
  product with zero inventory, expired subscription)
- Use realistic-looking data (not "test1", "test2" — proper names, emails, dates)

Schema:
---
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  display_name VARCHAR(100),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ  -- soft delete
);

CREATE TABLE subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  plan ENUM('free', 'pro', 'enterprise') NOT NULL,
  status ENUM('active', 'cancelled', 'past_due', 'trialing') NOT NULL,
  current_period_end TIMESTAMPTZ NOT NULL,
  cancelled_at TIMESTAMPTZ
);

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id),
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
  status ENUM('pending', 'paid', 'refunded', 'failed') NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
---

Generating language-native fixture helpers

For teams using test frameworks that prefer fixtures in code (factories in TypeScript, pytest fixtures in Python, FactoryBot in Ruby), AI can generate the factory definitions from your schema or ORM models:

Generate TypeScript factory functions for our test suite using the 
@faker-js/faker library. Create factories for the User, Subscription, and Order 
types. Each factory should:
- Accept a partial override object (Partial<T>)
- Use faker to generate realistic defaults for every field
- Include a createWithRelations() variant that creates the full dependency chain
- Use our Prisma client for database persistence (include both build() and create() methods)

TypeScript types from our Prisma schema:
---
type User = {
  id: string;
  email: string;
  displayName: string | null;
  createdAt: Date;
  deletedAt: Date | null;
}

type Subscription = {
  id: string;
  userId: string;
  plan: 'free' | 'pro' | 'enterprise';
  status: 'active' | 'cancelled' | 'past_due' | 'trialing';
  currentPeriodEnd: Date;
  cancelledAt: Date | null;
}
---

Generating teardown scripts

Teardown is as important as setup — incomplete teardown causes test pollution between runs:

Generate a teardown utility for our Jest test suite that:
1. Deletes all records created during the test run in reverse dependency order
   (orders → subscriptions → users, respecting FK constraints)
2. Uses transaction rollback where possible for speed
3. Falls back to DELETE statements with proper ordering if transactions aren't supported
4. Resets auto-increment sequences (PostgreSQL: ALTER SEQUENCE ... RESTART)
5. Is safe to run multiple times (idempotent)
6. Logs what was deleted to help debug test pollution issues

Database: PostgreSQL via Prisma
Test framework: Jest with --runInBand (sequential) and --maxWorkers=4 (parallel) modes

Managing multi-schema and multi-tenant test state

For multi-tenant applications, fixture management becomes exponentially complex. Use AI to generate tenant-aware fixtures:

Our application is multi-tenant with schema-per-tenant isolation in PostgreSQL.
Generate a test fixture utility that:
1. Creates a fresh tenant schema for each test suite (schema name: test_tenant_{uuid})
2. Runs migrations on the tenant schema (using our Knex migrate:latest)
3. Seeds the tenant schema with baseline data from our fixtures/baseline.sql
4. Provides a cleanup() function that drops the tenant schema after the test suite
5. Works in parallel (multiple test suites each get their own isolated schema)

The utility should be usable as a Jest globalSetup/globalTeardown module.

Learning Tip: When generating seed data with AI, always review it for hidden biases. AI-generated data can cluster around common patterns (e.g., generating only common English names, or generating dates clustered around recent months). Explicitly request data that represents your actual user distribution — internationalized names, non-US date formats, edge-case numeric values — to avoid fixtures that only test the happy path of your happy-path users.

How to use AI for schema validation and data integrity testing?

Schema validation and data integrity testing sit below the API surface — they verify that what's actually stored in your database matches your application's invariants. These tests are often absent or thin because writing them manually is tedious. AI makes comprehensive data integrity testing practical.

What to test in data integrity testing

Data integrity has several distinct layers:

Generating schema constraint tests

Generate a pytest test suite that validates our PostgreSQL schema constraints 
are properly enforced. For each constraint type, test both the passing case and 
the violation case.

Cover:
1. NOT NULL constraints — attempt INSERT with NULL for each NOT NULL column
2. UNIQUE constraints — attempt INSERT of duplicate values
3. CHECK constraints — attempt INSERT of values that violate each CHECK expression
4. Foreign key constraints — attempt INSERT with non-existent FK reference; 
   test CASCADE DELETE behavior
5. ENUM constraints — attempt INSERT of invalid ENUM values

For constraint violations, assert that:
- The database raises the correct error code (e.g., 23502 NOT NULL, 23505 UNIQUE)
- The error message contains the constraint name
- The transaction is correctly rolled back

Schema:
---
[paste CREATE TABLE statements]
---

Generating data integrity queries for production audits

AI excels at generating SQL audit queries that verify business invariants across your data:

Generate SQL audit queries for our e-commerce database that detect data 
integrity violations. For each query:
- Query name and description
- The SQL (runnable against PostgreSQL)
- What a non-zero result means
- Severity: Critical (data corruption) / Warning (potential issue) / Info (anomaly)

Business invariants to check:
1. Users with active subscriptions should never have deleted_at set
2. Orders with status='paid' must have payment_id set (not null)
3. Order total_cents must equal the sum of order_line_items.unit_price_cents * quantity
4. No order can have an older updated_at than its line items
5. Subscription current_period_end must be in the future for status='active'
6. Refunded orders must have refund_amount_cents ≤ original total_cents
7. No two active subscriptions for the same user_id

Schema context:
---
[paste schema]
---

AI-assisted migration validation

When running database migrations, data integrity can be violated if the migration logic is wrong. Use AI to generate pre-and-post migration validation:

I'm running a database migration that:
1. Adds a new column: orders.currency VARCHAR(3) DEFAULT 'USD'
2. Backfills currency from the users.preferred_currency column
3. Adds a NOT NULL constraint after backfill

Generate:
1. A pre-migration validation query that identifies any users without preferred_currency 
   (which would cause the backfill to leave NULLs)
2. A post-migration validation query that confirms:
   a. All orders have currency set
   b. currency values are valid ISO 4217 codes (3 uppercase letters)
   c. Row counts match (no data lost)
   d. The backfill correctly matched user → order for all records
3. A rollback script that safely removes the column if validation fails

Generating API response validation schemas

For APIs returning complex nested JSON, AI can generate comprehensive JSON Schema validators:

Generate a JSON Schema for this API response that:
1. Makes all documented fields required (use "required" array)
2. Adds format validation for all string fields (email, uuid, date-time, uri)
3. Adds range validation for all numeric fields (using minimum/maximum)
4. Validates array items recursively
5. Uses additionalProperties: false to catch undocumented fields

API response (actual example):
---
{
  "user": {
    "id": "550e8400-e29b-41d4-a716-446655440000",
    "email": "[email protected]",
    "createdAt": "2024-01-15T10:30:00Z",
    "subscription": {
      "plan": "pro",
      "status": "active",
      "currentPeriodEnd": "2025-01-15T10:30:00Z"
    }
  },
  "orders": [
    {
      "id": "660e8400-e29b-41d4-a716-446655440001",
      "totalCents": 4999,
      "currency": "USD",
      "status": "paid",
      "createdAt": "2024-01-10T08:00:00Z"
    }
  ]
}
---

Learning Tip: Data integrity tests are highest value when run against a production replica (or anonymized snapshot) rather than just against your test fixtures. Fixture data is clean by design — it was created with your rules in mind. Production data accumulates historical violations, migration artifacts, and edge cases no one anticipated. Run your AI-generated integrity queries against both environments: fixture failures catch regressions, production failures catch real issues.

How does AI-assisted consumer-driven contract testing work?

Contract testing solves a specific and expensive problem: in microservices and distributed systems, services are tested in isolation, but integration failures appear at the boundaries — when service A calls service B and their assumptions diverge. Consumer-driven contract testing (CDCT) makes those assumptions explicit and testable.

The contract testing model

In CDCT:
- Consumer: The service that calls the API (has expectations about the response)
- Provider: The service that serves the API (has obligations to fulfill)
- A contract is the formal record of what the consumer expects

The consumer writes tests that define their expectations (the contract). The provider runs the consumer's contract against their actual service to verify compliance. The contract lives in a shared registry (Pact Broker, PactFlow) accessible to both teams.

AI's role: Generating contract definitions, identifying contract drift, and generating the provider verification tests that are most commonly neglected.

Generating consumer contracts with AI

I'm building a Pact contract test for our OrderService (consumer) that calls the 
UserService API to fetch user subscription status. The consumer needs this data 
to decide if the user is eligible for a discount.

Generate a complete Pact consumer contract test in TypeScript that:
1. Defines the interaction: GET /users/{userId}/subscription
2. Specifies the minimum required response fields (not the full response — 
   only what OrderService actually uses):
   - subscription.status (string, one of: active, cancelled, past_due, trialing)
   - subscription.plan (string, one of: free, pro, enterprise)
3. Uses Pact matchers appropriately (exact vs. type vs. regex matching)
4. Covers these scenarios:
   - Active pro subscriber (happy path)
   - Cancelled subscription (should not receive discount)
   - User not found (404 response)
5. Generates the pact file to ./pacts/
6. Includes proper Jest lifecycle hooks (pact setup, teardown)

Consumer: OrderService (Node.js)
Pact library: @pact-foundation/pact v12

Generating provider verification tests

Provider-side contract verification is often where contract testing breaks down — teams generate contracts but don't write the provider verification scaffolding. AI makes this straightforward:

Generate a Pact provider verification test for UserService that:
1. Reads consumer contracts from our PactFlow broker (URL and credentials via environment variables)
2. Starts a test server for UserService using our Express app
3. Defines provider states matching those used in consumer contracts:
   - "user with active pro subscription exists" → seed user + subscription fixtures
   - "user with cancelled subscription exists" → seed cancelled subscription fixture  
   - "user does not exist" → ensure no user in DB with the test ID
4. Runs all consumer contracts against the live service
5. Tags the verification result with the current git branch (for PactFlow compatibility matrix)

Tech stack: Node.js, Express, Jest, @pact-foundation/pact, our Prisma database client

Identifying contract drift with AI

When an API evolves without updating contracts, drift accumulates silently. Use AI to compare your current API response against existing contracts:

I have an existing Pact contract that specifies what OrderService expects from 
UserService. I also have the current UserService OpenAPI spec. 

Compare these and identify:
1. Fields the contract requires that are missing from the current API spec
2. Fields where the contract expects one type but the API spec returns another
3. Fields in the API response that aren't covered by any contract (potential dead weight)
4. Response status codes in the contract that differ from what the API spec documents
5. Breaking changes: if the API spec changed in a way that would fail the contract

Existing Pact contract:
---
[paste pact JSON]
---

Current OpenAPI spec:
---
[paste relevant OpenAPI spec fragment]
---

Generating contract tests from OpenAPI specs

When starting from an OpenAPI spec rather than an existing contract:

Generate Pact consumer contract tests for a new service integration. 
The consumer is ReportingService and it will call AnalyticsService.

From the OpenAPI spec below, identify which endpoints ReportingService 
would realistically consume and generate consumer contracts for:
1. The 3 most likely-used endpoints
2. Only the response fields a reporting consumer would care about 
   (not every field in the full response)
3. At least 2 scenarios per endpoint (success + at least one error scenario)
4. Correct Pact matcher usage (type matching for dynamic fields like IDs and timestamps, 
   exact matching for enum values and business-critical fields)

OpenAPI spec:
---
[paste OpenAPI spec]
---

Learning Tip: The biggest mistake in contract testing is over-specifying. Consumers that pin exact values for fields they don't use (e.g., userId must equal "abc123") create brittle contracts that break without any real integration risk. AI has a tendency to be over-precise when generating contracts — always review generated contracts and relax any matchers that aren't actually required for the consumer's business logic. If your consumer only checks subscription.status === 'active', the contract should use a type matcher for all other fields.

How to generate service dependency mocking and simulation scenarios with AI?

When testing backend services in isolation, you need to simulate the behavior of every dependency — databases, caches, external APIs, message queues, and downstream services. AI excels at generating the scaffolding for these mocks, and more importantly, at generating the failure simulation scenarios that make your service tests meaningful.

The spectrum of mocking strategies

AI helps generate configurations for all four levels.

Generating WireMock stubs from API specs

Generate WireMock stub mappings for simulating our payment provider (Stripe) 
in our integration test environment. Generate stubs for:

1. POST /v1/payment_intents — successful creation (returns PaymentIntent with status: requires_payment_method)
2. POST /v1/payment_intents/:id/confirm — successful confirmation (status: succeeded)
3. POST /v1/payment_intents/:id/confirm — card declined (status: requires_payment_method, last_payment_error.code: card_declined)
4. POST /v1/payment_intents/:id/confirm — insufficient funds (last_payment_error.code: insufficient_funds)
5. POST /v1/payment_intents/:id/confirm — network timeout (simulate 30s delay, then 500 error)
6. GET /v1/customers/:id — customer found
7. GET /v1/customers/:id — customer not found (404 with Stripe error format)

Use WireMock JSON stub format. Include:
- Request matching (URL pattern, HTTP method, required headers)
- Response body matching Stripe's actual response format
- Appropriate response delays for timeout simulation
- Stateful scenarios where needed (e.g., PaymentIntent state transitions)

Generating Mock Service Worker (MSW) handlers

For Node.js and browser-based integration tests:

Generate MSW (Mock Service Worker) handlers in TypeScript for our frontend 
integration tests. Mock our internal API gateway with handlers for:

1. Happy path responses for all user-facing features
2. Loading states (1500ms delay on data-heavy endpoints)
3. Error states for each endpoint:
   - 401 Unauthorized (expired session)
   - 403 Forbidden (insufficient permissions)
   - 404 Not Found (resource deleted)
   - 422 Validation Error (with field-level error messages)
   - 500 Internal Server Error (with error correlation ID)
   - 503 Service Unavailable (with Retry-After header)
4. Empty state responses (empty arrays, null resource)

Organize handlers by domain in separate files:
- handlers/auth.ts
- handlers/orders.ts
- handlers/products.ts

API spec context:
---
[paste relevant API endpoints]
---

Generating chaos and failure simulation scenarios

The most valuable mocking work is failure simulation — and it's the most commonly skipped:

Generate a test suite that validates our OrderService's resilience to 
downstream service failures. Use nock to simulate:

1. UserService failures:
   - Connection refused (service down)
   - Timeout (response after 5000ms, exceeding our 3000ms timeout)
   - Random 503 responses (test retry logic — should retry 3 times)
   - Correct 404 for valid request (test graceful handling)

2. PaymentService failures:
   - Idempotency key collision (409 Conflict)
   - Rate limiting (429 Too Many Requests with Retry-After: 60)
   - Partial failure (payment created but confirmation times out)

For each failure scenario, assert:
- The error is handled without crashing the service
- Appropriate error response is returned to the caller (correct status code)
- The failure is logged with correct severity and correlation ID
- Retry logic activates where expected (exponential backoff)
- Circuit breaker trips after 5 consecutive failures

Tech stack: Node.js, Express, nock, Jest

Generating stateful mock scenarios

For scenarios that require stateful simulation (e.g., a payment that transitions through states):

Generate a stateful WireMock scenario that simulates an async payment 
processing workflow:

State machine:
1. Initial: POST /payments → returns { id: "pay_123", status: "processing" }
2. Polling state 1: GET /payments/pay_123 → returns { status: "processing" }  (first 2 calls)
3. Polling state 2: GET /payments/pay_123 → returns { status: "processing" }  (next call)
4. Final: GET /payments/pay_123 → returns { status: "succeeded" }  (after 3 polls)

This simulates a real async payment processor where the client must poll for status.
Generate the WireMock scenario JSON files and a test that:
- Creates the payment
- Polls with exponential backoff
- Asserts final status is "succeeded" after ≤ 5 polls
- Fails if polling exceeds 5 attempts (circuit breaker test)

Learning Tip: Failure simulation is the single most underdone area of backend testing. Most teams test what happens when everything works; production outages happen because of what happens when one thing fails. When generating mocks with AI, explicitly ask for the failure mode scenarios by name — connection refused, timeout, partial failure, rate limiting, idempotency violations. The happy path is easy to test; the failure modes are where your service's real quality lives.