Using Embedded PostgreSQL Databases for Unit Testing

Historically, testing database functionality has been difficult. I have worked on multiple projects that were plagued by either using a different datastore for testing, H2, or worse, not testing their SQL.

By using the open-source embedded Postgres library, we were able to:

• Test all SQL with each build
• Test all our database features, including flyway, create modifications and migrations, stored procedures, queries, as well as several other features of current Postgres releases that were not compatible with H2, our old way of testing.

All Examples Will Be Using Spring-Boot, Maven, JUnit, and Java 8.

Basic Example

Include pg-embedded and Postgres JARs in your dependency management service. We use maven.

<properties>
  <postgres.version>42.1.1</postgres.version>
  <pg-embedded.version>0.7.1</pg-embedded.version>
</properties>

#in your pom.xml include postgres and opentable libraries
<dependencies>

  <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>${postgres.version}</version>
  </dependency>

  #we are running only during the build, so we can scope 'test' so that we dont bloat our final JAR
  <dependency>
    <groupId>com.opentable.components</groupId>
    <artifactId>otj-pg-embedded</artifactId>
    <version>${pg-embedded.version}</version>
    <scope>test</scope>
  </dependency>

</dependencies>

  @Test
  public void testEmbeddedPg() throws Exception
  {

    try (EmbeddedPostgres pg = EmbeddedPostgres.start();
        Connection c = pg.getPostgresDatabase().getConnection()) {

        Statement s = c.createStatement();
        ResultSet rs = s.executeQuery("SELECT 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertFalse(rs.next());

    } catch (Exception e) {
      System.out.print(e);
      fail();
    }
  }

Production Schema (Flyway)

In order to use a flyway preparer and easily run against your production flyway, you will need to also include flyway as one of your dependencies.

If you are using Spring @EnableAutoConfiguration, you may start getting compile-time errors, and you will need to add the following to your properties. If you are not getting any build errors, then you can skip the rest of this paragraph. What is happening is Spring will detect JARs on your classpath and then enable what it assumes are reasonable defaults. In this case, it is detecting the flyway JAR and is trying to create a bean and run flyway in your runtime environment.

flyway.enabled=false

The following is a good example of something you can use for a large suite of tests. The database is only created once, at a class level, and then reused within each test. Line 5 shows how I configured it to look for my flyway scripts by passing the location within my project. This saves us from needing to copy this SQL into our tests.

The method annotated @BeforeClass will run once, before any tests execute, and can be a good place to insert test data that will not be changed within your individual tests. It is also a good place for any static initialization, such as instantiation of Jdbc classes.

Since JUnit runs tests in parallel and in no set order, if you need the same data reset before each test you should add it into a @Before setup method, or even drop all and re-initialize data within each test block. Since the database itself is a shared resource, resetting any data before each test may be the best option in keeping with the atomic nature of a unit test.

  //my system under test
  private static MyDao sut;

  @ClassRule
  public static PreparedDbRule db = EmbeddedPostgresRules.preparedDatabase(FlywayPreparer.forClasspathLocation("db/migration"));

  @BeforeClass
  public static void setUp() {
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(db.getTestDatabase());

    sut = new MyDaoJdbcImpl(namedParameterJdbcTemplate);

    try (Connection c = db.getTestDatabase().getConnection();
         Statement stmt = c.createStatement()) {
      stmt.execute(
        "delete from schema.table;\n" +

          "insert into schema.table\n" +
          "  (body, type, resource_uri, created, last_modified) values\n" +
          "  ('foo', 'TEXT', '/resource/uri', '2011-11-09', '2011-11-09');\n");
    } catch (Exception e) {
      fail();
    }
  }

  @Test
  public void test_read() {
    List<MyClass> myObjects = sut.doRead();
    assertFalse(myObjects.isEmpty());
  }

Production Queries

If you use the same version of PostgreSQL as your database is running, you can have confidence that your queries will work syntactically and within your schema when you do push to production.

This also becomes an invaluable tool in maintaining that your code behaves the same way as you continue to develop. You can even change your underlying SQL queries and know that your tests will act as a contract in the future.

Drawbacks

The only drawback would be that it can slow your tests down some. This can be minimized by reusing a single instance and thus only building your embedded database once, rather than in each test.