1. By Girish Bapat

2. Need of database migration How Flyway works Different ways to use flyway Demo Java API Command line Maven Ant

3. Need of Database migration Initial setup Database Application

4. Need of Database migration Database Application Development setup Database Application QA setup Database Application Integration setup Database Application Production setup Different environments Real world scenario

5. Comparison between source code and database Source code Version control tools are available Maven, Jenkins, Hudson reproducible builds and continuous integration Maven release plug-ins for tagging and for newer versions Database Manually applying database scripts What is current state of database? Which scripts are applied? If quick fix is applied on production after dev machine? How to setup new DB instance?

6. Solution is Database migrations Allows to recreate a database from scratch / update from any previously migrated state Make it clear at all times what state a database is in Migrate in a deterministic way from your current version of the database to a newer one

7. Flyway Application EMPTY Database Flyway will try to find out metadata table. As the Database is empty it does not find the same. How Flyway works

8. How Flyway works SCHEMA_VERSION Flyway first creates single empty table SCHEMA_VERSION by default: This table will be used for tracking state of database. Once SCHEMA_VERSION is created, Flyway will start scanning classpath for migrations. Migrations are sorted based on version number and applied in order

9. How Flyway works SCHEMA_VERSIONSCHEMA_VERSION Empty Database Version 1 SCHEMA_VERSION Version 2

10. API- Flyway can be configured directly into application. Flyway checks database version and before actual application starts, applies newer migrations Command-line tool- CLI is for users: Who wish to migration their database from the command- line without having to install Maven or Ant Flyway comes with plugin for Maven Gradle Plugin SBT ANT- Ant Tasks are provided as an AntLib Different ways to use flyway

11. Demo- Prerequisites & Setup-API Prerequisites: Java 5+, Maven 2 or 3 Create project mvn archetype:generate -B -DarchetypeGroupId=org.apache.maven.archetypes -DarchetypeArtifactId=maven-archetype-quickstart -DarchetypeVersion=1.1 -DgroupId=foo -DartifactId=bar -Dversion=1.0-SNAPSHOT -Dpackage=foobar Adding the dependencies ... com.googlecode.flywayflyway-core2.3com.h2databaseh21.3.170 ... ...

12. Demo- Integrating flyway- API Create App.java in src/main/java/foobar

13. Demo- first migration- API First migration: Migration directory: src/main/resources/db/migration First migration: src/main/resources/db/migration/V1__Create_person_table.sql Create table PERSON ( ID int not null, NAME varchar (100) not null ); Executing our program bar> mvn package exec:java -Dexec.mainClass=foobar.App Check the output INFO: Creating Metadata table: "PUBLIC"."schema_version" INFO: Current version of schema "PUBLIC": > INFO: Migrating schema "PUBLIC" to version 1 INFO: Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.062s).

14. Demo- Second migration- API Second migration: src/main/resources/db/migration/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'); Executing our program bar> mvn package exec:java -Dexec.mainClass=foobar.App Check the output: INFO: Current version of schema "PUBLIC": 1 INFO: Migrating schema "PUBLIC" to version 2 INFO: Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.090s).

15. Demo- Integrating flyway- API Create ExistingDB.java in src/main/java/foobar

16. Demo- migrations Existing database Existing database: Migration directory: src/main/resources/db/migration migrations: src/main/resources/db/migration/V1__Base_version.sql Check the output: INFO: Schema "PUBLIC" is up to date. No migration necessary. production status: State: SUCCESS version:1 INFO: Creating Metadata table: "PUBLIC"."schema_version INFO: Current version of schema "PUBLIC": > INFO: Migrating schema "PUBLIC" to version 1.0 INFO: Successfully applied 1 migration to schema "PUBLIC Sells status: State: SUCCESS version:1.0 Executing our program bar> mvn package exec:java -Dexec.mainClass=foobar.ExistingDB We have production db, we need to replicate database on other databases Generate a sql script that includes the entire DDL (including indexes, triggers, procedures, ...) of the production database. Add all insert scripts of data available on production. This script will form your base migration.

