How to populate a PostgreSQL test container in Go for integration testing

In this article I will show you how to setup a minimal test using testcontainers-go. We are going to extend some ideas from my last article on generating fake data to to insert random data into the test database and verify we can retrieve the data in a test.

Test Containers are great for running integration tests especially ones that need to verify the behavior of your code as it integration with components like databases, caches, etc... that you will most likely use in production. Test Containers are an additional tool to mocks which just "pretend" to be the real thing, test containers give us the real thing, running in a container.

Before we continue, you will need Go and Docker on your machine to following along with this article.

Alright, let's get right into it. As usual, I try to keep my posts as fully contained examples (pun not intended, :) ) .

Start by creating a new go module project and creating the files.

mkdir go_testing

cd go_testing

go mod init example

touch main.go

touch main_test.go

NOTE: Hashnode's markdown rendering for code blocks seems to be janky, note that the parameters should use dollar signs $1, $2 etc... not \(1 as it renders.

Next, lets fill the main.go which houses the functions to create a database migration, generate random data, insert records into database, retrieve records with the following content:

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"time"

	_ "github.com/lib/pq"

	"github.com/go-faker/faker/v4"
)

type Person struct {
	ID          int64
	FirstName   string
	LastName    string
	DateOfBirth *time.Time
	Email       string
	Phone       string
	Address     string
}

const N_PEOPLE = 1000

func main() {
	connStr := fmt.Sprintf("host=%s port=%s database=example user=%s password=%s sslmode=disable", "localhost", "5432", "my_example", "app-testing")

	dbConn, err := sql.Open("postgres", connStr)
	defer dbConn.Close()

	if err != nil {
		log.Fatalf("failed to open database: %v", err)
	}

	people := GeneratePeopleData()

	err = InsertPeople(dbConn, people)
	if err != nil {
		log.Fatalf("failed to create people: %v", err)
	}

	dbPeople, err := FindAllPeople(dbConn)
	if err != nil {
		log.Fatalf("failed to find records in database: %v", err)
	}

	data, err := json.Marshal(dbPeople)
	if err != nil {
		log.Fatalf("failed to marshal: %v", err)
	}

	fmt.Printf("Got people %s\n", string(data))
}

// Generate random data
func GeneratePeopleData() []Person {
	people := make([]Person, 0)
	for i := range N_PEOPLE {
		dateOfBirth, err := time.Parse(time.DateOnly, faker.Date())
		if err != nil {
			panic(err)
		}
		person := Person{
			ID:          int64(i + 1),
			FirstName:   faker.FirstName(),
			LastName:    faker.LastName(),
			DateOfBirth: &dateOfBirth,
			Email:       faker.Email(),
			Phone:       faker.Phonenumber(),
			Address:     faker.GetRealAddress().Address,
		}
		people = append(people, person)
	}
	return people
}

// Migration to create database tables
func Migrate(db *sql.DB) error {
	sql := `
	CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    date_of_birth DATE NOT NULL,
    email TEXT NULL, 
    phone TEXT NOT NULL,
    address TEXT
);
	`

	_, err := db.Exec(sql)
	return err
}

// Insert records into the database
func InsertPeople(db *sql.DB, people []Person) error {
	for _, person := range people {
		_, err := db.Exec(`INSERT INTO people(first_name, last_name, date_of_birth, email, phone, address) VALUES ( \(1, \)2, $3, $4, $5, $6) RETURNING *`,
			person.FirstName, person.LastName, person.DateOfBirth, person.Email, person.Phone, person.Address)

		if err != nil {
			return err
		}
	}
	return nil
}

// Retrieve all records from people table
func FindAllPeople(db *sql.DB) ([]Person, error) {
	rows, err := db.Query("SELECT first_name, last_name, date_of_birth, email, phone, address FROM people")
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	people := make([]Person, 0)
	for rows.Next() {
		var person Person
		err := rows.Scan(&person.FirstName, &person.LastName, &person.DateOfBirth, &person.Email, &person.Phone, &person.Address)
		if err != nil {
			return nil, err
		}
		people = append(people, person)
	}
	return people, nil
}

