Database Mocking and Seeding for Ephemeral Environments

Ephemeral preview infrastructure collapses when the database layer is an afterthought. A frontend that renders correctly against a stale fixture set tells you nothing about migration safety, ORM query correctness, or referential integrity under realistic data volumes. This page details the provisioning patterns that close that gap — strategy selection, pipeline wiring, anonymization, benchmarks, and the failure modes that burn teams repeatedly. It assumes you already have automated preview deployments on pull requests running and are trying to harden the data layer beneath them.

Prerequisites

• Docker Engine ≥ 24 or a Kubernetes cluster with InitContainer support (k8s ≥ 1.26)
• A migration tool committed to version control: Flyway, Liquibase, or Prisma Migrate
• CI runner with ≥ 4 GB RAM and 10 GB ephemeral disk per concurrent preview
• Secrets manager (HashiCorp Vault, AWS Secrets Manager, or native Kubernetes Secrets) for database credentials
• Write access to a container registry for custom seed images
• If using production-derived data: an anonymization pipeline approved by your data-compliance team

How the mechanism works

Every provisioning strategy sits on the same three-phase timeline: init → migrate → seed. Understanding where each phase can block or race is what lets you debug failures without guesswork.

Init starts the database process and blocks until the health-check probe confirms the port is accepting connections. Migrate applies all pending DDL in version-controlled order. Seed inserts synthetic or anonymized rows. Only after all three complete does the readiness gate lift and the application pod receive traffic.

The most common failure — application startup before seed scripts finish — happens because teams model only the health-check probe and treat migrate+seed as fire-and-forget. The patterns below enforce the full three-phase gate.

Step-by-step implementation

Step 1 — Choose your data provisioning strategy

Pick the right approach for the test scope before writing any pipeline code. Using a snapshot where synthetic seed would do wastes 20–60 seconds per preview spin-up and adds compliance surface area.

Verification: confirm the choice matches your test contract. If tests assert on specific row counts or ORM-generated SQL, mocking is insufficient — use container seeding.

Step 2 — Wire the database into Docker Compose (CI runners and local parity)

This pattern drives local development and most GitHub Actions / GitLab CI runner setups. The volumes mount is the critical seeding hook: every .sql and .sh file in the mounted directory runs automatically on PostgreSQL’s first start.

# docker-compose.preview.yml
services:
  db:
    image: postgres:16-alpine          # pin a minor version to prevent silent breaking changes
    environment:
      POSTGRES_DB: preview_db
      POSTGRES_USER: ci_user
      POSTGRES_PASSWORD: ci_password   # inject from ${{ secrets.PREVIEW_DB_PASS }} in real pipelines
    volumes:
      - ./db/seeds:/docker-entrypoint-initdb.d   # SQL and shell scripts run in filename order
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ci_user -d preview_db"]
      interval: 5s
      timeout: 3s
      retries: 10
    tmpfs:
      - /var/lib/postgresql/data       # ephemeral storage; no volume to clean up on teardown

  migrate:
    image: flyway/flyway:10             # or your migration tool of choice
    command: migrate
    environment:
      FLYWAY_URL: jdbc:postgresql://db:5432/preview_db
      FLYWAY_USER: ci_user
      FLYWAY_PASSWORD: ci_password
      FLYWAY_LOCATIONS: filesystem:/flyway/sql
    volumes:
      - ./db/migrations:/flyway/sql
    depends_on:
      db:
        condition: service_healthy      # blocks until health-check passes

  app:
    build: .
    depends_on:
      migrate:
        condition: service_completed_successfully   # blocks until migrations finish
    environment:
      DATABASE_URL: postgres://ci_user:ci_password@db:5432/preview_db

Inline verification:

docker compose -f docker-compose.preview.yml up --wait
docker compose -f docker-compose.preview.yml exec db \
  psql -U ci_user -d preview_db -c "\dt"
# Expected: table list matches your schema

Step 3 — Kubernetes InitContainer pattern (cluster-native workloads)

For teams running preview namespaces in Kubernetes, the InitContainer enforces the same three-phase gate at the pod level. The main application container does not start until all init containers exit with code 0.

# preview-deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: preview-app
  namespace: preview-{{ .Values.prNumber }}   # Helm value; one namespace per PR
