Database test data generator and seeding guide

Start from table constraints

Translate each required column into a generator field: Row Number or UUID for primary keys, realistic person and location fields for customers, prices and currencies for orders, and controlled custom lists for status columns. Match minimums, maximums and date ranges to the application rules instead of generating arbitrary values.

Make fixtures repeatable

Use a named seed such as checkout-schema-v3 and export the schema alongside the data. The same seed and schema produce identical rows on every machine, which makes failed tests reproducible and keeps screenshots and demos stable.

Test nullability and uniqueness

Set Blank % on optional fields to produce NULL in SQL or empty cells in CSV. Enable Unique on collision-sensitive fields such as usernames and external IDs, but keep the requested row count within the field's possible value space. Explicitly add boundary rows for constraints that random generation cannot guarantee.

Choose SQL or CSV

• SQL inserts are convenient for small and medium fixtures, source-controlled seed scripts and a database that already matches the inferred types.
• CSV is faster for bulk loaders such as PostgreSQL COPY and MySQL LOAD DATA, and separates generation from database-specific DDL.

Example PostgreSQL workflow

# Generate a seeded CSV with a header, then load it
psql mydb -c "TRUNCATE customers RESTART IDENTITY"
psql mydb -c "\copy customers FROM 'customers.csv' CSV HEADER"

Generating related tables

Each column is generated independently, so a foreign key will not match a parent row by accident. Building a related set is a two-pass job, and it is simpler than it sounds:

1. Generate the parent table first — say 500 customers with Row Number as the primary key — and export it.
2. Feed those keys back in. Create the child schema with a Custom List field for customer_id, pasting in the parent IDs. Every generated order then references a customer that exists.
3. Weight the list if you want realistic skew. A custom list can repeat values, so listing your busiest customers several times produces the hot-key distribution a uniform draw never will.

Load parents before children, or defer constraint checking for the duration of the load:

# PostgreSQL — one transaction, constraints checked at COMMIT
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
\copy customers FROM 'customers.csv' CSV HEADER
\copy orders    FROM 'orders.csv'    CSV HEADER
COMMIT;

Loading 100,000 rows quickly

Seeding that feels slow is usually the schema's fault, not the file's. In rough order of impact:

• Use COPY or LOAD DATA, not INSERT. Bulk loaders bypass per-statement overhead entirely and are commonly an order of magnitude faster. That is the main reason to prefer CSV over SQL at this size.
• Create indexes after the load. Every index is maintained on every inserted row. Dropping non-essential indexes, loading, then recreating them is usually far quicker overall.
• Load inside one transaction. Autocommit forces a flush per statement; a single transaction lets the database write once.
• Reset identity sequences afterwards. Explicit primary keys don't advance a serial sequence, so the next application insert collides. TRUNCATE … RESTART IDENTITY before loading, or setval() after it.

Keep synthetic and production data separate

Generated values are for testing, not identity, payment or address verification. Use a clearly labeled development database, avoid production credentials and review the privacy and safety methodology before sharing fixtures.

Schema checklist

• Primary and foreign-key strategy is explicit.
• Required columns never receive blanks.
• Optional columns receive a realistic null rate.
• Status values come from the application's accepted set.
• Dates cover past, present and boundary conditions.
• The seed and exported schema are versioned with the test.

Common questions

How do I generate test data that satisfies my foreign keys?

Generate the parent table first, then paste its ID column into a Custom List field on the child schema. Every child row then references a parent that exists. Repeating some IDs in that list produces realistic skew rather than a uniform one-to-one spread.

Last updated 6 September 2026