Testing database interactions in Golang

You can have a test for a method that executes an SQL query, but it won't tell you if the query is actually correct. Seeing a 100% coverage for it is a false assurance. You actually have to test your query against a real database.

Preparing the database

When I worked with Django, it offered a built-in test framework that runs tests against a temporary database. It created the database, ran the migrations, and then ran the tests. They could even be isolated within a transaction without causing bad interactions.

For Django there was even a sidekick library called factory_boy, that would hook into the ORM and create the right objects with the right relations and plug in fake data where needed. You'd be able to override a field nested inside a relation to make your assertions work.

# this creates a person, 
# and a related address with the city set to 'New York'.
p = PersonFactory.create(
    name="John Doe", 
    address__city="New York",
)
assert p.address.city == "New York"

There are some solutions for database testing in Golang, such as sqlmock or testcontainers, but they all feel clunky, cumbersome to use because of one thing: fixtures. How do you populate the database with the right data for your tests?

ORMs are not welcome in Golang. The language is not really built for dynamism. We write SQL statements directly without using an ORM, so the relations are only mapped in the developer's head. We can accept this as a fact and build our harness around it.

The testfixtures library helps with this. You write YAML files that describe which tables have which rows, and the library will insert those rows.

persons:
  - id: 1
    name: John Doe
    address_id: 21

addresses: 
  - id: 21
    city: Berlin
    street: Boxhagenstr. 10

We can build a test helper that populates the database with the right data and runs the queries against it.

func applyFixtures(t *testing.DB, db *sql.DB, fixturePath string) {
    t.Helper()
    fixtures, err := testfixtures.New(
        testfixtures.Database(db),
        testfixtures.Dialect("postgres"),
        testfixtures.Files(fixturePath),
    )
    require.NoError(t, err)
    
    return fixtures.Load()
}

func newTestDB(t *testing.T) *sql.DB {
    // this is a database created from the latest seed
    db, err := sql.Open("postgres", "user=postgres dbname=test sslmode=disable")
    require.NoError(t, err)

    t.Cleanup(func() {
        db.Close()
    })

    return db
}

func TestGetUserAddress(t *testing.T) {
    db := newTestDB(t)
    applyFixtures(t, db, "testdata/get_user_address.yaml")

    repo := NewRepo(db)
    
    userID := 1 // this refers to an ID in the fixture
    addr, err := repo.GetUserAddress(t.Context(), userID)
    require.NoError(t, err)

    assert.Equal(t, "Berlin", addr.City)
}

Isolating tests

This is nice and all, but it leaves the database in a dirty state. Those rows remain in the database, and if we run multiple tests, they might interfere with each other. We need to wrap each test in a transaction and roll it back when the test ends.

The txdb library fits well here.

import (
    ...	
    "github.com/DATA-DOG/go-txdb"
    _ "github.com/lib/pq"
)

func init() {
    // we register an SQL driver named "txdb"
    txdb.Register("txdb", "postgres", "user=postgres dbname=test sslmode=disable")
}

func newTestDB(t *testing.T) *sql.DB {
    // this is a database created from the latest seed
    realDB, err := sql.Open("postgres", "user=postgres dbname=test sslmode=disable")
    require.NoError(t, err)

    // apply the migrations to prepare it for tests
    err = migrations.Run(t.Context(), realDB)
    require.NoError(t, err)

    // use a dynamic name for the database connection to avoid conflicts
    testDB, err := sql.Open("txdb", t.Name())
    require.NoError(t, err)	

    t.Cleanup(func() {
        testDB.Close()
    })
    return testDB
}

Dealing with the setup overhead

Running migrations for every test means the schema version is checked each time. The logic in migrations.Run hopefully skips over applied migrations, but still, it adds overhead across multiple packages. We need to run it once per test run, which includes all packages being tested.

We might reach for a sync.Once, but it won't work as you'd expect. Golang compiles your tests and executes them in parallel as separate processes. So the sync.Once will only work for a single test process, not across all of them.

We need a global mutex to synchronize all those migrations. Postgres has our back.

func lock(t *testing.T, db *sql.DB) func() {
    t.Helper()
    const lockID = 1234567890 // some unique number
    _, err := db.Exec("SELECT pg_advisory_lock($1)", lockID)
    require.NoError(t, err)

    return func() {
        _, err := db.Exec("SELECT pg_advisory_unlock($1)", lockID)
        require.NoError(t, err)
    }
}

lockID is a hardcoded random number ¹ that is shared across all runs. This removes the need for a sync.Once and allows us to run migrations once per test run.

func migrateTestDB(*testing.T, db *sql.DB) {
    unlock := lock(t, db)
    defer unlock()

    // migrations will run once per test run.
    err := migrations.Run(t.Context(), db)
    require.NoError(t, err)
}

func newTestDB(t *testing.T) *sql.DB {
    // this is a database created from the latest seed
    realDB, err := sql.Open("postgres", "user=postgres dbname=test sslmode=disable")
    require.NoError(t, err)

    migrateTestDB(t, realDB)
    ...
}

Where it falls short

This is a simplified version of what we use in production. However, it has some downsides.

For one, the fixtures are not easy to prepare. Relations and values should make sense. Denormalized columns should be consistent across tables. We often end up with a lot of boilerplate data in the YAML files, because real-world tables don't consist of a handful of columns — they have tens of columns. Queries often join many tables, so you have to specify the supporting rows for your query ².

Also, the queries are often not simple. If they have many WHERE clauses, it is not easy to prepare the "right" fixture that would satisfy the right combination of conditions. So you end up with huge fixtures that are difficult to maintain and reason about.

The fixtures are also not reviewable, because you can't put them next to the test code that uses them. You often end up referring to records by their IDs, which are not meaningful. The reviewer has to take the author's word for it.

This setup makes it straightforward to test your business logic that's embedded in SQL queries against a real database. The harder part is maintaining the fixtures, keeping them small enough that the test's intent remains clear.