Database Migration with Flyway

Flyway

Flyway is database migration tool for several environment.
It’s like Rails migration management.
We can use a lot of database(MySQL, PostgreSQL, SQLite3, Oracle, SQL Server etc…), and manage version of database.

Install

Please check following documentation
documentation

Mac

Windows

Download and decompress, all which is related with flyway under its directory.

Way?

We have several choices to manage database with Flyway

  • Command line tool
  • gradle task
  • Create simple application with Flyway API

Configuration

Configuration has inside of directory…. Umm… it means that we can use only one setting with it.
installdirectory/conf/flyway.conf

Directory structure

Directory
|- sql
|   |- sql files   V1_xxxxx.sql     # V is fixed 1 is version __  is prefix for file name
|- flyway   : execution

Practice

configuration

Configuration(/conf/flyway.conf)

flyway.url=jdbc:mysql://localhost/flysample
flyway.user=<username>
flyway.password=<password>

create database

Create database

mysql> create database flysample;

prepare version sql file

V1__Create_member_table.sql

create table members (
    id int not null,
    name varchar(255) not null
);

migrate

Run flyway command

flyway migrate
Database: jdbc:mysql://localhost:3306/flysample (MySQL 5.7)
Successfully validated 1 migration (execution time 00:00.005s)
Creating Metadata table: `flysample`.`schema_version`
Current version of schema `flysample`: << Empty Schema >>
Migrating schema `flysample` to version 1 - Create person table
Successfully applied 1 migration to schema `flysample` (execution time 00:00.149
s).

Insert data

V2_Add_member.sql

insert into members (id, name) values (1, 'honoka');
insert into members (id, name) values (2, 'kotori');
insert into members (id, name) values (3, 'umi');

It is same as creating

flyway migrate

Flyway with Spring Boot(gradle)

gradle documentation
We can cover with gradle task
Prepare build.gradle

buildscript {
    repositories {
        maven {
            url "https://plugins.gradle.org/m2/"
        }
    }
    dependencies {
        classpath 'mysql:mysql-connector-java:5.1.37'
        classpath "gradle.plugin.com.boxfuse.client:flyway-release:4.0.3"
    }
}

dependencies {
   compile group: 'org.flywaydb', name: 'flyway-core', version: '4.0.3'
}

gradle.properties

flyway.url = jdbc:mysql://localhost:3306
flyway.user = root
flyway.password
flyway.schemas = flysample

Run gradle task