Grégoire Paris for ManoMano Tech Team

Posted on Jun 26

Uncompromising integration tests for PostgreSQL with pgdbtemplate, Liquibase and Testcontainers

#go #testcontainers #postgres

Recently, I wanted to replace a brittle integration testing setup I had in a Golang project: it resorted to a system of calls to TRUNCATE that hopefully put database tables back into a state suitable for running other tests. The codebase was riddled with calls to a homemade package using filesystem-based locks that ensured that two tests dealing with the same table would not run at the same time, so that they would not interfere with each other. Managing the state of my test database had become a real pain point. I had already done several iterations and had a pretty clear idea of what I wanted at this point. I wanted something that wouldn't be a compromise between the following things:

having a single system to create database objects like tables and indices in production or locally when testing;
being able to run tests that query the same tables at the same time;
having a test suite that is fast;
having a test suite that does not start or initializes more Docker containers than needed.

Then I found 2 hidden gems: a PostgreSQL feature and a Golang library leveraging it that made my life much easier. Read on.

The search for a better system

In the past, while working on PHP projects, I had been using transactions, for instance with the doctrine-test-bundle. Transactions provide isolation, and if you don't commit them, they allow you to start afresh for each test. This means the code under test itself should not be using transactions, or at least detect them and resort to savepoints instead, which can get tricky.

During my quest for a similar Golang library, I came across pgdbtemplate, and was exposed to the very interesting concept of PostgreSQL templates.

I learnt CREATE DATABASE works by copying a template, and that by default, it contains 2 templates:

template0, that you should never change and represents a pristine database;
template1, that you may change so that every database you create contains database objects you define.

So when using PostgreSQL templates for testing, it's simple: you create a database for each and every test. You can run those tests in parallel, you start from a clean slate for every test. You can have a single Docker container with several tests interacting with it at the same time, and still have perfect isolation, and you can test code that resorts to transaction without thinking twice about it. Having a single Docker container is interesting from a performance point of view, it removes the overhead of creating and starting containers.

