SpringBoot database management - database management and migration with Liquibase?

Liquibase is an open source tool for tracking, managing and applying database changes. It records database changes (changeset) in the form of log files (changelog), and then executes the modifications in the log files to update or rollback the database (rollback). ) to a consistent state. Its goal is to provide a database-type-agnostic solution that migrates by executing schema-type files. This article mainly introduces the integration of SpringBoot and Liquibase. @pdai

• SpringBoot database management - database management and migration with Liquibase?

  • Knowledge preparation

    • What is Liquibase? What problems are these tools trying to solve?
    • What concepts does Liquibase have? How does it work?

  • Simple example

    • POM dependencies
    • yml configuration
    • add changelog
    • test

  • further understanding

    • Better changelog practice?
    • In addition to addColumn, what other changeTypes do addTable have?
  • Sample source code

Knowledge preparation

It is necessary to understand what Liquibase is, and what problem it came to solve.

What is Liquibase? What problems are these tools trying to solve?

Liquibase is an open source tool for tracking, managing and applying database changes. It records database changes (changeset) in the form of log files (changelog), and then executes the modifications in the log files to update or rollback the database (rollback). ) to a consistent state. Its goal is to provide a database-type-agnostic solution that migrates by executing schema-type files.

Its main advantages are as follows :

• It supports almost all mainstream databases, and currently supports various databases including Oracle/Sql Server/DB2/MySql/Sybase/PostgreSQL, which can help the application system support multiple databases during database deployment and upgrade;
• Support version control, so that it can support the collaborative maintenance of multiple developers;
• The log file supports multiple formats, such as XML, YAML, JSON, SQL, etc.;
• Provides the rollback function of change application, which can roll back applied changes by time, quantity or tag. In this way, developers can easily restore the state of the database at any point in time
• Supports multiple running methods, such as command line, Spring integration, Maven plugin, Gradle plugin, etc.

Why is there a tool like Liquibase ?

In the actual online application, with the iteration of the version, it is often encountered that the database tables and fields need to be changed, and it is inevitable to encounter the need to record and manage these changes, as well as rollback, etc.; at the same time, only scripted and version-able Management can enable the database to achieve true DevOps (automatic execution + rollback, etc.). In such a scenario, the emergence of tools such as Liquibase has become inevitable.

What concepts does Liquibase have? How does it work?

Workflow : Record SQL changes to changeset , multiple changeset changes form a log file ( changelog ), and liquibase synchronizes the changelog update log file to the specified RDBMS .

The log file (databaseChangeLog) supports a variety of formats, such as XML, YAML, JSON, SQL; we take xml as an example to see the relevant configuration

 <?xml version="1.0" encoding="UTF-8"?> 
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.9.0.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.9.0.xsd">
    <changeSet id="1" author="bob">  
        <comment>A sample change log</comment>  
        <createTable/> 
    </changeSet>  
    <changeSet id="2" author="bob" runAlways="true">  
        <alterTable/>  
    </changeSet>  
    <changeSet id="3" author="alice" failOnError="false" dbms="oracle">
        <alterTable/>  
    </changeSet>  
    <changeSet id="4" author="alice" failOnError="false" dbms="!oracle">
        <alterTable/>  
    </changeSet>  
</databaseChangeLog>

Simple example

Here we mainly introduce the integration of liquibase based on SpringBoot to manage database changes.

POM dependencies

The dependencies of the Maven package mainly include the mysql driver, JDBC (here spring-boot-starter-data-jpa includes the jdbc package, of course, the jdbc package can also be imported directly), and the liquibase package.

 <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
</dependency>
<dependency>
    <groupId>com.github.wenhao</groupId>
    <artifactId>jpa-spec</artifactId>
    <version>3.1.0</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
    <version>4.9.1</version>
</dependency>

yml configuration

SpringBoot AutoConfig already includes the configuration of liquibase by default, under the spring.liquibase configuration.

The basic configuration can be used directly as follows (mainly to specify the location of change-log, the default location is classpath:/db/changelog/db.changelog-master.yaml):

 spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test_db_liquibase?useSSL=false&autoReconnect=true&characterEncoding=utf8
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: bfXa4Pt2lUUScy8jakXf
  liquibase:
    enabled: true
    # 如下配置是被spring.datasource赋值的，所以可以不配置
#    url: jdbc:mysql://localhost:3306/test_db_liquibase?useSSL=false&autoReconnect=true&characterEncoding=utf8
#    user: root
#    password: bfXa4Pt2lUUScy8jakXf
    change-log: classpath:/db/changelog/db.changelog-master.yaml

During development, more configuration can be found from the following SpringBoot AutoConfig.

add changelog

