Test Data Factories vs Fixtures vs Seed Scripts

A practical decision guide for QA engineers, test‑automation leads, developers, and engineering managers

---

Why the choice matters

Test data is the silent partner of every test suite. When data is stable, tests run fast and stay deterministic. When data is flexible, you can explore edge cases without rewriting the test harness. The three most common ways to provision that data are:

Approach	Typical use case	Primary strength	Primary weakness
Test Data Factories	Unit / integration tests that need many variations of the same entity	Programmatic, composable, version‑controlled	Can become a maintenance burden if the domain model changes often
Fixtures	End‑to‑end or contract tests that require a known database snapshot	Simple, declarative, easy to share across teams	Hard to evolve; large JSON/YAML files become brittle
Seed Scripts	CI pipelines, staging environments, performance runs	Realistic volume, can exercise migration paths	Slow to run, often coupled to a specific DB schema version

Choosing the wrong strategy leads to flaky tests, long CI times, or data‑driven bugs that slip into production. The rest of this post walks through a decision framework, a worked example, common pitfalls, and a next‑step checklist you can apply today.

---

1. Decision criteria matrix

Before you write a single line of factory code or dump a fixture file, score each approach against the criteria that matter to your team. Use the table below as a worksheet; fill in High / Medium / Low for each cell.

Criterion	Weight (1‑5)	Factory	Fixture	Seed Script
Test isolation (each test gets its own data)	5
Data realism (production‑like volume & relationships)	4
Maintenance effort (model changes, schema migrations)	4
Execution speed (setup + teardown)	5
Team familiarity (language, tooling)	3
Version control friendliness (diffable, reviewable)	3
Cross‑environment reuse (local, CI, staging)	4
Regulatory / PII constraints	5

How to use it

1. Agree on the weight column with the whole team (product, QA, DevOps).
2. Score each approach honestly—don’t inflate a favorite.
3. Multiply weight × score (High = 3, Medium = 2, Low = 1) and sum.
4. The highest total points to the default strategy; the others become supplementary tools.

Tip: Keep the matrix in a shared spreadsheet. Re‑evaluate quarterly or after a major schema change.

---

2. Worked example: an e‑commerce order domain

Assume a simplified schema:

CREATE TABLE customers (
  id UUID PRIMARY KEY,
  email TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);


CREATE TABLE products (
  id UUID PRIMARY KEY,
  sku TEXT NOT NULL,
  price_cents INT NOT NULL
);


CREATE TABLE orders (
  id UUID PRIMARY KEY,
  customer_id UUID REFERENCES customers(id),
  status TEXT NOT NULL,          -- 'pending' | 'paid' | 'shipped'
  total_cents INT NOT NULL,
  placed_at TIMESTAMPTZ DEFAULT now()
);


CREATE TABLE order_items (
  id UUID PRIMARY KEY,
  order_id UUID REFERENCES orders(id),
  product_id UUID REFERENCES products(id),
  quantity INT NOT NULL,
  line_total_cents INT NOT NULL
);

2.1 Factory implementation (TypeScript + factory.ts)

// factories/orderFactory.ts
import { faker } from '@faker-js/faker';
import { Customer, Product, Order, OrderItem } from '../domain';


export const buildCustomer = (overrides: Partial<Customer> = {}): Customer => ({
  id: faker.string.uuid(),
  email: faker.internet.email(),
  createdAt: faker.date.past(),
  ...overrides,
});


export const buildProduct = (overrides: Partial<Product> = {}): Product => ({
  id: faker.string.uuid(),
  sku: faker.string.alphanumeric(8).toUpperCase(),
  priceCents: faker.number.int({ min: 100, max: 10000 }),
  ...overrides,
});


export const buildOrder = (
  customer: Customer,
  items: OrderItem[],
  overrides: Partial<Order> = {}
): Order => ({
  id: faker.string.uuid(),
  customerId: customer.id,
  status: 'pending',
  totalCents: items.reduce((sum, i) => sum + i.lineTotalCents, 0),
  placedAt: faker.date.recent(),
  ...overrides,
});


