Testing PostgreSQL Applications From Scratch (Almost)
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.
['Daniel George']
['Technology', 'Postgresql', 'Java', 'Python', 'Docker']