A Fresh Database Per Test

Look back at any exercise in this book and the hidden test always starts the same way: open a :memory: database, apply schema, maybe seed a row or two, and hand you a ready *sql.DB. You've read that pattern a dozen times without writing it. This lesson is where you write it — not because the mechanics are new, but because there's a mistake this pattern quietly prevents, and you only really learn to avoid it by building the thing that avoids it.

var db *sql.DB // package-level, opened once in TestMain

func TestCreate(t *testing.T)  { /* uses db */ }
func TestDelete(t *testing.T)  { /* uses the SAME db */ }

This looks like a reasonable optimization — why reopen a database for every test? — and it's exactly how test pollution gets in. TestDelete runs against whatever TestCreate left behind. Run the tests in a different order, or run only one of them with -run, and the results change depending on what ran before. go test doesn't guarantee execution order across files, and table-driven subtests can run in parallel with t.Parallel() — either one turns a shared db from an optimization into a race.

The fix is the constructor, not the discipline

func newSeededDB(t *testing.T, titles ...string) *sql.DB {
	t.Helper()
	db, err := sql.Open("sqlite", ":memory:")
	if err != nil {
		t.Fatalf("open: %v", err)
	}
	db.SetMaxOpenConns(1)
	t.Cleanup(func() { db.Close() })
	// ... apply schema, insert titles ...
	return db
}

Calling this at the top of every test gives each one its own fully isolated database — schema and all — with no coordination between tests required and no way to get it wrong by accident. t.Cleanup closes it when that specific test ends, not when the whole package finishes, so a :memory: database never outlives the test that owns it. The discipline isn't "remember not to share state" — it's "there's nothing here capable of being shared," which is the version of the rule that survives someone else adding a test next year without having read this lesson.

t.Helper() matters too, for a smaller reason: without it, a failure inside newSeededDB reports its own line number, and you're looking at the wrong file trying to figure out which test actually broke.

Try it

Implement newSeededDB. Called twice, it must return two completely independent databases — inserting into one must never be visible from the other — each with schema already applied and the given titles already seeded, in order.