Skip to content

Database migrations with Flyway

Using Flyway for Migrations

  • Flyway
  • Recommendations:
  • Use timestamp to tag migration. Makes it easier to manage especially if you’re going to be in a multi-developer environment
  • Use git to track migrations - flyway notices if a migration file changes and causes issues

Setting up flyway

  • Sqlite jdbc driver - xerial/sqlite-jdbc - for prototyping
  • Postgresql JDBC Driver - Postgresql
  • Flyway ant library - Flyway Ant
  • Directory Structure
  • db
    • conf/
    • flyway.conf
    • libs/
    • flyway/ # Flyway ant
    • postgresql-9.4.1212.jar
    • ant-contrib-1.0b3.jar
    • migrations
    • build.xml
  • Using the serial date for tracking migrations - date +%s

Using ant to run migrations

Deleting a migration.

Can’t delete a migration, only repair it.

  1. Comment out the commands in the migration mistake you are eliminating.
  2. Run ant repair to fix the checksums for the migrations
  3. Validate the current tatus ant validate
  4. Flyway should be back to the status it is suppose to be in.

Using with Gradle

  • If using SDK - Need to use Oracle java. java 9.0.4-oracle for using with Gradle.
  • Can use maven dependencies 
    buildscript {
    dependencies {
        classpath 'org.postgresql:postgresql:42.2.1'
    }
}

plugins {
    id "org.flywaydb.flyway" version "5.0.7"
}

  • Created gradle.properties file with flyway configuration. 

    flyway.url = jdbc:postgresql://localhost:5432/paseo_dev
flyway.user = name
flyway.password = password
flyway.locations = filesystem:'migrations'

  • Custom task to add date-timestamp to file before running migration. In build.gradle. 

    // https://stackoverflow.com/questions/43813724/gradle-script-to-append-timestamp-to-files
task renameFile {
    println(" :migrate : renameForMigration")
    def files = fileTree(dir: "${rootDir}/migrations")
    files.include '**/*.sql'
    files.exclude '**/V*.sql'
    // , include: '**/*.sql', exclude: '^V\\d+.*\\.sql')
    doLast {
        files.each{ file ->
            def newFileName = "V${getDate()}__${file.getName()}"
            ant.move(file: file, toFile:"${file.parent}/${newFileName}")
            println(newFileName)
            }
    }
}

task migrateDatabase(type: org.flywaydb.gradle.task.FlywayMigrateTask) {
    dependsOn 'renameFile'
}

Notes

  • Use timestamp to tag migration. Makes it easier to manage especially if you’re going to be in a multi-developer environment
  • Lessons Learned Using Flyway DB with Distributed Version Control
  • Get date on mac os x command line date +%s
  • Use git to track migrations - flyway notices if a migration file changes and causes issues
  • Moving away from Ant - seems support is not as strong anymore.

Tasks

  • Delete public.schema_version and ran gradle flywayBaseline to start migrations from scratch.
Last update: April 13, 2020 15:25:03