guide flyway

Table of Contents

• Flyway
  • Organizational Advice
  • Technical Configuration
  • Naming Conventions

Flyway

Flyway is a tool for database migration and schema versioning. See why for a motivation why using flyway.

Flyway can be used standalone e.g. via flyway-maven-plugin or can be integrated directly into your app to make sure the database migration takes place on startup. For simplicity we recommend to integrate flyway into your app. However, you need to be aware that therefore your app needs database access with full schema owner permissions.

Organizational Advice

A few considerations with respect to project organization will help to implement maintainable Flyway migrations.

At first, testing and production environments must be clearly and consistently distinguished. Use the following directory structure to achieve this distinction:

  src/main/resources/db
  src/test/resources/db

Although this structure introduces redundancies, the benefit outweighs this disadvantage. An even more fine-grained production directory structure which contains one sub folder per release should be implemented:

  src/main/resources/db/migration/releases/X.Y/x.sql

Emphasizing that migration scripts below the current version must never be changed will aid the second advantage of migrations: it will always be clearly reproducible in which state the database currently is. Here, it is important to mention that, if test data is required, it must be managed separately from the migration data in the following directory:

  src/test/resources/db/migration/

The migration directory is added to aid easy usage of Flyway defaults. Of course, test data should also be managed per release as like production data.

With regard to content, separation of concerns (SoC) is an important goal. SoC can be achieved by distinguishing and writing multiple scripts with respect to business components/use cases (or database tables in case of large volumes of master data ^[1]. Comprehensible file names aid this separation.

It is important to have clear responsibilities regarding the database, the persistence layer (JPA), and migrations. Therefore a dedicated database expert should be in charge of any migrations performed or she should at least be informed before any change to any of the mentioned parts is applied.

Technical Configuration

Database migrations can be SQL based or Java based.

To enable auto migration on startup (not recommended for productive environment) set the following property in the application.properties file for an environment.

flyway.enabled=true
flyway.clean-on-validation-error=false

For development environment it is helpful to set both properties to true in order to simplify development. For regular environments flyway.clean-on-validation-error should be false.

If you want to use Flyway set the following property in any case to prevent Hibernate from doing changes on the database (pre-configured by default in devonfw):

spring.jpa.hibernate.ddl-auto=validate

The setting must be communicated to and coordinated with the customer and their needs. In acceptance testing the same configuration as for the production environment should be enabled.

Since migration scripts will also be versioned the end-of-line (EOL) style must be fixated according to this issue. This is however solved in flyway 4.0+ and the latest devonfw release. Also, the version numbers of migration scripts should not consist of simple ascending integer numbers like V0001…​, V0002…​, …​ This naming may lead to problems when merging branches. Instead the usage of timestamps as version numbers will help to avoid such problems.

Naming Conventions

Database migrations should follow this naming convention: V<version>__<description> (e.g.: V12345__Add_new_table.sql).

It is also possible to use Flyway for test data. To do so place your test data migrations in src/main/resources/db/testdata/ and set property

flyway.locations=classpath:db/migration/releases,classpath:db/migration/testdata

Then Flyway scans the additional location for migrations and applies all in the order specified by their version. If migrations V0001__... and V0002__... exist and a test data migration should be applied in between you can name it V0001_1__....

---

1. "Stammdaten" in German.