In this example we are going to run integration tests that depend on a real Postgres database in Golang. It will rely on a Docker container and is CI/CD friendly approach.


Running tests will bring up a new Docker container with a new port to connect. Once finished, container and port will be released. There will only be single database connection for the whole tests run. The example also contains data fixtures which is a bonus point here. Each test case will reload data fixtures for a fresh run. Pay attention to the lines where +++ FIX is printed.


Every integrations test function names must be prefixed with Test_int_ so running unit tests could easily be differentiated which you can refer to $ make test-run-unit command.


Structure


├── Makefile
├── internal
│   └── storage
│   └── postgres
│   ├── args.go
│   ├── models.go
│   ├── storage.go
│   ├── user.go
│   └── user_test.go
├── pkg
│   └── errorx
│   └── error.go
│   └── postgresx
│   └── error.go
│   └── test
│   └── postgres_database.go
├── scripts
│   └── postgres
│   ├── fixtures
│   │   ├── fixtures.go
│   │   └── users.sql
│   └── migrations
│   └── up
│   └── 000001_create_table_users.up.sql
...

Files


pkg/errorx/error.go


package errorx

import (
"errors"
)

var (
ErrInternal = errors.New("internal")
ErrResourceConflict = errors.New("resource conflict")
ErrResourceNotFound = errors.New("resource not found")
ErrOutdatedResourceState = errors.New("outdated resource state")
)

pkg/postgresx/error.go


package postgresx

import (
"errors"

"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/pgconn"
)

func IsNotFoundError(err error) bool {
return errors.Is(err, pgx.ErrNoRows)
}

func IsConflictError(err error) bool {
var e *pgconn.PgError
if errors.As(err, &e) && e.Code == "23505" {
return true
}

return false
}

internal/storage/postgres/storage.go


package postgres

import (
"context"
"time"

"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/pgconn"
)

type querier interface {
Begin(ctx context.Context) (pgx.Tx, error)
Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
}

type Storage struct {
database querier
timeout time.Duration
}

// Pass `*pgxpool.Pool` as `querier`.
func NewStorage(database querier, timeout time.Duration) Storage {
return Storage{
database: database,
timeout: timeout,
}
}

internal/storage/postgres/user.go


package postgres

import (
"context"

"pkg/errorx"
"pkg/postgresx"
"github.com/jackc/pgx/v5"
"github.com/pkg/errors"
)

func (s Storage) CreateUser(ctx context.Context, args CreateUserArgs) error {
ctx, cancel := context.WithTimeout(ctx, s.timeout)
defer cancel()

qry := `
INSERT INTO users
(id, name, email, created_at)
VALUES
($1, $2, $3, $4)
`

_, err := s.database.Exec(ctx, qry,
args.User.ID,
args.User.Name,
args.User.Email,
args.User.CreatedAt,
)
switch {
case err == nil:
return nil
case postgresx.IsConflictError(err):
return errors.Wrap(errorx.ErrResourceConflict, err.Error())
}

return errors.Wrap(errorx.ErrInternal, err.Error())
}

internal/storage/postgres/args.go


package postgres

type CreateUserArgs struct {
User UserModel
}

internal/storage/postgres/models.go


package postgres

import (
"time"
)

type UserModel struct {
ID string
Name string
Email string
CreatedAt time.Time
DeletedAt *time.Time
}

internal/storage/postgres/user_test.go


package postgres

import (
"context"
"errors"
"testing"
"time"

"pkg/test"
"scripts/postgres/fixtures"
"github.com/stretchr/testify/assert"
)