spec:
  replicas: 1
  selector:
    matchLabels:
      app: preview-app
  template:
    spec:
      initContainers:
        - name: db-migrate
          image: ghcr.io/your-org/flyway:10   # pin digest in production
          command: ["flyway", "migrate"]
          envFrom:
            - secretRef:
                name: preview-db-creds        # injected by external-secrets-operator or vault-agent
          env:
            - name: FLYWAY_URL
              value: jdbc:postgresql://$(DB_HOST):5432/$(DB_NAME)

        - name: db-seed
          image: ghcr.io/your-org/seed-runner:latest
          command: ["sh", "-c", "psql $DATABASE_URL -f /seeds/preview.sql"]
          envFrom:
            - secretRef:
                name: preview-db-creds
          volumeMounts:
            - name: seed-scripts
              mountPath: /seeds

      containers:
        - name: app
          image: ghcr.io/your-org/app:{{ .Values.imageTag }}
          envFrom:
            - secretRef:
                name: preview-db-creds

      volumes:
        - name: seed-scripts
          configMap:
            name: preview-seed-scripts

Inline verification:

kubectl get pods -n preview-<PR_NUMBER>
# Both init containers should show "Completed" before app shows "Running"
kubectl logs -n preview-<PR_NUMBER> -c db-seed deploy/preview-app
# Expected: "psql: ... INSERT 0 <N>" lines with no error exit

This integrates cleanly with synchronizing environment variables across stages — the same external-secrets-operator sync that populates app credentials also populates the init container secrets, preventing credential drift between tiers.

Step 4 — Application-layer mocking with Prisma (unit and component tests)

When the test suite does not exercise the database engine itself, intercept at the ORM layer. This eliminates the 10–30 s container spin-up from every unit test run.

// src/__tests__/setup/prisma.mock.ts
import { PrismaClient } from '@prisma/client'
import { mockDeep, DeepMockProxy } from 'jest-mock-extended'

// Create a fully-typed recursive proxy for the Prisma client
export const prismaMock = mockDeep<PrismaClient>()

// Replace the module singleton before any test file imports it
jest.mock('../../lib/prisma', () => ({
  __esModule: true,
  prisma: prismaMock,
}))

beforeEach(() => {
  // Reset all mocks between tests; prevents bleed-across between test cases
  jest.resetAllMocks()
})

// src/__tests__/user.service.test.ts
import { prismaMock } from './setup/prisma.mock'
import { getUserById } from '../services/user.service'

test('returns user when found', async () => {
  prismaMock.user.findUnique.mockResolvedValue({
    id: 'usr_01',
    email: '[email protected]',
    name: 'Alice',
    createdAt: new Date('2024-01-01'),
  })

  const user = await getUserById('usr_01')
  expect(user?.email).toBe('[email protected]')
  // prismaMock records every call — assert on query shape if needed
  expect(prismaMock.user.findUnique).toHaveBeenCalledWith({
    where: { id: 'usr_01' },
  })
})

Inline verification:

npx jest --testPathPattern="user.service" --verbose
# Expected: test suite completes in < 2 s with no database connection

Step 5 — Sanitize data before seeding from production

If your highest-fidelity previews require production-shaped data, run every export through an anonymization pipeline before ingestion. This is non-negotiable for GDPR and SOC 2 compliance.

#!/usr/bin/env bash
# scripts/anonymize-dump.sh — run in a secure, network-isolated environment
set -euo pipefail

SOURCE_DUMP="$1"        # path to pg_dump output
OUTPUT_SEED="$2"        # destination path for sanitized seed file

# 1. Restore to a temporary local database
psql "$TEMP_DB_URL" < "$SOURCE_DUMP"

# 2. Apply column-level anonymization (using pganaonymize or custom SQL)
psql "$TEMP_DB_URL" <<'SQL'
  -- Replace PII with deterministic pseudonyms so foreign-key joins still work
  UPDATE users SET
    email      = 'user_' || id || '@example-preview.invalid',
    name       = 'Preview User ' || id,
    phone      = NULL,
    ip_address = '0.0.0.0';

  UPDATE payment_methods SET
    card_last_four = '0000',
    billing_name   = 'Test Account';
SQL

# 3. Dump the sanitized state for use as a seed file
pg_dump --data-only --no-owner "$TEMP_DB_URL" > "$OUTPUT_SEED"

echo "Sanitized seed written to $OUTPUT_SEED"

Inline verification:

grep -i "real.email@" "$OUTPUT_SEED" && echo "FAIL: PII detected" || echo "PASS: no PII found"

Configuration reference

Integration with upstream and downstream

