[Java] Let’s automate migration with Spring Boot Flyway

4 minute read

Continuing from the previous article [How to build a docker environment with Gradle for intelliJ], settings to automatically create tables in the DB at application startup are set. To do.

In the previous article, I went to creating a database called todo in the DB.

check build.gradle

Check if there is flyway in plugins and flyway-core in dependencies.

build.gradle



plugins {
    id'java'
    id'org.springframework.boot' version '2.3.1.BUILD-SNAPSHOT'
    id'io.spring.dependency-management' version '1.0.9.RELEASE'
    id'com.avast.gradle.docker-compose' version '0.12.1'
    id'org.flywaydb.flyway' version '6.4.3' // here
}

// ... omitted

dependencies {
    implementation'org.springframework.boot:spring-boot-starter-data-jdbc'
    implementation'org.springframework.boot:spring-boot-starter-thymeleaf'
    implementation'org.springframework.boot:spring-boot-starter-web'
    implementation'org.flywaydb:flyway-core' // here
    developmentOnly'org.springframework.boot:spring-boot-devtools'
    runtimeOnly'mysql:mysql-connector-java'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group:'org.junit.vintage', module:'junit-vintage-engine'
    }
}

create application.yml

https://github.com/miyabayt/spring-boot-doma2-sample The repository settings are used as they are. I removed the parts such as doma that are not needed now.

applocation.yml


# Define common settings
spring:
  profiles:
# Specify default explicitly
    default: local
    active: local
  main:
    Temporary support for # DataValueProcessor Bean overwrite
    allow-bean-definition-overriding: true
  messages:
# Set including the path of the message definition file
    basename: messages,ValidationMessages,PropertyNames
    cache-duration: -1
    encoding: UTF-8
  jackson:
# Make the JSON key a string delimited by underscores
    property-naming-strategy: CAMEL_CASE_TO_LOWER_CASE_WITH_UNDERSCORES
  thymeleaf:
# HTML5 mode has been deprecated, so switch to HTML mode
    mode: HTML
  resources:
