Spring Boot 2.x Basics: Managing database versions using Flyway

Many cases of MySQL use in Spring Boot have been introduced before, including the most original JdbcTemplate of Spring Boot, Spring Data JPA and MyBatis, the most commonly used in our country. It also covers complex scenarios such as switching Druid data sources, or multiple data sources.

No matter which implementation framework we use, we cannot leave the management of database table structure. There has always been a problem with this type of management: because database table metadata is stored in the database, our access logic is stored in Git or some other code repository. Git has helped us complete multi-version management of the code, so how to do the database table version control?

Today we will show you how to use Flyway in Spring Boot to manage database versions.

Flyway profile

Flyway is a simple open source database version controller (convention > configuration) that provides migrate, Clean, INFO, validate, baseline, repair, and other commands. It supports SQL (PL/SQL, T-SQL) mode and Java mode, supports command line client, and provides a series of plug-in support (Maven, Gradle, SBT, ANT, etc.).

Official website: flywaydb.org/

This article doesn’t go into much detail about Flyway’s own features, but you can read the official documentation or use search engines for more information. Let’s look at the application in Spring Boot, how to use Flyway to create databases and check for structural inconsistencies.

Began to try

Let’s first set a development goal:

Suppose we need to develop a user management system, so we are bound to design a user table, and implement the user table to add, delete, change and check operations.
After the functionality of task 1 is complete, we have a new requirement to add a field to the user table to see how to implement changes to the database table structure.

Achievement of Goal 1

Step 1: Create a basic Spring Boot project and add the necessary dependencies related to Flyway, MySQL connection, and data access to PUM.xml (spring-boot-starter-JDBC is used as an example)

<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>
Copy the code

Step 2: Create a versioned SQL script according to Flyway’s specifications.

In the engineering ofsrc/main/resourcesDirectory creationdbDirectory,dbCreate one in the directorymigrationdirectory
inmigrationCreate versioned SQL scripts in the directoryV1__Base_version.sql

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;

Copy the code

Note: If you don’t want to put your SQL script in another directory, you can use the spring.flyway.locations parameter to do this. This is different from the configuration item flyway.locations in the 1.x version

Step 3: Write the entity definition according to the structure of the User table

@Data
@NoArgsConstructor
public class User {

    private Long id;
    private String name;
    private Integer age;

}
Copy the code

Step 4: Write user action interfaces and implementations

public interface UserService {

    /** * Add user **@param name
     * @param age
     */
    int create(String name, Integer age);

    /** * Query user ** based on name@param name
     * @return* /
    List<User> getByName(String name);

    /** * Delete user ** according to name@param name
     */
    int deleteByName(String name);

    /** * Total number of users */
    int getAllUsers(a);

    /**
     * 删除所有用户
     */
    int deleteAllUsers(a);

}

@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) {
        List<User> users = jdbcTemplate.query("select * from USER where NAME = ?", (resultSet, i) -> {
            User user = new User();
            user.setId(resultSet.getLong("ID"));
            user.setName(resultSet.getString("NAME"));
            user.setAge(resultSet.getInt("AGE"));
            return user;
        }, name);
        return users;
    }

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

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

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

The application of Flyway is mainly introduced here, so this relatively simple writing method is adopted. In the actual project application, the specific operation of MyBatis is recommended.

Step 5: Write test cases

@Slf4j
@SpringBootTest
public class Chapter311ApplicationTests {

    @Autowired
    private UserService userSerivce;

    @Test
    public void test(a) 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);

        // query the user named Oscar to see if the age matches
        List<User> userList = userSerivce.getByName("Oscar");
        Assertions.assertEquals(21, userList.get(0).getAge().intValue());

        // There should be 5 users
        Assertions.assertEquals(5, userSerivce.getAllUsers());

        // Delete two users
        userSerivce.deleteByName("Tom");
        userSerivce.deleteByName("Mike");

        // There should be 5 users
        Assertions.assertEquals(3, userSerivce.getAllUsers()); }}Copy the code

Note that because the junit version of the Spring Boot 2.4 application is different from the previous Spring Boot 1.x release, the unit tests are written slightly differently. Interested readers can see the differences between the unit tests in the introduction and this article, which are not covered in detail here.

Step 6: Run the unit tests written above to verify the results.

Not surprisingly, the unit tests run ok

Connect to the database. There should be two more tables:

userThe table is the one we maintain to create in the SQL script
flyway_schema_historyThe table is flyway’s management table, which records the scripts that run on the database and the basis for checking each script. In this way, every time the application starts, you can know which script needs to be run, or which script has changed, which may run on the wrong basis, causing confusion in the data structure and preventing it from running.

Achievement of Goal 2

With the above foundation, we will talk about the following table structure to do table changes how to operate, this is also before the reader appeared most problems, so in the 2.x version of the tutorial specifically to speak about.

First of all, after you start using Flyway, you should close these channels for database table interface changes:

Modify the table structure directly through the tool login data
Published SQL scripts cannot be modified

The correct way to adjust the table structure: Write a new script in the Flyway script configuration path and start the program to perform the changes. This has several big benefits:

Scripts are controlled by Git version management, making it easy to find past history
Scripts are loaded at program startup and then interface services are provided to complete the deployment steps
The history of all table structures can be traced by version number in the admin directory

The following operations are based on a practical requirement. Suppose we now want to add a field to the User table, address, to store the User’s mailing address.

Step 1: Create the script file v1_1__alter_table_user.sql and write the statement that adds the address column

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

The basic rule for script file names is: version number __ Description.sql. Of course, if you have more detailed requirements, then you can do more detailed file name planning, the details of the reader can refer to the official documentation in the end of the article resources.

Step 2: Execute the unit test again and you can see the following log in the console:

The 2021-01-11 16:58:12. 37330-025 the INFO [main] O.F.C.I.D atabase. Base. The DatabaseType: the Database: JDBC: mysql: / / localhost: 3306 / test (mysql 8.0) 16:58:12 2021-01-11. 37330-063 the INFO [main] o.f.core.internal.command.DbValidate : Successfully validated 2 Migrations (Execution time 00:00.020S) 2021-01-11 16:58:12.075 INFO 37330 -- [main] o.f.core.internal.command.DbMigrate : Current version of schema `test`: 1 2021-01-11 16:58:12. 37330-082 the INFO [main] o.f.core.internal.com mand. DbMigrate: Migrating schema 'test' to version "1.1-ALTER table user" 2021-01-11 16:58:12.113 INFO 37330 -- [main] O.f.core.internal.com mand. DbMigrate: Successfully applied 1 migration to schema ` test ` (execution time 00:00, 045 s)Copy the code

Take a look at data China again:

If you haven’t seen the benefits of introducing Flyway to our table structure yet, feel free to share your management style in the comments!

More free tutorials in this series”Click to go to summary directory”

Code sample

For an example of this article, see the chapter3-11 directory in the repository below:

Github:github.com/dyc87112/Sp…
Gitee:gitee.com/didispace/S…

If you found this article good, welcomeStarSupport, your attention is my motivation!

The resources

Flyway is used in Spring Boot to manage database versions
Flyway official documentation

Welcome to pay attention to my public account: Program monkey DD, get the exclusive arrangement of learning resources, daily dry goods and welfare gifts.

Spring Boot 2.x Basics: Managing database versions using Flyway

Flyway profile

Began to try

Code sample

The resources

Related Posts

Leetcode 190. Reverse Bits (Python)

Java test questions and sorting (multi-threaded and concurrent) (with answers, continuous update)

Trade-offs are the essence of cache design