How to Seed a Database with Fake Data (PostgreSQL, MySQL, SQLite)

A seed script is code that runs more often than almost anything else in a project — on every new laptop, every CI run, every fresh preview environment — and it is usually the least carefully written. It starts as ten hand-typed INSERTs, grows a foreign key, then a second table, then a TRUNCATE that someone forgets to run, and one day a new developer spends their first afternoon on a duplicate-key error.

A good seed has five properties:

1. Deterministic — the same rows every run, so failures reproduce.
2. Referentially sound — every foreign key points at something that exists.
3. Realistic — the distributions of real data, not uniform noise.
4. Edge-cased — the awkward values are there on purpose.
5. Idempotent — running it twice leaves the database in the same state as running it once.

Start from generated data#

The fastest way to get realistic rows is to generate them. The SQL INSERT generator writes ready-to-run scripts for PostgreSQL, MySQL, SQLite and SQL Server, and the CSV generator produces files for bulk loaders. Both build each row around one imaginary person, so names, emails and usernames agree.

In code, every mainstream Faker library can be seeded:

import { faker } from "@faker-js/faker";
faker.seed(42); // the same sequence of values on every run

from faker import Faker
Faker.seed(42)
fake = Faker()

Seeding is half the job. The other half is pinning the library version: a release that adds one surname to a word list shifts every value after it. If you cannot pin the generator, commit the generated output instead of the seed — see Seeded randomness for when to choose which.

Insert in dependency order#

Foreign keys define an order. Insert every parent before its children:

organisations → users → products → orders → order_items

Assign parent ids yourself — 1 to N — and have children pick from that range. It is simpler and more reproducible than reading generated ids back from the database.

When the order is genuinely circular (a user belongs to a team, and a team has an owner who is a user), insert one side with the foreign key set to NULL, insert the other, then UPDATE. In PostgreSQL you can also declare such constraints DEFERRABLE INITIALLY DEFERRED, so they are checked at commit rather than per statement.

The sequence gotcha#

Inserting explicit ids into a PostgreSQL serial or GENERATED BY DEFAULT AS IDENTITY column does not advance its sequence. The seed succeeds, and the application's first real insert fails:

ERROR: duplicate key value violates unique constraint "users_pkey"
DETAIL: Key (id)=(1) already exists.

Reset each sequence at the end of the seed:

SELECT setval(pg_get_serial_sequence('users', 'id'), (SELECT MAX(id) FROM users));

MySQL's AUTO_INCREMENT and SQLite's INTEGER PRIMARY KEY move past the largest inserted id on their own, so this is a PostgreSQL problem — but a very common one.

Make it realistic#

Uniformly random data makes every query look fast. Real data is skewed: a few customers place most of the orders, a few products get most of the views, most accounts are dormant. In PostgreSQL you can produce that skew in SQL, and setseed makes random() repeatable within a session:

SELECT setseed(0.42);

INSERT INTO orders (user_id, total, created_at)
SELECT
  1 + floor(1000 * power(random(), 3))::int,       -- cubing skews towards low ids
  round((5 + random() * 495)::numeric, 2),
  now() - random() * interval '365 days'
FROM generate_series(1, 20000);

With that distribution, user 1 has dozens of orders and most users have one or none — and the "orders per customer" page stops looking suspiciously fast.

Seed the edge cases on purpose#

Random generation finds edge cases eventually. A seed should contain them always. Add a handful of hand-picked rows alongside the generated ones:

• a name with an apostrophe and one with non-Latin characters (O'Brien, 张伟)
• a field at its maximum length, and one empty string where empty is allowed
• a user with no orders, and one with thousands
• a timestamp on 29 February and one at a daylight-saving transition
• an email with +tag sub-addressing

The boundary value and Unicode edge case generators produce these lists for you.

Load it fast#

For a few thousand rows, multi-row INSERT statements inside one transaction are plenty; the transaction alone is often a tenfold speed-up, especially in SQLite, where each statement outside a transaction is its own disk sync.

For hundreds of thousands of rows, use the bulk loaders with a CSV file:

-- PostgreSQL (from psql; \copy reads the file on your machine)
\copy users FROM 'users.csv' WITH (FORMAT csv, HEADER true)

-- MySQL (requires local_infile to be enabled on server and client)
LOAD DATA LOCAL INFILE 'users.csv' INTO TABLE users
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES;

Creating indexes after the bulk load, rather than before, speeds it up further on large tables.

Make it idempotent#

A seed that can only be run on an empty database will eventually be run on a full one. Start by clearing what you are about to insert:

-- PostgreSQL: resets identity sequences and follows foreign keys
TRUNCATE users, orders, order_items RESTART IDENTITY CASCADE;

In MySQL, TRUNCATE refuses tables referenced by foreign keys; delete in reverse dependency order, or temporarily set FOREIGN_KEY_CHECKS = 0 for the seed session only.

Framework seeders#

Most frameworks have a standard place for this, and it is worth using so that git clone plus one command gives a working app:

Each of these can execute a generated SQL file as well as build rows in code, so you can mix the two: reference data from a committed SQL script, volume data from a seeded generator.

Never seed from production#

Copying production into development feels like the shortcut, and it is usually the longer road. The copy is personal data, with all the obligations that brings; anonymising it properly is harder than generating data from scratch; and production rarely contains the edge cases your tests actually need. Production data in test environments covers the legal side in detail.

Generated data, seeded and committed, gives you a database that any developer can rebuild in seconds — and one that never needs to be explained to a data protection officer.