Some things in software development require more than mocks and unit testing. If your application uses a database it makes sense to also hit that database in automated testing to ensure custom SQL queries work correctly, Hibernate relations are set up properly and also that database migrations are successful.

This blog post was written with a focus on the latter. I will be using Spring Boot talking to a PostgreSQL database. The database structure is managed via Flyway and, basically customary for Java applications, Maven serves as the build and dependency management tool. Docker will also play a role because we’ll be creating and running a PostgreSQL docker image for testing. From Maven. Every time the test is executed. And to spice things up, we’ll also create a custom database and user in that dockerized PSQL image.

I have created a working sample on Github and you can follow every single step by taking a look at the commit history. There you can see individual changes, starting from an empty Spring Boot application with no database to the final solution with Spring Data JPA and Flyway.

In the following sections and snippets, I will highlight the important parts of each step.

Initial commit; unit testing

The sample starts with a bare-bones Spring Boot application. I have added a pseudo-dto class named Headphone so that I have something to “test".

The more interesting part is probably DockerDbTestingApplicationTests which will come into play later. Note that this test spins up the whole Spring container to check that everything is wired up correctly. It is an integration test.

Add integration tests

Using the maven-failsafe-plugin I have added integration tests. I elected to store them in a separate directory “test-integration" which requires an additional plugin called build-helper-maven-plugin. Its configuration adds two additional directories to the build process. One for the integration test sources and one for the resource files. This way, Maven knows that I’ve ventured outside of its standard src/main and src/test convention and includes my files in the test phase.

<plugin>
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>build-helper-maven-plugin</artifactId>
    <executions>
        <execution>
            <id>add-integration-test-sources</id>
            <phase>generate-test-sources</phase>
            <goals>
                <goal>add-test-source</goal>
            </goals>
            <configuration>
                <sources>
                    <source>src/test-integration/java</source>
                </sources>
            </configuration>
        </execution>
        <execution>
            <id>add-integration-test-resources</id>
            <phase>generate-test-resources</phase>
            <goals>
                <goal>add-test-resource</goal>
            </goals>
            <configuration>
                <resources>
                    <resource>
                        <filtering>true</filtering>
                        <directory>src/test-integration/resources</directory>
                    </resource>
                </resources>
            </configuration>
        </execution>
    </executions>
</plugin>

To test the setup, I also added the test HeadphoneTestIT. It’s not really an integration test and only serves the purpose of making sure that the plugin setup is correct and the Java test file is picked up by the plugin. It will become more useful in the next step.

Add JPA repository

Nothing special here except that Spring Data JPA is added, together with a database entity HeadphoneEntity (creative, right?) and a corresponding repository that extends CrudRespositor<HeadphoneEntity, Long>. The integration test is revised to now access the database.

@ExtendWith(SpringExtension.class)
@DataJpaTest
@AutoConfigureTestDatabase(replace= AutoConfigureTestDatabase.Replace.NONE)
class HeadphoneTestIT {
    @Autowired
    private HeadphoneEntityRepository headphoneEntityRepository;

    @Test
    void uselessIntegrationTest() {
        // Given
        final var earpods = new HeadphoneEntity();
        earpods.setModel("Apple Earpods");

        final var akg = new HeadphoneEntity();
        earpods.setModel("AKG K240 MKII");

        // When
        final var savedEarpods = headphoneEntityRepository.save(earpods);
        final var savedAkg = headphoneEntityRepository.save(akg);

        // Then
        assertTrue(0 < savedEarpods.getId());
        assertTrue(0 < savedAkg.getId());
    }
}

One important thing to note here is that you must add @AutoConfigureTestDatabase(replace= AutoConfigureTestDatabase.Replace.NONE) to the test class. Otherwise you’ll run into the following error.

Error creating bean with name 'dataSource': Invocation of init method failed; nested exception is java.lang.IllegalStateException: Failed to replace DataSource with an embedded database for tests. If you want an embedded database please put a supported one on the classpath or tune the replace attribute of @AutoConfigureTestDatabase.

