Flyway With Spring Boot And MySQL

Flyway With Spring Boot And MySQL

As many development teams now use automated database migration, you should know what tools can help you configure and maintain database schemas. Using Flyway is just the right solution if you have a basic understanding of SQL. Database migration using Flyway is quickly gaining popularity in software development because it’s easy to use and can be integrated with your existing application system. Furthermore, it supports many databases, including PostgreSQL, MySQL, Oracle DB, H2 db, and many others.

In this article, we’ll review the Flyway database migration tool — one of the most popular in the Spring Boot world.

Let’s Go Over a Use Case With MySQL

To start, we’ll go to the Spring Initializr website and create a simple project.

Suppose you have the MySQL database up and running. Ours is running at the local host and 3306 port. First, we create a simple User class.

@Entity
@Table(name = "users")
public class User {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "email") private String email;
@Column(name = "password") private String password;
@Column(name = "createdAt") private Date createdAt;
}

We also need to configure our app to connect to local MySQL. Let’s do this in application.yml

We also need to configure our app to connect to local MySQL. Let’s do this in application.yml

Creation of SQL Table Users in MySQL

It’s also necessary to create SQL table users in MySQL.

First, create a schema called flyway_db in your MySQL instance.

Then you can create your migration scripts; the first step is to create a users table.
By default, Flyway will monitor the db/migration folder under the resources directory and check scripts there. The naming convention for the script is the following:

V{incremental_version_number}__{your_custom_script_name}.sql

So our script for creating a users table has the following name:

V1__create_users_table.sql

SQL Statement Creation

We also need to create an SQL statement to make a MySQL table:

This will create a ‘users’ table in our flyway_db database.
With Flyway, you can create a migration script to populate db with some data. So the following script will add some users to the ‘users’ table.

It’s named: V2__insert_users.sql

When we run our application, the users table will be populated with these five users. And when you review them, you’ll notice that the created At column contains only date value, without hours, minutes, and seconds which is not useful for this type of data. We should change the column type to the one that will better fit our needs. Let’s change it to the TIMESTAMP data type. Flyway will let us do this with ease.
The new script will be the following: V3__change_created_at_data_type.sql

After this script, the users table has the createdAt column that is up to date. It holds the TIMESTAMP of user creation, including hours, minutes, and seconds

You probably want to make your app work as fast as you expect. Spring Framework has something special for you. Take a closer look at this guide.

Let’s move on

Suppose you change an existing column name for any reason you may have during app development. Of course, you want all entities in your database to have these changes applied. To do this, we create another script.

V4__change_column_name.sql

When the application is launched, it will automatically pick up the newly created script and apply changes to the users table. So now the createdAt column is renamed to created_at.

Adding a new column to the user entity

The app is growing and includes a lot of developed features. At some point, you may decide to add a new column to the user entity. For this, we need to create another script.

V5__add_username_to_users.sql

This script will add a ‘username’ column to all user entities. So now you can create users with a username. However, old users do not have a username, and that’s not a good practice. You may want to fix that and apply some random string using your own pattern. In our case, we just use a random 10 chars length string for username for old users. Flyway will assist us again.

V6__set_default_values_for_username.sql

As you see, it works. All the old users registered before the implementation of our super feature now also receive a random username. Of course, they will be able to change it any time, but the point is that now your app won’t crash because of some NullPointerExceptions due to missing value for the username.

Here is what our project structure looks like:

To sum up

To sum up
Now all the scripts are located in the db/migrations folder and have an incrementing version number at the beginning of their names.
So this is how Flyway works — it allows you to manage any changes you add to your MySQL database over time during app development.
Thank you for reading the guide! I hope it let you better understand the Spring Boot world and the necessity of the Flyway tool for the successful migration of your database.

Spring Boot Profiles

How many times did you have to use different credentials or configuration in production and development applications? I bet your answer is ‘Every single time!’. The simplest example describing this is using payment systems in your application. In development mode you want to use test payments in order not to loose all off your money […]

Ihor Sokolyk

25 May, 2018 · 3 min read

10 Tips To Keep Your Code Clean

Did you ever look into your or your team members code and think ‘What a hack? Why does this method look like a cat was walking on the keyboard?’. If so, then you definitely need to spend some time to clean your project’s code, because high-quality code is easier to understand, modify and scale. In […]

Ihor Sokolyk

5 Dec, 2017 · 5 min read

Microservices with Java. Quick Start. Part 2

Hello! In this part we will speak about communication between microservices, and will add an API Gateway to our project. If you missed the first part of this topic, you may find it here. When you build an application with microservice architecture, you will definitely have couple services running separately. Of course you will need them […]

Ihor Kosandyak

5 Nov, 2017 · 5 min read