Introduction
Database migration is essential and integral process of every software development. From the very begging of application development the number of database migration scripts increases with every feature, which requires database change. This slows down the process of testing, deployment and application integration. All these issues can be effectively solved by automating the process of applying database migration scripts. Flyway is one of the most popular tools in this area. It has plenty of features but most importantly it provides database version control. In this article we are going to configure Flyway tool to automatically apply database migration scripts in a typical Java EE application running inside JBoss container.
Preparing Environment
The following environment will be prepared:
Preparing MySQL JDBC Driver
MySQL JDBC Driver or, more specifically, MySQL Java Connector can be directly downloaded from the public Nexus repository: MySQL Java Connector
In order to install MySQL Java Connector, perform the following steps:
1. Create the new module directory: $JBOSS_HOME/modules/com/mysql/main
2. Put the downloaded MySQL Java Connector jar file into created directory
3. Create module.xml file inside the created directory with the following content:
<?xml version="1.0" encoding="UTF-8"?>
<module name="com.mysql" xmlns="urn:jboss:module:1.0">
<resources>
<resource-root path="mysql-connector-java-5.1.40-bin.jar"/>
</resources>
<dependencies>
<module name="javax.api"/>
</dependencies>
</module>In case if updating the version of MySQL Java Connector it must be also updated in
module.xmlfile
Configuring Database Connection on JBoss
After installing the necessary MySQL Java Connector, the new data source and database connection can be easily configured in standalone.xml JBoss configuration file:
...
<drivers>
<driver name="mysql" module="com.mysql">
<xa-datasource-class>com.mysql.jdbc.Driver</xa-datasource-class>
</driver>
</drivers>
...
<datasource jta="true" jndi-name="java:/MySqlDS" pool-name="MySqlDS" enabled="true" use-ccm="true">
<connection-url>jdbc:mysql://127.0.0.1:3306/zoo</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<driver>mysql</driver>
<security>
<user-name>root</user-name>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
<background-validation>true</background-validation>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
</validation>
<statement>
<prepared-statement-cache-size>32</prepared-statement-cache-size>
<share-prepared-statements>true</share-prepared-statements>
</statement>
</datasource>
...An alternative way would be configuring JBoss via Application Management Console in Web UI: http://localhost:9990/console => Configuration => Subsystems => Create new Datasource
Integrating Flyway
Configuring Gradle Build Script
First of all, the necessary dependencies and plugins must be configured in the Gradle project build.gradle file:
apply plugin: 'java'
apply plugin: "org.flywaydb.flyway"
apply plugin: 'war'
buildscript {
repositories {
maven {
url "https://plugins.gradle.org/m2/"
}
}
dependencies {
classpath 'mysql:mysql-connector-java:5.1.40'
classpath 'gradle.plugin.com.boxfuse.client:flyway-release:4.2.0'
}
}
repositories {
mavenCentral()
}
flyway {
url = 'jdbc:mysql://127.0.0.1:3306/zoo'
user = 'root'
password = ''
}
sourceCompatibility = 1.8
dependencies {
compile 'javax.ejb:javax.ejb-api:3.2'
compile 'org.flywaydb:flyway-core:4.2.0'
compile 'org.apache.commons:commons-lang3:3.5'
compile 'org.slf4j:slf4j-api:1.7.22'
compile group: 'mysql', name: 'mysql-connector-java', version: '5.1.40'
}Implementing Application Initializer
Automatic migration requires a so-called initializer, which will call the proper service for database initialization during the Application startup:
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import persistence.DbService;
import javax.annotation.PostConstruct;
import javax.ejb.EJB;
import javax.ejb.Singleton;
import javax.ejb.Startup;
@Startup
@Singleton
public class ApplicationInitializer {
private static final Logger LOGGER = LoggerFactory.getLogger(ApplicationInitializer.class);
@EJB
private DbService dbService;
@PostConstruct
private void init() {
dbService.initialize();
LOGGER.info("DBase successfully initialised");
}
}Implementing Database Service
Database Service, in turn, would be responsible for performing database clean and migrate tasks. In this case, the variable tasks is declared explicitly, but depending on the environment, you can take it out from the configuration file:
package persistence;
public interface DbService {
void initialize();
void clean();
void migrate();
}package persistence;
import org.apache.commons.lang3.StringUtils;
import org.flywaydb.core.Flyway;
import org.flywaydb.core.api.FlywayException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.annotation.PostConstruct;
import javax.ejb.Singleton;
import javax.ejb.TransactionManagement;
import javax.ejb.TransactionManagementType;
import javax.naming.InitialContext;
import javax.sql.DataSource;
@Singleton
@TransactionManagement(TransactionManagementType.BEAN)
public class DbServiceEjb implements DbService {
private static final Logger LOGGER = LoggerFactory.getLogger(DbServiceEjb.class);
private static final String CLEAN_TASK_NAME = "clean";
private static final String MIGRATE_TASK_NAME = "migrate";
private String tasks = "clean,migrate";
private String datasourceJndiName = "java:/MySqlDS";
private Flyway flyway;
@PostConstruct
private void init() {
DataSource dataSource = InitialContext.doLookup(datasourceJndiName);
flyway = new Flyway();
flyway.setDataSource(dataSource);
LOGGER.info("About to use {} locations for database management", "sql/main");
flyway.setLocations("sql/main");
flyway.setBaselineOnMigrate(true);
}
@Override
public void initialize() {
if (StringUtils.isNotBlank(tasks)) {
if (tasks.contains(CLEAN_TASK_NAME)) {
clean();
}
if (tasks.contains(MIGRATE_TASK_NAME)) {
migrate();
}
}
}
@Override
public void clean() {
LOGGER.info("About to clean database");
flyway.clean();
}
@Override
public void migrate() {
LOGGER.info("About to migrate database");
try {
flyway.migrate();
} catch (FlywayException e) {
LOGGER.warn("Flyway has encountered an error while performing migrate. About to repair");
flyway.repair();
flyway.migrate();
}
}
}Creating SQL Migration Scripts
SQL migration scripts can be stored in project’s resources directory having some predefined structure: src/main/resources/sql/main/v1
Flyway recognizes sql scripts only having the following naming pattern: V<Number>__FILE_NAME.sql
-- V1.1__CREATE_TABLE_ANIMALS.sql
CREATE TABLE ZOO.ANIMALS
(
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(25),
NICKNAME VARCHAR(25),
AGE INT(2)
)
;
CREATE UNIQUE INDEX ANIMALS_ID_IDX ON ZOO.ANIMALS (ID)
;
INSERT INTO ZOO.ANIMALS (NAME, NICKNAME, AGE) VALUES ('Lion', 'Simba', 5)
;
COMMIT
;
-- V1.2__UPDATE_TABLE_ZOO.sql
ALTER TABLE ZOO.ANIMALS ADD CELL INT NULL
;
INSERT INTO ZOO.ANIMALS (NAME, NICKNAME, AGE, CELL) VALUES ('Penguin', 'Kowalski', 3, 35)
;
COMMIT
;Project Directory Structure
As a result the following directory structure is expected:
├───main
│ ├───java
│ │ ├───core
│ │ │ ApplicationInitializer.java
│ │ │
│ │ └───persistence
│ │ DbService.java
│ │ DbServiceEjb.java
│ │
│ ├───resources
│ │ └───sql
│ │ └───main
│ │ └───v1
│ │ V1.1__CREATE_TABLE_ANIMALS.sql
│ │ V1.2__UPDATE_TABLE_ZOO.sql
│ │
│ └───webapp
└───test
├───java
└───resourcesChecking
After the application is deployed, among other things, the database migration messages appear in log output:
INFO [persistence.DbServiceEjb] (ServerService Thread Pool -- 167) About to use sql/main locations for database management
INFO [persistence.DbServiceEjb] (ServerService Thread Pool -- 167) About to clean database
INFO [org.flywaydb.core.internal.util.VersionPrinter] (ServerService Thread Pool -- 167) Flyway 4.2.0 by Boxfuse
INFO [org.flywaydb.core.internal.dbsupport.DbSupportFactory] (ServerService Thread Pool -- 167) Database: jdbc:mysql://127.0.0.1:3306/zoo (MySQL 5.5)
INFO [org.flywaydb.core.internal.command.DbClean] (ServerService Thread Pool -- 167) Successfully cleaned schema `zoo` (execution time 00:00.109s)
INFO [persistence.DbServiceEjb] (ServerService Thread Pool -- 167) About to migrate database
INFO [org.flywaydb.core.internal.command.DbValidate] (ServerService Thread Pool -- 167) Successfully validated 2 migrations (execution time 00:00.013s)
INFO [org.flywaydb.core.internal.metadatatable.MetaDataTableImpl] (ServerService Thread Pool -- 167) Creating Metadata table: `zoo`.`schema_version`
INFO [org.flywaydb.core.internal.command.DbMigrate] (ServerService Thread Pool -- 167) Current version of schema `zoo`: << Empty Schema >>
INFO [org.flywaydb.core.internal.command.DbMigrate] (ServerService Thread Pool -- 167) Migrating schema `zoo` to version 1.1 - CREATE TABLE ANIMALS
INFO [org.flywaydb.core.internal.command.DbMigrate] (ServerService Thread Pool -- 167) Migrating schema `zoo` to version 1.2 - UPDATE TABLE ZOO
INFO [org.flywaydb.core.internal.command.DbMigrate] (ServerService Thread Pool -- 167) Successfully applied 2 migrations to schema `zoo` (execution time 00:00.226s).
INFO [core.ApplicationInitializer] (ServerService Thread Pool -- 167) DBase successfully initialised
Bogdan Shulga
Backend Engineer at Scalified
