Spring with Kotorin --9 Database migration --Flyway

Overview / Description

Most web applications use databases to query and store data. Therefore, we have previously implemented a layer in the application that is responsible for database connection. -Spring with Kotorin-- 8. Repository layer

On the other hand, the layout of the database generally changes as the application grows. It is unlikely that it will not change from the initial design. Therefore, it is important to manage the state of the database.

I think that the source code of the application is versioned by a management tool such as Git. Similarly, the tool (framework) used for versioning the state of the database is ** Flyway **, which is used this time.

flyway-logo-tm.png

Assumptions / Environment

Runtime version

Spring Dependencies

Development environment

Procedure / Explanation

Dependency added

First, edit each definition file of Gradle (build.gradle) or Maven (pom.xml) and add the Dependency of Flyway.

--For Gradle (build.gradle)

dependencies {
	implementation('org.flywaydb:flyway-core')
}

--For Maven (pom.xml)

<dependency>
	<groupId>org.flywaydb</groupId>
	<artifactId>flyway-core</artifactId>
</dependency>

Also, if you want to use Spring Initializr when you first create a Spring project, you can add Flayway in the Dependencies item as shown in the figure below. Is possible.

flyway-initilizr.png

Description of database connection definition

Define the connection definition of the database to be migrated by Flyway and the settings related to Flyway behavior in ʻapplication.yml` (or applicatation.properties).

  flyway:
    enabled: true
    url: jdbc:h2:mem:app;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=TRUE
    schemas: PUBLIC
    user: guest
    password: guest
    baseline-on-migrate: true
    baseline-version: 1.0.0
    baseline-description: Initial
    locations: classpath:db/migration
item Description
enabled Whether Flyway can be executed
Default: true(Execute)
url Database connection string to be migrated
schemas Target schema
The default schema of H2DB targeted this time isPUBLICDesignated for(Uppercase for case sensitive)
user/password Database connection user ID/password
baseline-on-migrate Whether to start the execution version of the Flyway migration script in the middle
Default: false(All done from the first version)
baseline-version baseline-on-Version to start if migrate is true
baseline-description baseline-Comments recorded when implemented in version
locations Location of migration script
classpath:Specify a location on the classpath
filepath:Specify a directory on the file system

Reference: Flyway Migrate Command [^ 1]

Placement of database migration scripts

Flyway automatically executes the placed SQL based on the determined rules and executes the migration. And by default, the location of the script will be the following directory created under src / main / resources.

db-migration-dir.png

The SQL file placed in db / migration must have a file name that follows the naming convention recognized by Flyway. The naming convention is as follows.

item Description
PREFIX The default isV
Flyway scans and executes files starting with V
flyway.sqlMigrationPrefixPropertiesapplication.ymlCan be defined and changed in
VERSION Dot(.)Or underscore(_)Can separate major version and minor version with
Version should start from 1
DESCRIPTION Item for description notation
Easily express the changes made in the corresponding version

Example: V1.0.0_my_first_flyway.sql

Creating a database migration script

V1.0.0-Create table

First define the DDL that creates the table.

CREATE TABLE message (
    id VARCHAR(36) NOT NULL PRIMARY KEY,
    title VARCHAR(255),
    message VARCHAR(255),
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Run the application.

$ ./gradlew clean bootRun

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.1.1.RELEASE)
  :
  :
2019-03-28 13:26:52.822  INFO 7128 --- [           main] o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 5.2.4 by Boxfuse
2019-03-28 13:26:53.118  INFO 7128 --- [           main] o.f.c.internal.database.DatabaseFactory  : Database: jdbc:h2:mem:app (H2 1.4)
2019-03-28 13:26:53.254  INFO 7128 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 1 migration (execution time 00:00.018s)
2019-03-28 13:26:53.280  INFO 7128 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table: "PUBLIC"."flyway_schema_history"
2019-03-28 13:26:53.329  INFO 7128 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema "PUBLIC": << Empty Schema >>
2019-03-28 13:26:53.331  INFO 7128 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "PUBLIC" to version 1.0.0 - Create-InitialTable
2019-03-28 13:26:53.358  INFO 7128 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.089s)
  :
  :

From the startup log, you can see that the table creation process by Flyway Migrating schema" PUBLIC "to version 1.0.0 --Create-InitialTable has been executed.

Also, if you check the H2DB console as shown below, you can see that the processing of 1.0.0 is recorded in the Flyway history table as shown below.

flyway_1_0_0.png

V1.1.0-Add data

Next, define the SQL to add data to the created table.

INSERT INTO message(id, title, message) VALUES ('7b23257c-e9d9-4d1e-ba79-01f8b8715ba9', 'INIT', 'Inserted by FLYWAY');

INSERT INTO message(id, title, message) VALUES ('12345678-e9d9-4d1e-ba79-01f8b8715ba9', 'INIT', 'Inserted by FLYWAY');

Run the application as before.

$ ./gradlew clean bootRun

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.1.1.RELEASE)
  :
  :
2019-03-28 13:43:30.226  INFO 7430 --- [           main] o.f.c.internal.license.VersionPrinter    : Flyway Community Edition 5.2.4 by Boxfuse
2019-03-28 13:43:30.431  INFO 7430 --- [           main] o.f.c.internal.database.DatabaseFactory  : Database: jdbc:h2:mem:app (H2 1.4)
2019-03-28 13:43:30.588  INFO 7430 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 2 migrations (execution time 00:00.029s)
2019-03-28 13:43:30.617  INFO 7430 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table: "PUBLIC"."flyway_schema_history"
2019-03-28 13:43:30.658  INFO 7430 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema "PUBLIC": << Empty Schema >>
2019-03-28 13:43:30.659  INFO 7430 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "PUBLIC" to version 1.0.0 - Create-InitialTable
2019-03-28 13:43:30.694  INFO 7430 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "PUBLIC" to version 1.1.0 - Insert-InitialData
2019-03-28 13:43:30.713  INFO 7430 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 2 migrations to schema "PUBLIC" (execution time 00:00.107s)

From the startup log, you can see that the added migration script is being executed.

If you check the H2DB console, you can see in the history table that two migration processes have been executed.

flyway_1_1_0.png

Also, if you query the created MESSAGE table, the SQL defined as the migration script will be reflected and you can confirm that the data has been added.

flyway_1_1_0-select-data.png

Summary / Looking back

Migration-1-2.png

When developing an application, the database layout tends to change frequently, and the work such as data registration tends to occur frequently. It is also necessary to manage when the layout is changed (requirements). If you manage the database using this Flyway, you can reduce the time and effort of such work.

This source

Recommended Posts

Spring with Kotorin --9 Database migration --Flyway
Spring with Kotorin ―― 1. SPRING INITIALIZR
Spring with Kotorin --8 Repository layer --Supplement: H2 Database
Try to automate migration with Spring Boot Flyway
Spring with Kotorin --8 Repository layer
Spring with Kotorin --6 Asynchronous processing
Spring with Kotorin ―― 7. Service layer
Spring with Kotorin --4 REST API design
I tried Flyway with Spring Boot
Spring with Kotorin --3. Omitting curly braces from the function
[Java] DB migration (Flyway)
Self-made Validation with Spring
Database environment construction with Docker in Spring boot (IntellJ)
With Kotorin ―― 7. Scoping Function
Download with Spring Boot
Connect to database with spring boot + spring jpa and CRUD operation
Generate barcode with Spring Boot
Hello World with Spring Boot
Java Config with Spring MVC
Get started with Spring boot
Hello World with Spring Boot!
Run LIFF with Spring Boot
SNS login with Spring Boot
Login function with Spring Security
Using Mapper with Java (Spring)
Spring Boot starting with Docker
Error in Spring database connection
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
Link API with Spring + Vue.js
Create microservices with Spring Boot
Send email with spring boot
Coexistence of Flyway in the embedded database (h2) of the development environment and the release database (SQL Server) with Spring Boot