Post1

Flyway Integration with Spring MVC

Posted by Vinit Kumar Tyagi

Flyway is an open-source database migration tool that supports simplicity and convention over configuration. It makes setting up and maintaining database schemas a breeze. Flyway is used across all environments including production, making it a perfect fit for projects that require continuous delivery and minimum downtime pipeline. With Flyway, we can realize the complete power of SQL with solid versioning.

Versioning of the database includes the creation, migration, and determination of its state and structure with content. In short, Flyway allows us to take control of the database; recreate it across environments or versions of the applications, while tracking the chronological amendments.

Database Integration/Migration with Flyway

When working with Flyway all alterations to the database are referred to as migrations.

Flyway can be used via command line tool, a maven plug-in or a supporting program from within an application – it all depends on the type of application. Generally, there are two types of Flyway migration: SQL-based and JAVA-based.

In this blog post, though, we are discussing Flyway integration with Spring MVC, which is JAVA-based and we would use a JPA backed database to proceed with the migration.

Let’s get started.

Integrating Flyway with Spring MVC

Step 1: Add Flyway dependencies in pom.xml
<dependency>

<groupId>org.flywaydb</groupId>

<artifactId>flyway-core</artifactId>

<version>4.0.3</version>

</dependency>

Step 2: Create flyway configuration required to load dataSource

package com.store.persistence.lifecycle;

import java.io.Serializable;

import javax.annotation.Resource;

import javax.sql.DataSource;

import org.flywaydb.core.Flyway;

import org.flywaydb.core.api.MigrationInfo;

import org.flywaydb.core.api.MigrationInfoService;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.context.SmartLifecycle;

import org.springframework.stereotype.Component;

import com.store.spring.config.ConfigurationSpring;

@SuppressWarnings(“serial”)

@Component(“flywayContext”)

public class FlywayContext implements Serializable, SmartLifecycle {

@Resource

private DataSource dataSource;

@Autowired

public FlywayContext(DataSource c3p0DataSource) {

System.out.println(“Inside FlywayContext deploying db-scripts…”);

ConfigurationSpring cs = new ConfigurationSpring();

this.dataSource = cs.getDataSource(); // Get DataSource Object for connect DB

}

private void flywayinit() {

System.out.println(“Deploying database changes if any…”);

Flyway flyway = new Flyway();

flyway.setDataSource(dataSource);

MigrationInfoService infoService = flyway.info();

if (null != infoService) {

MigrationInfo[] info = infoService.all();

if (null != info && info.length == 0) {

flyway.baseline();

}

}

// STORE1.1_Sprint1.sql

flyway.setSqlMigrationPrefix(“STORE”); //’Store’ is Prefix of SQL script i.e. STORE1.1__Sprint1.sql.

flyway.setSqlMigrationSeparator(“_”);

flyway.setBaselineOnMigrate(true);

// Start the migration

flyway.setLocations(“db/migration”); // Path where sql script present.

flyway.migrate();

System.out.println(“Deployed database successfully…”);

}

@Override

public boolean isRunning() {

System.out.println(“flywayContext isRunning returned false”);

return false;

}

@Override

public void start() {

System.out.println(“Starting flyaway context calling flyway-init”);

flywayinit();

}

@Override

public void stop() {

System.out.println(“stoping flyway context”);

}

@Override

public int getPhase() {

System.out.println(” phase is set to 10″);

return 10;

}

@Override

public boolean isAutoStartup() {

System.out.println(“isAutoStartup is true”);

return true;

}

@Override

public void stop(Runnable arg0) {

System.out.println(“stoping flyway context in runnable…”);

}

}

Step 3: Write SQL script for ‘db.migration’ folder.

CREATE TABLE  customer (

cust_id int(10) unsigned NOT NULL auto_increment,

name varchar(100) default NULL,

email varchar(100) default NULL,

address varchar(100) default NULL,

telephone varchar(100) default NULL,

PRIMARY KEY  (cust_id)

);

insert into customer(cust_id, name, email, address, telephone) values(101, ‘Vinit Tyagi’, ‘test@gmail.com’,’noida’, 9876543210);

The SQL script inserts value into STORE1.1__Sprint1.sql file.

A point to note here is that there’s a pattern to naming the migration scripts that need to be followed. Below is the example while creating a SQL file.

Where:

  • Prefix: is configurable, by default: V
  • Version: dots or underscores separate the parts and we can have as many parts as we want
  • Separator: is configurable, by default: __ (two underscores)
  • Description: file name with an underscore as a separator. We can use sprint no as well
  • Suffix: is configurable, by default: .sql

Closing Lines

Flyway is a flexible and powerful database migration tool. It allows users to personalize the filed names, transfer data within columns, and perform other tasks related to database migration. In addition, Flyway enables users to employ it as a standalone migration tool using its robust command line tool.

In all, Flyway gets the job done well.

That is all from us this time. Implement Flyway to migrate data and share your experiences with us.

Until next time!

Related Posts

  • Restful API Documentation using SWAGGER with Spring MVCRestful API Documentation using SWAGGER with Spring MVC

    What is Swagger? It is a specification for documenting REST API. It specifies the format (URL, method, and representation) to describe REST web services. Swagger is meant to enable the…

  • Content Data Store

    Content Data Store Content Data Store (CDS) is a system to provide storage facilities to massive data sets in the form of images, pdfs, documents and scanned documents. This dataset…

  • Working with SQL Server Database on Microsoft Azure (Part 2)Working with SQL Server Database on Microsoft Azure (Part 2)

    The huge volume of data generated around us, today, requires optimum storage and management to have any benefit for organizations. With a host of advantages that MS Azure possesses, especially…

  • Microsoft SQL Server Execution PlanMicrosoft SQL Server Execution Plan

    In a database environment, certain questions related to performance pop up repeatedly: why is my query running slowly? And why is SQL Server not using the index? etc.  As a…

  • Different Application of Joins in SQLDifferent Application of Joins in SQL

    Structurally organized with large quantities of information, databases are the backbone of every organization. That is why companies invest in database applications such as MySQL, PostgreSQL, Microsoft SQL Server and…

  • Amazon Athena: An OverviewAmazon Athena: An Overview

    It is difficult to fathom the progress of the computing world. Only a couple of decades ago, 1.44MB of memory space offered by floppy disks seemed a lot of storage…

Leave a Reply

Your email address will not be published. Required fields are marked *