Flyway integration with Java/Spring

Its really tough to migrate a DB from one type to another but there is a simple way which is 'flyway' which takes care of this migration if you do it in proper way using sql scripts . You can put all your DDL changes as well as DML changes into scripts . Once Application gets started , flyway will be triggered and it will take care of creating all the tables and insert or update the rows if any DML is provided.

So we can achieve this in very simple way using below approach-

Java-based migrations

Java-based migrations are a great fit for all changes that can not easily be expressed using SQL.

These would typically be things like

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

Naming

In order to be picked up by Flyway, Java-based Migrations must implement the JavaMigration interface. Most users however should inherit from the convenience class BaseJavaMigration instead as it encourages Flyway’s default naming convention, enabling Flyway to automatically extract the version and the description from the class name. To be able to do so, the class name must comply with the following naming pattern:

Versioned Migrations

Prefix  Separator
      
   V2__Add_new_table
              
 Version    Description

Undo Migrations

Prefix  Separator
      
   U2__Add_new_table
              
 Version    Description

Repeatable Migrations

Prefix Separator
     
    R__Add_new_table
               
           Description

The file name consists of the following parts:

• Prefix: V for versioned migrations, U for undo migrations, R for repeatable migrations
• Version: Underscores (automatically replaced by dots at runtime) separate as many parts as you like (Not for repeatable migrations)
• Separator: __ (two underscores)
• Description: Underscores (automatically replaced by spaces at runtime) separate the words

If you need more control over the class name, you can override the default convention by implementing the JavaMigration interface directly.

This will allow you to name your class as you wish. Version, description and migration category are provided by implementing the respective methods.

Discovery

Flyway discovers Java-based migrations on the Java classpath in the packages referenced by the locations property.

 my-project
   src
     main
       java
         db
   migration             classpath:db/migration 
             R__My_view
             U1_1__Fix_indexes
             V1__Initial_version
             V1_1__Fix_indexes
   pom.xml

New java migrations are discovered automatically through classpath scanning at runtime. The scanning is recursive. Java migrations in subpackages of the specified ones are also picked up.

Checksums and Validation

Unlike SQL migrations, Java migrations by default do not have a checksum and therefore do not participate in the change detection of Flyway’s validation. This can be remedied by implementing the getChecksum() method, which you can then use to provide your own checksum, which will then be stored and validated for changes.

Sample Class

package db.migration;

import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import java.sql.PreparedStatement;

/**
 * Example of a Java-based migration.
 */
public class V1_2__Another_user extends BaseJavaMigration {
    public void migrate(Context context) throws Exception {
        try (PreparedStatement statement = 
                 context
                     .getConnection()
                     .prepareStatement("INSERT INTO test_user (name) VALUES ('Obelix')")) {
            statement.execute();
        }
    }
}

Spring

If your application already uses Spring and you do not want to use JDBC directly you can easily use Spring JDBC’s JdbcTemplateinstead:

package db.migration;

import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import java.sql.PreparedStatement;

/**
 * Example of a Java-based migration using Spring JDBC.
 */
public class V1_2__Another_user extends BaseJavaMigration {
    public void migrate(Context context) {
        new JdbcTemplate(new SingleConnectionDataSource(context.getConnection(), true))
                .execute("INSERT INTO test_user (name) VALUES ('Obelix')");
    }
}

Transactions

By default, Flyway always wraps the execution of an entire migration within a single transaction.

Alternatively you can also configure Flyway to wrap the entire execution of all migrations of a single migration run within a single transaction by setting the group property to true.

If Flyway detects that a specific statement cannot be run within a transaction due to technical limitations of your database, it won’t run that migration within a transaction. Instead it will be marked as non-transactional.

By default transactional and non-transactional statements cannot be mixed within a migration run. You can however allow this by setting the mixed property to true. Note that this is only applicable for PostgreSQL, Aurora PostgreSQL, SQL Server and SQLite which all have statements that do not run at all within a transaction. This is not to be confused with implicit transaction, as they occur in MySQL or Oracle, where even though a DDL statement was run within within a transaction, the database will issue an implicit commit before and after its execution.

Important Note

If your database cleanly supports DDL statements within a transaction, failed migrations will always be rolled back (unless they were marked as non-transactional).

If on the other hand your database does NOT cleanly supports DDL statements within a transaction (by for example issuing an implicit commit before and after every DDL statement), Flyway won’t be able to perform a clean rollback in case of failure and will instead mark the migration as failed, indicating that some manual cleanup may be required.

Query Results

Flyway Pro

Migrations are primarily meant to be executed as part of release and deployment automation processes and there is rarely the need to visually inspect the result of SQL queries.

There are however some scenarios where such manual inspection makes sense, and therefore Flyway Pro and Enterprise Edition also display query results in the usual tabular form when a SELECT statement (or any other statement that returns results) is executed.

Schema History Table

To keep track of which migrations have already been applied when and by whom, Flyway adds a special schema history table to your schema. You can think of this table as a complete audit trail of all changes performed against the schema. It also tracks migration checksums and whether or not the migrations were successful.

Read more about this in our getting started guide on how Flyway works.

Migration States

Migrations are either resolved or applied. Resolved migrations have been detected by Flyway’s filesystem and classpath scanner. Initially they are pending. Once they are executed against the database, they become applied.

When the migration succeeds it is marked as success in Flyway’s schema history table.

When the migration fails and the database supports DDL transactions, the migration is rolled back and nothing is recorded in the schema history table.

When the migration fails and the database doesn’t supports DDL transactions, the migration is marked as failed in the schema history table, indicating manual database cleanup may be required.

Versioned migrations whose effects have been undone by an undo migration are marked as undone.

Repeatable migrations whose checksum has changed since they are last applied are marked as outdated until they are executed again.

When Flyway discovers an applied versioned migration with a version that is higher than the highest known version (this happens typically when a newer version of the software has migrated that schema), that migration is marked as future.

I hope you got the enough knowledge to migrate your RDBMS DB , please feel free to contact me in comment box for any help if you stuck somewhere configuring this.