February 21, 2014 / by Jörg Rathlev / Software engineer / @

Database Integration Tests with Vagrant and Maven

How to use virtualization to run integration tests against a test environment that provides the same database as your production system.

When you’re developing an application that connects to a database, the parts of your code that directly interact with the database cannot be covered by traditional, stand-alone unit tests. To test those parts, you need integration tests. This is especially important if your database queries contain parts of your business logic, for example because you want to filter a large result set on the database layer before loading it into your application.

One common approach to running tests against a database is to use an embedded database like (for Java projects) HSQLDB. However, this approach restricts you to the subset of database features that are available in both the embedded database engine and the database you use in production. In our current project, we’re using PostgreSQL and making use of features like common table expressions and window functions, so we wanted to run our integration tests against a PostgreSQL database.

Virtualization with Vagrant

To make it simple for developers to set up the required test database instance, we use Vagrant to automatically create and provision a local, virtual machine. The PostgreSQL database server and the test database instance are automatically set up in the virtual machine during the provisioning step of Vagrant, so a developer only needs to run the vagrant up command and they’re ready to go.

To learn more about Vagrant, read the execellent documentation on the Vagrant website. My colleague Daniel has also written an article about setting up test environments with Vagrant.

Initializing the database

Once you have your test server, you also need to initialize the test database with the database schema. We use Flyway, a simple and lightweight Java library for applying database migrations. Flyway integrates with Maven (or Gradle or Ant if you’re using that). We’ve set up our Maven POM so that the integration test database is automatically cleared and re-created before running the integration tests.

First, the Flyway plugin is configured in the pluginManagement section:

<plugin>
   <groupId>com.googlecode.flyway</groupId>
   <artifactId>flyway-maven-plugin</artifactId>
   <version>2.3.1</version>
   <configuration>
      <locations>
         <location>filesystem:${basedir}/src/main/resources/database</location>
      </locations>
      <url>${database-url}</url>
   </configuration>
   <dependencies>
      <dependency>
         <groupId>postgresql</groupId>
         <artifactId>postgresql</artifactId>
         <version>9.1-901.jdbc4</version>
      </dependency>
   </dependencies>
</plugin>

Once the Flyway plugin is configured, you can include the database migration in the build section of your POM. In our project, we’re running the integration tests only when they’re activated via a profile.

<profile>
   <!-- Run integration tests against the integration test database -->
   <id>integration-test</id>
   <properties>
      <database-url>jdbc:postgresql://localhost:5432/...</database-url>
   </properties>
   <build>
      <plugins>
         <plugin>
            <groupId>com.googlecode.flyway</groupId>
            <artifactId>flyway-maven-plugin</artifactId>
            <executions>
               <execution>
                  <goals>
                     <goal>clean</goal>
                     <goal>migrate</goal>
                  </goals>
               </execution>
            </executions>
            <inherited>false</inherited>
         </plugin>
         <!-- ... -->
      </plugins>
   </build>
</profile>

Conclusion

By combining Vagrant and a lightweight database migration tool like Flyway, you can provide developers with a means to create their own test environments that closely match your production systems. The tools can be integrated with your build system to automatically run database integration tests as part of the build process.