func Test_int_Storage_CreateUser(t *testing.T) {
postgres, err := test.NewPostgresDatabase()
assert.NoError(t, err)
err = postgres.LoadFixtures([]string{fixtures.User})
assert.NoError(t, err)

storage := NewStorage(postgres.Database(), time.Minute)

ctx, cancel := context.WithTimeout(context.Background(), time.Microsecond)
defer cancel()

tests := []struct {
name string
haveContext context.Context
haveArgs CreateUserArgs
wantError error
}{
{
name: "internal error",
haveContext: ctx,
haveArgs: CreateUserArgs{},
wantError: errors.New(`context deadline exceeded: internal`),
},
{
name: "duplicate user error",
haveContext: context.Background(),
haveArgs: CreateUserArgs{
User: UserModel{
ID: "0b870c06-e61b-4299-a7bb-e532c5c3a630",
Name: "user",
Email: "email",
CreatedAt: time.Now(),
},
},
wantError: errors.New(`ERROR: duplicate key value violates unique constraint "users_prk_id" (SQLSTATE 23505): resource conflict`),
},
{
name: "success",
haveContext: context.Background(),
haveArgs: CreateUserArgs{
User: UserModel{
ID: "29ab709a-4584-48ff-aea2-2a8730f11fdb",
Name: "user",
Email: "email",
CreatedAt: time.Now(),
},
},
wantError: nil,
},
}

for _, test := range tests {
t.Run(test.name, func(t *testing.T) {
err := storage.CreateUser(test.haveContext, nil, test.haveArgs)

if err != nil {
assert.EqualError(t, test.wantError, err.Error())

return
}

assert.Equal(t, test.wantError, err)
})
}
}

pkg/test/postgres_database.go


package test

import (
"context"
"fmt"
"os"
"path/filepath"
"strings"
"sync"
"time"

"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/pgxpool"
)

var (
postgresDatabaseOnce sync.Once
postgresDatabaseConn *pgxpool.Pool
postgresDatabasePort = "15432"
postgresDatabasePath = "postgres://postgres:postgres@0.0.0.0:%s/postgres?sslmode=disable"
postgresDatabaseMaxRetry = 10
)

type PostgresDatabase struct{}

func NewPostgresDatabase() (PostgresDatabase, error) {
var (
err error
cfg *pgxpool.Config
i int
)

fmt.Println("+++ FIX: Connecting to the database ...")

postgresDatabaseOnce.Do(func() {
if val := os.Getenv("POSTGRES_RANDOM_PORT"); val != "" {
postgresDatabasePort = val
}
postgresDatabasePath = fmt.Sprintf(postgresDatabasePath, postgresDatabasePort)

cfg, err = pgxpool.ParseConfig(postgresDatabasePath)
if err != nil {
return
}

ticker := time.NewTicker(time.Second)
defer ticker.Stop()

for {
i++
if i > postgresDatabaseMaxRetry {
return
}

postgresDatabaseConn, err = pgxpool.NewWithConfig(context.Background(), cfg)
if err != nil {
return
}

err = postgresDatabaseConn.Ping(context.Background())
if err == nil {
return
}

fmt.Println("+++ FIX: Connection attempt", i, err.Error())

<-ticker.C
}
})

if err != nil {
return PostgresDatabase{}, fmt.Errorf("unable to establish database connection: %w", err)
}

if cfg != nil {
fmt.Println("+++ FIX: Connected")
} else {
fmt.Println("+++ FIX: Recycled")
}

return PostgresDatabase{}, nil
}

func (PostgresDatabase) Database() *pgxpool.Pool {
return postgresDatabaseConn
}

