Automate Database Migration using Flyway Framework

Slides from DevDay Conference held at Da Nang, Vietnam on 14th April 2018:

Imagine the hassles of running your database migrations manually on your Dev, Staging and Prod. Server. Flyway is an extremely convenient open source database migration tool. Migrations can be written in plain SQL, PL/SQL, T-SQL or Java. Flyway acts as a version control for your DB’s. It is easy to set up and has plugins for various built automation tools & application frameworks.

Transcript

1. Automate Database Migration using Flyway framework Karthickeyan Narasimhan | @hellokarthic Product Developer, Freshworks
2. DISCLAIMER The views expressed during and with respect to this presentation are those of the presenter and do not necessarily reflect the views of the company, Freshworks. The logos or service names (collectively, "Trademarks") used in the presentation are for the purposes of identifying the relevant service providers. Freshworks does not claim any right or ownership over such Trademarks. The images used in the presentation are for illustrative purposes only. For any questions pertaining to the presentation's use, distribution or publication, please write to the presenter. All rights reserved by Freshworks Inc.
3. About Me • Product Developer at Freshworks Technologies Private Limited. • Over 2+ years of Experience in Web Development • FOSS Enthusiast • Mozilla Rep(India) & Tech Speaker • Mozilla Campus Advisory Committee Board Member
4. • A Tale of Manual DB Migration • What are the ways to solve the DB Migration problem • How flyway came to our rescue • Live Demo on applying flyway to your project • Challenges we faced while using Flyway • Q&A, Resource sharing Overview
5. A Tale of Manual DB Migration
6. Ways to solve?
7. Come let’s fly with Flyway! Supports Plain SQL / Java based migrations 6 major commands- Info, Migrate, Repair, Clean, Baseline & Validate Multiple & Simple Setup - Supports Gradle, Maven, ANT, etc Flyway is an Open source tool for managing Database migration -KISS Approach Conventions over Configuration
8. • Usually contains DDL statements • Or reference data, data fixes. ◦ Did we change the country code(or currency) table on this machine? • Use JDBC code for complex migration ◦ Not checksummed by default unlike SQL migrations. What’s in a Migration?
9. build.gradle
10. Naming Conventions
11. Let’s see in Action Image Source: www.giphy.com
12. How you did that? • Working with maintenance branches • Working with feature branches • Working with lots of migrations
13. Use Case 3 Use Case 1 Use Case 2 Use Case 3 Problem: Working with maintenance branches
14. • DB prod schema evolves linearly • Ensure maintenance migrations happen before migrations belonging to later release • use major.minor version scheme for maintenance ◦ V1.1 comes after V1 but before V2 ◦ In practice V001_01__increase_comment_size.sql Use Case 3 Solution: Working with maintenance branches Use Case 1 Use Case 2 Use Case 3
15. Use Case 3 Use Case 3 Use Case 1 Use Case 2 Use Case 3 Problem: Working with feature branches
16. Use Case 3 Use Case 1 Use Case 2 Use Case 3 Solution: Working with feature branches V2018_01_01_2330__Add_newTable.sql
17. Use Case 3 Use Case 1 Use Case 2 Use Case 3 Solution: Working with feature branches
18. Use Case 3 Use Case 1 Use Case 2 Use Case 3 Problem: Working with lot of migrations Image Source: www.images.google.com
19. • Squashing migrations • Squash all applied migration files into one or two files ◦ DDL+Reference data • Use baseline gradle flywaybaseline -Dflyway.baselineVersion=<version> Use Case 3 Use Case 1 Use Case 2 Use Case 3 Solution: Working with lot of migrations
20. What are you waiting for?
21. How to Contribute? https://stackoverflow.com/questions/tagged/flyway https://flywaydb.org/documentation/contribute/website/ https://flywaydb.org/documentation/contribute/code/
22. https://tinyurl.com/DevDayDaNang Resources
23. Image Source: From the movie “A Serious Man”
24. Karthickeyan Narasimhan Product Developer, Freshworks www.facebook.com/hellokarthic @hellokarthic https://www.linkedin.com/in/hellokarthic/ karthickeyan@freshworks.com