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 Photo: R. Parmiggiani

Agenda • Deploying database changes • My past experience ( 4D ) • Problem: Changes ( add, modify, drop ) • Solution: Tooling ( Flyway ) 3

4D 4D struc 4D data tables/columns forms code data http://www.4D.com 4

4D deploy tables/columns forms code data 4D struc 4D data Development 5 4D struc 4D data 4D struc Production

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 PostreSQL H2 Database

SQL This page intentionally left blank. 8

SQL snippets 9

some code 10

emails 11

diff tool • “diff” tools • Compare & contrast two databases, determine differences • Perhaps generate SQL • Issues • Imperfect, incomplete • Reactive, not proactive 12

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

Flyway FlywayDB.org 14

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

how it works

how it works

how it works

optional Java classes 19

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

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

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

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

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

multiple locations • Classpath and/or external files • Set `locations` to include specific folders 25

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 .

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 .

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

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

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

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

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

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

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

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

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

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

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

support • Creator: Axel Fontaine of BoxFuse • Professional Support Subscription • Training • Sponsor-a-feature • StackOverflow.com – ‘flyway’ tag • Issue tracker on GitHub.com 39

databases 40 Postgres – your own, EnterpriseDB, Heroku, Amazon RDS JDBC drivers bundled, or `drivers` folder

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

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

` • 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 »