func (PostgresDatabase) LoadFixtures(files []string) error {
ctx, cancel := context.WithTimeout(context.Background(), time.Minute)
defer cancel()

tx, err := postgresDatabaseConn.BeginTx(ctx, pgx.TxOptions{})
if err != nil {
return fmt.Errorf("unable to start transaction: %w", err)
}
//nolint: errcheck
defer tx.Rollback(ctx)

// Truncating tables -------------------------------------------------------

fmt.Println("+++ FIX: Truncating tables ...")

query := `SELECT tablename FROM pg_tables WHERE schemaname = 'public';`

rows, err := tx.Query(ctx, query)
if err != nil {
return fmt.Errorf("unable to select tables: %w", err)
}
defer rows.Close()

var tables []string

for rows.Next() {
var table string

if err := rows.Scan(&table); err != nil {
return fmt.Errorf("unable to scan rows: %w", err)
}

tables = append(tables, table)
}

if err := rows.Err(); err != nil {
return fmt.Errorf("unable to finish row iteration: %w", err)
}

if len(tables) == 0 {
fmt.Println("+++ FIX: Nothing to truncate ...")

return nil
}

query = fmt.Sprintf("TRUNCATE %s RESTART IDENTITY CASCADE;", strings.Join(tables, ","))

if _, err := tx.Exec(ctx, query); err != nil {
return fmt.Errorf("unable to truncate tables: %w", err)
}

fmt.Println("+++ FIX: Truncated")

// Loading fixtures --------------------------------------------------------

fmt.Println("+++ FIX: Loading fixtures ...")

var queries string

for _, file := range files {
qry, err := os.ReadFile(filepath.Clean(file))
if err != nil {
return fmt.Errorf("unable to read fixture file: %w", err)
}

queries += string(qry) + "\n"
}

if queries == "" {
fmt.Println("+++ FIX: Nothing to load ...")

return nil
}

if _, err := tx.Exec(ctx, queries); err != nil {
return fmt.Errorf("unable to insert fixtures: %w", err)
}

fmt.Println("+++ FIX: Loaded")

if err := tx.Commit(ctx); err != nil {
return fmt.Errorf("unable to commit transaction: %w", err)
}

return nil
}

scripts/postgres/fixtures/fixtures.go


package fixtures

// List of all individual fixtures. Path here is relative to the caller package.
const (
User = "../../../scripts/postgres/fixtures/users.sql"
Post = "../../../scripts/postgres/fixtures/posts.sql"
)

// All return all fixtures in database constraint scecific order.
func All() []string {
return []string{
User,
Post,
}
}

scripts/postgres/fixtures/users.sql


INSERT INTO users
(id, name, email, created_at, deleted_at)
VALUES
('0b870c06-e61b-4299-a7bb-e532c5c3a630', 'user-1', 'user-1@example.com', '2022-12-05 20:57:36.716935+00', null),
('973825d2-f91d-4221-975e-2f825a0c2ac5', 'user-2', 'user-2@example.com', '2022-12-08 20:57:43.778081+00', null),
('8c6497ad-fa5c-474f-a8e6-7dbf60af33aa', 'user-3', 'user-3@example.com', '2022-12-07 20:57:22.448012+00', null),
('a5510ebe-1356-4639-aaf5-1a70ec960a5e', 'user-4', 'user-4@example.com', '2022-12-01 20:57:12.228012+00', '2022-12-02 20:57:12.228012+00'),
('ef105283-82dd-4c1a-bd50-fac595187956', 'user-5', 'user-5@example.com', '2022-12-04 20:57:11.338012+00', '2022-12-05 20:57:12.338012+00')
;

scripts/postgres/migrations/up/000001_create_table_users.up.sql


