Seed data and throwaway test databases

Every database task in this course started from a loaded database — the Kothrud library, the tiffin service. Somebody had to create that data. This lesson is how to do it well, because seed data and test databases are where a surprising amount of a team's daily friction lives, and a little discipline removes most of it.

Two related problems: getting useful data into a development database, and giving automated tests a clean, fast, isolated database to run against.

Seed data: what it is for

Seed data is the sample data a developer loads to work against. Good seed data means a new teammate runs one command and has a realistic system to click around in. Bad or absent seed data means everyone invents their own, hits different bugs, and cannot reproduce each other's.

Make it a single command, checked into the repository:

psql -U postgres -d library_dev -f seed/seed.sql
# or: python manage.py loaddata seed.json
# or: npm run seed

Four properties separate seed data that helps from seed data that rots.

Realistic in shape. Indian names, real pincodes, plausible dates, ₹ amounts in the right range — this course's data is a model. Data that looks real surfaces real problems: a name with an apostrophe, a very long address, a member with no email. foo/bar/test123 hides all of them.

Deliberately messy at the edges. The library seed has members with no email, loans never returned, a book nobody borrowed. Those edge cases are the point — they are where the NULL handling, the LEFT JOIN and the empty-result bugs show up. Seed data where every row is complete tests only the happy path.

Idempotent, or trivially resettable. Running it twice should not create duplicates or error. Either guard the inserts:

INSERT INTO plans (code, name) VALUES ('veg', 'Vegetarian')
ON CONFLICT (code) DO NOTHING;

or — usually simpler for a dev database — reset first:

TRUNCATE members, books, authors, loans RESTART IDENTITY CASCADE;
-- then insert

RESTART IDENTITY resets the id sequences so you get the same ids every run, and CASCADE truncates dependent tables in the right order. Predictable ids make the data much easier to work with.

Insert in dependency order. Authors before books, books and members before loans. A foreign key will otherwise reject the child row, and this ordering is the first thing that breaks when someone adds a table.

Generating a lot of it

Hand-writing 20 rows is fine. For volume — the 500,000-row table in the performance module — generate it. In pure SQL, generate_series is the tool:

INSERT INTO members (name, email, joined)
SELECT
  'Member ' || g,
  'member' || g || '@example.com',
  DATE '2024-01-01' + (random() * 700)::int
FROM generate_series(1, 10000) AS g;

For realistic values rather than Member 1, a library helps — Faker (Python) and Faker.js are the standard, with Indian locales (Faker('en_IN')) that produce plausible names, addresses and phone numbers:

from faker import Faker
fake = Faker("en_IN")
rows = [(fake.name(), fake.email(), fake.date_between("-2y", "today")) for _ in range(10000)]
cur.executemany("INSERT INTO members (name, email, joined) VALUES (%s, %s, %s)", rows)

Two performance notes that matter at volume:

Batch the inserts. executemany, or psycopg's faster cursor.copy, or COPY from a file. Ten thousand individual INSERT statements is ten thousand round trips; one COPY is one.
Add indexes after the bulk load, not before, so the index is built once at the end rather than maintained on every insert — the write-cost lesson from the performance module, applied.

Do not use production data as seed data. It contains real people's names, emails, phone numbers and payment details. Copying it into a development database — which is less protected, on laptops, in logs — is a privacy breach and in many places illegal (GDPR, India's DPDP Act). If you need production-shaped data, anonymise it: replace names and emails with fakes, keep the shapes and distributions. Never the real values.

Test databases: the requirements

Automated tests need a database that is:

Isolated — tests must not see each other's data, or the order they run in changes the result.
Clean — each test starts from a known state.
Fast — tests run thousands of times a day; a slow database makes the whole suite slow, and a slow suite stops being run.
Like production — SQLite in tests and PostgreSQL in production means tests pass on behaviour the real database rejects. Test against the same engine you deploy.

The pattern that gives you all four: transaction rollback

The technique worth taking from this lesson. Wrap each test in a transaction and roll it back at the end instead of committing:

import pytest, psycopg

@pytest.fixture
def db():
    conn = psycopg.connect(TEST_DSN)
    conn.autocommit = False
    yield conn
    conn.rollback()        # undo everything the test did
    conn.close()

def test_borrowing_reduces_available(db):
    db.execute("INSERT INTO loans (book_id, member_id, borrowed_on, due_on) "
               "VALUES (1, 1, CURRENT_DATE, CURRENT_DATE + 14)")
    n = db.execute("SELECT count(*) FROM loans WHERE book_id = 1").fetchone()[0]
    assert n >= 1
    # no commit — the rollback in the fixture erases this insert

