inanzzz | Working with database test fixtures in Golang unit tests

You can use this simple database test fixtures example in your Golang test cases. All you need is a database instance and table truncation feature which I created a small package for.

Database helper package

package test

import (
	"database/sql"
	"fmt"
)

func OpenDB(driver, address string, maxIdleConns int) *sql.DB {
	db, _ := sql.Open(driver, address)
	db.SetMaxIdleConns(maxIdleConns)

	return db
}

func CloseDB(db *sql.DB) {
	_ = db.Close()
}

func TruncateTables(db *sql.DB, tables []string) {
	_, _ = db.Exec("SET FOREIGN_KEY_CHECKS=0;")

	for _, v := range tables {
		_, _ = db.Exec(fmt.Sprintf("TRUNCATE TABLE %s;", v))
	}

	_, _ = db.Exec("SET FOREIGN_KEY_CHECKS=1;")
}

Test

Let's say we have a table called leagues in database. We want to first truncate it so that it is clean. Afterwards we want to populate it with certain set of records so that our tests can work with it. The basic flow is below.

Obtain a database instance.

Truncate table leagues.

Populate leagues with test data.

Explicitly call loadFixtures function before each tests.

Helper file

This is for first three steps.

package league

import (
	"database/sql"
	"fmt"
	"os"
	"strings"
	"testing"

	"internal/pkg/test"
)

// -----------------------------------------------------------------------------

var DB *sql.DB

func TestMain(m *testing.M) {
	setup()
	code := m.Run()
	teardown()
	os.Exit(code)
}

func setup() {
	// Prepare database.
	dbAdr := "user:pass@tcp(0.0.0.0:3306)/football?charset=utf8mb4&collation=utf8mb4_unicode_ci"
	dbDrv := "mysql"
	dbIdl := 5

	// Obtain a database instance.
	DB = test.OpenDB(dbDrv, dbAdr, dbIdl)

	fmt.Printf("\033[1;36m%s\033[0m", "> Setup completed\n")
}

func teardown() {
	// Close the database instance.
	test.CloseDB(DB)

	fmt.Printf("\033[1;36m%s\033[0m", "> Teardown completed")
	fmt.Printf("\n")
}

// -----------------------------------------------------------------------------

func loadFixtures(db *sql.DB) {
	test.TruncateTables(db, []string{"leagues"})
	fmt.Printf("\033[1;36m%s\033[0m", "> Tables truncated\n")

	data := []struct {
		name              interface{}
		isActive          interface{}
		createdAt         interface{}
		deletedAt         interface{}
	}{
		{
			"La Liga",
			"true",
			"2019-12-31 23:59:59",
			"null",
		},
		{
			"Premier League",
			"true",
			"2019-12-31 23:59:59",
			"null",
		},
	}

	query := `
INSERT INTO leagues
(name, is_active, created_at, deleted_at)
VALUES
`

	for _, d := range data {
		query += fmt.Sprintf(
			"('%v', %v, '%v', %v),\n",
			d.name,
			d.isActive,
			d.createdAt,
			d.deletedAt,
		)
	}
	query = strings.TrimSuffix(query, ",\n")

	_, _ = db.Exec(query)

	fmt.Printf("\033[1;36m%s\033[0m", "> Fixtures loaded\n")
}

Important note

Depending on your tests, you will highly likely get an error similar to Error 1062: Duplicate entry '1' for key 'PRIMARY'. Whether this happens or not, you must execute LOCK TABLES {your_table_name} WRITE; just before test.TruncateTables() line and UNLOCK TABLES; right after.

Test file

This is for the last step where your test calls loadFixtures function.

package league

import (
	"net/http"
	"net/http/httptest"
	"testing"
)

func TestCreate(t *testing.T) {
	// DB is already ready for you!
	loadFixtures(DB)
	
	rq := httptest.NewRequest(http.MethodGet, "/leagues", nil)
	rw := httptest.NewRecorder()

	createHandler := NewCreate(DB)
	createHandler.Create(rw, rq)
	
	// Assert against rw.Body.String()
}

Output

As you can see below I have only TestCreate test case depends on fixtures, not the others.

$ go test ./... -v

> Setup completed
=== RUN   TestRetrieve
--- PASS: TestRetrieve (0.00s)
> Tables truncated
> Fixtures loaded
=== RUN   TestCreate
--- PASS: TestCreate (0.00s)
=== RUN   TestList
--- PASS: TestList (0.00s)
PASS
> Teardown completed
ok  	internal/league	0.051s