Master Database Version Control in Spring Boot with Flyway

Flyway Introduction

Flyway is a lightweight open‑source database version control tool that follows the convention‑over‑configuration principle. It provides commands such as migrate, clean, info, validate, baseline, and repair. It supports SQL (including PL/SQL, T‑SQL) and Java migrations, command‑line clients, and plugins for Maven, Gradle, SBT, ANT, etc.

Official site: https://flywaydb.org/

Hands‑on Demo

We will build a simple user‑management system, create a user table, and implement CRUD operations while using Flyway to version the database schema.

Implementation of Goal 1

Step 1: Create a Spring Boot project and add dependencies

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>

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

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <scope>provided</scope>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

Step 2: Create a versioned SQL script

Create src/main/resources/db/migration directory.

Add V1__Base_version.sql with the following content:

DROP TABLE IF EXISTS user;
CREATE TABLE `user` (
  `id`   bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `name` varchar(20) NOT NULL COMMENT 'name',
  `age`  int(5) DEFAULT NULL COMMENT 'age',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Note: If you want to place the scripts in a different folder, configure spring.flyway.locations accordingly (different from the 1.x flyway.locations property).

Step 3: Define the entity

@Data
@NoArgsConstructor
public class User {
    private Long id;
    private String name;
    private Integer age;
}

Step 4: Write the service interface and implementation

public interface UserService {
    int create(String name, Integer age);
    List<User> getByName(String name);
    int deleteByName(String name);
    int getAllUsers();
    int deleteAllUsers();
}

@Service
public class UserServiceImpl implements UserService {
    private JdbcTemplate jdbcTemplate;
    UserServiceImpl(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; }

    @Override
    public int create(String name, Integer age) {
        return jdbcTemplate.update("insert into USER(NAME, AGE) values(?, ?)", name, age);
    }

    @Override
    public List<User> getByName(String name) {
        return jdbcTemplate.query("select * from USER where NAME = ?",
            (rs, i) -> {
                User u = new User();
                u.setId(rs.getLong("ID"));
                u.setName(rs.getString("NAME"));
                u.setAge(rs.getInt("AGE"));
                return u;
            }, name);
    }

    @Override
    public int deleteByName(String name) {
        return jdbcTemplate.update("delete from USER where NAME = ?", name);
    }

    @Override
    public int getAllUsers() {
        return jdbcTemplate.queryForObject("select count(1) from USER", Integer.class);
    }

    @Override
    public int deleteAllUsers() {
        return jdbcTemplate.update("delete from USER");
    }
}

Step 5: Write a SpringBootTest

@Slf4j
@SpringBootTest
public class Chapter311ApplicationTests {
    @Autowired
    private UserService userSerivce;

    @Test
    public void test() throws Exception {
        userSerivce.deleteAllUsers();
        // Insert 5 users
        userSerivce.create("Tom", 10);
        userSerivce.create("Mike", 11);
        userSerivce.create("Didispace", 30);
        userSerivce.create("Oscar", 21);
        userSerivce.create("Linda", 17);
        // Verify Oscar's age
        List<User> userList = userSerivce.getByName("Oscar");
        Assertions.assertEquals(21, userList.get(0).getAge().intValue());
        // Verify total count
        Assertions.assertEquals(5, userSerivce.getAllUsers());
        // Delete two users
        userSerivce.deleteByName("Tom");
        userSerivce.deleteByName("Mike");
        Assertions.assertEquals(3, userSerivce.getAllUsers());
    }
}

Note: Spring Boot 2.4 uses a different JUnit version than Spring Boot 1.x; the test code reflects that change.

Step 6: Run the test

The test should pass, and Flyway will have created two tables:

user

– the table defined in our migration script. flyway_schema_history – Flyway’s internal table that records which migrations have been applied.

Implementation of Goal 2

To modify the schema (e.g., add an address column), create a new migration script V1_1__alter_table_user.sql:

ALTER TABLE `user` ADD COLUMN `address` VARCHAR(20) DEFAULT NULL;

Script naming follows the pattern version__description.sql . See Flyway’s documentation for more details.

Run the test again; Flyway logs show the new migration being applied:

2021-01-11 16:58:12.025  INFO --- o.f.c.i.database.base.DatabaseType : Database: jdbc:mysql://localhost:3306/test (MySQL 8.0)
2021-01-11 16:58:12.063  INFO --- o.f.core.internal.command.DbValidate : Successfully validated 2 migrations (execution time 00:00.020s)
2021-01-11 16:58:12.075  INFO --- o.f.core.internal.command.DbMigrate : Current version of schema `test`: 1
2021-01-11 16:58:12.082  INFO --- o.f.core.internal.command.DbMigrate : Migrating schema `test` to version "1.1 - alter table user"
2021-01-11 16:58:12.113  INFO --- o.f.core.internal.command.DbMigrate : Successfully applied 1 migration to schema `test` (execution time 00:00.045s)

After the migration, the user table now includes the address column, as shown in the database client screenshot:

Using Flyway ensures that all schema changes are version‑controlled, stored in Git, automatically applied on application startup, and fully traceable.

For the complete source code, see the chapter3-11 directory in the repositories:

GitHub: https://github.com/dyc87112/SpringBoot-Learning/

Gitee: https://gitee.com/didispace/SpringBoot-Learning/

If you found this tutorial helpful, please give the repository a star – your support keeps the project alive.

References

Spring Boot using Flyway for DB versioning: https://blog.didispace.com/spring-boot-flyway-db-version/

Flyway official documentation