# 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](/mimlet/guide/correlated-scenarios.md), 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](#prisma-and-drizzle) are covered below.

```sh
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

```ts
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](https://github.com/JeffreyNijs/mimlet/blob/main/examples/recipes/seed-shop.ts).

`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

```ts
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](https://github.com/JeffreyNijs/mimlet/blob/main/examples/recipes/seed-sqlite.ts).

`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

```ts
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](https://github.com/JeffreyNijs/mimlet/blob/main/examples/recipes/seed-test.ts).

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

```ts
// 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](https://github.com/JeffreyNijs/mimlet/blob/main/examples/recipes/seed-dev.ts).

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](/mimlet/guide/sessions-and-replay.md).

## 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](/mimlet/packages/consumers.md) for the
`persistFixtureBatch` contract, and [serve fixtures from MSW](/mimlet/guide/mock-service-worker.md)
for using fixtures in HTTP mocks and previews.