How to use liquibase with Spring Boot (MySQL)

How to use liquibase with Spring Boot (MySQL)

It seems that liquibase can be used to create a database when running a Spring Boot application, and also to create tables, populate data, delete tables, etc., to manage migration.

For now, I used liquibase because I wanted to create a database and CREATE tables.

Select liquibase from the Spring Boot starter project.

To create tables, you can create files in xml or yml format, which will be read and created one by one when the Spring Boot application starts.

We assume that MySQL is installed on the local PC.

Modify build.gradle as follows Now liquibase will be executed at startup.

buildscript {
  ext {
    springBootVersion = '2.1.1.RELEASE'
  }
  repositories {
    mavenCentral()
  }
  dependencies {
    classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
  }
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'org.springframework.boot'
apply plugin: 'io.spring.dependency-management'

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '1.8'

repositories {
  mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    implementation 'org.liquibase:liquibase-core' // 追加
    runtimeOnly 'mysql:mysql-connector-java'
    compileOnly 'org.projectlombok:lombok'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
}

The default file name seems to be db.changelog-master.yaml, according to the official site.

Place src\main\resources\db\db.changelog-master.yaml

db.changelog-master.yaml

databaseChangeLog:
- include:
    file: a.yaml

If there is more than one file, we will specify the file name with a new line.

The contents of db.changelog-master.yaml (which is the default file name) in the case of multiple files would be as follows.

databaseChangeLog:
- include:
    file: a.yaml
- include:
    file: b.yaml

In the above, b.yaml is read after a.yaml is read.

You can create tables in db.changelog-master.yaml, or split files as shown above.

Here is an example of a.yaml.

databaseChangeLog:
  - changeSet:
      id: 1
      author: confrage
      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)

Reference Site

Property Name

You can set properties in MySQL with query parameters

createDatabaseIfNotExist=true … create database if database does not exist

Reference Site

application.properties

This file should contain the MySQL connection information.

spring.datasource.url=jdbc:mysql://localhost:3306/sampledb?serverTimezone=UTC&createDatabaseIfNotExist=true
spring.datasource.username=root
spring.datasource.password=system
spring.jpa.database=MYSQL
spring.liquibase.change-log=classpath:db/db.changelog-master.yaml

You can change the classpath and file name in spring.liquibase.change-log=classpath:db/changelog/db.changelog-master.yaml.

Running a Spring Boot application

Now let’s run the Spring Boot application, and you should see the database sampledb created, and the databasechangelog table, databasechangeloglock table, and person table created.

mysql> desc person;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| firstname | varchar(50) | YES  |     | NULL    |                |
| lastname  | varchar(50) | NO   |     | NULL    |                |
| state     | char(2)     | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)

Adding columns to a table

liquibase creates a table, but then creates a new YML file and issues an ALTER TABLE statement against the existing table when additional columns are added to the table.

The ALTER TABLE statement is executed as follows

databaseChangeLog:
  - changeSet:
      id:11111112
      comment: ID
      author: takahashi
      changes: 
        - addColumn:
            catalogName: schema name
            columns:
              - column:
                  name: column name
                  remarks: Remarks and notes
                  type: DATETIME
            schemaName: schema name
            tableName: table name

This is equivalent to doing the following, except that catalogName and schemaName are optional in MySQL.

ALTER TABLE schema.table_name ADD カラム名 DATETIME NULL;

If you want to add more than one column, write only the sentence you want to add in the – column: field.

Rename a table column name

LIQUIBASE is a migration tool, but simply put, it creates tables. Then, the column name is changed, so the column name is changed. After that, the table is no longer needed, so it is dropped, and so on.

In this case, we will create a yml to rename column names.

databaseChangeLog:
  - changeSet:
      id:11111112
      comment: ID
      author: takahashi
      changes: 
        - renameColumn:
            catalogName: schema name
            columnDataType:VARCHAR(10)
            newColumnName: New column name
            oldColumnName: Old column name
            remarks: Remarks and notes
        schemaName: public
        tableName: table name