Note that at this point in the game I’m still running against a “permanent" PostgreSQL database server. It is a Docker image, but independent of the project. I use this PSQL server for local development on my computer.

Add Flyway

Now it’s time to introduce Flyway to manage the database. The only thing that is required in Spring Boot is to add the dependency…

<dependencies>
  ...

  <dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
  </dependency>

  ...
</dependencies>

… and a folder for the migrations. By default, db/migration (singular!) is expected on the classpath and that’s what I’ll be going with here. That means the migration files are located in src/main/resources/db/migration and are automatically picked up when the application starts.

CREATE SEQUENCE public.hibernate_sequence
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 1;

CREATE TABLE headphone_entity (
    id int8 NOT NULL,
    model varchar(255) NULL,
    CONSTRAINT headphone_entity_pkey PRIMARY KEY (id)
);

In this step I have also created a custom database and user for this demo application. I ran the following statements manually on the PSQL database and updated the application.properties.

postgres=# create database docker_db_testing;
CREATE DATABASE
postgres=# create role docker_db_testing WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION PASSWORD 'docker_db_testing';
CREATE ROLE
postgres=# GRANT CREATE, CONNECT ON DATABASE docker_db_testing TO docker_db_testing;
GRANT

When the application starts then Flyway does its magic and creates the sequence and table that are in the SQL file.

