Database migration with Spring Boot and Flyway

Database migration with Spring Boot and Flyway

Database migration or schema evolution is inevitable in any long-lasting project. While NoSQL databases give more leeway, relational databases are pretty rigid regarding schema evolution. Hence, having a suitable tool at your disposal saves you time and energy. Spring Boot not only eases working with databases by providing an abstract layer known at repositories but also has a fantastic integration with prominent database migration tools to facilitate ease of software development further. In this article, we cover how to do database migration with Spring Boot and Flyway.

Introduction (Flyway vs Liquibase)

There are two prominent database migration tools in Java/JVM world. Liquibase and Flyway. Both have been around for quite some time and have solid reputations. Their differences narrow down to migration script syntax and database support. While Flyway uses SQL script and only supports RDBMS, Liquibase supports multiple syntaxes, SQL, and database-agnostic languages such as XML, JSON, and YAML. Moreover, Liquibase is usable with both RDBMS and NoSQL databases. But the migration tool selection depends on the use cases and flexibility needed for a project.

For simplicity’s sake, in this article, we opted Flyway, but the process is quite similar for the Liquibase with slight modifications.

Flyway configuration

The first step to configure Flyway is to add the library dependency to the project. In Maven projects, open pom.xml and add the following lines to it.

Keep in mind the version compatibility between Flyway and Spring Boot. Always check Spring Boot and Flyway change logs.

As you can see in the above code, we have hard-coded the JDBC connection string, database username, and password. No worries, we can change these parameters on the fly when we want to run the migration scripts.

Adding migration scripts

After configuring the Flyway, the next step is to write some migration scripts. These scripts are plain old SQL scripts. The only trick is their placement and naming convention. The migration scripts should be put under src/main/resources/db/migration. As for the naming convention, script names start with V, followed by a number (which also indicates the execution order), and then two __. For example, V1__user_table.sql and V2__sample_users.sql in which user table script is executed before the insertion of some sample users. The Flyway scripts are capable of any SQL-related operations including but not limited to table creation, manipulation, deletion, etc.

Running migration scripts

To run the migration scripts we have created, we need to run the flyway goal as follows:

That uses the credentials and DB connection string set into the pom.xml file. To override those, we can pass each of them directly to Maven like this.

The most important thing about migration scripts is that you should not rename or modify them after they are executed. Doing so would probably won’t have any effect as the changes have been applied already.

How does Flyway keep track of changes

Flyway keeps track of migration history by adding a table (flyway_schema_history) to the database. We get the following details if we query that table in our example.

To rerun the migration scripts, you must remove the content of the flyway_schema_history table.

Conclusion

A robust database migration tool is crucial to have a smooth schema evolution. For SQL databases, the most prominent migration tools are Flyway and Liquibase. While Liquibase has flexibility and language-agnostic advantages, it can be overkill for many projects. Hence, we chose the former and demonstrated how to do database migration with Spring Boot and Flyway. The sample project code is available on GitHub: https://github.com/kasramp/spring-kafka-test. Check here for more Spring Boot content.

Inline/featured images credits