How Do You Generate Realistic Test Data for a Database?

July 23, 2026 7 min read by Chunky Munster

You generate realistic test data for a database by copying the structure of production data without copying the sensitive bits. That means matching schemas, constraints, relationships, and edge cases so your app behaves like it will in the real world. If you want a fast place to start, give the random data tool a spin and shape the output around your table rules.

Start with the schema, not the randomness

Random values are cheap. Useful values are not. Before you generate anything, read the table definition and map out what must always be true: required columns, unique keys, foreign keys, defaults, enums, and nullable fields.

A users table might need unique emails, usernames that fit a length limit, and dates of birth that imply a plausible age. An orders table might need user IDs that actually exist, totals that line up with line items, and timestamps that come after the account was created. If the data ignores those rules, your tests become noise.

Think of the schema as the contract. The generator should obey it, not improvise.

Make relationships look real

Most fake datasets fail because every row is isolated. Real systems are full of links: customers have orders, orders have items, invoices point to payments, and all of them live in time. If you generate each table independently, you can still import the rows, but your application logic will be testing fantasy.

Preserve referential integrity first. Generate parent rows before child rows, and reuse keys instead of inventing new ones for every record. In SQL terms, that usually means loading users before orders, products before order_items, and categories before products.

For example:

-- parent table first
INSERT INTO users (id, email, created_at)
VALUES
  (1, 'ada@example.com', '2024-01-10 09:12:00'),
  (2, 'sam@example.com', '2024-02-03 14:25:00');

-- child rows reference real parents
INSERT INTO orders (id, user_id, status, created_at)
VALUES
  (1001, 1, 'paid', '2024-01-12 11:05:00'),
  (1002, 1, 'refunded', '2024-01-15 16:40:00'),
  (1003, 2, 'pending', '2024-02-04 08:30:00');

That tiny bit of structure makes a huge difference when you test joins, filters, dashboards, and permissions.

Use realistic distributions, not flat randomness

Real data is rarely evenly spread. Most users sign up from a handful of countries, most orders are small, and most accounts are inactive. If you generate everything with a flat random distribution, your test set may look busy but still miss the patterns your code needs to handle.

This is where weighted values help. You want a lot of normal cases, a smaller number of unusual ones, and a few awkward edge cases. A customer dataset might be 80% active, 15% dormant, and 5% suspended. An address field might mostly be populated, with some nulls in the optional rows and a few malformed values for validation tests.

Use distributions that match the product. If your app serves mostly US users, do not give every record a random country code just because it feels varied. Variety is only useful when it reflects reality.

Build edge cases on purpose

Good test data does more than look plausible. It should also break things in controlled ways. That means including duplicates where uniqueness matters, empty values where the UI allows them, very long strings, boundary dates, odd Unicode, and values just inside or just outside allowed ranges.

A few examples worth including:

Emails with plus tags, subdomains, and long local parts
Names with apostrophes, accents, or non-Latin characters
Dates on leap days, month ends, and time zone boundaries
Numbers at min/max limits, zero, and negative values where allowed
Strings that exceed display width or storage limits

These cases catch bugs that normal-looking rows never will. Validation code, CSV imports, UI truncation, and search filters all tend to fail at the edges.

If you want a simple companion for generating fake dates while you do this, our guide on generating random dates for testing date range bugs covers the parts that usually go sideways.

Keep sensitive data out of the dataset

Realistic does not mean copied from production. Never clone live customer records into a shared test environment unless you have a very clear reason and proper controls. Even then, strip or replace anything sensitive: names, emails, phone numbers, addresses, tokens, session IDs, payment details, and free-text fields that might contain private information.

The safest pattern is synthetic data: values that follow the same format as production but are newly generated. A fake customer email should look like an email, but it should not belong to a real person. A phone number should fit the expected regex, but it should not route to an actual line.

Realistic test data should feel believable to code, not familiar to humans.

If compliance matters, treat test data like production-adjacent data. Store less, rotate it less often, and document where it came from and how it was produced.

Automate the generation flow

Hand-writing seed files is fine for ten rows. It gets miserable at ten thousand. Once the shape is clear, put the generation logic in a script so you can rebuild datasets on demand, adjust volumes, and version the rules alongside the app.

