On database-backed functionality testing in Go
TL;DR: I created a small package on top of pgx that simplifies testing of database-backed functionality. Check out pgxephemeraltest on GitHub - I think it’s pretty cool.
Remember, even if you’re a vibe-coder and think AI will replace software engineers, it’s still important to test code, no matter whose slop it is. So go and read the post, or check out the repo on GitHub.
Introduction
Say you’re a Go developer building a web app or service. You added some CRUD functionality, or something more advanced - doesn’t really matter. What matters is that it’s backed by a database, Postgres in this case. You consider yourself a responsible engineer and want to test your new functionality properly. So you add some tests on top of a shared pgxpool pool across all tests and call it a day.
Easy, right? Sure, but as the project grows, so does the number of tests. You start noticing that your test suite is getting slower and slower. As an experienced Go developer, you know that Go supports running tests in parallel - just mark a test with t.Parallel(), add the -parallel flag and you’re good to go. Phew, still easy, right? So you go ahead and add that t.Parallel() call to each and every test. With a smile on your face you run go test -parallel=$(nproc) -count=1 ./... and see that all tests have failed. What happened? Sure, some tests are easily fixable, but you quickly realize that database-backed tests are a different story.
They’re failing because of:
- timing out waiting for a connection from the pool
- race conditions due to concurrent access to the same data
Bumping pool size can help with issue 1 for a while, but it doesn’t solve 2. At this point, you need to rethink your approach and find a way to run tests in isolation. Luckily for you, I’ve been in your shoes and ran into the same problem, so I built a solution. No need to thank me - just throw a GitHub star my way.
The goal is to work with data in isolation in each test, so tests don’t see each other’s mutations during execution. A few database constructs come to mind: transactions and databases. Schemas could also work, but if you’re managing data across multiple schemas, that approach falls apart since Postgres doesn’t support nested schemas. Transactions and separate databases, however, do support working with multiple schemas.
Ephemeral Transaction
Let’s start with the simpler option: transactions. They’re a well-known construct for isolating data changes and applying them atomically. Multiple transactions can run in parallel against a single database, and since they’re isolated from each other, they can be used to run tests in isolation. The catch? Your test database must be free of side effects during execution. A side effect here means anything that affects the database outside of the transaction: schema changes, committed data - you know the drill.
Making sure the database is free of side effects during tests is crucial - otherwise the output becomes unpredictable and tests start failing randomly.
Practically speaking, to use transactions for tests you need to:
- Apply migrations to the target database before running transactions, otherwise there might be a risk of deadlocks.
- Roll back the transaction after each test
Here’s the implementation:
// TestTx returns an ephemeral database transaction for a given test.
// Once the test is complete the transaction is rolled back.
func TestTx(tb testing.TB) pgx.Tx {
tb.Helper()
// db here is a global variable that's initialized in the TestMain or in the global scope.
tx, err := db.BeginTx(tb.Context(), pgx.TxOptions{
// ReadCommitted is the default isolation level in Postgres, however,
// it might be overridden by the database configuration. We need to ensure
// that the transaction isolation level doesn't allow dirty writes.
IsoLevel: pgx.ReadCommitted,
})
if err != nil {
tb.Fatal(err)
}
tb.Cleanup(func() {
// It is important to pass a fresh context here as the tb.Context() is canceled
// when the test is finished, which happens right before the cleanup function is called.
if err := tx.Rollback(context.Background()); err != nil {
// If the transaction is already closed,
// it means that the test has already been cleaned up.
if !errors.Is(err, pgx.ErrTxClosed) {
tb.Fatal(err)
} else {
tb.Logf("Transaction already closed")
}
}
})
return tx
}Here’s how you can use it in tests:
func TestFancyFeature(t *testing.T) {
t.Parallel()
tx := TestTx(t)
feature := FancyFeature{dbExecutor: tx}
feature.CoolAction(t.Context())
rows, err := tx.Query(t.Context(), "SELECT * FROM fancy_feature_table")
require.NoError(t, err)
// Assert resulted rows
// ...
}This approach has limitations though. Some operations aren’t possible within a transaction, like changing the isolation level or read-only settings for a nested transaction, or creating new schemas (which might not be trivial sometimes). You also can’t leave data intact after a failed test for debugging. That’s where ephemeral databases come in.
Ephemeral Database
The basic idea is to create a separate database for each test. This way you still get the benefits of fully isolated tests while removing the limitations imposed by ephemeral transactions.
Generally, to create a new database ready to go, you need to run a few commands: create the database, apply migrations. But Postgres has a feature that makes this much simpler: template databases. You create a database, apply migrations (like creating new schemas) once, and then call it a template. That template can then be used to spin up new databases, skipping the repeated setup steps (i.e., migrations). Once it’s all set, copying a template database is much faster than recreating a new database from scratch. When copying, Postgres physically copies the template database on the filesystem instead of replaying DDL or running dump/restore.
In other words all we need from Postgres is:
The ability to mark a database as a template:
UPDATE pg_database SET datistemplate = true WHERE datname = <template-name>;And then use that template to spin up a new database:
CREATE DATABASE <database-name> TEMPLATE <template-name>;Everything else is gluing it all together so it presented via a nice helper function:
// Init creates a new database, applies migrations and marks it as a template,
// so it can be used to spin up new databases.
func Init(ctx context.Context, templateName string) error {
var doesTemplateExist bool
q := "SELECT EXISTS(SELECT 1 FROM pg_database WHERE datname = $1 AND datistemplate = true)"
if err := db.QueryRow(ctx, q, templateName).Scan(&doesTemplateExist); err != nil {
return err
}
// We are all set, no need to create a new template database.
if doesTemplateExist {
return nil
}
// If a non-template database with this name exists, clean it up before creating the template.
if _, err := db.Exec(ctx, fmt.Sprintf("DROP DATABASE IF EXISTS %s", templateName)); err != nil {
return err
}
if _, err := db.Exec(ctx, fmt.Sprintf("CREATE DATABASE %s", templateName)); err != nil {
return err
}
if err := applyMigrations(ctx, templateName); err != nil {
return err
}
// Mark the database as a template.
q = "UPDATE pg_database SET datistemplate = true WHERE datname = $1"
if _, err := db.Exec(ctx, q, templateName); err != nil {
return err
}
return nil
}
// TestPool creates a new ephemeral database from a given templateName
// and returns a pool connected to it.
func TestPool(tb testing.TB, templateName string, basePoolConfig *pgxpool.Config) *pgxpool.Pool {
tb.Helper()
dbName := randomDatabaseName()
if _, err := db.Exec(tb.Context(), fmt.Sprintf("CREATE DATABASE %s TEMPLATE %s", dbName, templateName)); err != nil {
tb.Fatal(err)
}
poolConfig := basePoolConfig.Copy()
poolConfig.ConnConfig.Database = dbName
pool, err := pgxpool.NewWithConfig(tb.Context(), poolConfig)
if err != nil {
tb.Fatal(err)
}
if err := pool.Ping(tb.Context()); err != nil {
pool.Close()
tb.Fatal(err)
}
tb.Cleanup(func() {
pool.Close()
// If the test failed, leave the database intact.
if tb.Failed() {
tb.Logf("Failed test, leaving database %s intact", dbName)
return
}
// Drop the database for successfully passed tests.
_, _ = db.Exec(context.Background(), fmt.Sprintf("DROP DATABASE %s", dbName))
})
return pool
}And here’s a basic test setup:
var templateName = "template-name"
var basePoolConfig *pgxpool.Config
func TestMain(m *testing.M) {
if err := Init(context.Background(), templateName); err != nil {
panic(err)
}
os.Exit(m.Run())
}
func TestFancyFeature(t *testing.T) {
t.Parallel()
t.Run("fancy test 1", func(t *testing.T) {
t.Parallel()
pool := TestPool(t, templateName, basePoolConfig)
feature := FancyFeature{dbExecutor: pool}
feature.CoolAction(t.Context())
rows, err := pool.Query(t.Context(), "SELECT * FROM fancy_feature_table")
require.NoError(t, err)
// Assert resulted rows
// ...
})
t.Run("fancy test 2", func(t *testing.T) {
t.Parallel()
pool := TestPool(t, templateName, basePoolConfig)
feature := FancyFeature{dbExecutor: pool}
feature.CoolAction(t.Context())
rows, err := pool.Query(t.Context(), "SELECT * FROM fancy_feature_table")
require.NoError(t, err)
// Assert resulted rows
// ...
})
// etc.
}pgxephemeraltest
Now that you know all the things, you probably feel, as most developers do, fatigue and no interest in implementing it yourself - all nuts are cracked, there’s only the boring part of gluing things together. This is where I come to rescue you, my fellow dev. I wrote it for you (myself) and exposed it as a neat package called pgxephemeraltest, which does all of this, exposing a small API on top of pgx/v5.
Enough talk, show me the code
The API of the package is quite simple, even your grandma can use it. There are two main structures the package exposes: TxFactory and PoolFactory. Each comes with a bunch of initialization functions for convenient configuration.
Typically, you create a global factory only once in the TestMain and use it across the package.
Here’s an example usage of the isolated-database factory, but the transaction factory can be set up in a similar manner.
var factory *pgxephemeraltest.PoolFactory
func TestMain(m *testing.M) {
ctx := context.Background()
connString, ok := os.LookupEnv("TEST_DATABASE_URL")
if !ok {
panic("TEST_DATABASE_URL environment variable not set")
}
// Assuming that the migration provided here is applying a DDL schema.
var err error
factory, err = pgxephemeraltest.NewPoolFactoryFromConnString(ctx, connString, &migrator{})
if err != nil {
panic(err)
}
os.Exit(m.Run())
}Once the factory is set, you’re free to use it across all tests.
func TestUsers(t *testing.T) {
t.Parallel()
ctx := context.Background()
pool := factory.Pool(t) // a pool connected to a newly created fully isolated database
_, err := pool.Exec(ctx, `INSERT INTO users (name) VALUES ($1)`, "Alice")
require.NoError(t, err)
var name string
err = pool.QueryRow(ctx, `SELECT name FROM users WHERE name = $1`, "Alice").Scan(&name)
require.NoError(t, err)
require.Equal(t, "Alice", name)
}See more examples in the examples folder of the repo.
I personally like the sharedpoolexample as it helps get rid of the TestMain and provides a convenient single function to create isolated databases for each test.
Performance
Finally, to prove that neither approach will tank your test suite, here’s a quick benchmark.
All benchmarks were run on a 14-inch MacBook Pro, M4 Max, 36GB RAM, 14 CPU cores using Postgres 17 with the following settings:
fsync = "off"
full_page_writes = "off"
synchronous_commit = "off"
log_statement = "all"
shared_buffers = "128MB"
max_connections = "10000"As you can guess, the transaction-based testing is outperforming the isolated database approach. But both are performing well - the transaction-based approach takes on average 2ms per transaction, while the isolated database approach takes on average 40ms per database on my machine.
The creation of the initial template takes around 70ms and is typically executed only once per Go package.
BenchmarkTx_NewInstance-14 1002 2103588 ns/op
BenchmarkPool_Template-14 13 78627228 ns/op
BenchmarkPool_NewInstance-14 32 43821270 ns/op