Flyway – Database migration made easy

Introduction to [Flyway](http://www.FlywayDB.org/), the open-source tool for migrating your database changes between your dev-test-QA-prod systems. Evolve your database schema easily and reliably across all your instances. Like version control for your code, but for your database.

If you manage the changes in the structure of your database with a rag-tag bunch of SQL scripts, a pile of emails, some shouts across the cube walls, luck, and desperate hope, then you may be interested in learning how to use Flyway or a similar tool to organize the process into a reliable, repeatable, testable, automated, and stress-free system.

Transcript

1. Flyway Database migration made easy Like version control for your source code, but for your database Basil Bourque basil.bourque@pobox.com LinkedIn: basilbourque 2017-11-16 1
2. 2 Photo: R. Parmiggiani
3. Agenda • Deploying database changes • My past experience ( 4D ) • Problem: Changes ( add, modify, drop ) • Solution: Tooling ( Flyway ) 3
4. 4D 4D struc 4D data tables/columns forms code data http://www.4D.com 4
5. 4D deploy tables/columns forms code data 4D struc 4D data Development 5 4D struc 4D data 4D struc Production
6. 4D details • New tables pop into existence • Ditto, columns • ‘On Server Startup’ tables/columns forms code data 4D struc 4D data 4D struc 4D data 4D struc Development Production 6 • Tokenized naming
7. 7 PostreSQL H2 Database
8. SQL This page intentionally left blank. 8
9. SQL snippets 9
10. some code 10
11. emails 11
12. diff tool • “diff” tools • Compare & contrast two databases, determine differences • Perhaps generate SQL • Issues • Imperfect, incomplete • Reactive, not proactive 12
13. get organized • Enlightenment • Discipline, conscious • No ad-hoc changes • Gather all scripts & code • Bundle scripts/classes • Keep with app (monolithic) • Centralize • Sequence the scripts/classes • String naming • Numbering, or date-time • Track current state of database • Create table to track this meta-data • Apply any later scripts/classes 13
14. Flyway FlywayDB.org 14
15. Flyway • Open-source ( Apache license ) • Free-of-cost • Simple design, not high-tech
 “low-friction” • Growing rapidly in popularity • Run from command-line, or Java 15 Downloads per year
16. how it works
17. how it works
18. how it works
19. optional Java classes 19
20. meta-data 20 installed_rank version description type script checksum installed_ by installed_on execution_time success 1 1 Initial Setup SQL V1__Initial_Setup.sql 1996767037 axel 2016-02-04 22:23:00.0 546 true 2 2 First Changes SQL V2__First_Changes.sql 1279644856 axel 2016-02-06 09:18:00.0 127 true schema_version 20
21. verbs • migrate • Find and apply migrations • Each migration wrapped in txn • clean • Wipes out all migrations • Refresh, to start again, for testing • Not in production !!! • `setCleanDisabled` 
 21 • validate • Compares migrations, found vs applied • info • Object containing data of applied migrations, as seen in grid earlier • baseline • For using Flyway with existing db • repair • More like ‘re-sync’ your table of applied migrations to the found migrations
22. automate • Auto-migrate • Startup
 ( monolithic single app ) • Shell scripts / Build scripts
 ( command-line, wraps Java code ) • Auto-detect scripts/classes • Apply the unapplied • Fail-fast • A migration fails • DB is inconsistent 22
23. useful for testing • Easily control your database as a test fixture • Start fresh, migrate to any point in development history • Drop-in scripts • Create mock data • Load sanitized data • Flyway::clean • Wipe applied migrations ( refresh ) • Not in production !!! • Flag to disallow clean 23
24. convention over configuration • Command-line • Folder within Flyway folder. • Java • Classpath • Where classes found to execute • db.migration • Automatic recursive scanning for: • SQL scripts ( naming convention ) • Java classes ( name & interface ) 24
25. multiple locations • Classpath and/or external files • Set `locations` to include specific folders 25
26. naming 26 version _ _ description dots & underscores V 3 1.7.19 2017_01_23 = versioned V2017_01_23_09_ _Create_users_and_groups.sql extension } converted to dots major.minor.micro.nano.etc R = repeatable .
27. naming 27 version _ _ description dots & underscores V R 3 1.7.19 2017_01_23 = versioned, run once = repeatable, run every time V2017_01_23_09_ _Create_users_and_groups.sql V2017.01.31.14_ _Create_invoicing_tables.sql extension } converted to dots major.minor.micro.nano.etc © 2017 Basil Bourque pair of underscores no spaces all one word = 2017.1.23.9 = 2017.1.31.14 .
28. bad names • 2017_41_02_Create_invoice_table.sql • 2017-12-31T01:23:34__Add_index_on_customer.sql • 2007.11.23.98__My_first_SQL • 2018.11.23.15__Delete_everything.sql • 192.68__.sql • 101.39.1__Redefine index on customer.sql 28
29. bad names – prefix ‘V’ • V2017_41_02_Create_invoice_table.sql • V2017-12-31T01:23:34__Add_index_on_customer.sql • V2007.11.23.98__My_first_SQL • V2018.11.23.15__Delete_everything.sql • V192.68__.sql • V101.39.1__Redefine index on customer.sql 29
30. bad names – solved • V2017_41_02_Create_invoice_table.sql
 needs pair of underscores in middle ( the '41' is a red-herring, legal, not really a date, 2017.41.2 ) • V2017-12-31T01:23:34__Add_index_on_customer.sql
 standard ISO 8601 date-time is not valid ( hyphens & colons & 'T' ) • V2007.11.23.98__My_first_SQL
 missing extension ( .sql ) • V2018.11.23.15__Delete_everything.sql
 trick question: Technically correct, but use the Flyway verb clean instead • V192.68__.sql
 missing description – might work technically, but poor practice ( note: arbitrary numbers OK ) • V101.39.1__Redefine index on customer.sql
 spaces in description – No spaces allowed, all one word 30