17. Demo- Prerequisites & Setup- CLI Prerequisites: Java 5+ Download flyway command line distribution http://repo1.maven.org/maven2/com/googlecode/flyway/flyway- commandline/2.3/flyway-commandline-2.3.zip Download H2 jars http://repo1.maven.org/maven2/com/h2database/h2/1.3.170/ h2-1.3.170.jar Setup Create flyway-commandline-2.3 and required directories Extract flyway-commandline-2.3.zip. It will create necessary structure Configuring Flyway Edit conf/flyway.properties flyway.url= jdbc:h2:file:///E:/PROJECTS/flywaydb/flyway-2.3/commandLineDB flyway.user=SA

18. Demo- first migration- CLI First migration: sql/V1__Create_person_table.sql Create table PERSON ( ID int not null, NAME varchar (100) not null ); Executing our program flyway-2.3> flyway migrate Check the output Flyway (Command-line Tool) v.2.3 Creating Metadata table: "PUBLIC"."schema_version" Current version of schema "PUBLIC": > Migrating schema "PUBLIC" to version 1 Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.194s).

19. Demo- Second migration- CLI Second migration: 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'); Executing our program flyway-2.3> flyway migrate Check the output: Flyway (Command-line Tool) v.2.3 Current version of schema "PUBLIC": 1 Migrating schema "PUBLIC" to version 2 Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.126s).

20. Demo- Prerequisites & Setup- Maven We will use the project as earlier created just change the pom.xml Adding the dependencies ... com.googlecode.flywayflyway-maven- plugin2.3jdbc:h2:file:///E:/PROJECTS/flywaydb/f lyway-maven/database/mavenDBsacom.h2databaseh21.3.170

21. Demo- first migration- Maven First migration: Migration directory: src/main/resources/db/migration First migration: src/main/resources/db/migration/V1__Create_person_table.sql Create table PERSON ( ID int not null, NAME varchar (100) not null ); Executing our program bar> mvn compile flyway:migrate Check the output [INFO] Creating Metadata table: "PUBLIC"."schema_version" [INFO] Current version of schema "PUBLIC": > [INFO] Migrating schema "PUBLIC" to version 1 [INFO] Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00 .171s).

22. Demo- Second migration- Maven Second migration: src/main/resources/db/migration/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'); Executing our program bar> mvn compile flyway:migrate Check the output: [INFO] --- flyway-maven-plugin:2.3:migrate (default-cli) @ bar --- [INFO] Current version of schema "PUBLIC": 1 [INFO] Migrating schema "PUBLIC" to version 2 [INFO] Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00 .075s).

23. Demo- Prerequisites & Setup- ANT Prerequisites: Java 5+, A working Ant install Create directories # mkdir flyway-antlibs flyway-antmigrations Download ant distribution and H2 jars http://repo1.maven.org/maven2/com/googlecode/flyway/flyway- ant/2.3/flyway-ant-2.3.zip Create build.xml as below

24. Demo- first migration- ANT First migration: Migration directory: migration First migration: migration/V1__Create_person_table.sql Create table PERSON ( ID int not null, NAME varchar (100) not null ); Executing our program flyway-ant > ant Check the output [flyway:migrate] Creating Metadata table: "PUBLIC"."schema_version" [flyway:migrate] Current version of schema "PUBLIC": > [flyway:migrate] Migrating schema "PUBLIC" to version 1 [flyway:migrate] Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.161s).

25. Demo- Second migration- ANT Second migration: migration/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'); Executing our program flyway-ant > ant Check the output: [flyway:migrate] Current version of schema "PUBLIC": 1 [flyway:migrate] Migrating schema "PUBLIC" to version 2 [flyway:migrate] Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.085s).

26. Questions & Answers

27. Contact me Twitter: @girishbapat Email: girish.bapat@gmail.com Slideshare: http://www.slideshare.net/girishbapat/getting-started-with- agile-database-migrations-for-java-flywaydb