Flyway

Flyway data migration tool

1. FLYWAY Rajesh Kumar 1

2. What IS Database Change management? Refers to incremental changes to database schema Change could be reversible as well Generally performed by database migration tools Performed by assigning versions to changes 2

3. Why Database Change management? Recreate a database from scratch Make it clear at all times what state a database is in Remove manual intervention from deployments Migrate in a deterministic way from your current version of the database to a newer one 3

4. What IS FLYWAY? Flyway is open source data migration tool Developed by Boxfuse GmbH in 2010 in Munchen, Germany Favors convention over configuration Widely adopted 850,000 downloads in 2015 Placed in the "Adopt" section of the  Thoughtworks Technology radar 4

5. Features Migrations can be written in SQL or Java Migrations can be run by Command line tool Java API Maven, Gradle, SBT or Ant plugins Simple, Easy to learn 5

6. Features Continued Plugins are available for Spring Boot, Grails , Play , Griffon , Grunt and more 6

7. Supported Databases Supported databases are Oracle , SQL Server, SQL Azure, DB2 , DB2 z/OS, MySQL (including Amazon RDS), MariaDB , Google Cloud SQL, PostgreSQL (including Amazon RDS and Heroku ), Redshift , Vertica , H2, Hsql , Derby, SQLite, SAP HANA, solidDB , Sybase ASE and Phoenix. 7

8. How flyway works? Flyway located metadata table i.e. schema_version If it is not present then create it Flyway then looks for migrations in file system Migrations are sorted based on their versions Version of a migration is coded in the migration file After each migration schema_version is updated accordingly 8

9. Schema_version Sample 9

10. How flyway works? The migrations are checked against the metadata table. If their version number is lower or equal to the one of the version marked as current, they are ignored. 10

11. Getting started Install flyway https:// flywaydb.org/getstarted/download Select the command line version 11

12. Download 12

13. What IS in the jar 13 c onf: contains flyway.conf, all configurations d rivers: contains database drivers, put your db driver file if not present j ars: put java migration jar here lib: contains flyway command line and flyway api jar sql: put sql migrations here

14. Migration Naming V1_1_0__Create_Person_Table.sql V is the suffix for migrations __ (double underscore) differentiates between version and name of migration _ (underscore ) differentiates between minor and major version 14

15. Sample migration with command line Edit flyway.conf URL, user name and password Create a sample migration in sql directory Run ‘flyway migrate’ Validate the migration in schema_version 15

16. Sample migration with Java API Prerequisites Java Maven Create a project with maven archetype plugin Add flyway-core and a db dependency Add a migration in src /main/resources/ db /migration 16

17. Sample migration with maven In previous project remove the code from app Add flyway plugin Run ’ mvn flyway:migrate ’ 17

18. Migration States When the migration  succeeds  it is marked as success in schema_version When the migration  fails  and the database supports  DDL transactions , the migration is  rolled back  and nothing is recorded in schema_version When the migration  fails  and the database doesn't supports  DDL transactions , the migration is marked as failed in schema_version , indicating manual database cleanup may be required O nly DB2, PostgreSQL, Derby, SQL Server support DDL transactions 18

19. Migration Types Versioned migrations Repeatable migrations 19

20. Versioned migrations Has a unique version and is applied exactly once . Each versioned migration must have a unique version and a description. 20

21. Repeatable migrations Repeatable migrations do not have a version They are applied every time their checksum changes Always applied after all pending versioned migrations have been executed 21

22. Flyway Commands migrate clean info validate baseline repair 22

23. migrate Migrates the schema to latest version Scans the file system for possible migrations Compares the migrations with schema_version Already applied New migrations Not applied, will not be applied 23

24. Clean Wipes the configured schemas completely Will delete all tables from schemas Even schema_version And other tables that are not created by flyway migrations Think twice before using in prod Can be disabled in flyway.conf 24

25. Info Prints the details and status information about all the migrations Basically prints schema_version table 25

26. Validate Validate applied migrations against resolved ones Validation fails if differences in migration names, types or checksums are found versions have been applied that aren't resolved locally anymore versions have been resolved that haven't been applied yet 26

27. Baseline Baselines a schema Baselining can be done to a particular version Only the migration with bigger version than the baseline can be applied Baselining can not be done after a migration is applied then 27

28. Repair Repairs the flyway metadata table Remove any failed migrations on databases without DDL transactions (User objects left behind must still be cleaned up manually) Correct wrong checksums 28

29. Api Hooks There are three ways to hook into flyway apis Java-based Migrations Java-based Callbacks Custom Migration resolvers & executors 29

30. Migrations There are two interfaces that can be implemented JdbcMigration SpringJdbcMigration Migrations should reside in db.migration package Must follow the naming conventions of migrations Do not have checksum by default Implement getChecksum method of MigrationChecksumProvider if desired 30

31. JDBC Migration 31

32. Spring JDBC Migration 32

33. Callbacks There are two ways to implement callbacks Implement FlywayCallback S ubclass BaseFlywayCallback To access Flyway Configuration Implement ConfigurationAware and flyway will automatically inject FlywayConfiguration instance 33

34. Custom Resolvers Implement MigrationResolver by default, custom resolvers will run in addition to built-in ones To escape built-in resolvers Set skipDefaultResolvers to true 34

35. Custom Executors Implement MigrationExecutor execute(Connection connection ) 35

36. Questions? 36

37. Food for thought Are Rollbacks really required?