Flyway’s SQL-script primarily based database migration is greater than highly effective sufficient for many use instances. However generally, you will need to take it one step additional to adapt your present knowledge to the brand new database schema. E.g., you would possibly have to extract knowledge from blobs or learn JSON paperwork so that you could fill the newly added database columns. In these instances, Flyway’s Java migration and callback strategies present a simple and highly effective option to implement the required migration logic.
Let’s implement a Java migration step first. As you will note, that is fairly easy. And after you implement it, you should use it in the identical method because the SQL migration steps I confirmed you within the earlier publish of this sequence.
Implement Advanced Migrations in Java
When looking for out there migration steps, Flyway not solely searches for migration scripts. It additionally picks up implementations of the Callback interface from the db/migration bundle. If you wish to use a distinct bundle, you’ll be able to configure it within the flyway.areas property.
A straightforward option to implement the Callback interface is to increase Flyway’s BaseJavaMigration class. It handles all of the technical complexity of a migration step and lets you concentrate on the precise migration. Once you do this, you could use a category title that follows Flyway’s naming schema V<VERSION>__DESCRIPTION.java. Flyway then picks up your migration step, checks if it must be executed, and does that if vital.
Right here’s an instance of a easy migration class that updates the database to model 2.0. The objective of this migration is to retailer the creator of the e-book in a separate desk. This requires the next operations:
- Create a brand new creator desk and sequence
- Learn all data from the e-book desk and get the id of the e-book and the title of the creator
- Persist every creator as a brand new report within the creator desk
- Set the id of the creator because the overseas key within the e-book desk
public class V2__extract_author extends BaseJavaMigration {
@Override
public void migrate(Context context) throws Exception {
Connection connection = context.getConnection();
// create creator desk
Assertion st = connection.createStatement();
st.execute(
"CREATE TABLE creator(id bigint NOT NULL, firstname character various(255), lastname character various(255), CONSTRAINT author_pkey PRIMARY KEY (id));");
st.execute("CREATE SEQUENCE author_seq");
// add fk_author to e-book desk
st.execute("ALTER TABLE e-book ADD COLUMN fk_author bigint REFERENCES creator (id);");
// migrate creator info
last PreparedStatement psAuthor = connection
.prepareStatement("INSERT INTO creator (id, firstname, lastname) VALUES (?, ?, ?)");
last PreparedStatement psBook = connection.prepareStatement("UPDATE e-book SET fk_author = ? WHERE id = ?;");
ResultSet rs = st.executeQuery("choose id, creator from e-book");
Assertion idSt = connection.createStatement();
whereas (rs.subsequent()) {
// get knowledge from e-book desk
Lengthy bookId = rs.getLong("id");
String creator = rs.getString("creator");
String[] title = creator.break up(",");
// get creator id from sequence
ResultSet authorIdRs = idSt.executeQuery("choose nextval('author_seq');");
authorIdRs.subsequent();
Lengthy authorId = authorIdRs.getLong(1);
// write new creator
psAuthor.setLong(1, authorId);
psAuthor.setString(2, title[1]);
psAuthor.setString(3, title[0]);
psAuthor.execute();
// replace e-book
psBook.setLong(1, authorId);
psBook.setLong(2, bookId);
psBook.execute();
}
rs.shut();
psAuthor.shut();
// add fk_author to e-book desk
st.execute("ALTER TABLE e-book DROP COLUMN creator;");
st.shut();
}
}
As you’ll be able to see, this requires nearly no Flyway-specific code. You simply have to implement the migrate technique of the JavaMigration interface. Inside this technique, you should use the offered Context object to get a java.sql.Connection to the database. Utilizing this Connection, you’ll be able to then outline and execute the required SQL statements.
This method provides you full flexibility to learn knowledge out of your database, rework it in any method you want, and retailer it in your database. That makes Java-based migration steps an excellent possibility for implementing advanced, multi-step migration operations.
Once you now run your migration, Flyway will detect the present database model, scan for all SQL and Java migration steps and execute the required ones. You may see the log output of those operations under.
15:42:53,864 INFO BaseDatabaseType:37 - Database: jdbc:postgresql://localhost:5432/test-flyway (PostgreSQL 10.14)
15:42:53,925 INFO DbValidate:37 - Efficiently validated 2 migrations (execution time 00:00.023s)
15:42:53,966 INFO JdbcTableSchemaHistory:37 - Creating Schema Historical past desk "public"."flyway_schema_history" ...
15:42:54,038 INFO DbMigrate:37 - Present model of schema "public": << Empty Schema >>
15:42:54,049 INFO DbMigrate:37 - Migrating schema "public" to model "1 - create database"
15:42:54,097 INFO DbMigrate:37 - Migrating schema "public" to model "2 - extract creator"
I triggered the migration on an empty database, and Flyway discovered the migration steps for variations 1 and a pair of. The 2nd one was the migration step I carried out as a Java class, which you noticed within the earlier code snippet.
After Flyway efficiently executes a migration step, it provides a report to the flyway_schema_history desk.
As you may have seen, a Java migration step is used the identical method as an SQL script and totally integrates into your migration course of. So, once you’re within the state of affairs that you would be able to’t describe the required migration in SQL, you simply have to implement the JavaMigration interface and observe Flyway’s naming conference.
Use Callbacks for Repetitive Duties
One other helpful characteristic for advanced migration situations is Flyway’s callback mechanism. It lets you execute an SQL script or a Java class when one of many lifecycle occasions outlined within the Occasion enum will get triggered inside Flyway. A couple of examples of those occasions AFTER_BASELINE, AFTER_CLEAN, AFTER_EACH_MIGRATE, AFTER_EACH_MIGRATE_ERROR, AFTER_UNDO, and AFTER_MIGRATE. You could find an inventory of all supported occasions in official javadoc.
We didn’t talk about Flyway’s Callback characteristic in any of the earlier articles. So, let’s additionally take a fast take a look at SQL callbacks earlier than I get into extra particulars about Java callbacks.
SQL Callbacks
The implementation of an SQL callback is simple. You solely want so as to add an SQL script with the title of the lifecycle set off you need to use in your migration listing. The migration listing is both the sql folder of the Flyway command-line consumer or your Java utility’s src/essential/assets/db/migration folder.
So, if you wish to execute a SQL script after Flyway migrated your database, you could put all SQL statements right into a file with the title afterMigrate.sql and duplicate it to the sql or src/essential/assets/db/migration folder.
Java Callbacks
In case your callback operation is just too advanced for an SQL script, you’ll be able to implement it in Java.
A callback implementation is similar to the beforehand mentioned migration step. It’s worthwhile to implement Flyway’s Callback interface and add your class to the db/callback bundle or the bundle configured by the flyway.callbacks property.
The best option to implement the Callback interface is to increase Flyway’s BaseCallback class. It gives all of the required technical boilerplate code so that you could think about implementing the callback operation.
For every Callback implementation, Flyway calls the deal with technique for every beforehand described occasion. When doing that, Flyway gives an Occasion enum worth and a Context object of the present migration. Just like the beforehand described implementation of a migration step, you should use the Context object to get a Connection to the database and carry out the operations of your callback.
I exploit that within the following instance to implement a callback that provides some instance knowledge if the database is empty. To try this, I first examine if the e-book desk comprises any knowledge. If it doesn’t, I insert a report into the creator and the e-book desk.
public class FillDatabaseAfterMigrate extends BaseCallback {
Logger log = Logger.getLogger(FillDatabaseAfterMigrate.class.getSimpleName());
@Override
public void deal with(Occasion occasion, Context context) {
if (occasion == Occasion.AFTER_MIGRATE) {
log.information("afterMigrate");
Assertion st;
strive {
st = context.getConnection().createStatement();
ResultSet rs = st.executeQuery("SELECT depend(id) FROM e-book");
rs.subsequent();
if (rs.getInt(1) == 0) {
st.execute(
"INSERT INTO creator (id, firstname, lastname) VALUES ((SELECT nextval('author_seq')), 'Thorben', 'Janssen');");
st.execute(
"INSERT INTO e-book (id, publishingdate, title, fk_author, value) VALUES ((SELECT nextval('book_seq')), '2017-04-04', 'Hibernate Suggestions - Greater than 70 options to widespread Hibernate issues', 1, 9.99);");
log.information("Database was empty. Added instance knowledge.");
} else {
log.information("Database comprises books. No instance knowledge wanted.");
return;
}
} catch (SQLException e) {
throw new MigrationException(e);
}
}
}
public class MigrationException extends RuntimeException {
public MigrationException(Throwable trigger) {
tremendous(trigger);
}
}
}
That’s all you could do to implement a callback. Once you now begin your utility and set off the database migration, Flyway will name the Callback implementation.
The next log output exhibits that Flyway referred to as our callback implementation after it accomplished the migration. The callback implementation then initialized the empty database with 2 instance data.
16:06:27,515 INFO BaseDatabaseType:37 - Database: jdbc:postgresql://localhost:5432/test-flyway (PostgreSQL 10.14)
16:06:27,605 INFO DbValidate:37 - Efficiently validated 2 migrations (execution time 00:00.030s)
16:06:27,659 INFO JdbcTableSchemaHistory:37 - Creating Schema Historical past desk "public"."flyway_schema_history" ...
16:06:27,745 INFO DbMigrate:37 - Present model of schema "public": << Empty Schema >>
16:06:27,760 INFO DbMigrate:37 - Migrating schema "public" to model "1 - create database"
16:06:27,822 INFO DbMigrate:37 - Migrating schema "public" to model "2 - extract creator"
16:06:27,893 INFO DbMigrate:37 - Efficiently utilized 2 migrations to schema "public", now at model v2 (execution time 00:00.162s)
16:06:27,909 INFO FillDatabaseAfterMigrate:19 - afterMigrate
16:06:27,919 INFO FillDatabaseAfterMigrate:30 - Database was empty. Added instance knowledge.
Abstract
I confirmed you within the earlier publish of this sequence that Flyway gives a simple however highly effective method to implementing a version-based migration course of. You solely want to supply a script with the required SQL statements to replace your database construction and migrate your knowledge.
In my expertise, you must have the ability to implement nearly all migrations inside these SQL scripts. However as you’ve seen on this publish, you’re not restricted to that method. When you want extra flexibility to carry out advanced migration operations, you’ll be able to implement them in Java.
And for all repetitive duties, like recompilation of saved procedures, database initialization with pattern knowledge, or dynamic creation of database triggers, you’ll be able to implement lifecycle callbacks in SQL scripts or Java courses.
Combining all this provides you a strong toolset to implement a version-based database migration method.
