October 22, 2016

TravisCI PostgreSQL flyway migrations

Overview

This post is about using Travis CI to test a Java Spring Boot (or similar) project that uses PostgreSQL (or any other supported DB) with flyway migrations built by gradle. I've done most of these steps separately, but getting them all to work together with Travis took some time.

To give some background, The documentation is sparse and I had to combine many other techniques to get everything to work correctly.

I probably could have got away with using build environment variables (I've done this with TeamCity to great effect) to set the database config, but a second config was the most straightforward approach on an existing codebase.

Prerequisites

  • Java Spring (or Spring boot) project compiled against java 8 - I tested against a fresh spring boot 1.4 project using gradle.
  • flyway is setup in your gradle build. instructions
  • furthermore, your database migrations are created in a way that they can flyway from an empty db
  • TravisCI already pointing at your github repository

Overview of steps

  1. create a test/CI only copy of application properties
  2. setup gradle + Flyway and spring toggling application properties files
  3. configuring Travis CI

Test specific app properties

I have an application.properties file setup with my usual spring datasource development database, eg,

    spring.datasource.driverClassName=org.postgresql.Driver
    spring.datasource.username=reload
    spring.datasource.password=reload
    spring.datasource.url=jdbc:postgresql://192.168.1.18:5432/reload?autoReconnect=true
    spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
    spring.jpa.show-sql=false

the first step is to copy this file to application.test.properties (in src/main/resources) and change the database username to 'postgres'. Travis CI will use the username 'postgres' with no password on a database you can choose to create. I stuck with the documentation example and used the database 'travis_ci_test'.

The updated application.test.properties would look like this


spring.datasource.driverClassName=org.postgresql.Driver
spring.datasource.username=postgres
spring.datasource.password=
spring.datasource.url=jdbc:postgresql://localhost/travis_ci_test
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.show-sql=false

Now we need to let the source know how to determine which file to use

Spring @PropertySource and gradle flyway setup

These instructions are from the following page on jayway, which was super helpful. I will summerize here the steps I took to implement this approach.

First, pick an environment variable name to set the application properties. You will need to remember this for java code and the Travis CI config. I used 'APP_PROPS_FILE'.

In your spring application entry point (Usually Application), wherever you have @SpringBootApplication, @Configuration, entity scan setup, etc, you need to add the following @PropertySource annotations.

    @SpringBootApplication
    @PropertySource("classpath:application.properties")
    @PropertySource(value="classpath:${APP_PROPS_FILE}", ignoreResourceNotFound = true)
    public class MyCoolApplication {
        // ...

There is two so if the environment variable is not set, it will default to the 'application.properties' file.

Next, we need to also update our gradle so flyway knows this trick as well.

make your entire flyway task look like this

    flyway {
        Properties props = new Properties()
        def springProp = System.getenv("APP_PROPS_FILE") ?: "application.properties"
        props.load(new FileInputStream("${projectDir}/src/main/resources/" + springProp))
        url = props."spring.datasource.url"
        user = props."spring.datasource.username"
        password = props."spring.datasource.password"
    }

this is the same concept, it will default to application.properties if the environment variable is not set. We're almost done

Configuring Travis CI for PostgreSQL and running flyway

Now the part that ties it all together, instructing travis to create a test database and flyway it.

the first thing we need to add is our environment variable we setup, setting the test file we want to use, add the following to .travis.yml

    env:
    - APP_PROPS_FILE="application.test.properties"

Travis will set the correct environment var for the build! Lets make it use PostgreSQL and create the database before the build with the following:

    services:
    - postgresql

    addons:
    postgresql: "9.4"

    before_script:
    - psql -c 'create database travis_ci_test;' -U postgres

This ensures we have an empty 'travis_ci_test' database ready. Lastly, we need to customize the build script to actually run flyway. I personally use gradle test task over check, so I have that here, but change anything after flywayMigrate -i to whatever you like (test, check, war, jar, etc)

    # if you're using gradlew wrapper, else replace with 'gradle'
    script: ./gradlew flywayMigrate -i test

Boom! Now we flyway a fresh db before running tests automagically every build. It's worth noting that gradle will run 'gradle assemble' as the install step first as well.

What's the '-i' on the flyway task? It is info and will display details and status of migrations, it's totally optional though.

Opinionated side notes

  • Flyway is excellent and is an effective way to version control your database. USE IT
  • You can automatically prefix your flyway migrations, see THIS GIST
  • Spring boot 1.4 added many nice testing features which made this even more low friction

PostgreSQL CI DevOps


Previous post
Static site gen madness I razed old site and finally got around to using a static site generator, Jekyll which is pretty nifty but damn if it's painful to get going on
Next post
PL/pgSQL Conway's game of life I recently started learning the details of PL/pgSQL. It actually started with me wanting to learn Oracle PL/SQL but giving up after looking at