Next, we need a file to initialize the database environment for our tests - create a file at the following path ./testdata/docker-entrypoint-initdb.d/01-init.sh (NOTE: of course you can place the init file in any other path but I find this path gives a clear indication of it's intention)

Place the following content into the file:

#!/bin/sh
set -e
export PGPASSWORD=$POSTGRES_PASSWORD;
psql -v ON_ERROR_STOP=1 --username "\(POSTGRES_USER" --dbname "\)POSTGRES_DB" <<-EOSQL
  CREATE USER example WITH PASSWORD '$POSTGRES_PASSWORD';
  CREATE DATABASE example OWNER example;
EOSQL

This is the init script that we will use to initialize our container. This file creates a new database named example as well as an associated user/owner for that database because it's bad practice to use the root postgres role or postgres database for your applications. Yes, even if we are just testing ;)

Finally, let's add code to the main_test.go file that houses test code which uses testcontainers-go to setup a database test container and uses the functions we defined in the main.go to insert records and retrieve them. Place the following into the test file:

package main_test

import (
	"context"
	"database/sql"
	"encoding/json"
	main "example"
	"fmt"
	"log"
	"path/filepath"
	"testing"
	"time"

	"github.com/moby/moby/api/types/container"
	"github.com/stretchr/testify/require"
	"github.com/testcontainers/testcontainers-go"
	"github.com/testcontainers/testcontainers-go/wait"
)

func TestConnectToDB(t *testing.T) {
	volumePath, _ := filepath.Abs(filepath.Join(".", "./testdata/docker-entrypoint-initdb.d/"))
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Minute)
	defer cancel()

	postgresC, err := testcontainers.Run(
		ctx,
		"postgres:15-alpine",
		testcontainers.WithEnv(map[string]string{
			"POSTGRES_USER":     "postgres",
			"POSTGRES_PASSWORD": "app-testing",
		}),
		testcontainers.WithExposedPorts("5432/tcp"),
		testcontainers.WithWaitStrategy(
			wait.ForAll(
				wait.ForListeningPort("5432/tcp"),
				wait.ForLog("database system is ready to accept connections").WithOccurrence(2),
			),
		),
		testcontainers.WithHostConfigModifier(func(hc *container.HostConfig) {
			hc.Binds = append(hc.Binds, volumePath+":/docker-entrypoint-initdb.d")
		}),
	)

	testcontainers.CleanupContainer(t, postgresC)
	require.NoError(t, err)

	host, err := postgresC.Host(ctx)
	port, err := postgresC.MappedPort(ctx, "5432/tcp")

	connStr := fmt.Sprintf("host=%s port=%s database=example user=%s password=%s sslmode=disable", host, port.Port(), "example", "app-testing")

	dbConn, err := sql.Open("postgres", connStr)

	require.NoError(t, err)
	require.NotNil(t, dbConn)

	err = main.Migrate(dbConn)
	require.NoError(t, err)

	people := main.GeneratePeopleData()

	err = main.InsertPeople(dbConn, people)
	require.NoError(t, err)

	dbPeople, err := main.FindAllPeople(dbConn)
    
    require.NoError(t, err)
	require.NotNil(t, dbPeople)
	
    // Uncomment the following lines to view the records as json
	// data, err := json.Marshal(dbPeople)
	// if err != nil {
	//	log.Fatalf("failed to marshal: %v", err)
	// }
	// fmt.Printf("Got people %s\n", string(data))
}

You can run this test with the following command:

go test -timeout 30s -run ./...

This should give a passing test, and if so - great. You now know how to run test containers and populate the test database with records

Considerations for real-world projects

In a real project setting, you would probably have another way to setup migrations using a migration tool like goose or golang-migrate, so you would have to adjust your database initialization code in the tests appropriately.

You would also have to decide how to structure your tests - typically you want to test things like Repositories or Services you have in your project rather than just testing the database itself directly.

You may also want to consider grouping tests as spawning and destroying containers, though it's fast for most part, could get costly for a large test suite.

You could also consider running these integration tests in separate CI pipelines from your Unit tests so that you have faster feedback on Unit tests and allow integration tests that could run longer to run separately.

I hope you found this useful or interesting.