Spring Boot: Database Migration using Flyway

Often adding a new feature will require a change to the database e.g. adding a new column to a table or creating a new table. These changes could be applied manually by a database administrator however this is likely to create inconsistencies and is difficult to replicate. To resolve these issues the database migrations should be:

• Applied automatically on deployment.
• Stored in source control (i.e. git).

In this article we will use Flyway to apply database migrations automatically and consistently on startup of a Spring Boot application.

Check out the full example on GitHub https://github.com/minibuildsio/stampy.

Pre-requisites

• Familiarity with relational databases and SQL.

Required Dependencies

To integrate Flyway into a Spring Boot application we need to add the flyway-core dependency. Flyway will automatically hook into the Spring Boot initialisation and begin finding a data source to connect using and migrations to apply.

implementation 'org.flywaydb:flyway-core'

Flyway Configuration

Flyway Database Configuration

By default Flyway will connect to the database using the default datasource used by the application however the user for this datasource should have limited privileges for security reasons. For example, your application code doesn’t create tables hence shouldn’t have that privilege but Flyway does need to create tables, etc.

It is advised that you have a separate more privileged user to apply the migrations. The details of the privilege user can be passed to Flyway via spring.flyway like so:

spring:
  # ...  
  flyway:
    url: ${FLYWAY_DB_URL:jdbc:postgresql://localhost:5432/stampydb}
    user: ${FLYWAY_DB_USERNAME:postgres}
    password: ${FLYWAY_DB_PASSWORD:postgres}

Migration Scripts

The migrations are sql scripts following the naming pattern V1__create_stamp_table.sql, V2__add_date_column.sql, … where:

• V - migration type V for versioned, U for undo, and R for repeatable.
• 1 - version prefix used to determine the order of the migrations.
• __ - separator.
• create_stamp_table - description.

It is possible to write migrations in Java but we’ll focus on SQL.

V1__create_stamp_table.sql might simply contain:

create table stamp (
  id serial primary key,
  name character varying(256) not null
);

Note: once applied to the database the migration contents must not change otherwise the migration will fail.

Migration Scripts Location

Flyway scans the classpath or filesystem for migrations to apply, by default Flyway will look in db/migrations in resources. The location that Flyway scans for the migrations can be configured like so:

spring:
  flyway:
    locations: a/different/location

Applying the Migrations

With everything setup when the application starts the migration progress will be logged. Here we can see Flyway successfully applied the migration from an empty database. If there were more migrations to apply Flyway would attempt to apply them.

INFO  Creating Schema History table "public"."flyway_schema_history" ...
INFO  Current version of schema "public": << Empty Schema >>
INFO  Migrating schema "public" to version "1 - create stamp table"
INFO  Successfully applied 1 migration to schema "public", now at version v1

Flyway records which migrations have been applied in a table called flyway_schema_history. After the first migration the flyway_schema_history table will look something like this:

Note: the checksum of the file is stored, this is used to validate that the migrations haven’t changed since being applied to the database.