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"

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.