The XML method is OK, but the yml format is still recommended.

 databaseChangeLog:
  - changeSet:
      id: 20220412-01
      author: pdai
      changes:
        - createTable:
            tableName: person
            columns:
              - column:
                  name: id
                  type: int
                  autoIncrement: true
                  constraints:
                    primaryKey: true
                    nullable: false
              - column:
                  name: firstname
                  type: varchar(50)
              - column:
                  name: lastname
                  type: varchar(50)
                  constraints:
                    nullable: false
              - column:
                  name: state
                  type: char(2)

  - changeSet:
      id: 20220412-02
      author: pdai
      changes:
        - addColumn:
            tableName: person
            columns:
              - column:
                  name: username
                  type: varchar(8)

  - changeSet:
      id: 20220412-03
      author: pdai
      changes:
        - addLookupTable:
            existingTableName: person
            existingColumnName: state
            newTableName: state
            newColumnName: id
            newColumnDataType: char(2)

test

Start springBootApplication, we can see that the following changeSets are executed in sequence

 2022-04-12 20:41:20.591  INFO 8476 --- [           main] liquibase.lockservice                    : Successfully acquired change log lock
2022-04-12 20:41:20.737  INFO 8476 --- [           main] liquibase.changelog                      : Creating database history table with name: test_db_liquibase.DATABASECHANGELOG
2022-04-12 20:41:20.783  INFO 8476 --- [           main] liquibase.changelog                      : Reading from test_db_liquibase.DATABASECHANGELOG
Running Changeset: classpath:/db/changelog/db.changelog-master.yaml::20220412-01::pdai
2022-04-12 20:41:20.914  INFO 8476 --- [           main] liquibase.changelog                      : Table person created
2022-04-12 20:41:20.914  INFO 8476 --- [           main] liquibase.changelog                      : ChangeSet classpath:/db/changelog/db.changelog-master.yaml::20220412-01::pdai ran successfully in 53ms
Running Changeset: classpath:/db/changelog/db.changelog-master.yaml::20220412-02::pdai
2022-04-12 20:41:20.952  INFO 8476 --- [           main] liquibase.changelog                      : Columns username(varchar(8)) added to person
2022-04-12 20:41:20.952  INFO 8476 --- [           main] liquibase.changelog                      : ChangeSet classpath:/db/changelog/db.changelog-master.yaml::20220412-02::pdai ran successfully in 31ms
Running Changeset: classpath:/db/changelog/db.changelog-master.yaml::20220412-03::pdai
2022-04-12 20:41:21.351  INFO 8476 --- [           main] liquibase.changelog                      : Lookup table added for person.state
2022-04-12 20:41:21.351  INFO 8476 --- [           main] liquibase.changelog                      : ChangeSet classpath:/db/changelog/db.changelog-master.yaml::20220412-03::pdai ran successfully in 389ms
2022-04-12 20:41:21.382  INFO 8476 --- [           main] liquibase.lockservice                    : Successfully released change log lock

Check the database and you will find that the data has changed

So what if we restart this SpringBootApplication?

Obviously, because there are already relevant execution records in the databasechangelog table, the changes will no longer be executed.

 2022-04-12 20:49:01.566  INFO 9144 --- [           main] liquibase.lockservice                    : Successfully acquired change log lock
2022-04-12 20:49:01.761  INFO 9144 --- [           main] liquibase.changelog                      : Reading from test_db_liquibase.DATABASECHANGELOG
2022-04-12 20:49:01.812  INFO 9144 --- [           main] liquibase.lockservice                    : Successfully released change log lock

further understanding

Further understanding through a few questions.

Better changelog practice?

In short: yml format + sql-file method

Execute changeSet in sqlFile format, as follows

The log of execution is as follows

 2022-04-12 21:00:28.198  INFO 17540 --- [           main] liquibase.lockservice                    : Successfully acquired change log lock
2022-04-12 21:00:28.398  INFO 17540 --- [           main] liquibase.changelog                      : Reading from test_db_liquibase.DATABASECHANGELOG
Running Changeset: classpath:/db/changelog/db.changelog-master.yaml::20220412-04::pdai
2022-04-12 21:00:28.516  INFO 17540 --- [           main] liquibase.changelog                      : SQL in file classpath:/db/changelog/db.changelog-20220412-04.sql executed
2022-04-12 21:00:28.516  INFO 17540 --- [           main] liquibase.changelog                      : ChangeSet classpath:/db/changelog/db.changelog-master.yaml::20220412-04::pdai ran successfully in 83ms
2022-04-12 21:00:28.532  INFO 17540 --- [           main] liquibase.lockservice                    : Successfully released change log lock

After execution, view the change log

The data table user table has been created and a piece of data has been inserted

In addition to addColumn, what other changeTypes do addTable have?

In addition to addColumn, what other changeTypes do addTable have?

At the same time, the following changeTypes are also supported:

In addition, it also supports the execution of command

 changeSet:  
  id:  executeCommand-example  
  author:  liquibase-docs  
  changes:  
  -  executeCommand:  
      args:  
      -  arg:  
          value:  -out  
      -  arg:  
          value:  -param2  
      executable:  mysqldump  
      os:  Windows 7  
      timeout:  10s

For example, the rollback operation can be performed by the following command

For another example, we can generate relevant differences through Liquibase, make them into changeSets, and finally deploy them.

Sample source code

https://github.com/realpdai/tech-pdai-spring-demos

Reference article

https://docs.liquibase.com