Database Migrations in Spring Boot Using Flyway

Applying changes to databases is a tedious job. Having to syncronize systems and remember what changes have been applied is hard, and it’s likely to make a mistake. Luckily there’s Flyway, which make things easier. In this article we’ll see how it’s possibile to set up database migrations in Spring boot using Flyway.

What is Flyway?

Flyway is a tool for versioning the state of an application’s SQL database. You write your own SQL scripts defining how a database is structured and Flyway will apply the changes to the database when your application gets deployed. This ensures that your application is always working with the correct version of the database.

Flyway Migrations

Flyway works around migrations, which are collection of SQL statements. Each migration lives in its own SQL file and based on how we name them, Flyway understands under what conditions and in which order they should be executed.

Flyway uses a special naming convention to identify migrations:

• Prefix: V for versioned, U for undo and R for repeatable migrations.
• Version: Version seprated with dots or underscores
• Separator: Two underscores
• Description: Words separated by underscores
• Suffix: .sql

Database Version

The first time Flyway runs, it creates a table named flyway_schema_history. In this table it keeps track of the applied migrations so that it can check the current version of your database and apply only the migration scripts that have a higher version number.

Setting Up the Project

First of all, let’s add the dependencies for this project:

<dependencies>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-web</artifactId>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-data-jpa</artifactId>
	</dependency>
	<dependency>
		<groupId>com.h2database</groupId>
		<artifactId>h2</artifactId>
	</dependency>
	<dependency>
		<groupId>org.flywaydb</groupId>
		<artifactId>flyway-core</artifactId>
	</dependency>
	<dependency>
		<groupId>org.flywaydb</groupId>
		<artifactId>flyway-mysql</artifactId>
	</dependency>
</dependencies>

As you can see, besides Spring and H2 dependencies we have Flyway dependencies. In this case we’re using MySQL but Flyway supports all the other databases.

Now it’s time to configure Spring. Create a file named application.yml in src/main/resources folder, then paste the following content:

spring:
  datasource:
    driver-class-name: org.h2.Driver
    url: jdbc:h2:mem:spring-boot-flyway;MODE=MYSQL
    username: sa

By default Flyway automatically uses the datasource from the application to connect to the DB.

At this point we need to create our migrations. Inside our resources folder, we need to create a folder named db and then inside it, another named migration. This folder will be used by Flyway to read and execute all our migration files.

In the migration folder create a file named V1_0_0__create_initial_scheme.sql.

CREATE TABLE customer (
    id int NOT NULL AUTO_INCREMENT,
    first_name varchar(50),
    last_name varchar(75),
    country varchar(60),
	PRIMARY KEY (id)
);

In the above file we simply create a table named customer. Now let’s add one more migration, this time create a file named V1_1_0__insert_customer.sql.

INSERT INTO customer (first_name, last_name, country) values('Mario', 'Rossi', 'Italy')

In the above migration we just insert a customer to populate the customer table.

Testing the Application

At this point let’s run the application and see if it works. After starting it, connect to the in-memory database. You’ll see that Flyway created the customer table and inserted the record as we specified in the migration files.

Conclusion

In this article we’ve seen how Flyway minimizes the potential for errors and ensures alignment as application grows. Developers don’t have to worry about the state of the database anymore and can focus on more important things. Configuring Flyway is not hard thanks to Spring Boot and its autoconfiguration that makes things easier.

Source code is available on GitHub.