# Cache static content
    cache:
      period: 604800
    chain:
  # Use resources if GZiped
      compressed: true
      strategy:
        content:
      # Versioning by MD5 hash value of content
          enabled: true
          paths: /**
  servlet:
    multipart:
  # Set upload size limit
      max-file-size: 5MB
      max-request-size: 5MB
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    hikari:
      autoCommit: false
      connectionTimeout: 30000
      idleTimeout: 30000
      maxLifetime: 1800000
      connectionTestQuery: SELECT 1
      minimumIdle: 10
      maximumPoolSize: 30
jmx:
  enabled: false
server:
  port: changed from 18082 # 18081
  compression:
    enabled: true
  servlet:
    session:
      timeout: 7200
  # Do not output session ID in URL
      tracking-modes: cookie
    contextPath: /
logging:
  pattern:
# Output the value set by MDC
    level: "[%X{FUNCTION_NAME}:%X{X-Track-Id}:%X{LOGIN_USER_ID}] %5p"

# Application settings
application:
  fileUploadLocation: changed from build/todo # sample

create application-local.yml

Set for local. Here, add the DB information of docker created in the previous article. (This is part of #change)

url jdbc:mysql://127.0.0.1:[port]/[DB_name]?useSSL=false&characterEncoding=UTF-8 [port] is the number specified in ports of docker-compose.yml [DB_name] is the table name (MYSQL_DATABASE) specified in environment of docker-compose.yml

username and password Username (MYSQL_ROOT_USER) and password (MYSQL_ROOT_PASSWORD) specified in environment of docker-compose.yml

application-local.yml


Settings for #local
spring:
  profiles: local
  messages:
    cache-duration: -1
  datasource:
    platform: mysql
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:33068/todo?useSSL=false&characterEncoding=UTF-8 # change
    username: root # change
    password: passw0rd # change
  resources:
# Invalidate cache
    cache:
      period: 0
  flyway:
    enable: true
    baseline-on-migrate: true
    placeholder-replacement: false
logging:
  level:
    org.springframework: INFO
    org.springframework.jdbc: INFO
    org.thymeleaf: INFO
    com.example: DEBUG # Changed from com.sample

####
# Application settings
application:
  fileUploadLocation: changed from build/todo # sample
  cors:
    allowedHeaders: "Origin, X-Requested-With, Content-Type, Accept, X-XSRF-TOKEN"
    allowedMethods: "POST, GET, PUT, OPTIONS, DELETE"
    allowedOrigins: "*" # specify domain names that allow CORS

Create sql file

Flyway-core documentation As the documentation says, there is a naming convention for sql files. This time I created the following two sql files.

  • R__0_create_tables.sql
  • R__1_insert_datas.sql

The default storage location is resource>db>migraion.

R__0_create_tables.sql


CREATE TABLE IF NOT EXISTS users(
  user_id INT(11) NOT NULL AUTO_INCREMENT COMMENT'User ID'
  , first_name VARCHAR(40) NOT NULL COMMENT'name'
  , last_name VARCHAR(40) NOT NULL COMMENT'last name'
  , email VARCHAR(100) UNIQUE DEFAULT NULL COMMENT'email address'
  , password VARCHAR(100) DEFAULT NULL COMMENT'password'
  , role VARCHAR(100) DEFAULT NULL COMMENT'privilege'
  , created_at DATETIME NOT NULL COMMENT'Registration date'
  , updated_by VARCHAR(50) DEFAULT NULL COMMENT'Updater'
  , updated_at DATETIME DEFAULT NULL COMMENT'update date'
  , deleted_by VARCHAR(50) DEFAULT NULL COMMENT'Deleted by', deleted_at DATETIME DEFAULT NULL COMMENT'Deleted date'
  , version INT(11) unsigned NOT NULL DEFAULT 1 COMMENT'revision number'
  , PRIMARY KEY (user_id)
  , KEY idx_users (email, deleted_at)
) COMMENT='user';

R__1_insert_datas.sql


INSERT INTO `users` VALUES
    (1,'Yamada','Hanako','[email protected]','passw0rd',NULL,NOW(),NULL,NULL,NULL,NULL,1),
    (2,'Suzuki','Taro','[email protected]','passw0rd',NULL,NOW(),NULL,NULL,NULL,NULL,1);

The password is not encoded here, so leave it as is. I will do that in the next article.

Application execution

Open the IntelliJ terminal and run ./gradlew bootRun.

$ ./gradlew bootRun

> Task :bootRun

  .____ _ __ _ _ _
 /\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
(( )\___ |'_ |'_| |'_ \/ _` | \ \ \ \
 \\/ ___)| |_)| | | | | || (_| |) ))))
  '|____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot :: (v2.3.1.BUILD-SNAPSHOT)


...

2020-06-13 22:15:41.439 [::] INFO 78329 --- [io-18082-exec-1] o.s.web.servlet.DispatcherServlet :Completed initialization in 7 ms
<=========----> 75% EXECUTING [10m 10s]
> :bootRun

If it is displayed in the terminal like this, it is successful. The number after EXECUTING is the startup time, so it will continue to move.

If the display is as shown below, it has failed. Check the error.

BUILD SUCCESSFUL in 5s
3 actionable tasks: 2 executed, 1 up-to-date

Check the table

[Last article](https://qiita.com/megumi_622/items/341cfbb684256e7c4dbc#%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AEEnterMySQLinthecontainerandcheckthetablelike(%E7%A2%BA%E8%AA%8D).

It is successful if the user is registered as follows.

mysql> select * from users;
+---------+------------+-----------+-------------- -+-----------+------+----------------------+------ ------+------------+------------+------------+---- -----+
first_name | last_name | email | password | role | created_at | updated_by | updated_at | deleted_by | deleted_at | version |
+---------+------------+-----------+-------------- -+-----------+------+----------------------+------ ------+------------+------------+------------+---- -----+
1 | Yamada | Hanako | [email protected] | passw0rd | NULL | 2020-06-13 22:05:03 | NULL | NULL | NULL | NULL |
2 | Suzuki | Taro | [email protected] | passw0rd | NULL | 2020-06-13 22:05:03 | NULL | NULL | NULL | NULL | 1 |
+---------+-----------+-------------+------------- ---+----------+------+---------------------+------ -----+------------+-------------+------------+---- -----+
2 rows in set (0.01 sec)

Next time, create a login screen.