How to check seed data in test database

Before blaming the test, verify the seed. node verify.mjs test.db prints a row count per table plus orphan and duplicate counts, so a failure names the state rather than the assertion. Then run the seed twice to see whether it is idempotent, and run the suite in a different file order.

Why check this

Run this after any change to the seed script or the schema, and the first time a suite starts failing for one engineer and passing for another. The failure it prevents: a test asserts that a customer has two orders, an earlier test deleted one of them, and the report blames the assertion. Nothing in that report mentions the database, so the investigation starts in the wrong file.

Three questions cover most seed defects. Did the seed run at all. Does running it twice produce the same database. Does a test leave the database as it found it. A red suite where the answer to the first is no looks the same as a red suite where the code is broken.

Prerequisites

• Node 22. node:sqlite ships SQLite 3.51.3 here and prints an experimental warning on every run.
• The sqlite3 command line client, used in step 5 as a second opinion. The one on this machine is the Android platform tools build, 3.50.6.
• vitest 5.0.0, invoked from the repository node_modules, and the fixture below.

// seed.mjs
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(process.argv[2] ?? 'test.db');
db.exec(`
  CREATE TABLE IF NOT EXISTS customer (id INTEGER PRIMARY KEY, email TEXT NOT NULL);
  CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customer(id),
    ref TEXT NOT NULL);
`);
const addCustomer = db.prepare('INSERT INTO customer (email) VALUES (?)');
for (const email of ['ada@example.test', 'grace@example.test']) addCustomer.run(email);
const addOrder = db.prepare('INSERT INTO orders (customer_id, ref) VALUES (?, ?)');
addOrder.run(1, 'ORD-1');
addOrder.run(1, 'ORD-2');
addOrder.run(2, 'ORD-3');
for (const t of ['customer', 'orders']) {
  console.log('%s=%d', t, db.prepare(`SELECT COUNT(*) AS n FROM ${t}`).get().n);
}

// verify.mjs, one line per invariant, exit 1 if any fails
const one = (sql) => Object.values(db.prepare(sql).get())[0];
const checks = [
  ['customer rows', one('SELECT COUNT(*) FROM customer'), 2],
  ['orders rows', one('SELECT COUNT(*) FROM orders'), 3],
  ['orphan orders', one('SELECT COUNT(*) FROM orders o LEFT JOIN customer c ON c.id=o.customer_id WHERE c.id IS NULL'), 0],
  ['duplicate emails', one('SELECT COUNT(*) FROM (SELECT email FROM customer GROUP BY email HAVING COUNT(*)>1)'), 0],
  ['duplicate order refs', one('SELECT COUNT(*) FROM (SELECT ref FROM orders GROUP BY ref HAVING COUNT(*)>1)'), 0],
];
for (const [name, got, want] of checks) {
  console.log(`${got === want ? 'ok  ' : 'FAIL'} ${name.padEnd(20)} got=${got} want=${want}`);
}

// vitest.config.mjs and seed-setup.mjs
export default { test: { globalSetup: './seed-setup.mjs', fileParallelism: false } };

export function setup() {
  rmSync('test.db', { force: true });
  execFileSync(process.execPath, ['seed.mjs', 'test.db']);
}

SQLite is the database on this machine, so the counts and the constraint behaviour below are SQLite results. The method transfers to PostgreSQL or MySQL; the exact pragma names do not.

Steps

1. 1

   Step 1.

   Run the seed against an empty file and read what it inserted.

   node seed.mjs test.db
   

   customer=2
   orders=3
   (node:27604) ExperimentalWarning: SQLite is an experimental feature and might change at any time
   …

2. 2

   Step 2.

   Check the invariants, not only that the script exited zero.

   node verify.mjs test.db
   

   ok   customer rows        got=2 want=2
   ok   orders rows          got=3 want=3
   ok   orphan orders        got=0 want=0
   ok   duplicate emails     got=0 want=0
   ok   duplicate order refs got=0 want=0

3. 3

   Step 3.

   Run the same seed a second time, the way a re-run of a CI job would.

   node seed.mjs test.db
   

   customer=4
   orders=6
   …

4. 4

   Step 4.

   Verify again. The database is now a different database.

   node verify.mjs test.db
   

   FAIL customer rows        got=4 want=2
   FAIL orders rows          got=6 want=3
   ok   orphan orders        got=0 want=0
   FAIL duplicate emails     got=2 want=0
   FAIL duplicate order refs got=3 want=0

5. 5

   Step 5.

   Insert a row that points at a customer who does not exist, through the command line client.

   sqlite3 test.db "PRAGMA foreign_keys; INSERT INTO orders (customer_id, ref) VALUES (999, 'ORD-X'); SELECT changes();"
   

   0
   1

   The first line is the pragma, the second is the row count the insert changed.

