Try to automate migration with Spring Boot Flyway

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

In the previous article, I even created 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
}

// ...Abbreviation

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 that I don't need now, such as doma.

applocation.yml


#Define common settings
spring:
  profiles:
    #Explicitly specify the default
    default: local
    active: local
  main:
    #Temporary support for overwriting Bean of DataValueProcessor
    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 an underscore delimited string
    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 any resources that are 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: 18082 #Changed from 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 in MDC
    level: "[%X{FUNCTION_NAME}:%X{X-Track-Id}:%X{LOGIN_USER_ID}] %5p"

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

Create application-local.yml

Make settings for local. Here, add the docker DB information created in the previous article. (This is the 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 in docker-compose.yml ** [DB_name] ** is the table name (MYSQL_DATABASE) specified in the environment of docker-compose.yml

** username and password ** Username (MYSQL_ROOT_USER) and password (MYSQL_ROOT_PASSWORD) specified in the 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 the 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 # com.Change from sample

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

Create sql file

Flyway-core documentation As stated in the documentation, the sql file has a naming convention. This time, I created the following two sql files.

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 'mail address'
  , password VARCHAR(100) DEFAULT NULL COMMENT 'password'
  , role VARCHAR(100) DEFAULT NULL COMMENT 'Authority'
  , created_at DATETIME NOT NULL COMMENT 'Registered Date'
  , updated_by VARCHAR(50) DEFAULT NULL COMMENT 'changer'
  , updated_at DATETIME DEFAULT NULL COMMENT 'Update date and time'
  , deleted_by VARCHAR(50) DEFAULT NULL COMMENT 'Deleter'
  , deleted_at DATETIME DEFAULT NULL COMMENT 'Delete date and time'
  , 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 it remains as it is. I will do it in the next article.

Application execution

Open an IntelliJ terminal and run ./gradlew bootRun.

$ ./gradlew bootRun

> Task :bootRun

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


...Abbreviation

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 looks like this in the terminal, it is successful. The number after ** EXECUTING ** is the startup time, so it will continue to work.

If the display below is displayed, it is a failure, so check the error.

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

Check inside the table

[Previous article](https://qiita.com/megumi_622/items/341cfbb684256e7c4dbc#%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AE % E7% A2% BA% E8% AA% 8D) Enter MySQL inside the container and check the table.

If the user is registered as shown below, it is successful.

mysql> select * from users;
+---------+------------+-----------+---------------+-----------+------+----------------------+------------+------------+------------+------------+---------+
| user_id | 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       |       1 |
|       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, we will create a ** login screen **.

Recommended Posts

Try to automate migration with Spring Boot Flyway
I tried Flyway with Spring Boot
[Java] Article to add validation with Spring Boot 2.3.1.
I wanted to gradle spring boot with multi-project
Try to display hello world with spring + gradle
[Introduction to Spring Boot] Authentication function with Spring Security
Download with Spring Boot
Try Dependency Inversion Principle with Multiple Spring Boot Projects
How to boot by environment with Spring Boot of Maven
Attempt to SSR Vue.js with Spring Boot and GraalJS
Try to work with Keycloak using Spring Security SAML (Spring 5)
Introduction to Spring Boot ① ~ DI ~
Getting Started with Spring Boot
Introduction to Spring Boot Part 1
Try using Spring Boot Security
Try Spring Boot on Mac
Create microservices with Spring Boot
Send email with spring boot
I tried to get started with Swagger using Spring Boot
(Intellij) Hello World with Spring Boot
Message cooperation started with Spring Boot
Implement paging function with Spring Boot + Thymeleaf
(IntelliJ + gradle) Hello World with Spring Boot
How to split Spring Boot message file
Use cache with EhCashe 2.x with Spring Boot
Form class validation test with Spring Boot
Add spring boot and gradle to eclipse
Run WEB application with Spring Boot + Thymeleaf
Achieve BASIC authentication with Spring Boot + Spring Security
I tried to clone a web application full of bugs with Spring Boot
Sample to batch process data on DB with Apache Camel Spring Boot starters
Part2 Part II. How to proceed with Getting Started Spring Boot Reference Guide Note ①
From creating a Spring Boot project to running an application with VS Code
Apply Twitter Bootstrap 4 to Spring Boot 2 using Webjars
Create Spring Boot environment with Windows + VS Code
Create a web api server with spring boot
[Spring Boot] How to refer to the property file
Try to summarize the common layout with rails
Spring Boot --How to set session timeout time
Create a Spring Boot development environment with docker
Create Spring Cloud Config Server with security with Spring Boot 2.0
Spring Boot Memorandum
gae + spring boot
[Java] LINE integration with Spring Boot
[Introduction to Spring Boot] Submit a form using thymeleaf
Extend Spring Boot DefaultErrorViewResolver to dynamically customize error screens
Until you start development with Spring Boot in eclipse 2
Customize REST API error response with Spring Boot (Part 1)
I wrote a test with Spring Boot + JUnit 5 now
Database environment construction with Docker in Spring boot (IntellJ)
Connect Spring Boot and Angular type-safely with OpenAPI Generator
File upload with Spring Boot (do not use Multipart File)
Hello World (console app) with Apache Camel + Spring Boot 2
Let's find out how to receive in Request Body with REST API of Spring Boot