Integration Testing with Go and PostgreSQL
Testing is an important part of software development; it helps verify that your code behaves as intended. Software tests come in many flavors: unit tests, integration tests, system tests and more. You may be familiar with unit testing, which tests individual components—such as functions or methods—in isolation, using assertions to check their behavior. Writing tests helps expose flaws in your logic and identify opportunities for improvement.
Integration tests evaluate how different components of a software application work together. Sure, you may have that awesome function that performs a SQL transaction, but does it behave as expected when connected to a real database? Does the transaction rollback work as intended?
In this post, I will show you how to write integration tests using Go and PostgreSQL. We’ll build a function that creates an isolated schema, applies migrations, and registers automated cleanup to drop the schema after each test. We’ll also demonstrate how to perform a round-trip test that inserts data into the database and then retrieves it to verify the result.
Setting up the Database
A typical software project may have three environments: development, QA, and production. Each environment has its own database. During development you’re free to use the database as a sandbox to test your features; maybe you want to perform a manual test of an API endpoint or a dry run of a SQL transaction, but you may not want automated tests to clutter the development database with test data.
We can create a separate schema, a namespace for database objects, to isolate our test data from our development data. As you will soon see, this gives our test data its own short-lived home. Go’s awesome testing package can do a lot of the heavy lifting for us, thanks to helpers like t.Cleanup.
We’ll first have a look at how we can create a schema:
Our Store is a thin wrapper around a PostgreSQL connection pool. This first excerpt includes its definition and the helper’s schema setup; migration loading and the return statement follow in the next excerpt. Neither excerpt is a standalone file—the complete helper is linked below.
package store
import (
"context"
"os"
"path/filepath"
"strings"
"testing"
"github.com/google/uuid"
"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/pgxpool"
)
type Store struct {
db *pgxpool.Pool
}
func NewStore(db *pgxpool.Pool) *Store {
return &Store{db: db}
}
func newTestStore(t *testing.T) *Store {
t.Helper()
dbURL := os.Getenv("TEST_DATABASE_URL")
if dbURL == "" {
t.Skip("TEST_DATABASE_URL is not set")
}
schemaName := "app_test_" + strings.ReplaceAll(uuid.NewString(), "-", "")
quotedSchema := pgx.Identifier{schemaName}.Sanitize()
config, err := pgxpool.ParseConfig(dbURL)
if err != nil {
t.Fatalf("parsing config: %v", err)
}
ctx := context.Background()
config.ConnConfig.RuntimeParams["search_path"] = quotedSchema
pool, err := pgxpool.NewWithConfig(ctx, config)
if err != nil {
t.Fatalf("creating connection pool: %v", err)
}
t.Cleanup(func() {
pool.Close()
})
_, err = pool.Exec(ctx, "CREATE SCHEMA "+quotedSchema)
if err != nil {
t.Fatalf("creating schema: %v", err)
}
t.Cleanup(func() {
_, err := pool.Exec(ctx, "DROP SCHEMA "+quotedSchema+" CASCADE")
if err != nil {
t.Errorf("dropping schema: %v", err)
}
})
// Migration loading and the return statement follow in the next excerpt.
}
newTestStore will be our reusable helper to set up and drop a schema for testing. It returns a *Store we’ll use to interact with the test database. We call t.Helper so that failures reported by the helper point to the calling test. We then read our database URL, generate a unique schema name, and safely quote it for use in SQL statements. Setting search_path in the pool’s configuration makes each connection use our test schema for unqualified table names. With our declared configuration we create a connection pool.
The real pièce de résistance of this function is its cleanup: t.Cleanup registers a callback that runs when the test finishes, even if it fails. These callbacks run in reverse order, so the schema is first dropped and then the connection pool closes.
You may be wondering: when do we create tables? We don’t need a separate function for that, we can read and execute the migrations in the same helper. Picking up inside newTestStore, just after registering cleanup:
func newTestStore(t *testing.T) *Store {
// ... schema setup and cleanup registration from above ...
migrationFiles, err := filepath.Glob("../../db/migrations/*.up.sql")
if err != nil {
t.Fatalf("finding migrations: %v", err)
}
if len(migrationFiles) == 0 {
t.Fatal("no migration files found")
}
for _, f := range migrationFiles {
sql, err := os.ReadFile(f)
if err != nil {
t.Fatalf("reading %s: %v ", f, err)
}
_, err = pool.Exec(ctx, string(sql))
if err != nil {
t.Fatalf("applying migration %s: %v", f, err)
}
}
return NewStore(pool)
}
As previously mentioned, you can just read your migration files and execute the SQL.
The complete example is available here. It includes Store, NewStore, the complete helper, the migration, and the round-trip test below, along with instructions for running the example against a disposable PostgreSQL database.
With this helper we can spin up a schema, run our migrations, and automatically drop the schema after each test. This keeps our test data separate from our development data, even though both share the same database. Now let’s see this helper in action.
Using the Helper
Here’s an example using the helper in a test case. For our example, let’s say we have a database of books. We’re able to store a book and retrieve it.
For reference, our migration creates the following table:
CREATE TABLE books (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title TEXT NOT NULL,
author TEXT NOT NULL
);
We might say, “The SQL query runs, so it works!” but a successful query is just part of the picture. Does our Go code pass the correct values, and do we get the same values back if we query for them? A round-trip test helps us check both.
func TestBookRoundTrip(t *testing.T) {
ctx := context.Background()
store := newTestStore(t)
type Book struct {
Title string
Author string
}
want := Book{
Title: "The Hobbit",
Author: "J. R. R. Tolkien",
}
// Insert a book and capture its generated ID.
var id int64
err := store.db.QueryRow(ctx, `
INSERT INTO books (title, author)
VALUES ($1, $2)
RETURNING id
`, want.Title, want.Author).Scan(&id)
if err != nil {
t.Fatalf("inserting book: %v", err)
}
// Retrieve the book using its ID.
var got Book
err = store.db.QueryRow(ctx, `
SELECT title, author
FROM books
WHERE id = $1
`, id).Scan(&got.Title, &got.Author)
if err != nil {
t.Fatalf("fetching book: %v", err)
}
// Verify that the stored values match the original input.
if got != want {
t.Errorf("book = %+v, want %+v", got, want)
}
}
We use newTestStore to prepare our test database. We then insert a book and capture its generated id, which is used to retrieve the book. Finally we assert the retrieved book’s values match the original Book we specified.
To recap, integration tests help us see the big picture by checking how our application’s components work together. In this post, we demonstrated this idea by creating a reusable helper that creates a unique schema and tears it down after a test completes. We showed how this helper works by creating a round-trip test that validates an insert and retrieval.
Some useful resources: