Quality is not optional. It's our standard. Free QA tools for testers and developers.

Multi-Tenant SaaS Database Test Data: Isolation Scenarios

Multi‑Tenant SaaS Database Test Data: Isolation Scenarios

When a SaaS product serves dozens—or thousands—of tenants from a single database cluster, the test data you feed into CI/CD pipelines must prove that tenant A never sees tenant B’s rows. A single leak can turn a compliance audit into a breach notification, and it can also surface as flaky integration tests that waste engineering hours. This post walks through the most common isolation models, the decision criteria for picking one, a worked example you can copy into a repository, validation checks you can automate, and the pitfalls that trip up even experienced QA teams.


1. Why Isolation Is a Test‑Data Problem First

SymptomRoot cause in test data
Intermittent “foreign key violation” in integration suiteTest rows inserted without a tenant‑scope filter, so a later cleanup deletes the wrong tenant’s data
Data‑privacy audit flags “cross‑tenant exposure”Seed scripts use a shared account_id = 1 for all fixtures
Performance regression only appears under multi‑tenant loadLoad generator creates a single massive tenant instead of realistic distribution

All three symptoms disappear when the test data generation step enforces the same isolation guarantees that production code enforces. In other words, the generator must be tenant‑aware, not the tests themselves.


2. Isolation Models in the Wild

ModelDescriptionTypical schema impactWhen it shines
Shared schema, discriminator column (tenant_id on every table)One physical table per entity; every row carries tenant_id.Minimal DDL changes; indexes on tenant_id + PK.Most B2B SaaS where tenant count is high and data volume per tenant is modest.
Separate schema per tenant (tenant_<id>.orders)Each tenant gets its own PostgreSQL schema (or MySQL database).DDL per tenant; migration tooling must run per schema.Strong regulatory boundaries (e.g., GDPR “data residency”) or when a tenant demands a dedicated DB.
Row‑level security (RLS) policiesDatabase enforces WHERE tenant_id = current_setting('app.current_tenant').Requires PG 9.5+ (or equivalent); policies on every table.Teams that want zero‑code enforcement and can rely on the DB engine.
Hybrid: shared core + tenant‑specific extensionsCore tables shared; extension tables live in tenant schema.Two migration pipelines (core + per‑tenant).Products with a stable core and highly customizable per‑tenant modules.

Trade‑off: Shared schema is the easiest to generate data for, but you must guarantee that every INSERT includes the correct tenant_id. Separate schemas give you physical isolation for free, yet they explode the number of objects you have to seed and clean up.


3. Decision Criteria – Choosing the Right Model for Your Test Suite

CriterionQuestions to askWeight (1‑5)
Regulatory / contractual isolationDoes any contract require separate encryption keys or physical separation?5
Tenant count & churnDo you spin up/down tenants daily?4
Data volume per tenantIs a single tenant > 10 GB?3
Migration tooling maturityCan your CI run per‑schema migrations reliably?4
Team familiarityDo developers understand RLS policies?2
Test‑execution speed budgetCan you afford a 5‑minute schema‑creation step per test run?3

Score each model against the criteria; the highest total usually points to the pragmatic choice. In practice, shared schema + discriminator column wins for >80 % of SaaS products because it keeps the test‑data generator simple and the CI fast.


4. Worked Example – Generating Isolated Test Data for a Shared‑Schema App

Assume a PostgreSQL database with three core tables:

CREATE TABLE tenants (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);


CREATE TABLE users (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    email       TEXT NOT NULL,
    role        TEXT NOT NULL CHECK (role IN ('admin','member')),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);


