DB migrations in Spring boot
In this document, let's discuss one of the ways to achieve DB migrations in Spring Boot. Follow the below pointers for implementation :
· What is meant by DB migration
· Use cases for DB migration
. What is FlyWay?
· How to Achieve it in Spring boot
· Conclusion
What is meant by DB migration?
If the Application running in the PROD environment requires a data model update, it asks for data type changes at the schema level. Due to the default behaviour of hibernate & Spring data, the JPA update will include truncation of existing data, where it will erase all the existing data present in the PROD environment (if any). This will cause severe data loss in the PROD environment.
We need to make sure we have a lift & shift of schema changes to PROD without having any data loss. To achieve this we have good support as a form of DB migrations, which included the first schema change to get executed before running the application. This will make sure of running of “.sql” files before the application start which will basically run all the SQL commands present in the “.sql” file. This won’t include a data loss in a table as it will directly deal with SQL commands but not Spring JPA update of schema.
Use cases for DB migration
As explained in the above section there might be scenarios where we need to update the existing DB schema without losing any data, The only way to do that was by running SQL commands in the DB client. This section will explain to you how to achieve that, meaning how to make sure a smooth DB changes migration by running SQL commands in a spring boot application.
We have several solutions to achieve that in Spring boot: Flyway & Liquibase libraries. This section will address one of those solutions which are using Flyway.
What is Flyway?
Flyway is a java library that will provide a way to migrate all schema changes by running SQL scripts as part of the Spring boot application. Flyway can be executed as part of the command line, as part of the application build process, etc.
Why we are adding Flyway
We have a couple of options to support DB migrations, Flyway and Liquibase. Why Flyway because both Flyway and Liquibase support the same functionalities but Flyway is very strict regarding migration file changes. Considering every new change in schema we have to create new versions of the .sql file because the older version already has its entry in the Flyway table.
Flyway also has paid subscription for supporting multiple updating of the same .sql file. This is one of the USPs for using Flyway as it provides more features.
How to Achieve it in Spring boot
1. Add Flyway dependency to the pom.xml file.
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
</dependency>2. Make sure to create a folder under “PROJECT_FOLDER/src/resources/db/migration/YOUR_DATABASE_NAME”.
“YOUR_DATABASE_NAME” is the name of the database client which you are using in your application.
Here we are using “postgresql” so this will be my URL “PROJECT_FOLDER/src/resources /db/migration/ postgresql”.
3. Add the below properties under “application.properties” file.
spring.jpa.hibernate.ddl-auto=none
#Flyway properties
spring.flyway.enabled=true
spring.flyway.url=YOUR_DB_URL
spring.flyway.password= YOUR_DB_PASSWORD
spring.flyway.user= YOUR_DB_USERNAME
spring.flyway.schemas=migrations
spring.flyway.locations=classpath:db/migration/postgresql4. Next step is to create a SQL script that holds all the SQL commands which are required for DB migrations.
Create a new .sql file under above mentioned folder which is under “../db/migrations/postgresql/”, this file should contain a specific naming format that will be acceptable by Flyway.
Syntax: V<VERSION>__<NAME>.sql
“V__<VERSION>”: will be a specific version of migration meaning V1, V2, V3… etc.
“<NAME>”: will specify what migration does this specific SQL file contains.
Note:
1. we haven’t added a version. Since Spring boot will automatically download the version based on the Spring boot version present in the current application.
2. Make sure always you create a new migration SQL file as flyway keeps a DB entry for each new migration (.sql files created before) if we update the already existing migration SQL file changes will not occur.
3. Make sure you have double underscores present after the version of a file, i.e “V1__FILENAME.sql”, if you provide a single underscore after the version file is skipped when starting the application by Flyway, i.e “V1_FILENAME.sql”.
4. We can have N number of SQL commands differentiated by semicolon present in a single .sql file, but once we execute an application, Flyway will make an entry of file based on the version in DB. If you need to run a different set of SQL commands in the next run we have to create a new file with an “updated” version under the above folder only then we will be able to run a new set of SQL commands. We can’t update the existing file contents.
Example .sql file: “V1__first_migration_script.sql”
That’s it we have now configured Flyway for creating new DB migrations. Once you have completed all the processes mentioned above and created a .sql file, push the changes and start an application to check all the updated DB schema present in the newer environment.
Conclusion
Version-based database migration is the recommended approach to implement reliable and reproducible database updates. Flyway is one the most popular libraries in the market that helps to make the DB migration process much simpler. You only need to define the update operations and integrate one of the libraries into your application.
Spring Boot then provides a default configuration and triggers the migration. If you want to use a different configuration, you can add your configuration parameters to the application.properties file.
Sources & for more information:
https://thorben-janssen.com/flyway-getting-started
https://www.baeldung.com/database-migrations-with-flyway
https://www.tutorialspoint.com/spring_boot/spring_boot_flyway_database.htm
About the author:
Pradeep Maiya is a Junior Java Developer here at Version 1.
