Documentation / Setup / DB Migration

Introduction

DB Migration introduction

Introduction to DB migration

Repeatable migrations

Use of @View, extra-ddl.xml and repeatable migrations

Overview

DB Migration is a feature for generation of DDL from the entity bean model that supports determining the diff from the prior version of the model and generating appropriate DDL changes based on the model difference.

You are expected to run the diff DDL using FlywayDb, LiquiBase or similar tool. In the future Ebean will likely have built in support to run the DDL as it already has most of the capability and could address some current issues with developer workflow and DDL script ordering.

DB migration has 2 output files:

• Migration model XML - This has the logical diff of the model as an apply or pendingDrops change set
• Apply SQL - This is the DDL script of the apply changes

When the DbMigration is run it loads any prior migration xml and combines that together to define the prior model which is then compared against the existing entity beans.

Pending Drops

Only non-destructive changes are included in apply and changes like drop table and drop column are put into a pendingDrops changeSet.

The drop pending changes need to be explicitly selected to go into a migration.

Given applications running in a clustered environment, pending drops typically need to trail the normal apply changes by at least 1 migration. This delay could be minutes, days and some drops might not ever be run.

Migration xml

When DbMigration is run the diff to the model is determined and output as the migration model xml document. This diff can have apply changes or pendingDrops changes.

From the migration apply changeSet the actual DDL that will be applied is generated.

Logical changes

Note that the changes in the migration xml are database agnostic and a single migration xml document can be used to generate migration DDL for multiple databases. For example, you can generate migration DDL scripts for Postgres, Oracle and SQL Server from the single migration xml. In the migration xml you will see logical types like JSON types (storing JSON documents in the DB) and for Postgres that could translate to a JSONB and for Oracle to a CLOB.

The changes are also "logical" in the sense that changes to entities with @History and @Draftable typically translates into multiple changes. For example adding a property to a @History entity could add a column to the base table, add a column to the history table and a change the triggers as required.

Example migration xml

Below is an example migration xml generated with apply changes. The customer table has 2 new columns added to it.

If the customer table has @History support this could also add columns to the history table and update the associated db triggers.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<migration xmlns="http://ebean-orm.github.io/xml/ns/dbmigration">
  <changeSet type="apply">
    <addColumn tableName="customer">
      <column name="registered" type="date"/>
      <column name="comments" type="varchar(1000)"/>
    </addColumn>
  </changeSet>
</migration>

Example migration with pendingDrops

Below is an example migration xml generated with pendingDrops changes.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<migration xmlns="http://ebean-orm.github.io/xml/ns/dbmigration">
  <changeSet type="pendingDrops">
    <dropColumn columnName="shortTitle" tableName="document" withHistory="true"/>
  </changeSet>
</migration>

Pending drop changes are not incorporated into the apply DDL script unless they are explicitly selected. Once pending drops are applied they drop off the 'pending drops' list.

Apply DDL

The apply ddl script contains the changes that we want to apply to the database. This is the DDL script that you get FlywayDB or similar to run.

Applying pending drops

INFO  c.a.ebean.dbmigration.DbMigration - Pending un-applied drops in versions [1.2]

The DB migration will log INFO level messages saying which migrations contain pending drops that have not yet been applied. At some point it is decided to apply one of the pending drops as the next migration.

// generate a migration as the drops from migration version "1.2"
System.setProperty("ddl.migration.pendingDropsFor", "1.2");

DbMigration dbMigration = new DbMigration();
dbMigration.setPlatform(DbPlatformName.POSTGRES);
dbMigration.generateMigration();

A migration is then generated with dropsFor set to the version of the migration that had the pending drops that we want to apply. Additionally the migration apply ddl contains the various drop statements that will be executed.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<migration xmlns="http://ebean-orm.github.io/xml/ns/dbmigration">
  <changeSet type="apply" dropsFor="1.2">
    <dropColumn columnName="shortTitle" tableName="document" withHistory="true"/>
  </changeSet>
</migration>

Version format

