Test Data Seeding: Factories, Fixtures, and Seed Scripts

Test Data Seeding: Factories, Fixtures, and Seed Scripts

Bad test data causes more test failures than bad test logic. Tests that share global state, fixtures that drift out of sync with the schema, and seeding approaches that create thousands of rows when three would do — these problems compound over time until the test suite becomes a maintenance burden that the team starts ignoring.

Getting test data right means choosing the correct seeding strategy for each scenario, keeping data creation local to tests that need it, and cleaning up deterministically. This guide covers the four major approaches — factory functions, SQL fixtures, programmatic seeding, and environment-specific seed scripts — with practical implementation patterns for each.

The Core Principle: Minimal, Explicit, Local

Before any specific technique, establish three principles:

Minimal: Create only the data each test needs. A test that needs one user should create one user, not inherit a fixture file with 50.

Explicit: Each test should be readable without consulting an external file. If understanding a test requires reading a fixture file, the test is harder to maintain.

Local: Data created by a test should be cleaned up when that test ends. Shared global state between tests creates ordering dependencies and makes failures hard to diagnose.

With these principles in mind, let's look at the tools.

Factory Pattern with @faker-js/faker

The factory pattern is the most flexible approach. A factory function creates a valid object with sensible defaults, and callers override only the fields they care about:

npm install --save-dev @faker-js/faker
// test/factories/index.js
const { faker } = require('@faker-js/faker');

function buildUser(overrides = {}) {
  return {
    id: faker.number.int({ min: 1, max: 1000000 }),
    email: faker.internet.email().toLowerCase(),
    name: faker.person.fullName(),
    passwordHash: faker.string.alphanumeric(60),
    role: 'user',
    createdAt: faker.date.past(),
    ...overrides,
  };
}

function buildProduct(overrides = {}) {
  return {
    id: faker.number.int({ min: 1, max: 1000000 }),
    name: faker.commerce.productName(),
    description: faker.commerce.productDescription(),
    price: parseFloat(faker.commerce.price({ min: 1, max: 500 })),
    sku: faker.string.alphanumeric(8).toUpperCase(),
    stock: faker.number.int({ min: 0, max: 1000 }),
    createdAt: new Date(),
    ...overrides,
  };
}

function buildOrder(overrides = {}) {
  const items = overrides.items || [buildOrderItem()];
  const total = items.reduce((sum, item) => sum + (item.price * item.quantity), 0);
  
  return {
    id: faker.number.int({ min: 1, max: 1000000 }),
    userId: faker.number.int({ min: 1, max: 1000000 }),
    items,
    total,
    status: 'pending',
    createdAt: new Date(),
    ...overrides,
  };
}

function buildOrderItem(overrides = {}) {
  const price = parseFloat(faker.commerce.price({ min: 1, max: 200 }));
  return {
    productId: faker.number.int({ min: 1, max: 1000000 }),
    name: faker.commerce.productName(),
    quantity: faker.number.int({ min: 1, max: 5 }),
    price,
    ...overrides,
  };
}

module.exports = { buildUser, buildProduct, buildOrder, buildOrderItem };

Using these factories in tests:

// test/services/orderService.test.js
const { buildUser, buildProduct, buildOrder } = require('../factories');
const OrderService = require('../../src/services/OrderService');

test('calculates correct total for multi-item order', () => {
  const order = buildOrder({
    items: [
      buildOrderItem({ price: 10.00, quantity: 3 }),
      buildOrderItem({ price: 24.99, quantity: 1 }),
    ],
  });

  const total = OrderService.calculateTotal(order);
  expect(total).toBeCloseTo(54.99);
});

test('applies discount for orders over $100', () => {
  const order = buildOrder({
    items: [buildOrderItem({ price: 150.00, quantity: 1 })],
  });

  const discounted = OrderService.applyVolumeDiscount(order);
  expect(discounted.total).toBeCloseTo(135.00); // 10% off
});

The advantage: each test is completely self-contained. You know exactly what data it uses without consulting any external file.

Database Factory: Persisting Test Data

For integration tests that need data in the database, extend the factory pattern to persist records:

