Schema Migration with Hibernate and FlywayDB

Introduction

In a relational database, data is stored in table rows, and relations are expressed through a foreign key. While the database is responsible for storing and retrieving data, the business logic is usually embedded in the application layer. When using an object-oriented programming language, the object graph becomes the most natural representation of data. The discrepancy between graph-like structures and database tuples is typically known as the object-relational impedance mismatch.

The object-relational mapping (ORM) pattern aims to address this problem, and Hibernate ORM is undoubtedly one of the most successful implementations of this pattern. However, Hibernate is more than an ORM framework that conforms to the Java Persistence API specification.

Hibernate is a data access framework implementing many patterns that have been popularized by Martin Fowler in his book Patterns of Enterprise Application Architecture. For instance, aside from the typical object-relational mapping feature, Hibernate also implements the following data access patterns:

• Unit of work


• Identity map


• Lazy load


• Optimistic and pessimistic locking


• Application-level conversations that span over multiple web requests

Data Model Duality

While the ORM tool allows us to translate the object graph state changes into SQL statements, we are still managing two distinct data models. On one side, the database schema defines the rules for how data is ought to be stored, whereas on the application side, the domain model is an object-graph mirror of the underlying database schema.

So, who is in charge of driving the model representation? Is it the database or the domain model?

Schema ownership

Hibernate has long been offering a schema generation tool which takes the entity mappings and translates them to an actual database schema. This tool is very useful for testing. For instance, Hibernate unit tests rely on it to generate a new database schema prior to running the test suite, only to drop every newly created database structure after the tests have been run.

Aside from creating and dropping the schema, this tool offers an update option which is supposed to generate a DDL script that is capable of migrating an old database schema to a newer version given by the current entity structure.

However, using hbm2ddl’s automatic schema generation for a production environment is not recommended because it does not support every database-specific DDL structure (like partial indexes, user-defined types, triggers, checks, etc.). So as soon as a database schema takes advantage of an advanced DDL feature, schema migration must also use them and this can only be done through manually created incremental scripts.

The schema ownership goes to the database, and the application-level domain model is mapped to the underlying database structures.

Schema migration

Just like the code base evolves, so does the database schema. In fact, the scripts that generate the database schema are source code as well, and all the source code resides in a Version Control System, like Git.

The database schema evolution must be captured by incremental schema migration scripts. Every time the database schema must undergo a certain change, a new schema migration script is added.

The beauty of having these schema migration scripts is that we can automate the whole application deployment. For this purpose, we need a tool to automate the database schema migration scripts deployment process.

In Java, there are two automatic schema migration options: Liquibase and FlywayDB. In this article, I’m going to show you how easy you can integrate FlywayDB in a Spring and Hibernate enterprise application.

First, the schema migration scripts must reside in the src/main/resources folder:

resources
 flyway
 db
 postgresql
 migration
 V1_0__initial_script.sql
 V1_1__post_details.sql
 V1_2__post_comment.sql
 V1_3__post_tag.sql

Considering we have a Spring framework application that uses Hibernate for persistence, in order to use FlywayDB, we just have to configure an
org.flywaydb.core.Flyway Spring bean:

@Bean(initMethod = "migrate")
public Flyway flyway() 
 Flyway flyway = new Flyway();
 flyway.setDataSource(actualDataSource());
 flyway.setBaselineOnMigrate(true);
 flyway.setLocations(
 String.format(
 "classpath:/flyway/db/%1$s/migration",
 databaseType()
 )
 );
 return flyway;

Another thing to consider is that the LocalContainerEntityManagerFactoryBean, which is responsible for instantiating the Java persistence EntityManagerFactory, must be initialized after the Flyway component so those migrations are executed prior to bootstrapping Hibernate.

@Bean
// depends on flyway bean, so that database initialization
// runs before Hibernate gets bootstrapped
@DependsOn("flyway")
public LocalContainerEntityManagerFactoryBean entityManagerFactory() 
 // the usual initialization

If in the current development iteration, we realized that we need a new database schema migration script:

CREATE TABLE users (id BIGINT NOT NULL, name VARCHAR(255), PRIMARY KEY (id));

We just have to add it to the Flyway resources folder:

resources
 flyway
 db
 postgresql
 migration
 V1_0__initial_script.sql
 V1_1__post_details.sql
 V1_2__post_comment.sql
 V1_3__post_tag.sql
 V1_4__users.sql

And when the application is started, Flyway is going to apply it automatically:

INFO : DbValidate - Validated 5 migrations (execution time 00:00.025s)
DEBUG : DbSchemas - Schema "public" already exists. Skipping schema creation.
DEBUG : Table - Locking table "public"."schema_version"...
DEBUG : Table - Lock acquired for table "public"."schema_version"
INFO : DbMigrate - Current version of schema "public": 1.3
INFO : DbMigrate - Migrating schema "public" to version 1.4 - users
DEBUG : SqlScript - Found statement at line 1: create table users (id bigint not null, name varchar(255), primary key (id))
DEBUG : SqlScript - Executing SQL: create table users (id bigint not null, name varchar(255), primary key (id))
DEBUG : DbMigrate - Successfully completed and committed migration of schema "public" to version 1.4
DEBUG : MetaDataTableImpl - MetaData table "public"."schema_version" successfully updated to reflect changes
DEBUG : Table - Locking table "public"."schema_version"...
DEBUG : Table - Lock acquired for table "public"."schema_version"
INFO : DbMigrate - Successfully applied 1 migration to schema "public" (execution time 00:00.091s).

From the application logs, we can see how Flyway managed to discover and apply the new schema migration script, bumping up the database schema version. Behind the scenes, Flyway uses a separate database table (e.g.schema_version) where it records every script that it has previously applied:

version	description	script	installed_on	execution_time	success
1.0	initial script	V1_0__initial_script.sql	2016-08-05 07:50:22.418753	126	t
1.1	post details	V1_1__post_details.sql	2016-08-05 07:50:22.729402	164	t
1.2	post comment	V1_2__post_comment.sql	2016-08-05 07:50:22.92953	97	t
1.3	post tag	V1_3__post_tag.sql	2016-08-05 07:50:23.038776	8	t
1.4	users	V1_4__users.sql	2016-08-05 07:51:35.315481	43	t

Conclusion

While the schema generation tool offered by Hibernate is useful for testing, or even for creating a first initial script, on the long run, it is much more flexible to use an automatic schema migration tool such as FlywayDB. This way we can leverage all the features offered by the database-specific DDL syntax with just a minimum amount of configuration.

http://www.onlinenewspaper.co.in/2016/10/04/schema-migration-hibernate-flywaydb/
#Db, #FlywayDB, #Hibernate