Most teams end up with one of three approaches:

A small SQL seed file for local development
A script in JavaScript, Python, or Go that emits CSV, JSON, or SQL
A fixture pipeline that creates related rows across multiple tables

Keep the script deterministic when you need repeatable tests. Seeding the random number generator gives you the same dataset every run, which is useful for debugging. When you need fresh data, change the seed or use a new batch ID.

import random
from datetime import datetime, timedelta

random.seed(42)

users = [
    {"id": 1, "email": "ada@example.com", "created_at": "2024-01-10"},
    {"id": 2, "email": "sam@example.com", "created_at": "2024-02-03"},
]

orders = []
for order_id in range(1001, 1006):
    user = random.choice(users)
    orders.append({
        "id": order_id,
        "user_id": user["id"],
        "status": random.choice(["pending", "paid", "refunded"]),
        "created_at": (datetime(2024, 2, 1) + timedelta(days=order_id - 1001)).isoformat(),
    })

That kind of script is boring in the right way. Boring means repeatable.

A Worked Example

Suppose you need test data for a subscription app with users, plans, and subscriptions. The bad version is three unrelated CSVs full of random text, random IDs, and dates that do not line up. It loads, but it does not help you test billing logic, active-user filters, or renewal jobs.

A better dataset starts with a few trusted parent rows, then fans out into child records that obey the rules.

users
id,email,created_at
1,ada@example.com,2024-01-10T09:12:00Z
2,sam@example.com,2024-02-03T14:25:00Z
3,jo@example.net,2024-02-11T18:03:00Z

plans
id,name,monthly_price_cents
10,Free,0
11,Pro,1900
12,Team,4900

subscriptions
id,user_id,plan_id,status,started_at,ended_at
5001,1,11,active,2024-01-12T11:05:00Z,
5002,2,12,canceled,2024-02-04T08:30:00Z,2024-03-01T00:00:00Z
5003,3,11,past_due,2024-02-12T10:20:00Z,
5004,1,12,trialing,2024-03-01T09:00:00Z,
5005,2,10,active,2024-03-02T13:45:00Z,

Now the data tells a story. User 1 upgrades, user 2 cancels, user 3 goes past due, and the plan prices make billing code easy to verify. You can test joins, status transitions, renewal logic, and UI labels without any hand-waving.

From there, add one broken row on purpose: an expired subscription with a missing ended_at, or a duplicate email to see whether your import rejects it. That is how you turn fake rows into useful coverage.

Frequently Asked Questions

How do you generate realistic test data for a database?

Start with the schema and generate data that obeys the same constraints as production. Preserve foreign keys, unique fields, value formats, and plausible date ranges. Then add a small number of edge cases so your tests cover failures as well as happy paths.

What makes test data look realistic?

Realistic data matches the patterns of the real system, not just the data types. That means believable names, valid emails, uneven distributions, and relationships between tables that actually make sense. If production has mostly active users and a few odd cases, your test data should too.

Should test data use real production records?

Usually no. Production records can leak sensitive information and create compliance problems, even in non-production environments. Synthetic data is safer because it keeps the structure and behaviour of real records without the privacy risk.

How do you seed a database with fake but valid data?

Use scripts or fixtures that insert parent records first, then child records that reference them. Keep the generator deterministic when you need repeatable test runs. If you only need a quick batch of values in the browser, use the random data tool to produce structured placeholders fast.

Wrapping Up

Good test data is not about volume. It is about matching the shape, rules, and weird corners of the real database closely enough that your app behaves honestly under test. If the rows look plausible but ignore constraints, the tests are mostly decorative.

Start with the schema, keep relationships intact, and add edge cases with intent. For quick experiments or a fast synthetic batch, try the random data tool and then tune the output until it fits your tables instead of fighting them.

That gets you data you can actually trust without spending all afternoon hand-building fixtures.

// try the tool

give the random data tool a spin →

// related reading

How Do You Generate Realistic Test Data for APIs and Databases? Pi & E Digit Generator: Generate Pi Digits for Testing How Do You Generate Fake IP Addresses for Testing Network Code?

← all posts