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.


  1. Obtain a database instance.

  2. Truncate table leagues.

  3. Populate leagues with test data.

  4. 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")
}

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