// test/factories/db.js
const { Client } = require('pg');
const { buildUser, buildProduct, buildOrder } = require('./index');

let client;

function setClient(dbClient) {
  client = dbClient;
}

async function createUser(overrides = {}) {
  const data = buildUser(overrides);
  
  const result = await client.query(`
    INSERT INTO users (email, name, password_hash, role, created_at)
    VALUES ($1, $2, $3, $4, $5)
    RETURNING *
  `, [data.email, data.name, data.passwordHash, data.role, data.createdAt]);
  
  return result.rows[0];
}

async function createProduct(overrides = {}) {
  const data = buildProduct(overrides);
  
  const result = await client.query(`
    INSERT INTO products (name, description, price, sku, stock, created_at)
    VALUES ($1, $2, $3, $4, $5, $6)
    RETURNING *
  `, [data.name, data.description, data.price, data.sku, data.stock, data.createdAt]);
  
  return result.rows[0];
}

async function createOrder(userId, overrides = {}) {
  const product = await createProduct();
  const orderData = buildOrder({
    userId,
    items: [buildOrderItem({ productId: product.id, price: product.price })],
    ...overrides,
  });

  const orderResult = await client.query(`
    INSERT INTO orders (user_id, total, status, created_at)
    VALUES ($1, $2, $3, $4)
    RETURNING *
  `, [orderData.userId, orderData.total, orderData.status, orderData.createdAt]);

  const order = orderResult.rows[0];

  for (const item of orderData.items) {
    await client.query(`
      INSERT INTO order_items (order_id, product_id, name, quantity, price)
      VALUES ($1, $2, $3, $4, $5)
    `, [order.id, item.productId, item.name, item.quantity, item.price]);
  }

  return order;
}

module.exports = { setClient, createUser, createProduct, createOrder };
// test/repositories/orderRepository.test.js
const dbFactory = require('../factories/db');
const OrderRepository = require('../../src/repositories/OrderRepository');

beforeAll(async () => {
  const dbClient = await setupTestDatabase();
  dbFactory.setClient(dbClient);
});

test('findByUser returns orders sorted by date descending', async () => {
  const user = await dbFactory.createUser();
  
  await dbFactory.createOrder(user.id, { createdAt: new Date('2024-01-01') });
  await dbFactory.createOrder(user.id, { createdAt: new Date('2024-03-01') });
  await dbFactory.createOrder(user.id, { createdAt: new Date('2024-02-01') });

  const orders = await OrderRepository.findByUser(user.id);

  expect(orders).toHaveLength(3);
  expect(new Date(orders[0].created_at).getTime())
    .toBeGreaterThan(new Date(orders[1].created_at).getTime());
});

SQL Fixture Files

Sometimes you need a fixed, well-known dataset — for testing reports, analytics queries, or anything where the exact values matter rather than just the structure. SQL fixture files work well here:

-- test/fixtures/ecommerce-baseline.sql
-- Known dataset for query and report testing

TRUNCATE users, products, orders, order_items CASCADE;

INSERT INTO users (id, email, name, role, created_at) VALUES
  (1, 'alice@example.com', 'Alice Smith', 'admin', '2024-01-01 09:00:00'),
  (2, 'bob@example.com',   'Bob Jones',   'user',  '2024-01-15 14:30:00'),
  (3, 'carol@example.com', 'Carol White', 'user',  '2024-02-01 10:00:00');

INSERT INTO products (id, name, price, sku, stock) VALUES
  (1, 'Widget Pro',    29.99, 'WGT-001', 100),
  (2, 'Gadget Basic',  9.99, 'GDG-001',  50),
  (3, 'Doohickey',    49.99, 'DOO-001',  25);

INSERT INTO orders (id, user_id, total, status, created_at) VALUES
  (1, 1, 39.98, 'delivered', '2024-01-20 11:00:00'),
  (2, 2, 49.99, 'shipped',   '2024-02-10 15:00:00'),
  (3, 1, 29.99, 'pending',   '2024-02-15 09:30:00'),
  (4, 3,  9.99, 'delivered', '2024-02-20 14:00:00');