export const buildOrderItem = (
  orderId: string,
  product: Product,
  qty: number
): OrderItem => ({
  id: faker.string.uuid(),
  orderId,
  productId: product.id,
  quantity: qty,
  lineTotalCents: product.priceCents * qty,
});

Why this works for unit/integration tests

• Composability – buildOrder accepts a pre‑built Customer and an array of OrderItems, letting a test cherry‑pick only the fields it cares about.
• Determinism – Seed the faker RNG (faker.seed(12345)) at the top of the test file; the same data appears on every run.
• Version control – The factory lives next to the domain model; a schema change triggers a compile‑time error.

2.2 Fixture file (JSON) for contract tests

// fixtures/order-contract.json
{
  "customer": {
    "id": "c1a2b3c4-d5e6-7f8g-9h0i-j1k2l3m4n5o6",
    "email": "alice@example.com",
    "createdAt": "2023-01-15T08:30:00Z"
  },
  "products": [
    { "id": "p1", "sku": "ABC12345", "priceCents": 2999 },
    { "id": "p2", "sku": "XYZ98765", "priceCents": 4999 }
  ],
  "order": {
    "id": "o1",
    "customerId": "c1a2b3c4-d5e6-7f8g-9h0i-j1k2l3m4n5o6",
    "status": "paid",
    "totalCents": 10997,
    "placedAt": "2024-03-10T12:00:00Z"
  },
  "orderItems": [
    { "id": "oi1", "orderId": "o1", "productId": "p1", "quantity": 2, "lineTotalCents": 5998 },
    { "id": "oi2", "orderId": "o1", "productId": "p2", "quantity": 1, "lineTotalCents": 4999 }
  ]
}

When fixtures shine

• Contract tests that must exactly match a published API spec.
• Shared across multiple repositories (e.g., a consumer‑driven contract repo).
• No code compilation step—just drop the file into the test resources folder.

2.3 Seed script (SQL + Node) for staging / performance

-- seed/01_customers.sql
INSERT INTO customers (id, email, created_at) VALUES
  ('c1a2b3c4-d5e6-7f8g-9h0i-j1k2l3m4n5o6', 'alice@example.com', '2023-01-15 08:30:00'),
  ('d2e3f4g5-h6i7-8j9k-l0m1-n2o3p4q5r6s7', 'bob@example.com',   '2023-02-20 14:45:00');

-- seed/02_products.sql
INSERT INTO products (id, sku, price_cents) VALUES
  ('p1', 'ABC12345', 2999),
  ('p2', 'XYZ98765', 4999);

-- seed/03_orders.sql
INSERT INTO orders (id, customer_id, status, total_cents, placed_at) VALUES
  ('o1', 'c1a2b3c4-d5e6-7f8g-9h0i-j1k2l3m4n5o6', 'paid', 10997, '2024-03-10 12:00:00');

-- seed/04_order_items.sql
INSERT INTO order_items (id, order_id, product_id, quantity, line_total_cents) VALUES
  ('oi1', 'o1', 'p1', 2, 5998),
  ('oi2', 'o1', 'p2', 1, 4999);

A tiny Node runner executes the files in order inside a transaction, then rolls back after the test suite finishes.

Seed‑script sweet spots

• Performance / load tests – you need thousands of rows, not a handful.
• Migration verification – run the same seed against a fresh DB after each migration to catch schema drift.
• Staging parity – the same script seeds the shared environment developers use for manual QA.

---

3. Trade‑off deep‑dive