CREATE TABLE IF NOT EXISTS users
(
id uuid NOT NULL,
name character varying(100) NOT NULL,
email character varying(100) NOT NULL,
created_at timestamp with time zone NOT NULL,
deleted_at timestamp with time zone,
CONSTRAINT users_prk_id PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS users_idx_created_at ON users USING btree (
created_at
);

CREATE UNIQUE INDEX IF NOT EXISTS users_unq_email ON users USING btree (
email
) WHERE deleted_at IS NULL;

Makefile


SERVICE_NAME = blog
POSTGRES_USER = postgres
POSTGRES_PASS = postgres
POSTGRES_NAME = postgres
POSTGRES_TEST_PORT = 15432
POSTGRES_RANDOM_PORT = $(shell date +1%M%S)
POSTGRES_TEST_ADDRESS = postgres://${POSTGRES_USER}:${POSTGRES_PASS}@0.0.0.0:${POSTGRES_TEST_PORT}/${POSTGRES_NAME}?sslmode=disable
POSTGRES_IMAGE = postgres:15.0-alpine
POSTGRES_CONTAINER = ${SERVICE_NAME}-postgres
POSTGRES_MIGRATIONS = ${PWD}/scripts/postgres/migrations
POSTGRES_FIXTURES = ${PWD}/scripts/postgres/fixtures

.PHONY: help
help: ## Display available commands.
@awk 'BEGIN {FS = ":.*?## "} /^[0-9a-zA-Z_-]+:.*?## / {printf "\033[36m%-30s\033[0m %s\n", $$1, $$2}' ${MAKEFILE_LIST}

.PHONY: test-run-unit
test-run-unit: ## Run unit tests (not prefixed with `Test_int_`).
@go test -race -timeout=180s -count=1 \
-run '^([^T]|T($|[^e]|e($|[^s]|s($|[^t]|t($|[^_]|_($|[^i]|i($|[^n]|n($|[^t]|t($|[^_]))))))))).*' ./... \
| grep -v "no tests to run"

.PHONY: test-run-int
test-run-int: ## Run integration tests (prefixed with `Test_int_`).
@docker run \
--rm \
--detach \
--env POSTGRES_DB=${POSTGRES_NAME} \
--env POSTGRES_USER=${POSTGRES_USER} \
--env POSTGRES_PASSWORD=${POSTGRES_PASS} \
--volume ${POSTGRES_MIGRATIONS}/up:/docker-entrypoint-initdb.d:ro \
--publish ${POSTGRES_RANDOM_PORT}:5432 \
--name ${POSTGRES_CONTAINER}-test-${POSTGRES_RANDOM_PORT} \
${POSTGRES_IMAGE}
@POSTGRES_RANDOM_PORT=${POSTGRES_RANDOM_PORT} go test -race -timeout=180s -count=1 \
-run '^Test_int_' ./... | \
grep -v "no tests to run"
@docker stop ${POSTGRES_CONTAINER}-test-${POSTGRES_RANDOM_PORT}

.PHONY: test-db-start
test-db-start: ## Start test database in attached mode.
@docker run \
--rm \
--env POSTGRES_DB=${POSTGRES_NAME} \
--env POSTGRES_USER=${POSTGRES_USER} \
--env POSTGRES_PASSWORD=${POSTGRES_PASS} \
--volume ${POSTGRES_FIXTURES}:/fixtures:ro \
--publish ${POSTGRES_TEST_PORT}:5432 \
--name ${POSTGRES_CONTAINER}-test \
${POSTGRES_IMAGE} \
postgres -c log_statement=all -c log_destination=stderr

.PHONY: test-db-migrate-up
test-db-migrate-up: ## Run test database migrations all the way up.
@docker run \
--rm \
--volume ${POSTGRES_MIGRATIONS}/up:/migrations:ro \
--network host \
migrate/migrate \
-path /migrations -database ${POSTGRES_TEST_ADDRESS} up

Tests


All


$ make test-run-int
c17cd48e4735321c24123fc695c1bdc6a4173a28a5a1e6d68eb1c69ac3fe181b
? pkg/errorx [no test files]
? pkg/postgresx [no test files]
? pkg/test [no test files]
? scripts/postgres/fixtures [no test files]
ok internal/storage/postgres 3.120s
blog-postgres-test-10841

This is what the container would look like just before the tests are completed.


$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
c17cd48e4735 postgres:15.0-alpine "docker-entrypoint.s…" 2 seconds ago Up 1 second 0.0.0.0:10841->5432/tcp blog-postgres-test-10841

Individual


You can individually run a test as usual with native go test command however, you must first use make test-db-start and make test-db-migrate-up commands to prepare database. After that, you can run individual tests.


$ go test -v -run Test_int_Storage_CreateUser ./internal/storage/postgres/
=== RUN Test_int_Storage_CreateUser
+++ FIX: Connecting to the database ...
+++ FIX: Connected
+++ FIX: Truncating tables ...
+++ FIX: Truncated
+++ FIX: Loading fixtures ...
+++ FIX: Loaded
=== RUN Test_int_Storage_CreateUser/internal_error
=== RUN Test_int_Storage_CreateUser/duplicate_user_error
=== RUN Test_int_Storage_CreateUser/success
--- PASS: Test_int_Storage_CreateUser (0.11s)
--- PASS: Test_int_Storage_CreateUser/internal_error (0.00s)
--- PASS: Test_int_Storage_CreateUser/duplicate_user_error (0.01s)
--- PASS: Test_int_Storage_CreateUser/success (0.00s)
PASS
ok internal/storage/postgres 0.567s

I have more tests so this the output.


$ go test -v ./internal/storage/postgres/
=== RUN Test_int_ListPostsByUserSortFields
--- PASS: Test_int_ListPostsByUserSortFields (0.00s)
=== RUN Test_int_Storage_CreatePost
+++ FIX: Connecting to the database ...
+++ FIX: Connected
+++ FIX: Truncating tables ...
+++ FIX: Truncated
+++ FIX: Loading fixtures ...
+++ FIX: Loaded
=== RUN Test_int_Storage_CreatePost/internal_error
=== RUN Test_int_Storage_CreatePost/unavailable_user_error
=== RUN Test_int_Storage_CreatePost/duplicate_post_error
=== RUN Test_int_Storage_CreatePost/success
--- PASS: Test_int_Storage_CreatePost (0.12s)
--- PASS: Test_int_Storage_CreatePost/internal_error (0.00s)
--- PASS: Test_int_Storage_CreatePost/unavailable_user_error (0.01s)
--- PASS: Test_int_Storage_CreatePost/duplicate_post_error (0.00s)
--- PASS: Test_int_Storage_CreatePost/success (0.01s)
=== RUN Test_int_Storage_CountPostsByUser
+++ FIX: Connecting to the database ...
+++ FIX: Recycled
+++ FIX: Truncating tables ...
+++ FIX: Truncated
+++ FIX: Loading fixtures ...
+++ FIX: Loaded
=== RUN Test_int_Storage_CountPostsByUser/internal_error
=== RUN Test_int_Storage_CountPostsByUser/zero_count_for_unknown_user
=== RUN Test_int_Storage_CountPostsByUser/non_zero_count_for_known_user
--- PASS: Test_int_Storage_CountPostsByUser (0.08s)
--- PASS: Test_int_Storage_CountPostsByUser/internal_error (0.00s)
--- PASS: Test_int_Storage_CountPostsByUser/zero_count_for_unknown_user (0.01s)
--- PASS: Test_int_Storage_CountPostsByUser/non_zero_count_for_known_user (0.00s)
=== RUN Test_int_Storage_ListPostsByUser
+++ FIX: Connecting to the database ...
+++ FIX: Recycled
+++ FIX: Truncating tables ...
+++ FIX: Truncated
+++ FIX: Loading fixtures ...
+++ FIX: Loaded
=== RUN Test_int_Storage_ListPostsByUser/internal_error
=== RUN Test_int_Storage_ListPostsByUser/empty_result_for_unknown_user
=== RUN Test_int_Storage_ListPostsByUser/list_deleted_posts_of_deleted_user
=== RUN Test_int_Storage_ListPostsByUser/list_all_using_default_order
=== RUN Test_int_Storage_ListPostsByUser/list_one_using_text_field_in_descending_order
--- PASS: Test_int_Storage_ListPostsByUser (0.09s)
--- PASS: Test_int_Storage_ListPostsByUser/internal_error (0.00s)
--- PASS: Test_int_Storage_ListPostsByUser/empty_result_for_unknown_user (0.01s)
--- PASS: Test_int_Storage_ListPostsByUser/list_deleted_posts_of_deleted_user (0.00s)
--- PASS: Test_int_Storage_ListPostsByUser/list_all_using_default_order (0.00s)
--- PASS: Test_int_Storage_ListPostsByUser/list_one_using_text_field_in_descending_order (0.01s)
=== RUN Test_int_Storage_DeleteAllPostsByUser
+++ FIX: Connecting to the database ...
+++ FIX: Recycled
+++ FIX: Truncating tables ...
+++ FIX: Truncated
+++ FIX: Loading fixtures ...
+++ FIX: Loaded
=== RUN Test_int_Storage_DeleteAllPostsByUser/internal_error
=== RUN Test_int_Storage_DeleteAllPostsByUser/success_even_if_user_is_not_found
=== RUN Test_int_Storage_DeleteAllPostsByUser/success_even_if_post_was_already_deleted
=== RUN Test_int_Storage_DeleteAllPostsByUser/success
--- PASS: Test_int_Storage_DeleteAllPostsByUser (0.07s)
--- PASS: Test_int_Storage_DeleteAllPostsByUser/internal_error (0.00s)
--- PASS: Test_int_Storage_DeleteAllPostsByUser/success_even_if_user_is_not_found (0.00s)
--- PASS: Test_int_Storage_DeleteAllPostsByUser/success_even_if_post_was_already_deleted (0.00s)
--- PASS: Test_int_Storage_DeleteAllPostsByUser/success (0.00s)
=== RUN Test_Storage_Transaction
+++ FIX: Connecting to the database ...
+++ FIX: Recycled
+++ FIX: Truncating tables ...
+++ FIX: Truncated
+++ FIX: Loading fixtures ...
+++ FIX: Loaded
--- PASS: Test_Storage_Transaction (0.08s)
=== RUN Test_int_Storage_CreateUser
+++ FIX: Connecting to the database ...
+++ FIX: Recycled
+++ FIX: Truncating tables ...
+++ FIX: Truncated
+++ FIX: Loading fixtures ...
+++ FIX: Loaded
=== RUN Test_int_Storage_CreateUser/internal_error
=== RUN Test_int_Storage_CreateUser/duplicate_user_error
=== RUN Test_int_Storage_CreateUser/success
--- PASS: Test_int_Storage_CreateUser (0.07s)
--- PASS: Test_int_Storage_CreateUser/internal_error (0.00s)
--- PASS: Test_int_Storage_CreateUser/duplicate_user_error (0.01s)
--- PASS: Test_int_Storage_CreateUser/success (0.01s)
=== RUN Test_int_Storage_FindUser
+++ FIX: Connecting to the database ...
+++ FIX: Recycled
+++ FIX: Truncating tables ...
+++ FIX: Truncated
+++ FIX: Loading fixtures ...
+++ FIX: Loaded
=== RUN Test_int_Storage_FindUser/internal_error
=== RUN Test_int_Storage_FindUser/user_does_not_exist
=== RUN Test_int_Storage_FindUser/found_deleted_user
=== RUN Test_int_Storage_FindUser/found_undeleted_user
--- PASS: Test_int_Storage_FindUser (0.08s)
--- PASS: Test_int_Storage_FindUser/internal_error (0.00s)
--- PASS: Test_int_Storage_FindUser/user_does_not_exist (0.01s)
--- PASS: Test_int_Storage_FindUser/found_deleted_user (0.00s)
--- PASS: Test_int_Storage_FindUser/found_undeleted_user (0.00s)
=== RUN Test_int_Storage_DeleteUser
+++ FIX: Connecting to the database ...
+++ FIX: Recycled
+++ FIX: Truncating tables ...
+++ FIX: Truncated
+++ FIX: Loading fixtures ...
+++ FIX: Loaded
=== RUN Test_int_Storage_DeleteUser/internal_error
=== RUN Test_int_Storage_DeleteUser/user_does_not_exist
=== RUN Test_int_Storage_DeleteUser/user_exists_but_already_deleted
=== RUN Test_int_Storage_DeleteUser/success
--- PASS: Test_int_Storage_DeleteUser (0.08s)
--- PASS: Test_int_Storage_DeleteUser/internal_error (0.00s)
--- PASS: Test_int_Storage_DeleteUser/user_does_not_exist (0.00s)
--- PASS: Test_int_Storage_DeleteUser/user_exists_but_already_deleted (0.00s)
--- PASS: Test_int_Storage_DeleteUser/success (0.00s)
PASS
ok internal/storage/postgres 1.381s