DEMO-08-SEEDOCT 2026

Initialize a development or test database from labelled datasets.

Seed data is the data used to initialize the database instances that an application or a test suite runs against. A label identifies seed data, and multiple labels can be used to compose datasets for different scenarios or regression tests.

Stack: PostgreSQL + Docker + git
Change: one column renamed, orders.total to amount; rows under the labels baseline and issue-4412
Reverse: none shown

Chapters

01 Capture the baseline and initialize a database from it31 secCapture the baseline seed data under the label baseline, render it with the schema as one SQL file, and start a container from that file. 02 Layer the data for a regression test over the baseline37 secAdd the data that reproduces a bug as a dataset under the label issue-4412, then initialize a database for the regression test with that dataset layered over the baseline. 03 Rename a column and initialize a database from the same datasets30 secRename a column that both datasets fill. The data files stay as they were captured, and a database initialized from the same two labels has every row under the new name. 04 Run it end to end1 min 51 secCapture a shared dataset as the baseline and start a container from the rendered file. Add a fixture for one regression test and initialize a database with it layered over the baseline. Then rename a column, and initialize a database from the same datasets.

Summary

Your development and test databases are initialized from a SQL file somebody loads: a shared dump, a seeds.sql, a fixtures directory.

Teams that develop database applications need a common, shared dataset. It may have been built up by hand, or be a sanitized copy of a production database, and is often called a gold copy. Here it is captured into data files under the label baseline and committed with the schema. A fixture is a smaller dataset for one scenario, and the data that reproduces one bug is added as a fixture under the label issue-4412. Then a column that both datasets fill is renamed.

A database instance is initialized with the datasets named on snapshot: baseline alone for development, baseline then issue-4412 for one regression test. Each dataset is kept in its own data files, so adding a fixture changes no other dataset. The data files are committed with the schema’s log, so a column renamed in the log is renamed in every dataset when the SQL is rendered, and no data file is edited.

pg_dump reads the database that holds the shared dataset and psql applies what snapshot emits; retrofit connects to no database. A copy of production is sanitized by other tools before it is captured. Capturing again records the baseline as it is then, and tests that build their rows in code with a factory run on top of it.

Chapter 01Capture the baseline and initialize a database from it

Where does a new development or test database get its rows?

The shared dataset is in a running database. pg_dump reads that database, and data capture --label baseline records the 3 customers and 5 orders as a dataset: one data file for each table, pinned to the log. The capture prints seed[baseline]: seed is the tool’s word for test data, and a label names a dataset.

snapshot --label baseline renders the schema and that dataset as one SQL file. The data files are committed with the log, and the rendered file is output that git ignores.

The official postgres image runs every SQL file in its init directory the first time a container starts, so a container with that directory mounted comes up with the baseline’s rows under the keys they were captured with, and with each identity sequence set past them. A new container from the same file is how a database gets back to the baseline.

Test harnesses that start a database for a test suite, or for each test, support initializing the instance from a file in the same way.

31 sec space to pause f for fullscreen

Read transcript

seed-data-demo: baseline

Generated from the demo script. Every command below is run verbatim by the recording.

1/3 capture the baseline

our team shares one dataset, kept in a database. pg_dump reads that database, and retrofit captures the rows as a dataset, in data files under the label baseline:

pg_dump --data-only --column-inserts --schema=shop $DATABASE_URL | retrofit data capture - --label baseline

seed is the tool’s word for test data, and a label names a dataset.

2/3 render one file

snapshot renders the schema and the dataset we name as one sql file, into the init directory we mount in our database container:

retrofit snapshot --label baseline > initdb/01-baseline.sql

grep -e '^CREATE TABLE' -e '^-- Data:' initdb/01-baseline.sql

the two tables, then each table’s rows, marked as test data with the label they belong to.

3/3 start a database

the postgres image runs every sql file in its init directory the first time a container starts. we mount ours:

docker run -d --name shop-db -p $PGPORT:5432 -e POSTGRES_DB=shop -e POSTGRES_PASSWORD=pw -v $PWD/initdb:/docker-entrypoint-initdb.d postgres:17

psql shop -X -c 'TABLE shop.customers' -c 'SELECT * FROM shop.orders ORDER BY order_id'

the baseline, under the keys it was captured with. the data files are what we commit, with the log. the rendered file is output, and git ignores it:

git status --short

pg_dump reads the database that holds the shared dataset, and data capture --label baseline records the 3 customers and 5 orders as one data file for each table under data/baseline/, pinned to the log. The label is the dataset’s name.
snapshot --label baseline renders the schema and the baseline as one SQL file, written to the init directory mounted into the database container. Each table’s rows sit under a -- Data: line that marks them seed, label baseline.
The official postgres image runs every SQL file in its init directory the first time a container starts. The container comes up with the 3 customers and 5 orders under their captured keys. git status shows the data files as the thing to commit; the rendered file is ignored.

