Seeding a Staging Database with Realistic Fake Data

A staging database with five rows in every table lies to you. The list page renders. The detail page opens. A smoke test walks the happy path and turns green. Then a distribution query returns one bar, a pagination control never shows its second page, and a unique constraint that will fire in production on day one has never once been exercised.

Seeding is not a chore to be done once. It is the step that decides whether every later test is testing the application or the fixture. This guide describes what a seed actually has to satisfy, when generated data is the right input and when it is not, and how to keep a seeding script from ever running against the wrong database.

What a seed set has to satisfy

Three properties matter, and teams usually implement only the first.

Shape. Column widths, enum values, nullability and date ranges must resemble production. A status column that only ever holds active in staging hides every branch that handles the others, and a nullable foreign key that is never null hides the code path that expects a missing parent.

Uniqueness and volume. Duplicate detection, sorting, pagination and index behaviour only appear once there are enough distinct rows. A unique index is the cheapest bug detector you have, and it finds nothing while every generated email is the same string.

Referential sanity. Foreign keys catch the easy half, since a row pointing at a parent that does not exist is rejected at insert. The hard half is semantic: a country, a city and a postal code that pass every column check and are still inconsistent with each other, which fails later at a shipping or tax lookup. That class of defect is described in why test addresses and postal codes do not match.

Reproducibility sits underneath all three. A seed you cannot rebuild after a schema change is a seed nobody dares touch.

Why lorem ipsum hides bugs

Filler text looks like data and behaves nothing like it.

• Lorem ipsum contains no digits, so numeric parsing, leading zeros and normalisation paths are never exercised.
• It contains no diacritics, hyphens, apostrophes or full-width characters, so Unicode handling is untested until a real name arrives.
• It is close to constant, so sorting, trimming and duplicate detection never fire.
• It is too clean. Real records carry legacy formatting from years of imports, and the code that copes with those shapes is exactly the code that breaks.

The fix is not to make every row awkward, which produces its own noise. Use two sets: a large consistent set that gives you volume and realistic distributions, and a small deliberately awkward set of perhaps a few dozen records that carry the oddities. The small set is written by hand or pinned by key, and it is where the interesting failures live.

Generated data or masked production?

Both are fake. They fail differently.

Masked production keeps the real distribution and the real joins, which is why it is the only input that can verify a query plan or an index. It also keeps the real risk. Masking needs a lawful basis, and a substitution that looks anonymous can be reversible in a low-cardinality column: a masked postcode plus a masked birth date in a small town may still identify one person. The trade-offs are set out in synthetic versus anonymized data.

Generated data has no compliance surface at all, because no record descends from a person. It can be committed, shared with a contractor, and loaded into a laptop. What it does not have is your distribution, so its shape is your responsibility.

A workable split:

One rule sits above the table: never use generated data to prove that a migration works. Generated rows can only prove that the migration code runs; whether real identifiers survive it is a claim that requires real shapes.

How do you stop a seeding script from hitting production?

A seeding script that truncates a table is a loaded weapon, and the safety must live in the script rather than in the operator’s discipline.

Require an explicit environment argument with no default. If the argument is missing, exit before opening a connection. Then compare that argument against something the target itself reports: the host name, the database name, or a row in a small environment table that says which environment this is. Compare both names, not just one, because a copied configuration file is a common way for a staging script to end up pointed at production.

Beyond that: use credentials per environment so the staging role cannot reach production, and give the seeding role no more rights than the schema it writes. Local, CI and demo environments get generated data only. Staging gets generated data plus, where a rehearsal requires it, a masked sample. The information about which data a person may see is in GDPR and test identity data.

Pinning a record instead of committing a dump

The most useful habit in seeded environments is the pinned key. A keyed generator returns the same record for the same key, country and gender, so a test can carry a short key and expect an exact person, address and identity number on every run. The fixture is then a few characters rather than a committed dump, and a schema change is handled by regenerating rather than by rewriting rows.

Keys also make failures reproducible across people. A bug report can carry a key, a QA engineer can regenerate the exact record in the identity generator, and nobody has to send a customer’s details through a chat tool.

What happens when the seed has to run twice?

Re-running a seed is where most scripts break, and there are three honest strategies.

Truncate and reload is the simplest and the most destructive. It is fine for a single-developer database and unacceptable in an environment that other people are using at the same time, because every session loses its data.

Upsert on a natural key is the middle path. The row is inserted when its key is absent and updated when it is present, so a second run converges rather than duplicating. It requires a real natural key, which is why randomly generated identifiers are hostile to this approach: a fresh identifier on every run makes each execution look like a brand new row.

Versioned batches are the strongest option. Hash the seed input and record the hash of each batch that has already been loaded, then skip batches whose input hash has not changed. A second run becomes a no-op, and changing one batch reloads only that batch.

The workflow, step by step

Decide the cardinality first, because page boundaries and index thresholds determine how many rows a test needs to be meaningful. A hundred rows often exercises more of the application than ten thousand, if the hundred span every state the UI can display.

Then match the distribution you care about rather than generating uniformly. Real data concentrates: a few cities and a few statuses dominate. Uniform selection produces aggregates that look wrong to anyone who knows the product.

Generate whole records rather than individual columns, so the country, city, postal code and phone number come from one source and cannot contradict each other. Export in the format the loader reads, whether that is CSV, JSON, NDJSON or a SQL file. After loading, assert: emails are distinct, the validator accepts every row, and no postal code maps to more than a handful of cities.

Volume, formats and leftovers

Load in pages rather than one insert per statement, and wrap each page in a transaction of a few thousand rows. Log the offset of each committed page so an interrupted load can resume instead of restarting.

CSV needs quoting and a warning: never open the export in a spreadsheet, because it will strip leading zeros from postal codes and reformat everything that looks like a date. Write UTF-8 without a byte order mark. Use NDJSON when the volume justifies streaming rather than a single file in memory.

Check the things foreign keys cannot see. Uniqueness has to hold across batches, not only inside one. Identity columns and sequences go stale when rows are inserted with explicit identifiers, so the next real insert collides. Timestamps must be coherent: an order cannot predate the user who placed it, a subscription cannot end before it starts, and update timestamps cannot precede creation.

What generated data cannot tell you

Generated data cannot describe your own traffic. It will not reproduce the country mix you actually see, the seasonal peaks that break pagination, or the partial records left behind by abandoned sign-ups. Each of those needs either production-shaped data or a deliberate, hand-built reproduction.

The honest summary is that a seed set is a supporting actor. It makes every other test possible and it proves nothing about production on its own, and a team that knows which of the two it is holding will read its own dashboards more carefully.

Next steps

Take the seeding script you have, add the explicit environment argument and the marker comparison first, then measure the row counts against the numbers you need, and finally fix the field-level consistency that the tests have been quietly working around.

Everything described on this page is example material. The records discussed are synthetic stand-ins for real people, and no generated identifier corresponds to a document ever issued to anyone.