In this new Refactoring entry, we will see how we can perform an SQL migration with Flyway in Spring Boot. If you want to learn more about using databases with Spring Boot and JPA, you can take a look at this article on Spring Boot and JPA with PostgreSQL.

What is Flyway?

Flyway is a robust database migration system that works with different databases, making any migration easy and straightforward. It has compatibility with Java and can also perform migrations through Maven and Gradle plugins, for example:

mvn flyway:migrate -Dflyway.url=... -Dflyway.user=... -Dflyway.password=...

Flyway creates a table in the specified database where it stores a history of updates and versions, allowing you to keep track of your SQL scripts at all times.

How Flyway works

Flyway helps us maintain traceability of migrated scripts, recording which scripts have been migrated, when, and by whom. The new table created by Flyway in our schema adds metadata with a checksum indicating whether the migration was successful.

When Flyway is executed for an SQL migration, the following checks are performed:

1. Create a table to store information.
2. Analyze migration files.
3. Compare files with previous migrations; if there have been changes in previous version files, the migration will fail.
4. Execute the new migration scripts and add the information about the new scripts.

Example of Flyway migration with Spring Boot

Now let’s use some SQL scripts to perform a migration using Flyway. Once we have added Flyway, we will see how it works in our Spring Boot application, to which we will add Spring Data to communicate with a PostgreSQL database.

Flyway Maven Dependencies

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

The Spring Boot parent will be responsible for adding the Flyway version. Once this dependency is added, Spring Boot will initialize Flyway.

Flyway Configuration in Spring Boot

Flyway can be configured using application.properties or application.yml. Let’s define the necessary properties:

spring:  
  flyway:
    enabled: true
    locations: classpath:/db/scripts/migration
    schemas: refactorizando

Of note in the above configuration is the locations, where we specify the path of the scripts. By default, if not specified otherwise, the path would be classpath:db/migration.

If you want to see more configuration properties, you can check the Spring documentation.

Creating SQL for Use with Flyway in Spring Boot

Once we have created the configuration file, specifying where Flyway will load the scripts, let’s add the SQL files.

It is crucial that the file name follows the format: V{version-number}__{name}.sql

For Flyway to process the SQL files, it is essential to name the file properly as V{version}__{name}.sql, for example:

V1.0__create_tables.sql

After creating the necessary files for the migration, we just need to configure it to work with Spring Data, as seen in this article.

Naming Convention for Use with Flyway

Possible Errors with Flyway

When working with Flyway and trying to deploy our project, we may encounter errors such as:

• Wrong migration name format: This indicates that the name we provided is incorrect; remember it must be in the format V{version}__{name}.sql.
• Flyway migration failed: This is undoubtedly one of the most common errors we may encounter when using Flyway. This error occurs when a previous file has been modified, and the Flyway-generated checksum detects the change and causes an error. To avoid this error, we must refrain from changing old files and generate new ones for any changes.

Conclusion

In this entry about SQL migration with Flyway in Spring Boot, we have seen how we can add Flyway to our applications to perform SQL migrations. However, it is better to see an example to understand it better, which you can find on our GitHub.