Chapter 02Layer the data for a regression test over the baseline

How do we add the data for a regression test without changing the baseline?

Issue 4412 is a regression: a bug that takes an order with a negative total to reproduce, and the baseline has none. data scaffold --label issue-4412 starts a fixture, a separate dataset, as a data file with one placeholder record for orders. We fill in the one row, and data check passes.

snapshot renders the datasets named on it. --label baseline renders the baseline alone, and a warning names the dataset left out. --label baseline --label issue-4412 renders the fixture over it, with a -- Data: line for each label’s rows.

psql initializes a new database for the regression test from both. It has the 5 orders from the baseline and the 1 from the fixture, the database from chapter 1 still has 5, and in git the data files under data/baseline/ are unchanged beside one new file.

37 sec space to pause f for fullscreen

Read transcript

seed-data-demo: regression

Generated from the demo script. Every command below is run verbatim by the recording.

1/3 a fixture for one regression

issue 4412 is a regression: it takes an order with a negative total to reproduce. the baseline has none, and we leave the baseline as it is. data scaffold starts a fixture, a separate dataset, with one row to fill:

retrofit data scaffold --label issue-4412 --table shop.orders

we fill in the row and check the file:

sed -i '' 's/<order_id>/9001/; s/<customer_id>/2/; s/<total>/-5.00/' data/issue*/*.data.sql && grep '^INSERT' data/issue*/*.data.sql

retrofit data check

2/3 layer the datasets

snapshot renders the datasets we name. the baseline alone, and a warning names the dataset we left out:

retrofit snapshot --label baseline | grep '^-- Data:'

then the fixture layered over the baseline:

retrofit snapshot --label baseline --label issue-4412 | grep '^-- Data:'

3/3 a database for the regression test

psql initializes a new database for the regression test from both:

createdb issue_4412 && retrofit snapshot --label baseline --label issue-4412 | psql issue_4412 -X -q -1 -v ON_ERROR_STOP=1

the two results are the key sequences, each set to its table’s highest key.

psql issue_4412 -X -c 'SELECT * FROM shop.orders ORDER BY order_id'

five orders from the baseline and one from the fixture. in git the data files under baseline are unchanged, and the fixture is one new file:

git status --short

data scaffold --label issue-4412 --table shop.orders mints a data file holding one placeholder record, and its two warnings say the file is unfilled and that its rows apply only when the label is named. We fill in order 9001 with a total of -5.00, and data check passes.
snapshot --label baseline renders two -- Data: lines, one for each table of the baseline, and an apply-risk-data-exclusions warning says one dataset, issue-4412, was not named. With --label issue-4412 named after it, a third line marks the fixture’s row for orders as seed, label issue-4412.
createdb makes a new database for the regression test, and psql loads what snapshot renders for both labels. The two results are the identity sequences, each set to its table’s highest key. The database has the baseline’s 5 orders and order 9001, and git status shows one new data file and no change under data/baseline/.

Chapter 03Rename a column and initialize a database from the same datasets

What happens to the seed data when the schema is refactored?

A seed file kept apart from the schema breaks when the schema changes, and nothing says so until a database fails to load. Here the data files are pinned to the log, and the rename goes in the same log.

We commit the fixture, then op add RenameColumn renames orders.total to amount. Its warning is for anything still reading total. In git the log is the one change: the data files under both labels still say total.

snapshot --label baseline --label issue-4412 renders all six orders under amount and prints a notice: line naming the table and the rename. psql initializes a new database from it, and the query shows the column as amount. The databases initialized before the rename are as they were.

30 sec space to pause f for fullscreen

Read transcript

seed-data-demo: refactor

Generated from the demo script. Every command below is run verbatim by the recording.

1/3 rename a column

we commit the fixture with the baseline. then we rename a column that both datasets fill: total, on orders, becomes amount. retrofit records the rename in the log, and warns about anything still reading total:

git add -A && git commit -qm 'a fixture for issue 4412'

retrofit op add RenameColumn shop.orders.total amount

2/3 the data files stay as captured

git shows one change, the log. the data files still say total:

git status --short

