Database Migration
When you have a schema-based database, you need a solution for schema versioning and migration for your database. A specific release of your app requires a corresponding version of the schema in the database to run. As you want simple and continuous deployment you should automate the schema versiong and database migration.
The general idea is that your software product contains "scripts" to migrate the database from schema version X to verion X+1.
When you begin your project you start with version 1 and with every increment of your app that needs a change to the database schema (e.g. a new table, a new column to an existing table, a new index, etc.) you add another "script" that migrates from the current to the next version.
For simplicity these versions are just sequential numbers or timestamps.
Now, the solution you choose will automatically manage the schema version in a separate metadata table in your database that stores the current schema version.
When your app is started, it will check the current version inside the database from that metadata table.
As long as there are "scripts" that migrate from there to a higher version, they will be automatically applied to the database and this process is protocolled to the metadata table in your database what also updates the current schema version there.
Using this approach, you can start with an empty database what will result in all "scripts" being applied sequentially.
Also any version of your database schema can be present and you will always end up in a controlled migration to the latest schema version.
Options for database migration
For database migration you can choose between the following options:
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/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.
Technical Configuration
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.
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/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__....
Liquibase
Liquibase is a tool for database migration and schema versioning.
See devon4j#303 for details and status.
Spring-boot usage
For using liquibase in spring see Using Liquibase with Spring Boot.
Quarkus usage
For uisng liquibase in quarkus see Using Liquibase.