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