Yesterday I responded to a thread on Reddit where a user asked how people test their database interactions. There’s a lot of different ways to do this and after explaining wht I did there was interest in an example, so this will be the example :) If you just want to see the code, the github link is at the bottom.

I like to do as much as I can in unit-tests. They run quickly and it creates a really tight feedback loop for me. Databases are no exception — if I can test my queries by running `go test` like I do for my other tests, that’s a win. To do this I use a library called test-containers. This library allows me to quickly create and teardown a docker container for testing. It provides methods for accessing the host and port which makes it pretty straightforward to construct the *sql.DB we need.

I’m gonna run through a simple example, but it should be sufficient for you to copy/paste it into your project so you can run your tests against a real database as well. We’ll start with defining a repository for a User.

package users

import (
 "database/sql"
 "fmt"
)

type User struct {
 ID   string
 Name string
}

type userRepo struct {
 db *sql.DB
}

func NewUserRepo(db *sql.DB) *userRepo {
 return &userRepo{db: db}
}

func (u *userRepo) Create(user User) error {
 query := `INSERT INTO users (id, name) VALUES ($1, $2)`
 _, err := u.db.Exec(query, user.ID, user.Name)
 if err != nil {
  return fmt.Errorf("failed to insert user: %w", err)
 }
 return nil
}

func (u *userRepo) Get(id string) (*User, error) {
 query := `SELECT id, name FROM users WHERE id = $1`
 row := u.db.QueryRow(query, id)

var user User
 err := row.Scan(&user.ID, &user.Name)
 if err != nil {
  return nil, fmt.Errorf("failed to get user: %w", err)
 }
 return &user, nil
}

Nothing too special about this repository, but we have Create and Get methods. Now, we need to build out a simple helper method for working with a test-containers docker container:

package users

import (
 "context"
 "database/sql"
 "fmt"
 "log"
 "os"

"github.com/docker/go-connections/nat"
 "github.com/google/uuid"

_ "github.com/jackc/pgx/v4/stdlib" // using pgx, but you can use whatever
 "github.com/testcontainers/testcontainers-go"
 "github.com/testcontainers/testcontainers-go/wait"
)

func CreateContainer(databaseName string) (testcontainers.Container, *sql.DB, error) {
 port := "5432/tcp"

var env = map[string]string{
  "POSTGRES_USER":     databaseName,
  "POSTGRES_PASSWORD": "password",
  "POSTGRES_DB":       databaseName,
 }

req := testcontainers.GenericContainerRequest{
  ContainerRequest: testcontainers.ContainerRequest{
   Image:        "postgres:12.5", // Feel free to use a different version
   ExposedPorts: []string{port},
   Cmd:          []string{"postgres", "-c", "fsync=off"},
   Env:          env,
   Name:         databaseName + uuid.New().String(),
   WaitingFor: wait.ForSQL(nat.Port(port), "pgx", func(host string, port nat.Port) string {
    return fmt.Sprintf("postgres://%s:password@%s:%s/%s?sslmode=disable", databaseName, host, port.Port(), databaseName)
   }),
  },
  Started: true,
 }

container, err := testcontainers.GenericContainer(context.Background(), req)
 if err != nil {
  log.Fatal(err)
 }

mappedPort, err := container.MappedPort(context.Background(), nat.Port(port))
 if err != nil {
  log.Fatal(err)
  return nil, nil, err
 }

url := fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s statement_cache_mode=describe", "localhost", mappedPort.Port(), databaseName, "password", databaseName)
 db, err := sql.Open("pgx", url)
 if err != nil {
  fmt.Fprintln(os.Stdout, "stuff here")
  return nil, nil, err
 }

return container, db, nil
}

Feel free to rework this method however you like, but it will return a Container and our *sql.DB object. When we run this method it will spin up a postgres docker container on version 12.5 that we can now use to run our tests against. When I use this I like to only have one docker container for all my tests — if you decide you like doing this as well make sure you use unique data for your primary keys! UUIDs work well for this. Also, using one container for all tests will be much faster than spinning up a docker container per-test. I really don’t advise using individual containers for each test.

To make it simple for myself, I use Go’s TestMain method for creating the container. TestMain will run before and after all the tests in a Go package. The benefit of this is you only construct one docker container and you don’t need to do anything special in each individual test. The downside is that since it runs for an entire package it will necessarily slow down the tests for that package. Your normal Go tests probably run in easily less than a second and that won’t be the case for the test-containers tests; these tests run somewhere between 2–5 seconds for me usually. It’s a trade-off and one I’m easily willing to make, but it’s important to be aware of, ymmv.

Here’s the TestMain implementation that you can place in any test file in your package:

var testSqlDB *sql.DB

func TestMain(m *testing.M) {
 container, testDB, err := CreateContainer("test-db")
 if err != nil {
  log.Fatal(err)
 }
 defer testDB.Close()
 defer container.Terminate(context.Background()) // nolint

testSqlDB = testDB

// Seed/Setup sql file is here
 content, err := os.ReadFile("setup.sql")
 if err != nil {
  log.Fatal(err)
 }

_, err = testDB.Exec(string(content))
 if err != nil {
  log.Fatal(err)
 }

os.Exit(m.Run())
}

Here we use that CreateContainer method from earlier and we set up some defer statements to make sure the container is properly terminated after our tests run.

The benefit of doing things this way is that you can run against a real database, but that’s not very helpful if the database doesn’t have the tables or seeded data that you want to use. Thankfully this is fairly easy to fix. This example shows us executing a setup.sql file which looks like this:

CREATE TABLE users (
    id TEXT PRIMARY KEY,
    name TEXT
);

You can add some INSERTs or other sql stuff here. You’ll also notice that this TestMain method is setting a testSqlDB variable. This variable will be used by our tests, so all we need to do is instantiate the repository and run our methods:

func TestCreateAndGet(t *testing.T) {

t.Run("happy path", func(t *testing.T) {
  userId := uuid.NewString()
  user := User{
   ID:   userId,
   Name: uuid.NewString(),
  }

testRepository := NewUserRepo(testSqlDB)
  err := testRepository.Create(user)
  assert.Nil(t, err)

gotUser, err := testRepository.Get(userId)
  assert.Nil(t, err)

assert.Equal(t, &user, gotUser)
 })
}

There’s the test! This runs our query against an actual database that is set up similarly to our production database. That’s it!

If you’ve read this post please let me know your thoughts :) I really like this method of testing my database interactions. It can extend to Redis, and any other db you want to test — and I’m happy to share examples of that if there is interest. These tests give me a lot of confidence when I’m writing sql queries and I hope it can help you as well. Thank you for reading!

For a working example you can check it out on github here: https://github.com/Little-Leverages/db-unit-testing