Tuesday, May 07, 2024

Using Flyway to Manage Database Migrations in Spring Boot

 This blog post explains how to use Flyway, a popular tool for managing database migrations in Spring Boot applications.

‘- What are Database Migrations?

Maintaining a database schema can be challenging, especially as your application grows and new features are added. This is where Flyway comes in. It helps you track and apply changes to your database schema in a controlled way.

‘- Benefits of Flyway

  • Version Control: Flyway versions your database schema changes, allowing you to roll back to previous versions if necessary.
  • Repeatability: Migrations can be applied consistently across different environments (development, testing, production).
  • Ease of Use: Flyway simplifies the process of managing database schema changes.

‘- Flyway Migration Types

  • Versioned Migrations: These are the most common type and are applied sequentially in the order defined. They are used for creating new tables, altering existing tables, or dropping data.
  • Repeatable Migrations: These are reapplied every time the application starts and are typically used for managing views, stored procedures, or bulk data updates.

‘- How Flyway Works

  1. Flyway Schema History Table: When you start your application with an empty database, Flyway creates a ‘schema_history’ table. This table tracks the migrations that have already been applied.
  2. Scanning for Migration Files: Flyway scans your project’s classpath for migration files. These files contain the SQL scripts to make changes to your database schema.
  3. Applying Migrations: Flyway applies the migration files based on their version numbers (for versioned migrations) or in the order they are defined (for repeatable migrations).

‘- Integrating Flyway in Spring Boot

Here’s a step-by-step guide on integrating Flyway into your Spring Boot project:

  1. Add Flyway Dependencies: Add the Flyway Core and Flyway MySQL dependencies to your project’s pom.xml file.
  2. Create Migration Files: Create SQL files for your migrations. Versioned migrations should start with “V” followed by a version number and two underscores. Repeatable migrations start with “R”.
  3. Configure Flyway: Configure Flyway in your application.properties file. Specify the location of your migration files and enable Flyway.
  4. Run the Application: Run your Spring Boot application. Flyway will automatically apply any pending migrations.

‘- Flyway Maven Plugin

Flyway also provides a Maven plugin that allows you to manage database migrations from the command line. This plugin offers various goals like ‘migrate’, ‘clean’, and ‘validate’.

lets do it practically now:-

1- Lets create a simple springboot project. Please follow below steps for the same.

Add following dependencies in pom.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<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.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

2- Now lets create a simple url to call mysql db and fetch the data

we will create app, controller, model and repository as shon below.

1- SpringJpaFlywayApplication

1
2
3
4
5
6
7
8
9
10
11
12
package com.siddhu;
 
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
 
@SpringBootApplication
public class SpringJpaFlywayApplication {
 
    public static void main(String[] args) {
        SpringApplication.run(SpringJpaFlywayApplication.class, args);
    }
}

2- SiddhuController

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
package com.siddhu.controller;
 
import java.util.List;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 
import com.siddhu.model.TableData;
import com.siddhu.repository.SiddhuRepository;
 
import lombok.RequiredArgsConstructor;
 
@RestController
@RequestMapping(value = "/api/1.0/tabledata")
@RequiredArgsConstructor
public class SiddhuController {
      
    private SiddhuRepository siddhuRepository;
 
     @Autowired
        public SiddhuController(SiddhuRepository siddhuRepository) {
            this.siddhuRepository = siddhuRepository;
        }
 
    @GetMapping()
    public List<TableData> getTableData() {
        return siddhuRepository.findAll();
    }
 
}

3- TableData

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
package com.siddhu.model;
 
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Data;
 
@Entity
@Data
public class TableData {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    Long id;
 
    String firstName;
    String lastName;
    /**
     * @return the id
     */
    public Long getId() {
        return id;
    }
    /**
     * @param id the id to set
     */
    public void setId(Long id) {
        this.id = id;
    }
    /**
     * @return the firstName
     */
    public String getFirstName() {
        return firstName;
    }
    /**
     * @param firstName the firstName to set
     */
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
    /**
     * @return the lastName
     */
    public String getLastName() {
        return lastName;
    }
    /**
     * @param lastName the lastName to set
     */
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
    @Override
    public String toString() {
        return "TableData [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + "]";
    }
     
     
}