Upstream — preview deployment triggers: the database provisioning pipeline fires as part of the same GitHub Actions workflow that triggers automated preview deployments on pull requests. The database readiness gate must resolve before the application image is deployed, not concurrently with it. Add an explicit job dependency in your workflow:

jobs:
  provision-db:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: docker compose -f docker-compose.preview.yml up -d --wait db migrate

  deploy-app:
    needs: provision-db     # enforces the three-phase gate at the workflow level
    runs-on: ubuntu-latest
    steps:
      - run: kubectl apply -f preview-deployment.yaml

Downstream — environment variable synchronization: database connection strings are the most common source of environment drift. Align your DATABASE_URL convention with the patterns in synchronizing environment variables across stages so every tier — local, preview, staging, production — resolves credentials from the same secret-manager hierarchy.

Performance benchmarks and cost impact

These figures are from a monorepo with 120 migrations and a 15 MB synthetic seed dataset running on GitHub-hosted ubuntu-latest runners (4 vCPU, 16 GB RAM):

Optimization levers:

• Pre-bake migrations into a custom image (FROM postgres:16-alpine; COPY migrations/ /docker-entrypoint-initdb.d/) — eliminates the migrate phase at runtime and cuts median init time to 8 s.
• Cache the seed .sql file in CI artifact storage keyed on its SHA256. On a cache hit, volume-mount the file directly rather than generating it. Saves 5–15 s per run on large seed sets.
• Use tmpfs storage for the PostgreSQL data directory. Eliminates I/O wait and cuts teardown to 0 s — no volume to delete.

Compared to using a shared staging database: per-branch isolation eliminates the 8–12 minutes of serialized test queuing that occurs when 10+ developers share one staging instance during peak hours.

Troubleshooting

Error: connection refused on first application request

Exact error: Error: connect ECONNREFUSED 127.0.0.1:5432 or PG::ConnectionBad: could not connect to server

Root cause: the application container started before the database health-check probe confirmed readiness. This happens when depends_on is configured with condition: service_started (the default) instead of condition: service_healthy.

Fix:

# docker-compose.preview.yml
services:
  app:
    depends_on:
      db:
        condition: service_healthy    # NOT service_started

Error: relation "X" does not exist after seeding

Exact error: ERROR: relation "users" does not exist (or any table name)

Root cause: seed scripts ran before the migration container completed, or migrations were not committed to version control before the preview branch was pushed.

Fix: ensure the migrate service uses condition: service_completed_successfully and that the seed script runs in a separate service that depends_on the migration service. Add a migration lint step to your PR check:

# Fail the PR if any migration file was modified without a corresponding schema snapshot update
flyway info --url="$FLYWAY_URL" | grep "Pending" && exit 1 || exit 0

Error: too many connections on parallel previews

Exact error: FATAL: remaining connection slots are reserved for non-replication superuser connections

Root cause: multiple concurrent previews each opening their own connection pool to a shared database proxy, exhausting max_connections.

Fix: deploy per-branch database instances rather than sharing a proxy. Use namespace-scoped Postgres StatefulSets in Kubernetes, or per-PR RDS instances with a small instance class (db.t4g.micro). Alternatively, front a shared instance with PgBouncer in transaction-pool mode and set pool_size to 5 per preview namespace.

Error: disk quota exceeded during snapshot seeding

Exact error: No space left on device during pg_restore or COPY FROM

Root cause: the CI runner’s ephemeral disk (typically 14 GB on GitHub-hosted runners) fills when restoring an uncompressed production dump.

Fix: compress seed files with pg_dump --compress=9 and decompress on the fly during restore. Switch to partial-dataset extraction using pg_dump --table flags to pull only the tables your tests actually need. Purge volumes at the end of every job with docker compose down -v.

Frequently Asked Questions

Related

• Automated Preview Deployments on Pull Requests — the workflow trigger layer that database provisioning must synchronize with; covers PR webhook events, deployment namespace lifecycle, and URL routing.
• Synchronizing Environment Variables Across Stages — the credential and config sync patterns that keep DATABASE_URL consistent between preview, staging, and production without manual overrides.
• Artifact Management Strategies for Frontend Builds — complements database seeding by addressing how build artifacts are versioned and promoted through the same pipeline that provisions preview environments.
• Managing Environment Matrices in GitHub Actions — if you run database integration tests against multiple Postgres versions or Node versions in parallel, the matrix patterns here compose directly with the seeding pipeline above.

← Back to Preview Environments & Environment Parity