INSERT INTO order_items (order_id, product_id, name, quantity, price) VALUES
  (1, 1, 'Widget Pro',   1, 29.99),
  (1, 2, 'Gadget Basic', 1,  9.99),
  (2, 3, 'Doohickey',    1, 49.99),
  (3, 1, 'Widget Pro',   1, 29.99),
  (4, 2, 'Gadget Basic', 1,  9.99);
// test/reports/revenue.test.js
const path = require('path');

beforeAll(async () => {
  await db.setup();
  await db.execFile(path.join(__dirname, '../fixtures/ecommerce-baseline.sql'));
});

test('monthly revenue report shows correct totals', async () => {
  const report = await ReportService.monthlyRevenue({ year: 2024, month: 2 });
  
  // February orders: order 2 ($49.99) + order 3 ($29.99) + order 4 ($9.99)
  expect(report.total).toBeCloseTo(89.97);
  expect(report.orderCount).toBe(3);
});

test('top products by revenue', async () => {
  const report = await ReportService.topProducts({ limit: 2 });
  
  // Doohickey: $49.99, Widget Pro: $59.98 total, Gadget Basic: $19.98
  expect(report[0].name).toBe('Widget Pro');
  expect(report[0].revenue).toBeCloseTo(59.98);
});

SQL fixtures are ideal when the exact values matter. The fixture file is a form of documentation — anyone can read it and understand exactly what data the tests assume.

Database Cleanup Strategies

How you clean up between tests matters as much as how you seed. There are three main strategies:

Truncation

The simplest approach: truncate tables between tests or test suites.

async function truncateAll(client) {
  // Truncate in dependency order (children first) or use CASCADE
  await client.query('TRUNCATE order_items CASCADE');
  await client.query('TRUNCATE orders CASCADE');
  await client.query('TRUNCATE products CASCADE');
  await client.query('TRUNCATE users CASCADE');
}

// In Jest setup
afterEach(async () => {
  await truncateAll(client);
});

Or more conveniently, truncate all tables at once:

async function truncateAllTables(client) {
  const result = await client.query(`
    SELECT tablename FROM pg_tables 
    WHERE schemaname = 'public'
    ORDER BY tablename
  `);
  
  const tables = result.rows.map(r => r.tablename).join(', ');
  if (tables) {
    await client.query(`TRUNCATE ${tables} CASCADE`);
  }
}

Truncation is fast and simple but requires tests to create all their own data.

Transaction Rollback

Wrap each test in a transaction and roll it back when done. This is faster than truncation and automatically handles cascading:

// test/helpers/transactional.js
const { Client } = require('pg');

let client;
let transactionActive = false;

async function beginTest(dbClient) {
  client = dbClient;
  await client.query('BEGIN');
  transactionActive = true;
}

async function rollbackTest() {
  if (transactionActive) {
    await client.query('ROLLBACK');
    transactionActive = false;
  }
}

module.exports = { beginTest, rollbackTest };
// test/repositories/user.test.js
const { beginTest, rollbackTest } = require('../helpers/transactional');

beforeEach(async () => await beginTest(dbClient));
afterEach(async () => await rollbackTest());

test('creates user and persists to database', async () => {
  const user = await UserRepository.create({
    email: 'test@example.com',
    name: 'Test User',
    passwordHash: 'hashed',
  });

  const found = await UserRepository.findById(user.id);
  expect(found.email).toBe('test@example.com');
  // This data disappears when afterEach rolls back the transaction
});

Transaction rollback is elegant but has a significant limitation: it doesn't work for code that opens its own database connections or uses transactions internally (since nested transactions behave differently). For those cases, truncation is safer.

Per-Test Database

The most isolated (and expensive) approach: give each test its own database:

async function createTestDatabase(client, name) {
  await client.query(`CREATE DATABASE ${name}`);
  return name;
}

async function dropTestDatabase(client, name) {
  // Disconnect all clients from this database first
  await client.query(`
    SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE datname = $1
  `, [name]);
  await client.query(`DROP DATABASE IF EXISTS ${name}`);
}

// In test
let testDbName;

