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!

Advertisements

September 19, 2009

Validating Xml with a Schema in an NUnit Test (.NET 3.5/C#)

Filed under: .NET,c# — Brian @ 2:19 pm
Tags: , ,

Recently we decided to make some updates to our web app. The front end website is in ASP.NET/C#, while the back end is all in Java and using REST APIs as the interface. One pair of programmers was assigned the task of updating the web app and the other pair was assigned the task of writing the new REST service used by the web app. To ensure that both sides would be able to talk to each other when completed, we wrote an XML Schema to act as our contract and placed it in an SVN external available to both projects.

In our functional test for the REST client side code (.NET/C#) we used a stub from which we could grab the XML being sent. To test that this XML conformed to the schema (contract) we agreed to, we created the following helper class:

Xml Validator Helper Class

using System;
using System.Xml;
using System.Xml.Schema;
using System.IO;

namespace Test.Helper
{
	public class XmlValidatorTestHelper
	{
		private bool isValidXml = true;
		private string validationError = "";

		public String ValidationError
		{
			get { return "Validation Error: " + validationError; }
			set { validationError = value; }
		}

		public bool IsValidXml
		{
			get { return isValidXml; }
		}

		/// 
		/// Validate an xml document against an xml schema.
		/// 
		public void ValidXmlDoc(XmlDocument xmlDocument, XmlSchema xmlSchema)
		{
			validateParameters(xmlDocument, xmlSchema);
			XmlReader xmlReader = createXmlReader(xmlDocument, xmlSchema);
					
			try
			{
				// validate        
				using (xmlReader)
				{
					while (xmlReader.Read())
					{}
				}
				isValidXml = true;
			}
			catch (Exception ex)
			{
				ValidationError = ex.Message;
				isValidXml = false;
			}
		}

		private static void validateParameters(XmlDocument xmlDocument, XmlSchema xmlSchema)
		{
			if (xmlDocument == null)
			{
				new ArgumentNullException("ValidXmlDoc() - Argument NULL: XmlDocument");
			}
			if (xmlSchema == null)
			{
				new ArgumentNullException("ValidXmlDoc() - Argument NULL: XmlSchema");
			}
		}

		private static XmlReader createXmlReader(XmlDocument xmlDocument, XmlSchema xmlSchema)
		{
			StringReader xmlStringReader = convertXmlDocumentToStringReader(xmlDocument);
			XmlReaderSettings xmlReaderSettings = new XmlReaderSettings 
                        { 
                               ValidationType = ValidationType.Schema 
                        };
			xmlReaderSettings.Schemas.Add(xmlSchema);
			return XmlReader.Create(xmlStringReader, xmlReaderSettings);
		}

		private static StringReader convertXmlDocumentToStringReader(XmlDocument xmlDocument)
		{
			StringWriter sw = new StringWriter();
			xmlDocument.WriteTo(new XmlTextWriter(sw));
			return new StringReader(sw.ToString());
		}
	}
}

Surfacing Validation Errors in your Test

Finding out that the XML our REST client produces matches the contract is great, however when it fails it would save a lot of time if the test just showed WHY validation failed. No problem, in our validation helper class we added a property to allow us to retrieve the validation error message in the event the test failed and output that as the failure message for the test:

XmlValidatorTestHelper xmlSchemaValidator = new XmlValidatorTestHelper();
XmlSchema myXmlSchema = XmlSchema.Read(...);
XmlDocument myXmlDocument = new XmlDocument();
myXmlDocument .Load(...);
xmlSchemaValidator.ValidXmlDoc(myXmlDocument, myXmlSchema);

Assert.IsTrue(xmlSchemaValidator.IsValidXml, "XML does not match Schema: " + xmlSchemaValidator.ValidationError);

Blog at WordPress.com.