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
| Symptom | Root cause in test data |
|---|---|
| Intermittent “foreign key violation” in integration suite | Test 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 load | Load 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
| Model | Description | Typical schema impact | When 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) policies | Database 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 extensions | Core 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
INSERTincludes the correcttenant_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
| Criterion | Questions to ask | Weight (1‑5) |
|---|---|---|
| Regulatory / contractual isolation | Does any contract require separate encryption keys or physical separation? | 5 |
| Tenant count & churn | Do you spin up/down tenants daily? | 4 |
| Data volume per tenant | Is a single tenant > 10 GB? | 3 |
| Migration tooling maturity | Can your CI run per‑schema migrations reliably? | 4 |
| Team familiarity | Do developers understand RLS policies? | 2 |
| Test‑execution speed budget | Can 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
- Deterministic tenant IDs – so tests can reference a known tenant.
- Per‑tenant row counts – configurable (e.g., 5 users, 20 orders).
- Referential integrity – every
order.user_idmust belong to the same tenant. - Clean‑up safety – a single
TRUNCATE ... CASCADEper 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.
| Check | SQL (PostgreSQL) | What it catches |
|---|---|---|
| Cross‑tenant FK | SELECT 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 rows | SELECT 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 drift | SELECT 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
| Pitfall | Symptom | Mitigation |
|---|---|---|
| Hard‑coded tenant IDs in test code | Tests break when generator changes UUIDs. | Always fetch tenant IDs from the seed (SELECT id FROM tenants WHERE name = 'Tenant-1'). |
| Shared sequences across tenants | order_id collisions when using BIGSERIAL per table. | Use UUIDs or per‑tenant sequences (CREATE SEQUENCE tenant_1_orders_seq). |
Bulk TRUNCATE without CASCADE | Foreign‑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 ROLE | Superuser sees all rows, masking a leak. | Run validation checks under a non‑superuser role that has the RLS policy applied. |
| Schema migration drift | New 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 mismatch | Performance 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 anomalies | Concurrent 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
- Parameterize tenant count – expose
TENANT_COUNTto the generator; keep the same YAML. - Parallel seed – split the YAML into chunks (e.g., 500 tenants per file) and run
qa3-test-data generatein parallel jobs. - Partitioned tables – if you move to PostgreSQL declarative partitioning on
tenant_id, the generator only needs to emit rows; the DB handles physical isolation. - 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_columnlinks are declared; no manualINSERTstatements in seed files. - Validation queries are added to the CI post‑test stage.
- Clean‑up script uses tenant‑scoped
DELETEorTRUNCATE … CASCADEonly. - RLS policies (if any) are exercised by at least one integration test that switches
app.current_tenant. - Documentation in the repo’s
TEST_DATA.mdexplains 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.