In this post, I am going to explain how to perform migrations using Java API on MySQL.
I am going to execute three migrations.
Migration 1: Create EMPLOYEE table.
create table EMPLOYEE (
ID int not null,
NAME varchar(100) not null
);
Migration 2: Insert some data into EMPLOYEE table.
INSERT INTO EMPLOYEE VALUES(1, 'Krishna');
INSERT INTO EMPLOYEE VALUES(2, 'Ram');
INSERT INTO EMPLOYEE VALUES(3, 'Sailaja');
INSERT INTO EMPLOYEE VALUES(4, 'Lahari');
Migration 3: Delete a record from EMPLOYEE table
DELETE FROM EMPLOYEE WHERE name = 'Krishna';
Following java snippet is used to perform migrations using Java API.
Flyway flyway = Flyway.configure().dataSource(url, user, password).load(); flyway.migrate();
Let’s build a java application.
Step 1: Create new maven project 'flyway-hello-world'.
Step 2: Update pom.xml with maven dependencies.
pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.sample.app</groupId>
<artifactId>flyway-hello-world</artifactId>
<version>1</version>
<dependencies>
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
<version>7.9.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
</dependencies>
</project>
Step 3: Create V1__Create_employee_table.sql file in src/main/resources/db/migration folder.
V1__Create_employee_table.sql
create table EMPLOYEE (
ID int not null,
NAME varchar(100) not null
);
Step 4: Define main application class.
App.java
package com.sample.app;
import org.flywaydb.core.Flyway;
public class App {
public static void main(String[] args) {
// Create the Flyway instance and point it to the database
Flyway flyway = Flyway.configure().dataSource("jdbc:mysql://localhost:3306/abc_org", "root", "tiger").load();
// Start the migration
flyway.migrate();
}
}
Total project structure looks like below.
Run App.java, you will see below messages in console.
May 22, 2021 4:41:50 PM org.flywaydb.core.internal.command.DbMigrate doMigrateGroup INFO: Migrating schema `abc_org` to version "1 - Create employee table" May 22, 2021 4:41:50 PM org.flywaydb.core.internal.command.DbMigrate logSummary INFO: Successfully applied 1 migration to schema `abc_org`, now at version v1 (execution time 00:00.030s)
How Flyway works?
When you run the migration for the first time, Flyway tries to find a 'flyway_schema_history' table. If the table doesn’t exists in the given database it creates one.
mysql> show tables;
+-----------------------+
| Tables_in_abc_org |
+-----------------------+
| EMPLOYEE |
| flyway_schema_history |
+-----------------------+
2 rows in set (0.00 sec)
flyway_schema_history table is used to track the state of the database.
After the flyway_schema_history table creation, Flyway starts scanning for the migration scripts located in classpath of the application. The migrations are then sorted based on their version number and executed in order.
On every migration script executed, the schema history table is updated accordingly.
Let’s query ‘flyway_schema_history’ table and confirm it.
mysql> SELECT * FROM flyway_schema_history;
+----------------+---------+-----------------------+------+-------------------------------+-------------+--------------+---------------------+----------------+---------+
| installed_rank | version | description | type | script | checksum | installed_by | installed_on | execution_time | success |
+----------------+---------+-----------------------+------+-------------------------------+-------------+--------------+---------------------+----------------+---------+
| 1 | 1 | Create employee table | SQL | V1__Create_employee_table.sql | -2061683220 | root | 2021-05-22 16:41:50 | 8 | 1 |
+----------------+---------+-----------------------+------+-------------------------------+-------------+--------------+---------------------+----------------+---------+
1 row in set (0.01 sec)
Once a migration script executed successfully, you can undo it by executing 'flyway.undo();' method. ‘undo’ method Undoes the most recently applied versioned migration. If target is specified, Flyway will attempt to undo versioned migrations in the order they were applied until it hits one with a version below the target. If there is no versioned migration to undo, calling undo has no effect. But this feature is not available in open source version of Flyway.
What will happen if I ran the App class again?
Flyway again scans for the migration scripts in the classpath, if it find any new migration script files, it execute those in the ascending order of version number, else ignore.
For example, When I rerun App.java file, I got following output.
May 22, 2021 4:51:56 PM org.flywaydb.core.internal.command.DbMigrate migrateGroup INFO: Current version of schema `abc_org`: 1 May 22, 2021 4:51:56 PM org.flywaydb.core.internal.command.DbMigrate logSummary INFO: Schema `abc_org` is up to date. No migration necessary.
Migration 2: Insert some data into EMPLOYEE table.
Create the file ‘V2__Insert_Into_employee_table.sql’ in src/main/resources/db/migration folder.
V2__Insert_Into_employee_table.sql
INSERT INTO EMPLOYEE VALUES(1, 'Krishna');
INSERT INTO EMPLOYEE VALUES(2, 'Ram');
INSERT INTO EMPLOYEE VALUES(3, 'Sailaja');
INSERT INTO EMPLOYEE VALUES(4, 'Lahari');
Execute App.java file. You will see below messages in console.
May 22, 2021 4:54:35 PM org.flywaydb.core.internal.command.DbValidate validate INFO: Successfully validated 2 migrations (execution time 00:00.020s) May 22, 2021 4:54:35 PM org.flywaydb.core.internal.command.DbMigrate migrateGroup INFO: Current version of schema `abc_org`: 1 May 22, 2021 4:54:35 PM org.flywaydb.core.internal.command.DbMigrate doMigrateGroup INFO: Migrating schema `abc_org` to version "2 - Insert Into employee table" May 22, 2021 4:54:35 PM org.flywaydb.core.internal.command.DbMigrate logSummary INFO: Successfully applied 1 migration to schema `abc_org`, now at version v2 (execution time 00:00.048s)
Query EMPLOYEE table.
mysql> SELECT * FROM EMPLOYEE;
+----+---------+
| ID | NAME |
+----+---------+
| 1 | Krishna |
| 2 | Ram |
| 3 | Sailaja |
| 4 | Lahari |
+----+---------+
4 rows in set (0.00 sec)
Query 'flyway_schema_history' table.
mysql> SELECT * FROM flyway_schema_history;
+----------------+---------+----------------------------+------+------------------------------------+-------------+--------------+---------------------+----------------+---------+
| installed_rank | version | description | type | script | checksum | installed_by | installed_on | execution_time | success |
+----------------+---------+----------------------------+------+------------------------------------+-------------+--------------+---------------------+----------------+---------+
| 1 | 1 | Create employee table | SQL | V1__Create_employee_table.sql | -2061683220 | root | 2021-05-22 16:41:50 | 8 | 1 |
| 2 | 2 | Insert Into employee table | SQL | V2__Insert_Into_employee_table.sql | -1900411443 | root | 2021-05-22 16:54:35 | 10 | 1 |
+----------------+---------+----------------------------+------+------------------------------------+-------------+--------------+---------------------+----------------+---------+
2 rows in set (0.00 sec)
Migration 3: Let’s perform some delete operation and update operation.
V3__delete_from_employee_table.sql
DELETE FROM EMPLOYEE WHERE name = 'Krishna';
Execute App.java file again.
You will see below messages in console.
May 22, 2021 4:58:27 PM org.flywaydb.core.internal.command.DbValidate validate INFO: Successfully validated 3 migrations (execution time 00:00.025s) May 22, 2021 4:58:27 PM org.flywaydb.core.internal.command.DbMigrate migrateGroup INFO: Current version of schema `abc_org`: 2 May 22, 2021 4:58:27 PM org.flywaydb.core.internal.command.DbMigrate doMigrateGroup INFO: Migrating schema `abc_org` to version "3 - delete from employee table" May 22, 2021 4:58:27 PM org.flywaydb.core.internal.command.DbMigrate logSummary INFO: Successfully applied 1 migration to schema `abc_org`, now at version v3 (execution time 00:00.044s)
Query EMPLOYEE table
mysql> SELECT * FROM EMPLOYEE;
+----+---------+
| ID | NAME |
+----+---------+
| 2 | Ram |
| 3 | Sailaja |
| 4 | Lahari |
+----+---------+
3 rows in set (0.00 sec)
Query flyway_schema_history table
mysql> SELECT * FROM flyway_schema_history;
+----------------+---------+----------------------------+------+------------------------------------+-------------+--------------+---------------------+----------------+---------+
| installed_rank | version | description | type | script | checksum | installed_by | installed_on | execution_time | success |
+----------------+---------+----------------------------+------+------------------------------------+-------------+--------------+---------------------+----------------+---------+
| 1 | 1 | Create employee table | SQL | V1__Create_employee_table.sql | -2061683220 | root | 2021-05-22 16:41:50 | 8 | 1 |
| 2 | 2 | Insert Into employee table | SQL | V2__Insert_Into_employee_table.sql | -1900411443 | root | 2021-05-22 16:54:35 | 10 | 1 |
| 3 | 3 | delete from employee table | SQL | V3__delete_from_employee_table.sql | -2000487616 | root | 2021-05-22 16:58:27 | 7 | 1 |
+----------------+---------+----------------------------+------+------------------------------------+-------------+--------------+---------------------+----------------+---------+
3 rows in set (0.00 sec)
You can download complete working application from below link.
https://github.com/harikrishna553/springboot/tree/master/flyway/flyway-hello-world
No comments:
Post a Comment