Seed data and fixtures for local databases: small, idempotent and versioned with the schema

Goal

Every developer, agent and CI job can create a local database with enough realistic, consistent data to run the application and its tests, from one command, in seconds, without a copy of production.

Prerequisites

Schema migrations under version control and a local database that can be dropped and recreated freely (a container is fine; see the related article on ephemeral databases). A classification of tables into reference data (countries, roles, plans), sample data (a few users, orders, documents) and large history that local work does not need.

Steps

1. Separate the three kinds. Reference data belongs with migrations or a seed step that runs in every environment including production. Sample data is for development and demos. Test-specific rows are created inside the tests by builders or fixtures, never by the global seed.
2. Write the seed as idempotent code. The Rails guide says of db/seeds.rb that the code should be idempotent so that it can be executed at any point in every environment; use upserts keyed on natural identifiers (email, slug), not bare inserts.
3. Keep the sample set small and named: a handful of users with known credentials and one record in each interesting state (a paid order, a refunded one, a suspended account). List the names in the README so that "log in as alice@example.test" is a known starting point.
4. Use the format the stack supports: Django's loaddata reads fixture files that dumpdata produces; other stacks use SQL files, CSV with COPY, or a script in the application language. Prefer the application language when rows must pass validations and hooks.
5. If realistic volume is needed, dump production with pg_dump --exclude-table-data for sensitive or huge tables, anonymise the rest in a separate step, and store the result outside the repository with an expiry date. Never seed developer machines from raw production data.
6. Wire the seed into the setup script and into the CI database step, after migrations in both; a seed broken by a new column is then found the same day.
7. Make the seed part of review whenever the schema changes: a migration that adds a required column also updates the seed.

Expected result

One task drops, migrates and seeds; two machines seeded from the same commit hold identical sample data, and tests do not depend on rows they did not create.

Limits and test basis

Seeds replace the empty database, not test data builders. Generated identifiers differ between runs unless the seed sets them explicitly. Anonymisation is its own discipline and is not covered here. The steps are a synthesis of the cited documentation; no timing claims are made.