Seed a database from scenarios

Integration tests and local development often need connected rows: a customer, their order and its lines, with a total that matches the lines. This recipe builds those rows with a scenario, writes them in one transaction, and gives the same rows for the same seed. Running it twice leaves the same rows instead of adding duplicates.

The recipe writes to SQLite through node:sqlite, which is built into Node 22, so it runs in CI without a database server or a driver package. node:sqlite is still marked experimental and prints an ExperimentalWarning. The scenario and the seeding steps are the same for PostgreSQL or MySQL; only the SQL in the write step changes. Prisma and Drizzle are covered below.

npm install --save-dev @mimlet/core@0.1.0-beta.3 @mimlet/consumers@0.1.0-beta.3

1. Describe the rows as a scenario

import { createBuilder, createScenario, createSession } from '@mimlet/core';
import type { GenerationSession } from '@mimlet/core';

const products = [
  { sku: 'MUG', unitPriceCents: 1200 },
  { sku: 'TEE', unitPriceCents: 2500 },
  { sku: 'CAP', unitPriceCents: 1800 },
];

// Ids and unique columns come from a session sequence: the same for a seed,
// and never repeated while the scenario below builds rows from one session.
export const customers = createBuilder((session: GenerationSession) => {
  const number = session.sequence('customer', 1);
  return {
    id: `customer-${number}`,
    name: session.pick(['Ada Lovelace', 'Grace Hopper', 'Alan Turing', 'Katherine Johnson']),
    email: `customer-${number}@example.test`,
  };
});

// One customer, one order and its lines. Foreign keys and the total are derived.
export const shop = createScenario({ name: 'shop' })
  .node('customer', [], (_, session) => customers.build(session))
  .node('items', [], (_, session) =>
    Array.from({ length: session.integer(1, 3) }, () => ({
      ...session.pick(products),
      quantity: session.integer(1, 4),
    }))
  )
  .node('order', ['customer', 'items'], ({ customer, items }, session) => ({
    id: `order-${session.sequence('order', 1)}`,
    customerId: customer.id,
    totalCents: items.reduce((sum, item) => sum + item.quantity * item.unitPriceCents, 0),
  }))
  .node('lines', ['order', 'items'], ({ order, items }) =>
    items.map((item, index) => ({ id: `${order.id}-${index + 1}`, orderId: order.id, ...item }))
  );

export type Shop = ReturnType<typeof shop.build>;

export const shopSession = (seed: string) =>
  createSession({ seed, fingerprint: 'shop-seed/v1', provider: 'shop-fixtures@1' });

View the tested scenario.

customers is an ordinary builder. The scenario adds an order and its lines, and derives the foreign keys and the order total from the nodes they depend on, so an override of one node keeps the others consistent. Each graph matches one customer row, one order row and the line rows of that order.

Ids and the unique email come from session.sequence(). They are the same for a given seed and never repeat while the scenario builds rows from one session. Avoid Date.now(), Math.random() or database-generated keys in seed data: the rows would differ on every run and a second run could not find the first run's rows.

Each scenario node gets its own view of the session, so a node's sequence counts only that node's rows. A builder called outside the scenario, with a different session or scope, starts counting at 1 again and can repeat an id. Add rows through the scenario and the same session, as the tests below do.

2. Write a batch in one transaction

import { DatabaseSync } from 'node:sqlite';
import { persistFixtureBatch } from '@mimlet/consumers';
import type { GenerationSession } from '@mimlet/core';
import { shop, type Shop } from './seed-shop.js';

export function openShopDatabase(path = ':memory:') {
  const db = new DatabaseSync(path);
  db.exec(`
    PRAGMA foreign_keys = ON;
    CREATE TABLE IF NOT EXISTS customers (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      email TEXT NOT NULL UNIQUE
    );
    CREATE TABLE IF NOT EXISTS orders (
      id TEXT PRIMARY KEY,
      customer_id TEXT NOT NULL REFERENCES customers (id),
      total_cents INTEGER NOT NULL
    );
    CREATE TABLE IF NOT EXISTS order_lines (
      id TEXT PRIMARY KEY,
      order_id TEXT NOT NULL REFERENCES orders (id),
      sku TEXT NOT NULL,
      quantity INTEGER NOT NULL,
      unit_price_cents INTEGER NOT NULL
    );
  `);
  return db;
}

