flywaydb mysql_Flyway for DB

痛定思痛。 2023-01-11 13:22 102阅读 0赞

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

>wgethttp://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

>wgethttp://cdn.mysql.com/Downloads/Connector-J/mysql-connector-java-5.1.29.tar.gz

Find and place the jarmysql-connector-java-5.1.29-bin.jarunder 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 (