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.
Spring Dependencies
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.
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 scriptclasspath: Specify a location on the classpathfilepath: Specify a directory on the file system |
Reference: Flyway Migrate Command [^ 1]
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
.
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.sqlMigrationPrefix Propertiesapplication.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
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.
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.
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.
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.
Recommended Posts