Dimension	Factories	Fixtures	Seed Scripts
Isolation	✅ Each test can call buildX() with overrides → no shared state.	❌ Shared static file → tests must clone or risk cross‑talk.	❌ Global DB state unless you wrap each test in a transaction.
Realism	🟡 Good for shape, weak on volume & referential integrity across many tables.	🟡 Realistic snapshots but static; hard to scale to 100k rows.	✅ Full referential integrity, realistic volume, indexes, constraints.
Maintenance	🟡 Code changes when domain model changes (compile‑time safety).	🟢 Very low if schema is stable; 🔴 high if you edit large JSON by hand.	🟢 Low for schema‑only changes; 🔴 high when you need new data shapes.
Speed	✅ In‑memory, sub‑ms per object.	✅ Load once, reuse; but parsing large files adds ms.	🔴 Seconds‑to‑minutes for bulk inserts; mitigated by transaction rollback.
Team skill fit	Requires comfort with TS/JS/Python + factory libraries.	Low barrier – JSON/YAML editors.	Requires DB admin rights, migration tooling knowledge.
Version control	✅ Diffable source, PR reviews catch breaking changes.	✅ Diffable but large files produce noisy diffs.	🟡 SQL files diff well; binary dumps do not.
PII / compliance	✅ Generate synthetic data on the fly → no production data leaves CI.	🔴 Often copied from prod → risk of leakage.	🔴 Same risk unless you anonymize before seeding.

3.1 When to combine

Real‑world test suites rarely rely on a single technique. A pragmatic blend looks like:

Layer	Primary technique	Complementary technique
Unit / fast integration	Factories	Fixtures for a handful of “golden” scenarios
Contract / API consumer tests	Fixtures	Factories to generate negative‑case payloads
End‑to‑end / staging smoke	Seed script (once per pipeline)	Factories for per‑test data tweaks via test‑specific SQL patches
Performance / chaos	Seed script (large volume)	Factories to inject targeted anomalies (e.g., malformed JSON)

---

4. Common pitfalls & how to avoid them

Pitfall	Symptom	Root cause	Mitigation
Factory bloat	One factory file > 2 k LOC, many optional fields.	Trying to cover every possible domain permutation in one place.	Split by aggregate root (CustomerFactory, OrderFactory). Use builder pattern for optional nested objects.
Fixture drift	Contract tests pass locally but fail in CI.	Fixture file edited locally but not committed; CI uses stale version.	Enforce fixture linting in CI (e.g., jsonlint), store fixtures in a dedicated test/fixtures folder with CODEOWNERS.
Seed‑script coupling	Migration fails because seed script references a dropped column.	Seed script written against a specific schema version, not updated with migrations.	Keep seed scripts versioned alongside migrations (e.g., db/migrations/20240310_add_order_status.sql + db/seeds/20240310_orders.sql). Run a seed‑validation job after each migration.
Data leakage	Production email addresses appear in test logs.	Fixtures or seed data copied from a prod dump.	Adopt a synthetic‑data‑only policy; use a tool that can generate realistic but fake PII (see the free generator at /tools/test-data-generator).
Flaky isolation	Tests intermittently fail due to leftover rows.	Seed script runs once per suite, but tests don’t clean up.	Wrap each test in a transaction (BEGIN; … ROLLBACK;) or use a test‑containers library that spins a fresh DB per test class.
Performance blind spot	CI passes locally (10 s) but takes 8 min on the shared runner.	Local DB is in‑memory SQLite; CI uses Postgres with full indexes.	Mirror the target DB engine in CI (Testcontainers, GitHub Actions services). Measure seed‑script runtime as a CI metric.

---

5. Evaluation path – a 4‑step workshop for your team

1. Inventory – List every test suite (unit, integration, contract, e2e, perf) and note the current data provisioning method.
2. Score – Fill the decision‑criteria matrix (Section 1) for each suite.
3. Prototype – Pick the highest‑scoring approach for one suite and implement a minimum viable data layer (≈ 2 h).
4. Measure & Decide – Run the suite on CI for a week. Capture:
   • Setup time (seconds)
   • Flaky‑test count
   • Maintenance tickets (PRs touching data code)
   • PII audit findings

If the prototype meets the team’s acceptance thresholds (e.g., < 5 s setup, 0 flaky, < 1 maintenance PR per sprint), adopt it as the default for that suite. Repeat for the next suite.

Outcome: A living document (Markdown in the repo) that maps suite → primary data strategy and records the measured metrics. Update it each quarter.

---

6. Quick‑start checklist

• Define criteria weights with product, QA, DevOps.
• Populate the matrix for each test suite.
• Select a pilot ] Build a factory for the most volatile domain entity.
• Add a fixture for the public API contract you must honor.
• Write a seed script that can load 10 k orders in < 30 s (use `COPY