// Upsert customers and orders by id and replace each order's lines, so writing
// the same graphs again leaves the same rows. One transaction: all or nothing.
export function writeShops(db: DatabaseSync, shops: readonly Shop[]) {
  const customer = db.prepare(`
    INSERT INTO customers (id, name, email) VALUES (?, ?, ?)
    ON CONFLICT (id) DO UPDATE SET name = excluded.name, email = excluded.email`);
  const order = db.prepare(`
    INSERT INTO orders (id, customer_id, total_cents) VALUES (?, ?, ?)
    ON CONFLICT (id) DO UPDATE SET
      customer_id = excluded.customer_id, total_cents = excluded.total_cents`);
  const clearLines = db.prepare('DELETE FROM order_lines WHERE order_id = ?');
  const line = db.prepare(`
    INSERT INTO order_lines (id, order_id, sku, quantity, unit_price_cents)
    VALUES (?, ?, ?, ?, ?)`);
  db.exec('BEGIN');
  try {
    for (const graph of shops) {
      customer.run(graph.customer.id, graph.customer.name, graph.customer.email);
      order.run(graph.order.id, graph.order.customerId, graph.order.totalCents);
      clearLines.run(graph.order.id);
      for (const item of graph.lines) {
        line.run(item.id, item.orderId, item.sku, item.quantity, item.unitPriceCents);
      }
    }
    db.exec('COMMIT');
  } catch (error) {
    db.exec('ROLLBACK');
    throw error;
  }
}

// Build every graph before the first write, then hand them over in one call.
export function seedShop(db: DatabaseSync, session: GenerationSession, count: number) {
  return persistFixtureBatch(
    count,
    (_index, context) => shop.build(context.session),
    (shops, context) => {
      writeShops(context.db, shops);
      return shops;
    },
    { db, session }
  );
}

View the tested SQLite seed.

persistFixtureBatch from @mimlet/consumers builds and copies the whole batch before it calls the write function once. If generation fails, nothing has been written. The batch limit defaults to 1,000 graphs. The shared session is passed in the context, so the sequences continue across the batch.

The write function makes the seed idempotent:

Stable keys. The same seed gives the same primary keys and unique values.
Upsert parents. Customers and orders are inserted or updated by id.
Replace owned children. An order's lines are deleted and inserted again, so lines from an earlier run cannot stay behind and no longer match the total.
One transaction. A failed write rolls back the whole batch.

The seed only adds or updates rows. Rows from an earlier run with a different seed or a larger count stay in the database. To start over, delete the database file or empty the tables before seeding.

3. Seed integration tests

import assert from 'node:assert/strict';
import { test } from 'node:test';
import type { DatabaseSync } from 'node:sqlite';
import { shop, shopSession } from './seed-shop.js';
import { openShopDatabase, seedShop, writeShops } from './seed-sqlite.js';

const allRows = (db: DatabaseSync) =>
  ['customers', 'orders', 'order_lines'].map((table) =>
    db.prepare(`SELECT * FROM ${table} ORDER BY id`).all()
  );

test('the same seed writes the same rows', async () => {
  const first = openShopDatabase();
  const second = openShopDatabase();
  await seedShop(first, shopSession('integration'), 5);
  await seedShop(second, shopSession('integration'), 5);
  assert.deepEqual(allRows(first), allRows(second));
});

test('seeding twice leaves the same rows', async () => {
  const db = openShopDatabase();
  await seedShop(db, shopSession('integration'), 5);
  const before = allRows(db);
  await seedShop(db, shopSession('integration'), 5);
  assert.deepEqual(allRows(db), before);
});

test('every order total matches its lines', async () => {
  const db = openShopDatabase();
  await seedShop(db, shopSession('integration'), 5);
  const mismatched = db
    .prepare(
      `SELECT orders.id FROM orders JOIN order_lines ON order_lines.order_id = orders.id
       GROUP BY orders.id
       HAVING orders.total_cents <> SUM(order_lines.quantity * order_lines.unit_price_cents)`
    )
    .all();
  assert.deepEqual(mismatched, []);
});

