|

Database Migration with Spring Boot

Avatar photoByThorben Janssen

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

All applications that get deployed to production should use an automated approach to migrate their database. It makes your job a lot easier. It also ensures you don’t miss any changes during the update process. That’s, of course, also true if you develop your application based on Spring Boot. The integration of Flyway and Liquibase makes it even more comfortable.

Flyway and Liquibase are two of the most popular Java libraries for version-based database migration. The general idea of this approach is to use a similar versioning and migration tactic as you did for your application code. For each version, you provide the required steps to migrate your database from the previous to the new version. Using Flyway or Liquibase, you can then automatically apply the required migration steps to update your database from any version to the current version.

A lot can be said about Version-based database migration and Spring Boot. I will focus on the integration of Flyway and Liquibase. To get the most out of this article, you should already be familiar with the following topics or follow the links below to learn more about them:

Using Flyway and Spring Boot

Let’s talk about Spring Boot’s Flyway integration first. Flyway can execute SQL scripts or Java classes to perform a migration step. You can run it from the command line, as part of your build process, or integrate it into your application.

Adding Flyway to Your Application

Spring Boot automatically integrates Flyway into the startup of your application. You only need to add a dependency to flyway-core to your build process.

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

Spring Boot will pick the Flyway version that’s supported by the current Spring Boot version and add the required code to run Flyway at application startup. Flyway will then use the configured datasource to check the current version of your database, scan the directory classpath:db/migration for migration steps, and migrate the database to the current version.

As mentioned earlier, you can use SQL scripts or Java classes to define your migration steps. The SQL scripts have to follow the naming pattern V<VERSION>__<NAME>.sql, e.g. V1__create_person_table.sql. And the name of your Java classes has to follow the pattern V<VERSION>__NAME, e.g. V2__extract_address.

If you want to learn more about Flyway’s features, how it keeps track of the current database version, and how it finds the required SQL scripts and Java classes, please read this article: Getting Started with Flyway and Version-Based Database Migration.

Configuring Flyway

You can use all of Flyway’s configuration parameters in Spring’s application.properties file to adapt the default behavior. You just need to add the prefix “spring.” to all parameter names. Flyway’s documentation contains a complete list of all supported configuration parameters.

In general, Flyway’s default configuration is a good fit for most applications. The Spring Boot integration reduces the number of the required configuration parameters even further. It automatically reuses the spring.datasource.url that you configured for Spring Data JPA for your Flyway configuration. If you want Flyway to use a different datasource, you can configure it using the spring.flyway.url property.

spring.flyway.url=jdbc:derby:c:/dev/flyway-db;create=true

There is one more parameter that gets used by most applications. It’s called spring.flyway.locations. It defines the locations in which Flyway searches for SQL scripts and Java classes.

spring.flyway.locations=classpath:db/migration/h2

Supporting Multiple Databases

The integration into your application and the automated execution of your Flyway migration isn’t the only usability feature provided by Spring Boot. It also makes it a lot easier to support multiple databases.

If you’re using Flyway standalone, you should create a separate folder for each database that contains the migration scripts. You then need to provide the correct path to that folder during application startup or when you execute Flyway on the command line.

The Spring Boot integration makes that a little easier. You still need to create a separate folder with the migration scripts for each database. But if you use the {vendor} placeholder in your spring.flyway.locations configuration, Spring picks the folder that matches your database driver.

I use that in the following configuration. My pom.xml file contains a dependency to a Derby JDBC driver and the spring.datasource.url defines a connection to a Derby database. Spring Boot recognizes that and replaces the {vendor} placeholder with derby. So, it executes the migration scripts in the classpath:db/migration/derby folder.

<project>
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.2.7.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.thorben.janssen</groupId>
	<artifactId>flyway-springboot</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>flyway-springboot</name>
	<description>Demo project for Spring Boot using Flyway</description>

	<dependencies>	
		<dependency>
			<groupId>org.apache.derby</groupId>
			<artifactId>derby</artifactId>
			<scope>runtime</scope>
		</dependency>
		
		...
	</dependencies>	
	
	...
