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.
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/dbAlthough 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.sqlEmphasizing 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.
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=falseFor 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=validateThe 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.
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/testdataThen 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__....