Testing PostgreSQL Applications From Scratch (Almost)

This article demonstrates how to use a lightweight, temporary PostgreSQL database to support unit and integration tests within a deployment pipeline built on Docker. This approach is particularly useful for applications which do not employ a framework that offers database configurations for testing.

Glacier National Park, Montana, USA (Source: Daniel George)

(Almost) From Scratch

The below implementation uses an external library: http://eradman.com/ephemeralpg/. At its core, this library is a series of shell commands which start a PostgreSQL database instance. While this approach isn’t as straightforward as modifying a config file, it’s still easy to understand and implement. Some requirements for understanding this approach are: familiarity with Docker (understanding Dockerfiles, what a container is, what an image is, etc.) and a little familiarity with shell and the terminal.

Quickstart

The Dockerfile contains the necessary commands to retrieve and unzip the external pg_tmp library. Here is the Dockerfile and an example test for a Java application. You can find this code in its entirety on Github.

Dockerfile:

FROM maven:3.5.2-jdk-8

ENV PATH="/usr/lib/postgresql/9.6/bin:${PATH}"

RUN apt-get update && \
    apt-get install -y python-pip postgresql

COPY src /src
COPY pom.xml /pom.xml
COPY target /target

RUN mvn install -U

ADD http://ephemeralpg.org/code/ephemeralpg-2.5.tar.gz /src

RUN tar -xzf /src/ephemeralpg-2.5.tar.gz -C /src && rm /src/ephemeralpg-2.5.tar.gz
RUN pg_tmp=$(find /src -maxdepth 2 -type d -name '*ephemeralpg*') && make install -C $pg_tmp

RUN chown -R postgres:postgres /src
RUN chmod 777 /src
RUN chmod 777 /target

# Switch USER to non-root to run
USER postgres

CMD [ "sh", "-c", "java -jar /target/name-of-your-jar-with-dependencies.jar" ]

Java:

public static void testInsert() throws Exception {

    try {

        Process p = Runtime.getRuntime().exec("pg_tmp -t");
        p.waitFor();

        BufferedReader input = new BufferedReader
                (new InputStreamReader(p.getInputStream()));

        String s = input.readLine();
        String pg_uri = "jdbc:postgresql://" +
            s.substring(s.lastIndexOf('@') + 1);
        input.close();

        Connection conn = DriverManager.getConnection(pg_uri);
        Statement stmt = conn.createStatement();

        stmt.execute("CREATE TABLE trees (id VARCHAR (13), " +
                "common_name VARCHAR (50), " +
                "scientific_name VARCHAR (50));");

        stmt.execute("INSERT INTO trees (id, common_name,
                    scientific_name)" +
                "VALUES (1, 'California Redwood', 'Sequoia
                    sempervirens')");

        ResultSet rs = stmt.executeQuery("SELECT * " +
                "FROM trees;");

        while(rs.next()) {
            assertEquals("Sequoia sempervirens", rs.getString(3));
        }

    } catch (IOException | SQLException e) {
        System.out.println(e);
    }
}

Summary

Lines 14–17 in the Dockerfile retrieve the external library, unzip it, and run make. In more recent versions of Postgres certain commands cannot be run, such as initdb, as the root user. It is suggested to switch users to the postgres user in the Dockerfile in line 24 before executing the file containing any database logic. We grant the necessary permissions beforehand on lines 19–21.

Here are links to this approach implemented in Java and Python.

That’s it! Now you have a lightweight PostgreSQL instance that fits neatly into a Docker CI/CD pipeline for unit and integration testing.