[JAVA] 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
Try Spring Boot from 0 to 100.
Spring with Kotorin --9 Database migration --Flyway
I tried Flyway with Spring Boot
How to use MyBatis2 (iBatis) with Spring Boot 1.4 (Spring 4)
How to use built-in h2db with spring boot
Try LDAP authentication with Spring Security (Spring Boot) + OpenLDAP
[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
Settings for connecting to MySQL with Spring Boot + Spring JDBC
Automatically map DTOs to entities with Spring Boot API
Try using OpenID Connect with Keycloak (Spring Boot application)
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)
Generate barcode with Spring Boot
Hello World with Spring Boot
Implement GraphQL with Spring Boot
Get started with Spring boot
Hello World with Spring Boot!
Run LIFF with Spring Boot
SNS login with Spring Boot
Introduction to Spring Boot ① ~ DI ~
File upload with Spring Boot
Spring Boot starting with copy
Spring Boot starting with Docker
Hello World with Spring Boot
Set cookies with Spring Boot
Use Spring JDBC with Spring Boot
Add module with Spring Boot
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
Output embedded Tomcat access log to standard output with Spring Boot
Extract SQL to property file with jdbcTemplate of spring boot
Until INSERT and SELECT to Postgres with Spring boot and thymeleaf
Try hitting the zip code search API with Spring Boot
Connect to database with spring boot + spring jpa and CRUD operation
Flow until output table data to view with Spring Boot
I tried to get started with Swagger using Spring Boot
Use Basic Authentication with Spring Boot
gRPC on Spring Boot with grpc-spring-boot-starter
Create an app with Spring Boot 2
Hot deploy with Spring Boot development
Database linkage with doma2 (Spring boot)
How to set Spring Boot + PostgreSQL
Spring Boot programming with VS Code
Until "Hello World" with Spring Boot
Inquiry application creation with Spring Boot
Get validation results with Spring Boot
Try to imitate marshmallows with MiniMagick
Create an app with Spring Boot
Check date correlation with Spring Boot
How to use ModelMapper (Spring boot)