My Adventures in Coding

August 14, 2015

Java – Automate database schema updates with Flyway

Filed under: Java — Brian @ 1:44 pm
Tags: , , , , , ,

I am currently working on a Java 8 project which is a REST API deployed as a WAR file to Jetty. Our deploy process is very simple, our deploy pipeline just copies the WAR file into the Jetty directory in each environment then verifies the app is up and running with the correct version and runs some integration tests.

We wanted to be able to apply database migration scripts automatically in each environment (Dev, Test, QA, Staging, Prod) as we did our deploy, so we would no longer have to worry about manually applying scripts. In the past for Java, Scala, and .NET projects I have used several different tools, but for this project we decided to use Flyway which is very flexible and simple to setup.

The documentation for Flyway is excellent, however I decided to just post what we did in our app in case it might help someone else out. Here is our “Quick Start” setup.

1. Add the Flyway dependency

Flyway can be setup using Maven, Gradle, SBT, Ant, etc. In our project we used Maven, so all we did was add the following to our pom.xml file:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>3.2.1</version>
</dependency>

2. Code

Now, to get Flyway to check for any database changes to apply, whenever the application is started, put the following code somewhere in your applications startup sequence.

Flyway flyway = new Flyway();
//Create the dbo.schema_version table if it does not already exist
flyway.setBaselineOnMigrate(true);
//Where the method "getDataSource()" provides your DataSource 
//object that has the jdbc url, username, and password.
flyway.setDataSource(getDataSource()); 
flyway.migrate();

3. SQL Script Location

All migrations scripts by default must go in the following folder in your app:

/resources/db/migration

4. SQL Script Naming convention

Scripts are run in version number order based on their names, the default naming convention is:

Version__description.sql
For example: “V1_0_1__create_tables.sql”

All scripts must start with the letter “V”, followed by major/minor version numbers, with two underscores “__” separating the version from the description.

5. Schema Version Table

Flywaydb will automatically create a table in each database called “dbo.schema_version” which stores a log of all migration scripts that have been applied.

The table looks like:

version_rank installed_rank version description type script checksum installed_by installed_on execution_time success
1 1 1 Flyway Baseline BASELINE Flyway Baseline NULL JohnSmith 2015-07-30 15:55:49.337 0 1
1 2 1.0.1 create tables SQL V1_0_1__create_tables.sql -440358290 JohnSmith 2015-07-30 15:55:49.337 109 1

6. Handling Failure

If a script fails, the app will fail to start and the failure information is written to our log files as well as a monitor is tripped. Flywaydb applies each script as a transaction so all changes in the script will be rolled back if any part of the script fails. This is very handy because if you commit a script with some invalid SQL syntax, all you have to do is update the script with the corrected syntax, commit it again, and let the build pipeline apply the changes from the fixed script. No messy cleanup or reset to worry about.

So that is it, you should have all you need to get your database schema changes easily synced up with the deploy of your app!

October 24, 2009

Migrating Data to JSON In SQL Server

Filed under: SQL — Brian @ 10:44 pm
Tags: , ,

Recently I was working on a task that required migrating some existing data (Phone Numbers) from being stored as separate columns in a separate table (e.g., dbo.FriendPhoneNumbers), to being stored as a single JSON document in one column of the table containing all related data (e.g., dbo.Friend).

My task was only to complete a one-time migration, so I did not want to spend a lot of time writing a custom application to complete this work. Just for fun I decided to see if I could first accomplish this migration in SQL. I was also curious if such a SQL statement would be efficient enough to be run in a live production environment.

Using the SQL UPDATE statement below I was able to create 100,000+ JSON documents in just a few seconds.

UPDATE f
SET PhoneNumber = phoneJSON.JSON 
from dbo.Friend f 
join 
(
	select FriendID, REPLACE( 
		'{'+
		CASE WHEN Home IS NOT NULL THEN '"Home":"'+Home+'",' ELSE '' END +
		CASE WHEN Work IS NOT NULL THEN '"Work":"'+Work+'",' ELSE '' END +
		CASE WHEN Fax IS NOT NULL THEN '"Fax":"'+Fax+'",' ELSE '' END +
		CASE WHEN Cell IS NOT NULL THEN '"Cell":"'+Cell+'",' ELSE '' END
		+'}', ',}', '}') as 'JSON'
	from dbo.FriendPhoneNumbers
) as phoneJSON ON phoneJSON.FriendID = f.FriendID
GO

While writing the script I ran into two issues:

  1. Nullable Columns: Wrapped each column in a SQL CASE statement so when a phone number was null, nothing was added to the JSON
  2. Trailing Comma: Added a SQL REPLACE to replace “,}” with “}” to handle the case where the last phone number in the list was null

That was all! It took only a few minutes to write this script.

Blog at WordPress.com.