Seed Data and Fixtures for Go Development Databases

A development database that starts empty is a poor place to build a feature. Seed data fixes that, and the one property it needs is that running it twice is safe. The simplest way to get there is a SQL file of inserts that skip rows that already exist, embedded in the binary and run from a command.

The file and the runner

insert into plans605 (slug, name, price_cents) values
    ('free', 'Free', 0),
    ('pro', 'Pro', 1200),
    ('team', 'Team', 4900)
on conflict (slug) do nothing;

The conflict target, (slug), names the unique key that identifies a row, and do nothing skips an insert that would collide with it. The program embeds the file and runs it three times against a fresh table.

package main

import (
	"context"
	"database/sql"
	_ "embed"
	"fmt"
	"log"

	_ "github.com/jackc/pgx/v5/stdlib"
)

//go:embed seed.sql
var seed string

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

func count(ctx context.Context, db *sql.DB) int {
	var n int
	if err := db.QueryRowContext(ctx, "select count(*) from plans605").Scan(&n); err != nil {
		log.Fatal(err)
	}
	return n
}

func main() {
	ctx := context.Background()
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	for _, q := range []string{
		"drop table if exists plans605",
		"create table plans605 (slug text primary key, name text not null, price_cents int not null)",
	} {
		if _, err := db.ExecContext(ctx, q); err != nil {
			log.Fatal(err)
		}
	}
	for run := 1; run <= 3; run++ {
		res, err := db.ExecContext(ctx, seed)
		if err != nil {
			log.Fatal(err)
		}
		n, _ := res.RowsAffected()
		fmt.Printf("run %d: rows affected %d, rows in table %d\n", run, n, count(ctx, db))
	}

	if _, err := db.ExecContext(ctx, "update plans605 set price_cents = 1500 where slug = 'pro'"); err != nil {
		log.Fatal(err)
	}
	var price int
	_ = db.QueryRowContext(ctx, "select price_cents from plans605 where slug = 'pro'").Scan(&price)
	fmt.Println("an editor changed pro to", price)
	if _, err := db.ExecContext(ctx, seed); err != nil {
		log.Fatal(err)
	}
	_ = db.QueryRowContext(ctx, "select price_cents from plans605 where slug = 'pro'").Scan(&price)
	fmt.Println("after seeding again with do nothing, pro is", price)
}

run 1: rows affected 3, rows in table 3
run 2: rows affected 0, rows in table 3
run 3: rows affected 0, rows in table 3
an editor changed pro to 1500
after seeding again with do nothing, pro is 1500

The first run reports three rows affected. The next two report zero, and the table still holds three rows, so the command can run on every start of the development stack without piling up duplicates. database/sql with the pgx driver sends the whole file as one multi-statement string when it has no parameters, which is why a plain ExecContext works here.

Skip or overwrite

The last two lines of the output show the choice. After an editor changed the Pro plan to 1500 cents, seeding again left it at 1500. do nothing never touches existing rows, which is what you want when the seed provides starting data and people then edit it. If the file should stay the source of truth, as it does for reference data such as plan limits or permission names, write on conflict (slug) do update set name = excluded.name, price_cents = excluded.price_cents. That overwrites any change made in the database, so use it only for rows nobody edits by hand.

A seed command is also the quickest way to give a new teammate a working database: clone, start the stack, run the migration command and then the seed command, and the first page they open has something on it. Print how many rows each run inserted, as the program above does, so an unexpected zero or an unexpected thousand is visible straight away.

Keep three kinds of data apart

• Reference data the application cannot run without (plans, roles, countries). It belongs in a seed file that production runs too, or in a migration if it must exist before the first request.
• Development data (fake customers, sample orders). A second file, run only by a dev command, and never wired to production start-up.
• Test fixtures. Create these inside each test and roll them back, as in rollback tests, so tests do not depend on a shared seed.

Keep seeding out of the migration files that change the schema: a migration runs once per environment and a seed may run any number of times, and mixing them makes both harder to reason about. The migration side is covered in migrations with goose and the wider picture in the database cornerstone. PostgreSQL documents the clause in its INSERT reference.