31. flexible • Flyway is quite flexible • Only showing defaults here • Hooks for overriding conventions ( naming, locations ) • Spring supported ( ‘SpringJdbcMigration’ class, ‘SpringJdbcTemplate’ ) • Callbacks during execution • Before/After each migration, clean, info, validate, baseline, repair 31
32. Java Servlet web apps • Location for scripts/classes in web apps
 not documented • ‘classpath’ varies by Servlet container • ‘WEB-INF’ • Folder guaranteed to not be served • ‘lib’ = nested folder for .jar files • ‘classes’ = nested folder for .class files • /db/migration/ suggested in doc • Nested folders scanned recursively 32
33. no magic • Flyway adds a table, ‘SCHEMA_VERSION’ • Track each migration applied • Last migration = Current version of db • Use JDBC connection to database • Flyway provides harness for executing your SQL script within a txn • Repeat, for each SQL file & each Java class 33
34. runtime flow 34 Java app shell script cmd wrapper around Flyway classes JRE (bundled) classes Flyway classes locations config & Java classpath JDBC driver/conn database server SQL (& Java) migrations JRE txn
35. 4 levels of hooks • SQL scripts • JdbcMigration sub-class, Java (auto-conn, auto-txn) • Callbacks, Java ( before & after each verb ) • Custom migration resolvers & executors • Implement your own MigrationResolver coupled with a custom MigrationExecutor. • Loading things like CSV-based migrations or other custom formats • Make your own database connection & transaction 35 Control & power easy medium hard medium
36. When things go wrong • Each migration in a transaction • Rollback DML ( changes to data ) • DDL ( structure of tables & columns ) • Rolled back only if database is capable ( Postgres, yes ) 36
37. caveats • No ‘downgrading’ 
 ( Rails Migrations ) • Not practicable • Cannot create a database • clean verb • Does create a schema 
 ( as needed ) • Identifying dev-test-qa-prod 37 • Best for greenfield • Consider re-creating your db • Can ‘baseline’ an existing db • Documentation organization • Confusing layout • More doc than you may realize
38. Postgres notes • 9.0 and later • org.postgresql.Driver • DDL exported by pg_dump can be used unchanged in a Flyway migration 38 • Limitations • JDBC only.
 No support for psql meta- commands with no Jdbc equivalent like \set • Multiline RULE declarations are not parsed correctly
 Issue 183
39. support • Creator: Axel Fontaine of BoxFuse • Professional Support Subscription • Training • Sponsor-a-feature • StackOverflow.com – ‘flyway’ tag • Issue tracker on GitHub.com 39
40. databases 40 Postgres – your own, EnterpriseDB, Heroku, Amazon RDS JDBC drivers bundled, or `drivers` folder
41. competition • Liquibase • XML configuration • Open-source (Apache), free-of-cost • Redgate • MS Windows world • Commercial • Flyway • Open-source ( Apache ), free-of-cost • Plain SQL files 
 ( optional Java classes ) • Not high-tech • No dependencies • Simple, yet flexible 41
42. puzzler • Why is there a checksum in the Flyway ‘SCHEMA_VERSION’ ? 42 installed_rank version description type script checksum installed_ by installed_on execution_time success 1 1 Initial Setup SQL V1__Initial_Setup.sql 1996767037 axel 2016-02-04 22:23:00.0 546 true 2 2 First Changes SQL V2__First_Changes.sql 1279644856 axel 2016-02-06 09:18:00.0 127 true schema_version
43. ` • Vaadin web app ( pure Java server ) • Create Vaadin project 
 via Maven ‘archetype’ • Add JDBC driver • Add Flyway library • Build & run • Add startup hook ( ServletContextListener ) • Set up DataSource ( JDBC conn ) • Call Flyway • Create SQL file, & folders • Run, verify output on console • Examine database for changes 43 Basil Bourque basil.bourque@pobox.com LinkedIn: basilbourque « fin »