Motivation

• Multiple test and production stages
• Complicated in-house solution
• Goals:
  • Automatic deployment of database changes
  • Avoidance of human error factor
  • Controll of the update process

Three Rules for Database Work

• Never use a shared database server for development work
• Always Have a Single, Authoritative Source For Your Schema
• Always Version Your Database

Solution

• Treat database migrations as integral part of the project (db scripts checked in source code repository)
• Fail-fast: check/migrate database at app startup
• Continuous Database Integration

Continuous Database Integration

"Continuous Database Integration (CDBI) is the process of
rebuilding your database and test data any time a change is
applied to a project's version control repository“

(P. M. Duvall, S. Matyas, A. Glover, Continuous Integration)

Tools

• Liquibase / Datical
• Flyway
• MyBatis Migrations
• ...

And a few dead projects:

 dbdeploy (2009), MIGRATEdb (2014), migrate4j (2008), dbmaintain (2011), AutoPatch (2014)

https://en.wikipedia.org/wiki/Schema_migration

Flyway

• Solves only one problem and solves it well
• Lightweight and easy to setup
• Continuous delivery - migrate database changes on application startup

Supported databases

How Flyway works

https://flywaydb.org/getstarted/how

How Flyway works

https://flywaydb.org/getstarted/how

How Flyway works

• Command-line: flyway migrate -url=... -user=... -password=...
• Maven: mvn flyway:migrate -Dflyway.url=... -Dflyway.user=... 
• Gradle: gradle flywayMigrate -Dflyway.url=... -Dflyway.user=... 
• Ant: <flyway:migrate url="..." user="..." password="..."/>
• SBT: sbt flywayMigrate -Dflyway.url=... -Dflyway.user=... 
• Java API:

Flyway flyway = new Flyway();
flyway.setDataSource(url, user, password);
flyway.migrate();

Flyway with Spring & Hibernate

@Configuration
public class PersistenceConfiguration {

  @Bean
  @DependsOn("flyway")
  public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean() {
    // do hibernate init
  }

  @Bean
  public Flyway flyway() {
    
    Flyway flyway = new Flyway();
    flyway.repair();
    flyway.migrate();

    return flyway;
  }

}

Migrations

• SQL-based
• Java-based

SQL-based migration

• DDL changes (CREATE/ALTER/DROP statements for TABLES,VIEWS,TRIGGERS,SEQUENCES,...)
• Simple reference data changes (CRUD in reference data tables)
• Simple bulk data changes (CRUD in regular data tables)

Location and discovery

Naming

Java-based migration

• BLOB & CLOB changes
• Advanced bulk data changes (Recalculations, advanced format changes, ...)

package db.migration;

import org.flywaydb.core.api.migration.spring.SpringJdbcMigration;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * Example of a Spring Jdbc migration.
 */
public class V1_2__Another_user implements SpringJdbcMigration {
    public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
        jdbcTemplate.execute("INSERT INTO test_user (name) VALUES ('Obelix')");
    }
}

Callbacks / hooks

• Typical use cases:
  • Stored procedure compiling
  • Material view update

• Hooks:
  • beforeMigrate
  • afterMigrate
  • beforeClean
  • afterClean
  • beforeRepair
  • afterRepair
  • ...