Flyway for DB

Flyway for DB

1. Command Tool
1.1 Command Tool with H2 DB demo
Find and download the flyway-commandline-2.3.zip, unzip and put it in the working directory.
>cd /opt/flyway
>cd jars
>wget http://repo1.maven.org/maven2/com/h2database/h2/1.3.170/h2-1.3.170.jar

Just download the driver and place them in the jars directory

Just use H2 for demo, set up the configuration file like this
>vi conf/flyway.properties
flyway.url=jdbc:h2:file:./foobardb
flyway.user=SA

Put the first SQL migration under sql
>vi sql/V1__Create_person_table.sql
create table PERSON (        ID int not null,        NAME varchar(100) not null );

The first letter should be V, can not be v.

>./flyway migrate
/opt/local/bin/tput Flyway (Command-line Tool) v.2.3 Current version of schema "PUBLIC": << Empty Schema >> Migrating schema "PUBLIC" to version 1 Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.104s).

Adding a second migration
sparkworker1:flyway carl$ cat sql/V2__Add_people.sql insert into PERSON (ID, NAME) values (1, 'Axel'); insert into PERSON (ID, NAME) values (2, 'Mr. Foo'); insert into PERSON (ID, NAME) values (3, 'Ms. Bar');

1.2 Let us play with MySql
Download the jar from the official website
>wget http://cdn.mysql.com/Downloads/Connector-J/mysql-connector-java-5.1.29.tar.gz
Find and place the jar mysql-connector-java-5.1.29-bin.jar  under the jars directory.

Edit the configuration file
>vi conf/flyway.properties
flyway.url=jdbc:mysql://localhost:3306/test
flyway.user=root
flyway.password=

>flyway migrate

There are a lot of other command parameters I can use. For example, clean, migrate, validate, info, repair

2. Flyway DB for SBT
First of all, if I want to use flyway plugin, I need to update my sbt version to 1.13.0 to support the latest version.
Check this file 
>cat project/build.properties sbt.version=0.13.0

And update the version of my other plugins to support the sbt 0.13
>cat project/plugins.sbt addSbtPlugin("com.googlecode.flyway" % "flyway-sbt" % "2.3") resolvers += "Flyway" at "http://flywaydb.org/repo" addSbtPlugin("io.spray" % "sbt-revolver" % "0.7.1") addSbtPlugin("com.eed3si9n" % "sbt-assembly" % "0.10.2") addSbtPlugin("com.typesafe.sbt" % "sbt-scalariform" % "1.3.0") addSbtPlugin("com.typesafe.sbteclipse" % "sbteclipse-plugin" % "2.2.0") resolvers += "Sonatype snapshots" at "http://oss.sonatype.org/content/repositories/snapshots/" addSbtPlugin("com.github.mpeltonen" % "sbt-idea" % "1.5.0-SNAPSHOT")

Check the build.sbt to make sure the flyway configuration
import com.googlecode.flyway.sbt.FlywayPlugin._
//flyway start
seq(flywaySettings: _*)

flywayUrl := "jdbc:h2:~/demo_test;DB_CLOSE_DELAY=-1"

flywayUser := "sa"

flywayPassword := "password"

//flyway end

And place the migration files in the directory like these>

src/main/resources/db/migration/V1__Create_person_table.sql
src/main/resources/db/migration/V2__Add_people.sql

create table PERSON (
    ID int not null,
    NAME varchar(100) not null
);

insert into PERSON (ID, NAME) values (1, 'Axel');
insert into PERSON (ID, NAME) values (2, 'Mr. Foo');
insert into PERSON (ID, NAME) values (3, 'Ms. Bar');

Run the command to migrate the DB
>sbt flywayMigrate

There are also other commands flywayClean, flywayInit, flywayMigrate, flywayValidate, flywayInfo, flywayRepair

References:
http://flywaydb.org/getstarted/firststeps/commandline.html

http://flywaydb.org/getstarted/firststeps/sbt.html

http://flywaydb.org/documentation/sbt/