Database automatic updates

[pbroman]

We have recently the database extended with a collections table and a work attribute, and of course, it is likely that we will have to do this also in the future with a running system. For this case, it is necessary to have an automatic update possibility for the database. If we therby should work with flyway or something else I cannot say. A solution only using hibernate would of course be very nice, but is there one?

[ponchofiesta]

So far I think by only using Hibernate this is not possible. Hibernates SchemaUpdate can create and delete but not migrate the data. By going back to DBMS related SQL scripts it might be possible. We could write our own migration tool by simply running these migration scripts. But I don't think this is faster than using a framework.

Flyway seems to be good for small projects and easy to use. It only supports forward migrations which might be OK for production use. Biggest drawback is it needs migrationsscripts for every DBMS. Actually I was happy we are DBMS independent by using Hibernates SchemaExport.

Liquibase supports forward and backward migration. It saves the migration config in a generic file format and generates SQL by itself (like Hibernate does). But I assume it is more complex to implement.

Spring Boot doc about Flyway and Liquibase

• https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html#howto-use-a-higher-level-database-migration-tool

Flyway and Spring Boot

• http://www.baeldung.com/database-migrations-with-flyway
• https://www.callicoder.com/spring-boot-flyway-database-migration-example/

Liquibase and Spring Boot

• https://pragtics.de/datenbankversionierung-in-spring-boot-mit-liquibase/
• https://medium.com/@harittweets/evolving-your-database-using-spring-boot-and-liquibase-844fcd7931da

[ponchofiesta]

This adds Flyway database migrations. Therefor we will depend on MySQL. Flyway will not automatically update the database (forced by noFlywayMigrationStrategy bean). You have to use shell command:

cli updatedb

This ensures this will not happen by mistake.

You may also use Flyway Maven plugin but you have to provide datasource parameters as spring can not provide them this way:

mvn flyway:migrate -Dflyway.url='jdbc:mysql://localhost/mediaserver?autoReconnect=true&useSSL=false' -Dflyway.user=kitodo -Dflyway.password=kitodo