If you use Testcontainers, you can end up with tests looking as follows (this is copied from pgdbtemplate's README):

func TestUserServiceWithContainer(t *testing.T) {
    ctx := context.Background()

    // Create test database from template.
    testDB, testDBName, err := templateManager.CreateTestDatabase(ctx)
    if err != nil {
        t.Fatal(err)
    }
    defer testDB.Close()
    defer templateManager.DropTestDatabase(ctx, testDBName)

    // Test your service with the isolated database.
    userService := NewUserService(testDB)

    user := &User{Name: "Alice", Email: "alice@example.com"}
    if err := userService.Create(ctx, user); err != nil {
        t.Fatal(err)
    }

    users, err := userService.List(ctx)
    if err != nil {
        t.Fatal(err)
    }

    if len(users) != 1 {
        t.Errorf("Expected 1 user, got %d", len(users))
    }
}

As you can see, there is no locking, no transactions, it is as simple as creating a database for your test, and cleaning it up when you are done.

Because it does not resort to rolling back transactions, you could even consider writing end to end tests with several HTTP requests calling several different endpoints that would test complex scenarios involving several transactions being committed, and data being persisted.

The rest of this blog post relies on some understanding of pgdbtemplate itself. Consider reading the blog post from the library's author or the README of the project before we move on.

The missing piece

But the thing is, nobody wants to start from an empty database for every test. You want to start from a known state, and that state is usually a database with empty tables. No data, but an up-to-date schema. In some cases, you may even want to seed some tables.

At ManoMano, we use several different programming languages, but we always reach out for the same tool for migrations: Liquibase. It is a tool from the Java world, but with Docker that hardly matters: you write migration files, you run Liquibase, pointing it to your PostgreSQL container, and voilà! Your database is ready to be used!

Here is an example of a migration (or, in Liquibase parlance, a changelog):

--liquibase formatted sql

--changeset gregoire.paris:1
create table test1 (
    id int primary key,
    name varchar(255)
);
--rollback drop table test1;

pgdbtemplate does not natively integrate with Liquibase at the time of writing, however it allows you to write your own implementation and use it, thanks to a dedicated interface:

// MigrationRunner executes migrations on a PostgreSQL database connection.
type MigrationRunner interface {
    // RunMigrations runs all migrations on the provided connection.
    RunMigrations(ctx context.Context, conn DatabaseConnection) error
}

Not too complicated, right? So to use Liquibase with pgdbtemplate, we need to write an implementation that starts a Docker container configured to point at our PostgreSQL instance, with the right access to it, and that has our Liquibase files mounted on it, and to run the liquibase binary like so:

liquibase --searchPath=/workspace \
    --changelog-file=db.changelog-master.xml \
    --url=jdbc:postgresql://the_postgresql_container:5432/the_template \
    --username=the_user \
    --password=the_password \
    update

The goal would be to end up with this:

The code that calls the migration runner looks like this:

// Connect to template database and run migrations.
// We do not use admin database to follow the least privilege principle.
templateConn, err := tm.provider.Connect(ctx, tm.templateName)
if err != nil {
    return fmt.Errorf("failed to connect to template database: %w", err)
}
defer templateConn.Close()

// Run migrations.
if err := tm.migrator.RunMigrations(ctx, templateConn); err != nil {
    return fmt.Errorf("failed to run migrations on template: %w", err)
}

As you can see, templateConn is a connection to the template database, which we need for building the JDBC URL above, so obtaining it should be as simple as SELECT current_database(). One less thing to configure. The rest (PostgreSQL host, port, username, password) will have to be provided when creating the migration runner.

In our case, we have a library that provides us with a struct named postgres.PostgresContainer providing just that, so that's what I've been using to implement the migration runner, but that's coupling that will have to be removed if this is ever open-sourced or even merged upstream.

Here is what the method building the JDBC URL could look like if I were to remove that coupling:

func (r *liquibaseMigrationRunner) buildJDBCUrl(
    ctx context.Context,
    conn pgdbtemplate.DatabaseConnection,
    containerIP string,
) (jdbcURL string, err error) {
    row := conn.QueryRowContext(ctx, "SELECT current_database()")
    var database string

    if err := row.Scan(&database); err != nil {
        return "", fmt.Errorf("failed to get current database name: %w", err)
    }

    // Build JDBC URL using internal container IP
    return fmt.Sprintf("jdbc:postgresql://%s:5432/%s", containerIP, database), nil
}

Using docker containers in tests

At ManoMano, we use testcontainers. That library allows us to start containers programmatically, from our tests, on demand. That is what we are going to use to create and start our Liquibase container.

Testcontainers comes with a concept of container request. Here is what the one we want to use should look like:

func (r *liquibaseMigrationRunner) buildContainerRequest(
    jdbcURL,
    user,
    password,
    bindMountPath string,
    networks []string,
) testcontainers.ContainerRequest {
    return testcontainers.ContainerRequest{
        Image: "library/liquibase:4",
        Cmd: []string{
            "--searchPath=/workspace",
            "--changelog-file=db.changelog-master.xml",
            "--url=" + jdbcURL,
            "--username=" + user,
            "--password=" + password,
            "update",
        },
        HostConfigModifier: func(hc *container.HostConfig) {
            hc.Binds = []string{bindMountPath + ":/workspace"}
        },
        WaitingFor: wait.ForExit(),
        Name:       "postgrestest-liquibase",
        Networks: networks,
    }
}

💡 It's always a good idea to name your containers. If you do not, Docker will pick random names for you, making things harder to figure out.

A word of warning: if you want the Liquibase container to be able to reach out to the PostgreSQL container, they should sit on the same Docker network. You should obtain the PostgreSQL networks, and reuse them in the container request.

Running the Liquibase container

Once you have built the testcontainers request, you can create a running container from it. And of course, you should stop anything you start, so let's make a deferred call to Terminate.

container, err := testcontainers.GenericContainer(ctx, testcontainers.GenericContainerRequest{
    ContainerRequest: req,
    Started:          true,
})
if err != nil {
    return fmt.Errorf("failed to start liquibase container: %w", err)
}
defer container.Terminate(ctx)

When Terminate is called, the container should have stopped already, but Terminate can remove it. What truly matters is checking the exit code, and if it is non-zero, gather logs and build an error from that:

state, err := container.State(ctx)
if err != nil {
    return fmt.Errorf("failed to get container state: %w", err)
}

if state.ExitCode == 0 {
    return nil
}

logs, logErr := container.Logs(ctx)
if logErr != nil {
    return fmt.Errorf(
        "liquibase migration failed with exit code %d, failed to get logs: %w",
        state.ExitCode,
        logErr,
    )
}
defer logs.Close()

logBytes, readErr := io.ReadAll(logs)
if readErr != nil {
    return fmt.Errorf(
        "liquibase migration failed with exit code %d, failed to read logs: %w",
        state.ExitCode,
        readErr,
    )
}

return fmt.Errorf(
    "%w exit code %d, logs: %s",
    errLiquibaseMigrationFailed, // custom error built with errors.New()
    state.ExitCode,
    string(logBytes),
)

Making it work in the CI

When running the test suite locally, bindMountPath needs to hold the path to your Liquibase files. In our CI, such bind mounts are limited to a handful of directories. If I try creating a bind mount from the directory where my test suite runs, I will face the infamous error message I already mentioned in an earlier blog post (yes, it's also about testing). The solution is similar: copy the files to a directory that is part of the allow list. Again, I picked /tmp for this.

Here is what the method returning the bindMountPath variable may look like:

func (r *liquibaseMigrationRunner) prepareLiquibaseFiles() (
    bindMountPath string,
    cleanup func() error,
    err error,
) {
    isCI := os.Getenv("CI") == "true"

    if !isCI {
        return r.changelogPath, nil, nil
    }

    tempDir, tempErr := os.MkdirTemp("/tmp", "liquibase-*")
    if tempErr != nil {
        return "", nil, fmt.Errorf("failed to create temp directory: %w", tempErr)
    }

    // In order to keep this blog post concise,
    // I leave the implementation of copyLiquibaseFiles()
    // as an exercise to the reader
    if copyErr := copyLiquibaseFiles(r.changelogPath, tempDir); copyErr != nil {
        if removeErr := os.RemoveAll(tempDir); removeErr != nil {
            return "", nil, fmt.Errorf("failed to copy liquibase files: %w (cleanup failed: %w)", copyErr, removeErr)
        }

        return "", nil, fmt.Errorf("failed to copy liquibase files: %w", copyErr)
    }

    return tempDir, func() error {
        return os.RemoveAll(tempDir) // Best effort cleanup
    }, nil
}

Using pgdbtemplate with multiple packages

Some folks like to write either unit tests, or tests that test the entire flow of their process. I believe there is a middle ground between unit tests and end to end tests, called integration tests, and that one should not skip that stage of the test pyramid.

That blog post I just linked to contains a paragraph that resonates with me:

I mentioned before that “unit tests” is a vague term, this is even more true for “integration tests”. For some people integration testing means to test through the entire stack of your application connected to other applications within your system. I like to treat integration testing more narrowly and test one integration point at a time by replacing separate services and databases with test doubles. Together with contract testing and running contract tests against test doubles as well as the real implementations you can come up with integration tests that are faster, more independent and usually easier to reason about.

I'm also in the latter camp. If I have an application that deals with e.g. Algolia and PostgreSQL, I will write a test that deals with Algolia, and a test that deals with PostgreSQL.

If the code that deals with Algolia has 3 codepaths, and the code that deals with PostgreSQL has 4, and you are in the former camp, you should probably write 3×4=12 tests. If you are in the latter camp, you should write 3+4=7 tests.

In my opinion, the value of an end-to-end test is to check that things are plugged together properly, or to validate the top 3 critical scenarios of your application, otherwise you will end up with a test suite that is slow, and failures that are hard to understand.

This means that if you have several packages that deal with postgresql, like foo and bar, you will write integration tests for these 2 packages, as opposed to writing a test for cmd, a single package.

While trying to achieve that, I learnt that in Go, testing 2 packages leads to building 2 test binaries and running them. That is what go test does. That means that you have 2 independent processes, and that is an issue if you want to have a single PostgreSQL container migrated once, with a lot of databases inside.

Luckily, testcontainers comes with an experimental option called WithReuseByName 🧪, that allows you to avoid creating a PostgreSQL instance per test binary.

Likewise, it is possible to ensure the migration is only run once by using a system of filesystem locks (so that it works across several binaries).

It was not easy to do, but once I cracked it, I contributed a documentation page explaining all the details.

What this means for my team

With this in place, my team has now a testing setup that:

is isolated;
is fast because there is no truncating, and there are no locks;
requires no clumsy wrapping where you need to run a command before running go test;
is the same between development and CI.

This means that writing integration tests for PostgreSQL is no longer a chore and that we end up having tests that really test the important part of the code that deals with PostgreSQL: the SQL queries themselves.

It works because: