Database Migration with Flyway

Motivation

We use Flyway to take care of our database migrations. Please read "Why database migrations?" for an introduction and "How Flyway works".

Our Implementation

We have our Flyway Migration Scripts in the subdirectories below Showcase/backend/src/main/resources/db/migration:

• "common" - includes migration artifacts which are independent of the database
• "h2" - includes migration artifacts which are specific to the h2 in-memory database
• "mysql" - includes migration artifacts which are specific to the MySQL database used in cloud deployments

We use the following schema when naming artifacts:

V<schema version>_<sequential number>__<description>.sql

Example: V01_003__create_shipment.sql

Description:

• schema version is always 01 (as long as we don't come up with a new schema)
• sequential number should always be incremented by the value one to the previous artifact
• description describes the change

Scenarios

The following scenarios show how migration artifacts might look like.

Scenario "New Entity"

When adding a new entity it helps to temporarily enable the following properties (in application.properties) so that you can use the generated file database-schema.sql as a template for the flyway migration scripts.

spring.jpa.properties.javax.persistence.schema-generation.create-source=metadata
spring.jpa.properties.javax.persistence.schema-generation.scripts.action=create
spring.jpa.properties.javax.persistence.schema-generation.scripts.create-target=database-schema.sql
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

Example:

create table customer (
  id bigint not null auto_increment,
  city varchar(255),
  street varchar(255),
  street_no varchar(255),
  zip_code varchar(255),
  name varchar(255) not null,
  uuid binary(255) not null,
  primary key (id)
);

Scenario "Modified Entity"

When modifying an entity you need to manually create an DDL SQL script which changes the database schema, e.g. adds a new column. In some cases you may also need DML scripts to migrate data. It might help to generate the database schema as described in "New Entity" above, e.g. to find out the type of a new column.

Example:

alter table customer add column xxx varchar(255);