beforeEach(async () => {
  testDbName = `test_${Date.now()}_${Math.random().toString(36).slice(2)}`;
  await createTestDatabase(adminClient, testDbName);
  await runMigrations(testDbName);
});

afterEach(async () => {
  await dropTestDatabase(adminClient, testDbName);
});

This is the most isolated option but creates significant overhead. Reserve it for migration tests or test suites where isolation is critical and you have time budget to spare.

Seed Scripts for Different Environments

Beyond individual test data, seed scripts populate a database for specific purposes:

// seeds/development.js
const { faker } = require('@faker-js/faker');
const { Client } = require('pg');

async function seed() {
  const client = new Client({ connectionString: process.env.DATABASE_URL });
  await client.connect();

  console.log('Seeding development database...');

  // Create admin user with known credentials
  await client.query(`
    INSERT INTO users (email, name, password_hash, role)
    VALUES ('admin@example.com', 'Admin User', $1, 'admin')
    ON CONFLICT (email) DO NOTHING
  `, [await hashPassword('admin123')]);

  // Create test users
  for (let i = 0; i < 20; i++) {
    await client.query(`
      INSERT INTO users (email, name, password_hash, role)
      VALUES ($1, $2, $3, 'user')
      ON CONFLICT (email) DO NOTHING
    `, [faker.internet.email().toLowerCase(), faker.person.fullName(), 'dev-hash']);
  }

  // Create products
  const productIds = [];
  for (let i = 0; i < 50; i++) {
    const result = await client.query(`
      INSERT INTO products (name, description, price, sku, stock)
      VALUES ($1, $2, $3, $4, $5)
      ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name
      RETURNING id
    `, [
      faker.commerce.productName(),
      faker.commerce.productDescription(),
      parseFloat(faker.commerce.price({ min: 5, max: 200 })),
      faker.string.alphanumeric(8).toUpperCase(),
      faker.number.int({ min: 0, max: 500 }),
    ]);
    productIds.push(result.rows[0].id);
  }

  console.log(`Created 20 users and ${productIds.length} products`);
  await client.end();
}

seed().catch(err => {
  console.error('Seed failed:', err);
  process.exit(1);
});
// seeds/staging.js — minimal, deterministic data for E2E tests
async function seed() {
  // Only known, stable data — no faker, no randomness
  // Staging seeds are checked into source control
  
  await createUser({ email: 'e2e-user@example.com', password: 'TestPass123!' });
  await createProduct({ sku: 'E2E-PROD-001', name: 'E2E Test Product', price: 9.99 });
}

The key difference between development and staging seeds: staging seeds use fixed, known values that E2E tests can reference by name. Development seeds use faker for variety.

Sequenced Factory Dependencies

Real-world data has dependencies: an order needs a user and products to exist first. A helper that manages creation order simplifies test setup:

// test/factories/scenario.js
const { createUser, createProduct, createOrder } = require('./db');

async function createOrderScenario(options = {}) {
  const user = await createUser(options.user);
  const product = await createProduct(options.product);
  const order = await createOrder(user.id, {
    ...options.order,
    items: [{ productId: product.id, name: product.name, quantity: 1, price: product.price }],
  });

  return { user, product, order };
}

async function createShippedOrderScenario() {
  const { user, product, order } = await createOrderScenario();
  await updateOrderStatus(order.id, 'shipped');
  return { user, product, order: { ...order, status: 'shipped' } };
}

module.exports = { createOrderScenario, createShippedOrderScenario };
test('cannot cancel a shipped order', async () => {
  const { order } = await createShippedOrderScenario();
  
  await expect(
    OrderService.cancel(order.id)
  ).rejects.toThrow('Cannot cancel a shipped order');
});

Named scenarios make tests read like specifications.

Conclusion

Effective test data management comes down to matching the tool to the need: factories for flexibility in unit and integration tests, SQL fixtures for known-value report and analytics tests, and deterministic seed scripts for E2E environments. Transaction rollback cleanup keeps individual tests fast and isolated; truncation is the pragmatic fallback when transactions can't be used.

The investment in a good factory library pays off quickly — once createUser() and createOrder() exist, writing new tests takes minutes rather than hours of figuring out what data to set up.

Read more

Start now free