Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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.

Basil Bourque

January 31, 2018
Tweet

More Decks by Basil Bourque

Other Decks in Programming

Transcript

  1. Flyway Database migration made easy Like version control for your

    source code, but for your database Basil Bourque [email protected] LinkedIn: basilbourque 2017-11-16 1
  2. Agenda • Deploying database changes • My past experience (

    4D ) • Problem: Changes ( add, modify, drop ) • Solution: Tooling ( Flyway ) 3
  3. 4D deploy tables/columns forms code data 4D struc 4D data

    Development 5 4D struc 4D data 4D struc Production
  4. 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
  5. diff tool • “diff” tools • Compare & contrast two

    databases, determine differences • Perhaps generate SQL • Issues • Imperfect, incomplete • Reactive, not proactive 12
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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 .
  14. 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 .
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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
  25. support • Creator: Axel Fontaine of BoxFuse • Professional Support

    Subscription • Training • Sponsor-a-feature • StackOverflow.com – ‘flyway’ tag • Issue tracker on GitHub.com 39
  26. 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
  27. 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
  28. ` • 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 [email protected] LinkedIn: basilbourque « fin »