This will rename the column name, but if the old column name had a not null constraint, the constraint will disappear. In that case, you need to write as follows

databaseChangeLog:
  - changeSet:
      id:11111112
      comment: ID
      author: takahashi
      changes: 
      - renameColumn:
          catalogName: schema name
          columnDataType:VARCHAR(10)
          newColumnName: New column name
          oldColumnName: Old column name
          remarks: Remarks and notes
          schemaName: public
          tableName: table name
      - addNotNullConstraint: 
          catalogName: schema name
          columnDataType: VARCHAR(10) 
          columnName: New column name
          defaultNullValue:
          schemaName: public
          tableName: table name

Change the type of a table column

Create a yml that changes the type of a table column. In the following, we change it to int.

databaseChangeLog:
  - changeSet:
      id:11111112
      comment: IDです
      author: takahashi
      changes: 
      - modifyDataType:
          catalogName: schema name
          columnName: Column name to be changed
          newDataType: int
          schemaName: public
          tableName: table name

set a unique key

Attach a unique key.

databaseChangeLog:
  - changeSet:
      id:11111112
      comment: IDです
      author: takahashi
      changes: 
       - addUniqueConstraint:
        columnNames: Key 1, Key 2, Key 3
        constraintName: unique constraint name
        deferrable: true
        disable: true
        initiallyDeferred: true
        catalogName: schema name
        schemaName: public
        tableName: table name

以下と同様です。

ALTER TABLE table name ADD CONSTRAINT unique constraint name UNIQUE (xx,yy,zz);

Insert initial data

LIQUIBASE creates the table, but there are no records when it is created.

This is inconvenient, but of course it is possible to insert initial records.

databaseChangeLog:
  - changeSet:
    id:11111113
    comment: ID
    author: takahashi
    changes:
    - createTable:
      tableName: sampleTbl
      remarks: Sample table.
      columns:
      - column:
        name: emp_id
        remarks: Employee ID
        type: VARCHAR(10)
        constrains:
          nullable: false
      - column:
        name: emp_name
        remarks: Employee Name
        type: VARCHAR(30)
        constrains:
          nullable: false
    - sql:
      sql: INSERT INTO sampleTbl (emp_id, emp_name) VALUES ('1', 'takahashi')
    - sql:
      sql: INSERT INTO sampleTbl (emp_id, emp_name) VALUES ('2', 'demien') 

This is an example of creating a table and then inserting two inserts.

Modify the DATABASECHANGELOG table

The DATABASECHANGELOG table is a management table, but if something goes wrong, you can delete only one record in the yml file that is causing the error and run it again.

At this time, if CREATE TABLE is listed in the yaml file, it is necessary to DROP TABLE the table in advance.

I don’t think the DATABASECHANGELOGLOCK table should be modified.

Partitioning with liquibase (MySQL)

The only way to partition in liquibase (MySQL) is to use an ALTER statement in sql.

Forum.

postgreSQL seems to support modifySql.

Reference site

  modifySql:
    dbms: postgresql
    append value: PARTITION BY RANGE COLUMNS(borned) (PARTITION p0 VALUES LESS THAN ('2000-01-01 00:00:00'),PARTITION p1 VALUES LESS THAN (MAXVALUE));

In MySQL, it must be written in sql as follows.

databaseChangeLog:
  - changeSet:
      id: 1
      author: takahashi
      changes:
        - createTable:
            tableName: person
            columns:
              - column:
                name: id
                type: int
                constraints:
                  nullable: false
              - column:
                name: firstname
                type: varchar(50)
              - column:
                name: lastname
                type: varchar(50)
                constraints:
                  nullable: false
              - column:
                name: borned
                type: datetime
            sql: |
              alter table person
              PARTITION BY RANGE COLUMNS(borned)
              (PARTITION p0 VALUES LESS THAN ('2000-01-01 00:00:00'),
              PARTITION p1 VALUES LESS THAN (MAXVALUE));

高橋