The "version" numbering format supported is the same as FlywayDB version numbering where you may use "." or "_" (period or underscore) as separating characters.

Examples:

So 1.1 and 1_1 both translate as version 1.1.

Note that version numbering controls the order in which migrations are executed and follows semantic versioning so for example 1.1.1 would execute before 1.2. By this means we can add a "patch" migration.

Workflow

If you use Ebean's migration runner to execute the migration note that to run a migration the requirement is that the previous migration needs to have been run. This is slightly different from FlywayDB which requires either strict version number order (or we can disable that to allow any ordering).

If you are using a workflow similar to "git flow" then Ebean's migration runner allows you to process merge requests in a non-strict order. The assumption made for this to work is that in the case where there are 2 or more merge requests (that are being reviewed and not yet merged into a common DEV branch) will not have conflicting DB migration changes (If they do Ebean will allow the migration to run but the migration itself will error).

Migration version and name

For a migration the developer needs to provide the version and name and these can be set via environment variables, system properties or ebean.properties or programmatically.

System.setProperty("ddl.migration.version", "1.1");
System.setProperty("ddl.migration.name", "support end dating");

Generating migration on startup

The DB migration can be generated when the Ebean instance is started by setting ddl.migration.generate system property or environment variable to true.

@Test
public void generate() {

  System.setProperty("ddl.migration.generate", "true");

  System.setProperty("ddl.migration.version", "1.1");
  System.setProperty("ddl.migration.name", "support end dating");

  // migration will be generated when Ebean starts
  // ... typically by running your application.
  DB.getDefault();
}

Generate offline

We can generate a migration programmatically without starting your application. In src/test/java add the following code with a main method.

Running this main method will generate the next migration and starts Ebean in offline mode. This offline mode means we don't need to start the application or need a database to generate the migration.

package main;

import io.ebean.Platform;
import io.ebean.dbmigration.DbMigration;

import java.io.IOException;

/**
 * Generate the DB Migration.
 */
public class MainDbMigration {

  /**
   * Generate the next "DB schema DIFF" migration.
   * <p>
   * These migration are typically run using FlywayDB, Liquibase
   * or Ebean's own built in migration runner.
   * </p>
   */
  public static void main(String[] args) throws IOException {

    // optionally specify the version and name
    //System.setProperty("ddl.migration.version", "1.1");
    System.setProperty("ddl.migration.name", "support end dating");

    // generate a migration using drops from a prior version
    //System.setProperty("ddl.migration.pendingDropsFor", "1.2");

    DbMigration dbMigration = new DbMigration();
    dbMigration.setPlatform(Platform.POSTGRES);
    // generate the migration ddl and xml
    // ... start in "offline" mode
    dbMigration.generateMigration();
  }
}

Running migration

You can run the migrations using FlywayDb, Liquibase or Ebean's own built in migration runner.

Ebean's migration runner

## run migrations when the Ebean starts
ebean.migration.run=true

## optionally specify different DB credentials
## to run the migration (own the tables)
datasource.db.adminusername=myDbTableOwner
datasource.db.adminpassword=secret

With ebean.migration.run=true then when the Ebean starts it will look at the migrations and run any that need to be run. The migration runner will by default create a table called db_migration that holds the meta data about the migrations that have been run and inserts into this table when migrations are successfully executed.

Repeatable migrations

Repeatable migrations are special migrations that start with R__ and do not have a version number. Repeatable migrations will run if they have not already been run or if there content has changed (MD5 checksum has changed).

Repeatable migrations can contain any DDL but for ORM we use these for defining database VIEWS and for example using @View for mapping entity beans to database views rather than tables.

Refer to the second video showing repeatable migrations with extra-ddl.xml and use with @View.

Repeatable migrations

Use of @View, extra-ddl.xml and repeatable migrations

FlywayDB note

For use with FlywayDB currently we need to prefix "version migrations" with V in order to support both "version migrations" and "repeatable migrations". To do this:

## must use V prefix on "version migrations" when using FlywayDB
## with both "version" and "repeatable" migrations
ebean.migration.applyPrefix=V