6. 6

   Step 6.

   Run the same insert through the client the tests use.

   node -e "const {DatabaseSync}=require('node:sqlite');const db=new DatabaseSync('test.db');console.log('foreign_keys =',db.prepare('PRAGMA foreign_keys').get().foreign_keys);try{db.exec(\"INSERT INTO orders (customer_id, ref) VALUES (999, 'ORD-Y')\");console.log('insert ok')}catch(e){console.log(e.constructor.name+':',e.message)}"
   

   foreign_keys = 1
   Error: FOREIGN KEY constraint failed
   …

7. 7

   Step 7.

   Reseed and run both test files in one order.

   // tests/a-orders.test.mjs asserts the seeded state
   expect(db.prepare('SELECT COUNT(*) AS n FROM orders WHERE customer_id = 1').get().n).toBe(2);
   
   // tests/b-cancel.test.mjs mutates it and never restores it
   db.exec("DELETE FROM orders WHERE ref = 'ORD-2'");
   

   npx vitest run --sequence.shuffle.files --sequence.seed=1 --reporter=verbose
   

         Running tests with seed "1"
   …
   ✓ tests/a-orders.test.mjs > ada has two seeded orders 2ms
   ✓ tests/b-cancel.test.mjs > cancelling an order removes it 6ms
   
   Test Files  2 passed (2)
       Tests  2 passed (2)

8. 8

   Step 8.

   Run the same two files in the other order. The seed is identical.

   npx vitest run --sequence.shuffle.files --sequence.seed=3 --reporter=verbose
   

         Running tests with seed "3"
   …
   ✓ tests/b-cancel.test.mjs > cancelling an order removes it 7ms
   × tests/a-orders.test.mjs > ada has two seeded orders 7ms
    → expected 1 to be 2 // Object.is equality
   …
   Test Files  1 failed | 1 passed (2)
       Tests  1 failed | 1 passed (2)

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | got=0 want=2 on a row count | The seed did not run, or ran against another file | Fix the connection string before reading any assertion | | Counts double on the second run | The seed is not idempotent | Truncate first, or insert with a unique business key and ignore conflicts | | duplicate emails got=2 | Two rows share the business key | Add the unique constraint the production schema has | | orphan orders above zero | A child row points at a missing parent | Check whether the client enforces foreign keys at all | | Same seed, different verdict by order | A test mutated seeded rows and left them | Wrap each test in a transaction and roll back, or reseed per file |

Step 4 is worth reading line by line. Two checks report a doubled count, two report duplicates, and the orphan check stays green, because inserting a second copy of every row breaks no reference. Row counts alone would have missed the duplicates, and the orphan query alone would have reported a healthy database. The set of invariants matters more than any single number.

Steps 5 and 6 show two clients disagreeing about the same file. The command line client reports foreign_keys as 0 and accepts the orphan, while node:sqlite reports 1 and refuses the identical statement. SQLite leaves foreign key enforcement off by default and each connection turns it on for itself, so a seed loaded by one client can contain rows the test client would have rejected. Confirm which client your seed runs under before trusting a clean orphan count.

Steps 7 and 8 are the same suite, the same seed data and the same machine. Only the file order changed, and the file order is what --sequence.shuffle.files exists to vary.

Common mistakes

What to check next

• How to check test isolation: the runner side of the order dependency in step 8.
• How to detect parallel test interference: two workers on one database file produce the same symptom without any ordering.
• How to check duplicate rows in sql: the query behind the duplicate invariants.
• How to check foreign key: why the orphan count can stay at zero while the constraint is off.
• How to verify row counts after migration: the same counting discipline applied to a schema change.

FAQ

What is the difference between fixtures and seed data?

Seed data is loaded once before a suite and is meant to stay untouched: reference rows, a few accounts, a catalogue. A fixture is built by one test for that test and thrown away. Treating a fixture as seed data is what produces step 8.

How do I load fixtures in tests without slowing the suite down?

Load the shared rows once in a global setup, as seed-setup.mjs does here, and create per test rows inside a transaction that rolls back. Reseeding the whole database per test is correct and is usually the reason a suite takes minutes.

How do I verify the seed ran before the tests start?

Query it. One SELECT COUNT(*) per table plus the orphan and duplicate checks takes milliseconds and turns a mystery failure into a named one. Run it in global setup and fail the run there rather than in the first assertion.

Should the test database keep the production constraints?

Yes. A test database without the unique index that production has accepts seed data production would reject, and the suite then passes on rows that can never exist. Step 4 finds exactly that case.

Verified