grep -h '^INSERT' data/*/shop.orders.*.data.sql | tail -2

3/3 initialize a database after the rename

we name the same two labels. snapshot applies the rename as it renders the rows, and says so:

createdb renamed && retrofit snapshot --label baseline --label issue-4412 | psql renamed -X -q -1 -v ON_ERROR_STOP=1

the two results are the key sequences, each set to its table’s highest key.

psql renamed -X -c 'SELECT * FROM shop.orders ORDER BY order_id'

all six orders, under amount. nobody edited a data file: the rename is in the log, and it is applied when the sql is rendered.

We commit the fixture with the baseline. op add RenameColumn shop.orders.total amount appends one operation to the log, and its apply-risk-rename-in-use warning says the rename breaks readers still using the old name.
git status shows the log as the one modified file. The last order of the baseline and the fixture’s order 9001 are still INSERT statements naming total, as they were captured and filled in.
snapshot prints a notice: line naming shop.orders and the rename as it renders both labels, and psql initializes a new database from the result. The two results are the identity sequences. The query shows all six orders with the column amount.

Chapter 04Run it end to end

Capture a shared dataset with data capture --label baseline, render it with the schema as one file, and start a postgres container from it. Then scaffold a fixture of one row under the label issue-4412, name both labels on snapshot to layer it over the baseline, and initialize a new database for the regression test from the result. Last, rename orders.total to amount with op add RenameColumn: the data files stay as captured, and snapshot renders both datasets under the new name.

1 min 51 sec space to pause f for fullscreen

Read transcript

seed-data-demo: complete walkthrough

Generated from the demo script. Every command below is run verbatim by the recording.

capture the baseline

our team shares one dataset, kept in a database. pg_dump reads that database, and retrofit captures the rows as a dataset, in data files under the label baseline:

pg_dump --data-only --column-inserts --schema=shop $DATABASE_URL | retrofit data capture - --label baseline

seed is the tool’s word for test data, and a label names a dataset.

render one file

snapshot renders the schema and the dataset we name as one sql file, into the init directory we mount in our database container:

retrofit snapshot --label baseline > initdb/01-baseline.sql

grep -e '^CREATE TABLE' -e '^-- Data:' initdb/01-baseline.sql

the two tables, then each table’s rows, marked as test data with the label they belong to.

start a database

the postgres image runs every sql file in its init directory the first time a container starts. we mount ours:

docker run -d --name shop-db -p $PGPORT:5432 -e POSTGRES_DB=shop -e POSTGRES_PASSWORD=pw -v $PWD/initdb:/docker-entrypoint-initdb.d postgres:17

psql shop -X -c 'TABLE shop.customers' -c 'SELECT * FROM shop.orders ORDER BY order_id'

the baseline, under the keys it was captured with. the data files are what we commit, with the log. the rendered file is output, and git ignores it:

git status --short

a fixture for one regression

retrofit data scaffold --label issue-4412 --table shop.orders

we fill in the row and check the file:

sed -i '' 's/<order_id>/9001/; s/<customer_id>/2/; s/<total>/-5.00/' data/issue*/*.data.sql && grep '^INSERT' data/issue*/*.data.sql

retrofit data check

layer the datasets

snapshot renders the datasets we name. the baseline alone, and a warning names the dataset we left out:

retrofit snapshot --label baseline | grep '^-- Data:'

then the fixture layered over the baseline:

retrofit snapshot --label baseline --label issue-4412 | grep '^-- Data:'

a database for the regression test

psql initializes a new database for the regression test from both:

createdb issue_4412 && retrofit snapshot --label baseline --label issue-4412 | psql issue_4412 -X -q -1 -v ON_ERROR_STOP=1

the two results are the key sequences, each set to its table’s highest key.

psql issue_4412 -X -c 'SELECT * FROM shop.orders ORDER BY order_id'

five orders from the baseline and one from the fixture. in git the data files under baseline are unchanged, and the fixture is one new file:

git status --short

rename a column

git add -A && git commit -qm 'a fixture for issue 4412'

retrofit op add RenameColumn shop.orders.total amount

the data files stay as captured

git shows one change, the log. the data files still say total:

git status --short

grep -h '^INSERT' data/*/shop.orders.*.data.sql | tail -2

initialize a database after the rename

we name the same two labels. snapshot applies the rename as it renders the rows, and says so:

createdb renamed && retrofit snapshot --label baseline --label issue-4412 | psql renamed -X -q -1 -v ON_ERROR_STOP=1

the two results are the key sequences, each set to its table’s highest key.

psql renamed -X -c 'SELECT * FROM shop.orders ORDER BY order_id'

all six orders, under amount. nobody edited a data file: the rename is in the log, and it is applied when the sql is rendered. each dataset is data files in git with the schema, under a label. a database instance is initialized with the datasets we name on snapshot: the baseline for development, and a fixture over it for a regression test. a rename in the log is applied to both when they are rendered.