$ mvn spring-boot:run
[INFO] Scanning for projects...
[INFO]
...
[INFO]
[INFO] --- spring-boot-maven-plugin:2.2.6.RELEASE:run (default-cli) @ docker-db-testing ---
[INFO] Attaching agents: []
.   ____          _            __ _ _
/\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/  ___)| |_)| | | | | || (_| |  ) ) ) )
'  |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot ::        (v2.2.6.RELEASE)
2020-04-26 11:28:42.735  INFO 19268 --- [           main] c.t.d.DockerDbTestingApplication         : Starting DockerDbTestingApplication on DESKTOP-37NSN4K with PID 19268 (C:\Users\Robert Lohr\Downlo
...
2020-04-26 11:28:43.360  INFO 19268 --- [           main] o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 6.0.8 by Redgate
2020-04-26 11:28:43.365  INFO 19268 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2020-04-26 11:28:43.430  INFO 19268 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2020-04-26 11:28:43.443  INFO 19268 --- [           main] o.f.c.internal.database.DatabaseFactory  : Database: jdbc:postgresql://localhost:5432/docker_db_testing (PostgreSQL 9.4)
2020-04-26 11:28:43.471  INFO 19268 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 1 migration (execution time 00:00.013s)
2020-04-26 11:28:43.489  INFO 19268 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table "public"."flyway_schema_history" ...
2020-04-26 11:28:43.537  INFO 19268 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema "public": << Empty Schema >>
2020-04-26 11:28:43.546  INFO 19268 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "public" to version 1 - ddl
2020-04-26 11:28:43.576  INFO 19268 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema "public" (execution time 00:00.048s)
...
2020-04-26 11:28:44.831  INFO 19268 --- [           main] c.t.d.DockerDbTestingApplication         : Started DockerDbTestingApplication in 2.368 seconds (JVM running for 2.691)
2020-04-26 11:28:44.835  INFO 19268 --- [extShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2020-04-26 11:28:44.839  INFO 19268 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2020-04-26 11:28:44.844  INFO 19268 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  4.871 s
[INFO] Finished at: 2020-04-26T11:28:44+02:00
[INFO] ------------------------------------------------------------------------

A quick look at the PostgreSQL server confirms the Flyway log messages.

docker_db_testing=# \dt
                    List of relations
Schema |         Name          | Type  |       Owner
--------+-----------------------+-------+-------------------
public | flyway_schema_history | table | docker_db_testing
public | headphone_entity      | table | docker_db_testing
(2 rows)

Note that the tests have not yet been updated to run against the Flyway managed database. This happens in the "Testing against database server" commit. Since this is merely a configuration change I’ll skip right over it.

Testing against dockerized database server

This is where the fun starts. Now we’ll replace the the database server running on my computer with a Docker image that is created when the integration tests are executed.

The first thing to do is add the docker-maven-plugin from fabric8.io to the pom.xml. The important thing here is the <assembly> element. This copies the file src/test-integration/resources/setup-postgres.sql to /docker-entrypoint-initdb.d in the PSQL Docker image. SQL Files in this location are run before starting the PSQL service. See the section Initialization scripts in the documentation.

This creates a custom database and a custom login, each named "docker_db_testing_tests".

<build>
  <plugins>
    ...

    <plugin>
      <groupId>io.fabric8</groupId>
      <artifactId>docker-maven-plugin</artifactId>
      <version>0.33.0</version>

      <configuration>
        <dockerHost>http://localhost:2375</dockerHost>
        <images>
          <image>
            <name>test-postgres</name>
            <alias>test-postgres</alias>
            <build>
              <from>postgres:9.4</from>
              <assembly>
                <mode>dir</mode>
                <targetDir>/docker-entrypoint-initdb.d</targetDir>
                <inline>
                  <files>
                    <file>
                      <source>src/test-integration/resources/setup-postgres.sql</source>
                    </file>
                  </files>
                </inline>
              </assembly>
            </build>
          </image>
        </images>
      </configuration>

      ...
    </plugin>

    ...
  </plugins>
</build>

The next interesting bit can be found in the <execution> element that builds and starts the docker image before the integration test phase. I have added an extra bit of configuration that:

1. Assigns the port to a variable name it-database.port.
2. Waits for the database to become available before continuing to execute the tests. This is done by watching the log output.

<executions>
  <execution>
    <id>start</id>
    <phase>pre-integration-test</phase>
    <goals>
      <goal>build</goal>
      <goal>start</goal>
    </goals>
    <configuration>
      <images>
        <image>
          <name>test-postgres</name>
          <alias>it-database</alias>
          <run>
            <ports>
              <port>it-database.port:5432</port>
            </ports>
            <wait>
              <log>(?s)database system is ready to accept connections.*database system is ready to accept connections</log>
              <time>20000</time>
            </wait>
          </run>
        </image>
      </images>
    </configuration>
  </execution>
  
  <execution>
    <id>stop</id>
    <phase>post-integration-test</phase>
    <goals>
      <goal>stop</goal>
    </goals>
  </execution>
</executions>

For this port configuration to work you also need to add a piece of config to the failsafe-plugin.

<plugin>
  <groupId>org.apache.maven.plugins</groupId>
  <artifactId>maven-failsafe-plugin</artifactId>
    
  ...
    
  <configuration>
    <environmentVariables>
      <it-database.port>${it-database.port}</it-database.port>
    </environmentVariables>
  </configuration>
</plugin>

Then you can finally use this variable in your application.properties like so.

spring.datasource.url=jdbc:postgresql://localhost:${it-database.port}/docker_db_testing_tests

This odd variable name usage is required for some reason. If you change it-database.port to it-database-port in all locations then you’ll get a very weird error.

java.lang.RuntimeException: Driver org.postgresql.Driver claims to not accept jdbcUrl, jdbc:postgresql://localhost:${it-database-port}/docker_db_testing_tests

Note that I have also moved DockerDbTestingApplicationTests to the integration test folders as this now requires a properly working database that is not available for unit test.

Introduce failing migration

The last step is to make sure that the build fails when the migration fails. Therefore, I have added another SQL file for Flyway to pick up. It contains the same exact table definition as the first file and lo and behold, the build fails. Unfortunately, it’ll print the same error message repeatedly, for every test as it seems. That’s not nice, but still better than a failing deployment due to an error in the database migration. Things like that should break the build, not the continuous deployment that comes after.

Famous Last Words

Please check the last commit for the final state of the pom.xml file. I have made some small improvements after the fact that don’t match the individual commits. In the respective sections I have also used the final configuration not the one from the commits.