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 : Version control for your database

Assumptions / Environment

Runtime version

Kotlin : 1.3.21
SpringBoot : 2.1.1.RELEASE

Spring Dependencies

Web
JDBC
JPA
Actuator
Flyway

Development environment

OS : Mac
IDE : IntelliJ IDEA
Build : Gradle

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.

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 is`PUBLIC`Designated 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

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.

<PREFIX><VERSION>__<DESCRIPTION>.sql

item	Description
PREFIX	The default isV Flyway scans and executes files starting with V `flyway.sqlMigrationPrefix`Properties`application.yml`Can 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.

db/migration/V1.0.0__Create-InitialTable.sql

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.

V1.1.0-Add data

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

db/migration/V1.1.0__Insert-InitialData.sql

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.

Migrating schema "PUBLIC" to version 1.0.0 - Create-InitialTable
Migrating schema "PUBLIC" to version 1.1.0 - Insert-InitialData

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

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.

Summary / Looking back

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

shinyay/spring-just-rest-kotlin/tree/first-flyway-h2