Making Database Seeds Deterministic Across Machines

You run the same seed script on two laptops and the tables come out different: mismatched primary keys, created_at values off by seconds, and fake names that are French on one machine and English on the other. This guide is part of database seeding and fixture parity within the environment sync, secrets and CI parity baseline. A non-deterministic seed quietly breaks everything downstream: snapshot tests that hard-code row 42, a demo that references user [email protected] at a fixed id, and any diff-based assertion that expected the same bytes in the same order. The fix is not luck — it is removing every source of entropy the seed touches: the pseudo-random number generator, the wall clock, the id generator, and the machine's locale.

The goal is a stronger property than "it usually works": byte-for-byte identical rows on every machine, every run, forever, until you deliberately change the seed data. That property is what lets you commit a golden checksum and let CI enforce it, the same way a lockfile enforces dependency parity. The rest of this guide reproduces the drift, names each entropy source, and closes them one at a time.

Diagnostic

Reproduce the drift by running the same seed twice and comparing an ordered dump of the table. A deterministic seed produces the same checksum both times; a leaky one does not.

#!/usr/bin/env bash
set -euo pipefail

seed_and_hash() {
  # Reset, seed, then dump rows in a stable order and hash them.
  psql "$DATABASE_URL" -q -c 'TRUNCATE users RESTART IDENTITY CASCADE;' >/dev/null
  node seed.js
  psql "$DATABASE_URL" -At -c \
    'SELECT id, email, full_name, created_at FROM users ORDER BY id' \
    | sha256sum | cut -d' ' -f1
}

first="$(seed_and_hash)"
second="$(seed_and_hash)"
echo "run 1: $first"
echo "run 2: $second"
[ "$first" = "$second" ] && echo "DETERMINISTIC" || echo "DRIFT DETECTED"

With a naive seed.js that calls faker.string.uuid() and new Date(), the two runs never match:

run 1: 7b41e0c2a9f3d5e18a6c4b2f0d9e7c31558a1b3c4d5e6f70819a2b3c4d5e6f70
run 2: c2f0d9e7c3155b41e0c2a9f3d5e18a6c4b2f01b3c4d5e6f70819a2b3c4d5e6f7
DRIFT DETECTED

To see which columns drift, dump both runs to files and diff them directly instead of only comparing the hash:

#!/usr/bin/env bash
set -euo pipefail
for run in a b; do
  psql "$DATABASE_URL" -q -c 'TRUNCATE users RESTART IDENTITY CASCADE;' >/dev/null
  node seed.js
  psql "$DATABASE_URL" -At -c \
    'SELECT id, email, full_name, created_at FROM users ORDER BY email' > "dump-$run.txt"
done
diff dump-a.txt dump-b.txt || true

The diff typically shows three moving parts at once: the id column (fresh v4 UUIDs on every run), the created_at column (wall-clock timestamps that advance between runs), and — if two developers on different locales run it — the full_name column, because an unpinned faker locale draws from a different name pool. Each is a distinct leak, and closing only one still leaves DRIFT DETECTED.

Root cause

