Flyway – Database migration made easy

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.

Df06000516b540a9d7d58309f9ad1a18?s=128

Basil Bourque

January 31, 2018
Tweet

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 »