</project>

spring.datasource.url=jdbc:derby:c:/dev/flyway-db;create=true

spring.flyway.locations=classpath:db/migration/{vendor}

Supporting Different Profiles

Another exciting feature in Spring is the support for different profiles, including dev, test, or prod. As you might have guessed from the names, profiles are often used to define environment-specific behaviors. One of the best ways to use profile-specific configurations is by providing separate configuration files. You can define your default configuration in your application.properties file. Profile-specific settings override these defaults. You specify them in separate configuration files that follow the naming convention application-profile.properties.

Because this is a standard Spring feature, you can also use it with the Flyway integration. Some teams use this to override the spring.flyway.location property to include an SQL script that adds test data.

Learn More About Flyway

OK, that’s all you need to know about Flyway’s Spring Boot configuration. There is still a lot more to learn about Flyway itself. The following articles give you a great overview of this popular database migration library:

Using Liquibase and Spring Boot

Liquibase is very similar to Flyway. All you need to do is add a dependency to your project and Spring Boot will handle everything else automatically.

<project>
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.2.7.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.thorben.janssen</groupId>
	<artifactId>liquibase-springboot</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>liquibase-springboot</name>
	<description>Demo project for Spring Boot using Liquibase</description>

	<dependencies>	
		<dependency>
			<groupId>org.liquibase</groupId>
			<artifactId>liquibase-core</artifactId>
		</dependency>
		
		...
	</dependencies>	
	
	...
</project>

Spring Boot calls Liquibase during application startup. Liquibase checks which database version is currently deployed. It then gets the available migration steps from classpath:/db/changesets/db.changelog-master.xml, and executes all the required ones.

All of these features are provided by Liquibase and I explained them in great detail in Version-Based Database Migration with Liquibase – Getting Started.

Configuring Liquibase

Spring Boot’s Liquibase integration also supports all of Liquibase’s configuration parameters, as it does for Flyway. You can configure them in your application.properties file if you add the prefix spring.liquibase to each of them. You can find a list of the most commonly used configuration parameters in the Liquibase documentation.

The default configuration is a good fit for most projects. Spring Boot even sets the configured spring.datasource as Liquibase’s datasource. Due to that, you don’t need to configure anything.

If you want to use a different datasource for the migration, you can set the properties spring.liquibase.url, spring.liquibase.user and spring.liquibase.password. They override the configured spring.datasource. So you only need to provide the ones you want to change, e.g. the username and password.

spring.liquibase.user=iCanChangeThings
spring.liquibase.password=$uper$ecr3t

And you can change the path to the master changelog file using the property spring.liquibase.change-log.

spring.liquibase.change-log=classpath:db/changelog/master.xml

Supporting Multiple Databases

The easiest and recommended approach to supporting multiple databases is to use Liquibase’s XML format to describe your migration steps. Liquibase then generates the required SQL statements for each DBMS. You can find out more about it in my Liquibase update guide.

Supporting Different Profiles

As explained earlier, Spring Boot supports different profiles. They enable you to define environment-specific configuration settings. You can configure the differences to the default configuration in profile-specific configuration files that follow the naming convention application-profile.properties.

Liquibase has a similar concept called contexts. You can use them in your changelog files to bind a changeset to a context. You then need to activate the context by setting the spring.liquibase.contexts configuration property. I recommend using the same name for your Spring profile and Liquibase context and activating it in the profile-specific configuration file. The Spring Boot profile test would then set the following property in the application-test.properties file.

spring.liquibase.contexts=test

Learn More About Liquibase

As you can see, Spring Boot’s Liquibase integration is very easy to use and requires almost no additional configuration. But if you need to be familiar with Liquibase. Here are a few articles that show you all you need to know about it:

Conclusion

Version-based database migration is the recommended approach to implement reliable and reproducible database updates. Flyway and Liquibase are the 2 most popular libraries that detect and execute the required update steps. You only need to define the update operations and to integrate one of the libraries into your application.

Spring Boot makes this integration very simple. You only need to add a dependency for Liquibase or Flyway and put a description of your database update operations in the default folder. 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.