A seed script is a pure function only if every input is fixed. In practice it silently reads four hidden inputs: the PRNG's internal state (faker and crypto.randomUUID() both draw from a re-seeded-per-process source), the wall clock (new Date(), Date.now(), and SQL NOW() all sample real time), the machine's locale and timezone (which change faker's data pools and how timestamps and collated strings render), and the effective insertion order (parallel inserts and unordered SELECT return rows in a database-chosen sequence). Any one of these makes the output a function of when and where it ran rather than a function of the seed data alone, so two machines diverge.

Resolution

Close each entropy source in turn, then verify the whole thing with a checksum. The steps are ordered so that after each one the diff from the Diagnostic shrinks.

1. Pin the pseudo-random number generator. Call faker.seed() with a constant before generating anything, so the same sequence of fake values comes out on every machine.

// seed.js
import { faker } from '@faker-js/faker';

const SEED = 42;
faker.seed(SEED);           // fixes the PRNG sequence
faker.setDefaultRefDate('2024-01-01T00:00:00.000Z'); // fixes faker's "now"

export function makeUser(i) {
  return {
    email: `user${i}@example.com`,   // stable, not faker.internet.email()
    fullName: faker.person.fullName(),
  };
}

Reset the seed at the start of every run, not once at import, because any prior faker call — even in a helper you imported — advances the generator. Prefer stable natural keys like user${i}@example.com for anything you assert on later; reserve faker for fields whose exact value does not matter, and let the fixed seed make even those reproducible.

2. Freeze the clock. Replace every new Date(), Date.now(), and SQL NOW() with a single fixed reference instant so timestamps stop advancing between runs.

// A single frozen instant shared by the whole seed.
export const FROZEN_NOW = new Date('2024-01-01T00:00:00.000Z');

export function makeUserRow(i) {
  const u = makeUser(i);
  return { ...u, id: deterministicId(u.email), createdAt: FROZEN_NOW };
}

In the SQL layer, never let the database supply the time. Pass the frozen timestamp as a bound parameter and drop any DEFAULT now() from the insert path used by the seed, so the row's created_at is data you control rather than the moment the insert happened to run.

3. Generate deterministic ids. Swap random v4 UUIDs (crypto.randomUUID(), gen_random_uuid()) for UUIDv5, which hashes a fixed namespace plus a stable name into the same id every time.

import { v5 as uuidv5 } from 'uuid';

// A constant namespace you pick once and never change.
const NS = '6ba7b810-9dad-11d1-80b4-00c04fd430c8';

export function deterministicId(name) {
  return uuidv5(name, NS); // same input name -> same UUID, on every machine
}

UUIDv5 is a pure function of (namespace, name), so deterministicId('[email protected]') yields an identical id on a Mac, a CI runner, and a WSL2 box. If you prefer integer keys, assign them explicitly from the loop index and insert with OVERRIDING SYSTEM VALUE rather than relying on an auto-increment sequence, whose next value depends on prior inserts.

4. Pin the locale and timezone. Faker's default locale and the process timezone both change output; fix them at the environment level so string pools and timestamp rendering are identical everywhere.

# compose.yaml — a one-shot seeding job with a pinned environment
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: seed
      TZ: UTC
      PGTZ: UTC
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 3s
      retries: 10
  seed:
    build: .
    depends_on:
      db:
        condition: service_healthy
    environment:
      DATABASE_URL: postgres://postgres:seed@db:5432/postgres
      TZ: UTC
      LANG: C.UTF-8
      LC_ALL: C.UTF-8
      FAKER_LOCALE: en
    command: ["node", "seed.js"]

In seed.js, construct faker from an explicit locale rather than the ambient default: new Faker({ locale: [en] }). TZ=UTC and LC_ALL=C.UTF-8 remove two machine-specific inputs at once — timestamp formatting and collation — so a developer in Berlin and a runner in us-east-1 see the same bytes.

5. Force a stable order. Insert in a deterministic sequence and always read back with an explicit ORDER BY, so the checksum does not depend on which row the database returned first.

#!/usr/bin/env bash
set -euo pipefail
# Always hash an explicitly ordered projection, never a bare SELECT *.
psql "$DATABASE_URL" -At -c \
  'SELECT id, email, full_name, created_at FROM users ORDER BY id' \
  | sha256sum | cut -d' ' -f1

Parallel inserts are the subtle version of this bug: if your seed fans out with Promise.all, rows land in a nondeterministic order and any sequence-based id drifts. Seed in a single ordered transaction, or sort the batch by its natural key before inserting, so the id assignment and the on-disk order are both reproducible.

Expected output

With all five entropy sources closed, the Diagnostic's two runs produce the same checksum, and the diff between dumps is empty:

run 1: 4f9a1c7e2b8d0f63a5c4e19b7d2f08e6c3a1b4d5e6f70819a2b3c4d5e6f70819
run 2: 4f9a1c7e2b8d0f63a5c4e19b7d2f08e6c3a1b4d5e6f70819a2b3c4d5e6f70819
DETERMINISTIC

The ids are stable UUIDv5 values, created_at reads 2024-01-01 00:00:00+00 on every row, and the fake names are identical because faker replays the same seeded sequence in the same locale. Because the checksum is now a property of the fixture data alone, you can commit it as a golden value and treat any change as a deliberate edit to review — the same discipline you would apply when reproducing CI-only test failures locally so that a local run and a CI run agree on the bytes.

Prevention

1. Gate on a golden checksum in CI. Store the expected hash and fail the build when the seed drifts, so a reintroduced new Date() is caught before merge.

#!/usr/bin/env bash
set -euo pipefail
EXPECTED="$(cat seed.sha256)"
psql "$DATABASE_URL" -q -c 'TRUNCATE users RESTART IDENTITY CASCADE;' >/dev/null
node seed.js
ACTUAL="$(psql "$DATABASE_URL" -At -c \
  'SELECT id, email, full_name, created_at FROM users ORDER BY id' \
  | sha256sum | cut -d' ' -f1)"
if [ "$ACTUAL" != "$EXPECTED" ]; then
  echo "seed drift: expected $EXPECTED got $ACTUAL" >&2
  exit 1
fi
echo "seed checksum verified"

2. Pin the generator's version. Faker changes its data pools between releases, so an unpinned @faker-js/faker can silently alter names even with the seed fixed. Commit a lockfile and pin the exact version, and pin the Postgres image tag (postgres:16) so ICU collation does not shift underneath you.

3. Ban ambient time and randomness in the seed path with a lint rule that flags Date.now, new Date() without an argument, crypto.randomUUID, and Math.random inside seed/. Import the frozen clock and the deterministic id helper instead, so the invariant is enforced mechanically rather than by reviewer memory.

The measurable payoff is the collapse in mismatched rows once each source is closed. The chart counts drifting columns in a 200-row seed as the fixes land cumulatively.

Platform caveats

WSL2: the distro's default LANG is often unset, so faker and Postgres collation fall back to C inconsistently between the Windows host and the Linux guest. Export LC_ALL=C.UTF-8 and TZ=UTC in the seed job's environment, and mark seed.sha256 as text eol=lf in .gitattributes so a Windows editor cannot flip the golden checksum's line ending and fail the gate.

macOS (Docker Desktop): containers inherit UTC by default, but a seed run outside the container picks up the host timezone, so NOW() and unpinned Date values differ from CI. Always run the seed inside the pinned Compose job, or export TZ=UTC in your shell before running it locally, so host and CI sample the same clock.

Apple Silicon (ARM64): the bundled ICU version can differ from an x86 CI image, changing how locale-collated strings sort under ORDER BY full_name. Order the checksum query by the immutable id (a UUIDv5) rather than by a collated text column, so the row order is independent of the platform's Unicode collation tables.

Rollback

If the golden-checksum gate blocks a merge because you intentionally changed the fixture data, regenerate and commit the new hash rather than disabling the check: run the seed once, write the new value with ... | sha256sum | cut -d' ' -f1 > seed.sha256, and commit it in the same change as the data edit so the diff shows both together. If a fix itself is the problem — say pinning the locale broke an assertion that depended on the old names — revert that single step and re-baseline the checksum, keeping the other four pins in place so you never fall back to fully random output.

Frequently Asked Questions

Related

• Reproduce CI-only test failures locally
• Keep seed data in sync with schema migrations
• Enforce runtime parity between local and CI
• Pin toolchain versions across machines