CREATE TABLE orders (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    user_id     UUID NOT NULL REFERENCES users(id),
    amount_cents INTEGER NOT NULL,
    status      TEXT NOT NULL CHECK (status IN ('new','paid','shipped')),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

4.1. Generator Requirements

  1. Deterministic tenant IDs – so tests can reference a known tenant.
  2. Per‑tenant row counts – configurable (e.g., 5 users, 20 orders).
  3. Referential integrity – every order.user_id must belong to the same tenant.
  4. Clean‑up safety – a single TRUNCATE ... CASCADE per tenant must remove only that tenant’s data.

4.2. Using QA3’s Free Test Data Generator

The generator at /tools/test-data-generator lets you describe the schema in YAML and emit SQL or CSV. Below is a minimal definition that satisfies the four requirements.



# qa3-test-data.yaml


tables:
  - name: tenants
    rows: 3
    columns:
      - name: id
        type: uuid
        primary_key: true
      - name: name
        type: string
        pattern: "Tenant-{{index}}"
      - name: created_at
        type: timestamp
        default: now()


- name: users
    rows_per_parent: 5               # 5 users per tenant
    parent_table: tenants
    parent_column: tenant_id
    columns:
      - name: id
        type: uuid
        primary_key: true
      - name: tenant_id
        type: uuid
        foreign_key: tenants.id
      - name: email
        type: string
        pattern: "user{{index}}@tenant{{parent_index}}.example.com"
      - name: role
        type: choice
        values: [admin, member]
      - name: created_at
        type: timestamp
        default: now()


- name: orders
    rows_per_parent: 20              # 20 orders per tenant
    parent_table: tenants
    parent_column: tenant_id
    columns:
      - name: id
        type: uuid
        primary_key: true
      - name: tenant_id
        type: uuid
        foreign_key: tenants.id
      - name: user_id
        type: uuid
        foreign_key: users.id
        # The generator will automatically pick a user that shares the same tenant_id
      - name: amount_cents
        type: integer
        range: [100, 100000]
      - name: status
        type: choice
        values: [new, paid, shipped]
      - name: created_at
        type: timestamp
        default: now()

Run it:

qa3-test-data generate \
  --definition qa3-test-data.yaml \
  --output seed.sql \
  --dialect postgres

The resulting seed.sql contains three INSERT batches, each scoped to a single tenant_id. Because the generator respects the parent_table relationship, every order.user_id points to a user that belongs to the same tenant—no cross‑tenant FK violations can appear.

4.3. CI Integration (GitHub Actions Example)



# .github/workflows/integration.yml


name: Integration Tests
on: [push, pull_request]


jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_PASSWORD: postgres
        ports: ["5432:5432"]
        options: >-
          --health-cmd "pg_isready -U postgres"
          --health-interval 5s
          --health-timeout 3s
          --health-retries 5


steps:
      - uses: actions/checkout@v4


- name: Install QA3 CLI
        run: |
          curl -fsSL https://qa3.io/cli/install.sh | bash
          echo "$HOME/.qa3/bin" >> $GITHUB_PATH


- name: Generate seed data
        run: |
          qa3-test-data generate \
            --definition qa3-test-data.yaml \
            --output /tmp/seed.sql \
            --dialect postgres


- name: Load schema & seed
        env:
          PGPASSWORD: postgres
        run: |
          psql -h localhost -U postgres -d postgres -f db/schema.sql
          psql -h localhost -U postgres -d postgres -f /tmp/seed.sql


- name: Run integration suite
        run: |
          ./gradlew integrationTest   # or pytest, go test, etc.

Result: Every pipeline run starts from a clean, tenant‑isolated dataset. The same YAML can be reused for local development (docker compose up -d && qa3-test-data …) so developers see the exact same isolation guarantees.


5. Validation Checks – Automating the “No Leak” Guarantee

Generating data is only half the battle. Add assertion queries that run after each test suite (or as a separate CI job) to prove isolation.

CheckSQL (PostgreSQL)What it catches
Cross‑tenant FKSELECT COUNT(*) FROM orders o JOIN users u ON o.user_id = u.id WHERE o.tenant_id <> u.tenant_id;Orders pointing to users of another tenant
Orphan rowsSELECT COUNT(*) FROM users u LEFT JOIN tenants t ON u.tenant_id = t.id WHERE t.id IS NULL;Users without a tenant (should be zero)
Tenant row‑count driftSELECT tenant_id, COUNT(*) FROM orders GROUP BY tenant_id HAVING COUNT(*) <> 20;Unexpected bulk inserts/deletes
RLS policy enforcement (if used)SET LOCAL app.current_tenant = '00000000-0000-0000-0000-000000000001'; SELECT COUNT(*) FROM orders;Verify that a session scoped to tenant 1 sees only its rows
Schema‑level isolation (separate‑schema model)SELECT COUNT(*) FROM information_schema.tables WHERE table_schema LIKE 'tenant_%' AND table_name = 'orders';Missing per‑tenant tables

Add these as read‑only steps in your pipeline; they cost milliseconds and give you a hard gate before any release candidate is promoted.


6. Pitfalls & How to Avoid Them

PitfallSymptomMitigation
Hard‑coded tenant IDs in test codeTests break when generator changes UUIDs.Always fetch tenant IDs from the seed (SELECT id FROM tenants WHERE name = 'Tenant-1').
Shared sequences across tenantsorder_id collisions when using BIGSERIAL per table.Use UUIDs or per‑tenant sequences (CREATE SEQUENCE tenant_1_orders_seq).
Bulk TRUNCATE without CASCADEForeign‑key errors on cleanup.TRUNCATE tenants, users, orders CASCADE; or run per‑tenant DELETE FROM orders WHERE tenant_id = $1;
RLS policy bypass via SET ROLESuperuser sees all rows, masking a leak.Run validation checks under a non‑superuser role that has the RLS policy applied.
Schema migration driftNew column added to orders but generator YAML not updated.Enforce a schema‑diff gate (e.g., sqldef or atlas) that fails CI if generator definition lags behind.
Data‑volume mismatchPerformance tests pass locally but fail in staging because staging uses 10× more tenants.Parameterize rows_per_parent via environment variable; run a “scale” profile nightly.
Snapshot isolation anomaliesConcurrent test workers read stale tenant data.Use REPEATABLE READ or run each worker against its own DB instance (container per worker).

7. Scaling the Approach – From 3 Tenants to 3 000

  1. Parameterize tenant count – expose TENANT_COUNT to the generator; keep the same YAML.
  2. Parallel seed – split the YAML into chunks (e.g., 500 tenants per file) and run qa3-test-data generate in parallel jobs.
  3. Partitioned tables – if you move to PostgreSQL declarative partitioning on tenant_id, the generator only needs to emit rows; the DB handles physical isolation.
  4. Tenant‑profile library – store realistic “profiles” (small, medium, enterprise) as separate YAML snippets and compose them at runtime. This avoids a single monolithic definition that becomes unreadable.

8. Checklist – Before You Merge the Next Test‑Data PR

  • Generator YAML matches the current database schema (run qa3-test-data validate).
  • All parent_table / parent_column links are declared; no manual INSERT statements in seed files.
  • Validation queries are added to the CI post‑test stage.
  • Clean‑up script uses tenant‑scoped DELETE or TRUNCATE … CASCADE only.
  • RLS policies (if any) are exercised by at least one integration test that switches app.current_tenant.
  • Documentation in the repo’s TEST_DATA.md explains how to regenerate seed locally.
  • Performance profile (TENANT_COUNT=1000) runs nightly and alerts on > 10 % slowdown.

9. Next Action

Pick one of the isolation models that matches your current architecture, copy the YAML skeleton above into a qa3-test-data.yaml file at the root of your repository, and run the generator locally:

qa3-test-data generate \
  --definition qa3-test-data.yaml \
  --output /tmp/seed.sql \
  --dialect postgres

Inspect /tmp/seed.sql for cross‑tenant references, then add the validation queries to your CI pipeline. Once the pipeline turns green, you have a repeatable, evidence‑backed guarantee that your test data respects tenant boundaries—no more “it works on my machine” surprises.

Read more

GraphQL Test Data Generation for Queries and Mutations

A practical guide to “GraphQL Test Data Generation for Queries and Mutations,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.

Postman Test Data Generation with CSV and JSON Files

A practical guide to “Postman Test Data Generation with CSV and JSON Files,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.

Generate API Test Data Directly from an OpenAPI Specification

A practical guide to “Generate API Test Data Directly from an OpenAPI Specification,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.