test('a test adds an order for a seeded customer', async () => {
  const db = openShopDatabase();
  const session = shopSession('integration');
  const [seeded] = await seedShop(db, session, 2);
  assert.ok(seeded);
  // Continue the same session, so the new order gets the next free id.
  const repeat = shop.override('customer', () => seeded.customer).build(session);
  writeShops(db, [repeat]);
  const count = db.prepare('SELECT COUNT(*) AS n FROM orders WHERE customer_id = ?');
  assert.equal(count.get(seeded.customer.id)?.n, 2);
});

View the tested integration tests.

Each test opens its own in-memory database, so tests need no cleanup and do not share rows. To add rows for one test, continue the session that seeded the database and override the node that should point at an existing row. The new order gets the next free id and the seeded customer's id as its foreign key.

With a database server, keep the seed function and change the isolation: open a transaction per test and roll it back afterwards, or give each test worker its own schema or database. Seed inside that boundary, with a session per test.

4. Seed a local development database

// A local development seed: pass the database file as the first argument.
// Running it again with the same seed leaves the same rows.
import { shopSession } from './seed-shop.js';
import { openShopDatabase, seedShop } from './seed-sqlite.js';

const db = openShopDatabase(process.argv[2] ?? 'dev.sqlite');
const shops = await seedShop(db, shopSession('local-dev'), 20);
db.close();
console.log(`Seeded ${shops.length} customers with orders`);

View the tested development seed.

Run it after your migrations, for example from a package script. Running it again with the same seed and count leaves the same rows, so it is safe to run on every start. Use a different seed for a different data set.

Determinism

The same seed and count give the same rows for the same recipe code and package versions. Editing a factory, for example adding a pick() call, can change the data that factory produces. Adding a scenario node does not change the other nodes' values, because each node draws from its own stream. When a seeded test fails, keep its seed and count to reproduce the rows. For the general rules, including session snapshots, see sessions and replay.

Prisma and Drizzle

The scenario does not change. Its fields use the camelCase names that Prisma and Drizzle models usually have, so each node's value can often be passed as the row data directly. Only the write function changes:

Step	`node:sqlite` (tested here)	Prisma	Drizzle
One transaction	`BEGIN`, then `COMMIT` or `ROLLBACK`	`prisma.$transaction(async (tx) => { ... })`	`db.transaction(async (tx) => { ... })`
Upsert a parent	`INSERT ... ON CONFLICT (id) DO UPDATE SET`	`tx.customer.upsert({ where: { id: customer.id }, create: customer, update: customer })`	`tx.insert(customers).values(customer).onConflictDoUpdate({ target: customers.id, set: customer })`
Replace owned children	`DELETE FROM order_lines WHERE order_id = ?`	`tx.orderLine.deleteMany({ where: { orderId: order.id } })`	`tx.delete(orderLines).where(eq(orderLines.orderId, order.id))`
Insert the children	`INSERT INTO order_lines ...`	`tx.orderLine.createMany({ data: lines })`	`tx.insert(orderLines).values(lines)`

Keep the batch step: call persistFixtureBatch with your ORM client in the context and do the writes above in its write function. Drizzle on MySQL uses onDuplicateKeyUpdate instead of onConflictDoUpdate. Write parents before children so foreign keys exist when a child row is inserted.

This repository does not install Prisma or Drizzle, so the calls in this table are not run by its tests. Check them against the documentation of your ORM version.

How this is tested

pnpm test:examples, which CI runs on Node 22.18, compiles these files against packed Mimlet packages and runs the integration tests above as written. It also checks that different seeds give different rows with valid foreign keys, that a failed write rolls back, that an oversized batch writes nothing, and that the development seed gives the same rows on a second run against a database file.

See the consumers package for the persistFixtureBatch contract, and serve fixtures from MSW for using fixtures in HTTP mocks and previews.

Seed a database from scenarios ​

1. Describe the rows as a scenario ​

2. Write a batch in one transaction ​

3. Seed integration tests ​

4. Seed a local development database ​

Determinism ​

Prisma and Drizzle ​

How this is tested ​