The insert is never committed, so the next test sees a pristine database. This is fast — a rollback is far cheaper than deleting rows or recreating the schema — and perfectly isolated. Django's TestCase and pytest-django's db fixture do exactly this for you; SQLAlchemy has the same pattern with a nested transaction.

The one limit: code under test that commits or manages its own transactions does not fit cleanly, because your outer rollback and its commit fight. For those, use a savepoint (a nested transaction the test rolls back to) or fall back to truncating between tests.

Faster still: template databases and tmpfs

Two PostgreSQL-specific tricks for the test suite.

Template databases. Build the schema and reference data once into a template, then create a fresh database from it per test run — a file copy, much faster than replaying migrations:

CREATE DATABASE test_run TEMPLATE library_template;

An in-memory data directory. Point a test-only PostgreSQL at a RAM disk (tmpfs), and turn off durability — fsync = off, synchronous_commit = off. Losing the data on crash is fine because it is test data. This can dramatically speed up a write-heavy suite. Only ever for tests — those settings risk real corruption on a database you care about.

The library's own seed, as a worked reference

The data this course used is a good template to imitate. It has:

A small, fixed core — 20 authors, 40 books, 28 members — so examples are reproducible and results are quotable ("one book, Swami and Friends, was never borrowed").
Deliberate holes — members without email, unreturned loans, an unborrowed book — chosen so the tricky query lessons had something to bite on.
Enough loans (180) to make aggregates interesting without being unwieldy to scan by eye.
A separate large table (big, 500,000 rows) generated with generate_series, kept apart from the readable core, purely for the performance lessons.

Small and hand-crafted for teaching and clarity; large and generated for performance. Most projects want both: a readable fixture you can reason about, and a bulk generator for load and performance work.

Check your work

What seed data is for. A realistic development database from one command, shared across the team.

Why realistic shape matters. Real-looking data surfaces real bugs — apostrophes, long values, missing fields — that foo/bar hides.

Why deliberate messiness helps. The NULLs and empty relations are where the interesting bugs live; all-complete data tests only the happy path.

Two ways to make a seed idempotent. ON CONFLICT DO NOTHING, or TRUNCATE ... RESTART IDENTITY CASCADE first.

What RESTART IDENTITY and CASCADE do. Reset id sequences for stable ids; truncate dependent tables in order.

Why insert in dependency order. Foreign keys reject a child before its parent exists.

The tool for generating volume in SQL. generate_series.

The library for realistic values. Faker, with a locale like en_IN.

Two speed rules for bulk loading. Batch with COPY/executemany, and add indexes after the load.

Why not to use production data as seed data. It is real personal data; copying it to a less protected environment is a privacy breach and often illegal. Anonymise instead.

Four requirements of a test database. Isolated, clean, fast, and the same engine as production.

The rollback pattern. Wrap each test in a transaction and roll it back; fast and perfectly isolated.

Its one limitation. Code that commits or manages its own transactions — use savepoints or truncation.

Two PostgreSQL tricks for faster suites. Template databases, and a tmpfs data directory with durability off — tests only.

Practice

Write a seed.sql for a small schema of yours and load it with one command.
Run it twice. Fix it so the second run neither duplicates nor errors.
Add TRUNCATE ... RESTART IDENTITY CASCADE and confirm ids are identical across runs.
Deliberately add edge cases — a NULL email, an empty relation — and write a query that only behaves correctly because they are there.
Reorder the inserts so a child comes before its parent. Read the foreign-key error.
Generate 10,000 rows with generate_series.
Generate 10,000 with Faker and an en_IN locale. Compare how the two look.
Load 100,000 rows with individual inserts, then with COPY. Time both.
Build the same data with indexes present, then added afterwards. Compare load time.
Write a pytest fixture that rolls back after each test. Prove two tests do not see each other's data.
Make a test call code that commits internally and watch the rollback pattern fail. Fix it with a savepoint.
Create a template database and time creating a test database from it versus replaying your migrations.
Take a table of "production" data and write an anonymiser that keeps the shape but replaces every personal value.

Official documentation

PostgreSQL — generate_series — Generating rows for bulk data.
PostgreSQL — COPY — The fast bulk-load path.
PostgreSQL — TRUNCATE — RESTART IDENTITY and CASCADE.
PostgreSQL — Template databases — Creating a database from a template.
PostgreSQL — Non-durable settings — fsync and synchronous_commit off, for test databases only.
Faker — Realistic fake data, with locales.
pytest-django — Database access — The transaction-rollback test pattern, done for you.

Next module: document databases in depth.