4- SiddhuRepository

1
2
3
4
5
6
7
8
9
10
11
package com.siddhu.repository;
 
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
 
import com.siddhu.model.TableData;
 
@Repository
public interface SiddhuRepository extends JpaRepository<TableData, Long> {
 
}

3- Now lets create a simple table TableData in mysql using below script and add data into it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE table_data
(
    id         bigint NOT NULL AUTO_INCREMENT,
    first_name varchar(255) DEFAULT NULL,
    last_name  varchar(255) DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;
 
-- Inserting data into the 'actor' table
INSERT INTO table_data (first_name, last_name)
VALUES ('firstname1', 'lastname1'),
       ('firstname2', 'lastname2'),
       ('firstname3', 'lastname3'),
       ('firstname4', 'lastname4');

Now lets call the same from our springboot application and check it is working

4- Lets make changes in our application.properties file to connect to Mysql Db.

spring.application.name=flyway-migration-feature

DB properties

spring.datasource.url=jdbc:mysql://localhost:3306/sys
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

Run the spring boot application and check belwo url

Now if you see here we had done everything related to DB changes by own. i.e. creating table, entering data etc. Now lets take the help of flyway to do the same.

for that add following dependencies in pom.xml

1
2
3
4
5
6
7
8
9
10
<dependency>
           <groupId>org.flywaydb</groupId>
           <artifactId>flyway-core</artifactId>
           <version>10.11.1</version>
       </dependency>
       <dependency>
           <groupId>org.flywaydb</groupId>
           <artifactId>flyway-mysql</artifactId>
           <version>10.11.1</version>
       </dependency>

Also lets create the sql script for creating table and inserting data into it. We will create this sql script inside resource folder as shown below.

As per the rule for file that are used for creation, deletion, alteration of table need to be start with R followed with version we had done the same

RVersionNumber__Nameofthefiles.sql

Also modify the application.properties files as show belwo

spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migrations

Now lets delete our table and data inside it and then we will run the spring boot application and it will create table_data and insert the data inside it automatically.

As you can see we dont have any table in our sys

now lets create run the springboot application.

Lets hit the url as see if we are getting the response.

Also if you have DB and you want to integrate flyway you can use the belwo configuration in applicaiton.properties.

spring.flyway.baseline-on-migrate=true
spring.flyway.validate-on-migrate=true

Flyway also itegrate with maven command as show below

as shown above to use maven plugin command we need to modify pom.xml as given below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
<build>
        <plugins>
            <plugin>
                <groupId>org.flywaydb</groupId>
                <artifactId>flyway-maven-plugin</artifactId>
                <version>10.10.0</version>
                <configuration>
                    <url>jdbc:mysql://localhost:3306/sys</url>
                    <user>root</user>
                    <password>root</password>
                    <locations>classpath:db/migrations</locations>
                </configuration>
            </plugin>
 
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
     
now as you see we had given hard code userid and pwd in side the pom.xml which is not good practise. To overcome it we use use below parameters
 
<build>
        <plugins>
            <plugin>
                <groupId>org.flywaydb</groupId>
                <artifactId>flyway-maven-plugin</artifactId>
                <configuration>
                    <url>jdbc:mysql://localhost:3306/flyway_demo</url>
                    <user>${database.username}</user>
                    <password>${database.password}</password>
                    <locations>classpath:db/migrations</locations>
                </configuration>
            </plugin>
 
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

and also add below parameter in pom.xml

1
2
<database.username>YOUR_TEST_USERNAME</database.username>
<database.password>YOUR_TEST_PASSWORD</database.password>

and now finally execute belwo command on maven prompt

1
/mvnw -Ddatabase.username=root -Ddatabase.password=root -flyway:migrate

and it will work properly.

code can be found from below url locations.
https://github.com/shdhumale/flyway-migration-feature

‘- Conclusion

Flyway is a powerful tool that simplifies database schema management in Spring Boot applications. By using Flyway, you can ensure that your database schema is always up-to-date and consistent across different environments.

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)