Skip to content

Database Seeding

Populate databases with initial and test data using seed scripts and fixtures.

What you'll learn

  • What seeding is and why it is essential for development
  • Writing seed scripts with INSERT statements
  • Using fixtures and faker libraries for test data
  • Idempotent seeding — safe to run repeatedly
  • Seeding for development vs production environments

Concept

What Is Seeding?

Seeding is the process of populating a database with initial data — reference data, demo data, or test data. When a new developer joins the team, they create the database schema and run the seed script to have a working database with realistic data. Seeding is also used in automated tests to set up a known state before each test run.

Reference Data vs Demo Data

Reference data is required for the application to function — a list of countries, currencies, user roles, or product categories. This data is part of the schema and should be seeded in all environments. Demo data is sample data for development and testing — fake users, sample orders, test products. This should only be seeded in development and staging, never in production.

Writing Seed Scripts

A seed script is typically a SQL file or a program (Node.js, Python) that inserts rows. For reference data, write explicit INSERT statements with known values. For demo data, use a faker library to generate realistic random data — names, emails, addresses, dates. Generate enough data to test pagination, search, and performance (e.g. 10,000 users, 100,000 orders).

Idempotent Seeding

A seed script should be idempotent — safe to run multiple times without creating duplicates. Use INSERT ... ON CONFLICT DO NOTHING (PostgreSQL) or INSERT IGNORE (MySQL) to skip existing rows. Alternatively, start the script with TRUNCATE to clear the table before inserting. Idempotency lets you re-seed after schema changes without manual cleanup.

Seeding in Practice

Most frameworks have a seeding system: Rails uses db/seeds.rb, Django uses data migrations or management commands, Prisma uses prisma db seed, Drizzle ORM has seed scripts. The pattern is the same: define the data in code, run a command, and the database is populated. Keep seed scripts in version control alongside the schema.

Example

              -- Reference data: user roles (idempotent)
INSERT INTO roles (name, description) VALUES
  ('admin',    'Full access to all features'),
  ('editor',   'Can edit content'),
  ('viewer',   'Read-only access')
ON CONFLICT (name) DO NOTHING;

-- Reference data: product categories
INSERT INTO categories (name, slug) VALUES
  ('Electronics', 'electronics'),
  ('Books',       'books'),
  ('Clothing',    'clothing')
ON CONFLICT (slug) DO NOTHING;

-- Demo data: sample users (idempotent with fixed IDs)
INSERT INTO users (id, name, email, role_id) VALUES
  (1, 'Ada Lovelace',  'ada@example.com',  1),
  (2, 'Alan Turing',   'alan@example.com', 2),
  (3, 'Grace Hopper',  'grace@example.com', 2)
ON CONFLICT (id) DO UPDATE SET
  name  = EXCLUDED.name,
  email = EXCLUDED.email;

-- Demo data: sample products
INSERT INTO products (name, price, category_id, in_stock) VALUES
  ('Wireless Mouse',  29.99, 1, TRUE),
  ('SQL Book',        39.99, 2, TRUE),
  ('Cotton T-Shirt',  19.99, 3, FALSE)
ON CONFLICT DO NOTHING;
            

Idempotent seed script: reference data (roles, categories) and demo data (users, products) using ON CONFLICT to avoid duplicates on re-runs.

Try it

  • SQL Formatter

    Format seed scripts with multiple INSERT statements.

  • UUID Generator

    Generate UUIDs for seed data with unique identifiers.

Common mistakes

The mistake

Running demo seed data in production

The fix

Separate reference data (needed in all environments) from demo data (development only). Use environment checks to prevent demo data from running in production.

The mistake

Non-idempotent seeds that create duplicates on re-run

The fix

Use ON CONFLICT DO NOTHING or ON CONFLICT DO UPDATE, or TRUNCATE before inserting. Seeds should be safe to run multiple times.

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors