Database Continuous Integration

Database Continuous Integration (further DCI) is a useful way to manage a database schema during an application life-cycle. There are several community libraries to implement the DCI but all of them (those I have seen) integrate into a build process. But I have an idea to integrate this functional into an application, a web application in my particular point of view.
The idea is simple; let me list its goals:

  • There should be API which can check a current application database schema version and compare it to a version which the application demands. And if an update is required it should be done automatically
  • The API should support both updates and rollback
  • The process should be run in a transaction
  • The result should be provided to a caller (the application)
  • The API should be database provider agnostic

To view the implementation of this idea let’s go from the consumer’s point of view. The application is ASP .NET MVC or Web Forms, no matter. We are adding such function call into its Application_Start method:

protected void Application_Start()
{

RegisterDatabase();

}

The RegisterDatabase looks like this:

private static void RegisterDatabase()
{

var dbIntegration = new DatabaseIntegration(ConnectionString, ProviderName);

var info = dbIntegration.Install();

}

The DatabaseIntegration class is our implementation of DCI. It is fairly simple: a client needs to construct its instance with ConnectionString and ProviderName, which can be grabbed from the web.config. And then the client calls the Install method where all magic is located.

Then you have to add the App_GlobalResources folder and.resx file. Four required strings have to be defined in this resource file. Their keys are:

  • InsertCurrentVersion – this is a provider specific sql to store into a database the current version when the update is done at first time
  • GetOriginalVersion – this is a provider specific sql to get this current version when the class wants to compare the versions
  • UpdateCurrentVersion – this is a provider specific sql to store into a database the current version when the update is done at next times
  • CurrentDatabaseVersion – this is a one number version which the application requires

The version numbers are a one number in ascending order, e.g. 1, 2, 3…N

You create two strings for each version number:

  • <number> – this is for an update to this version, e.g. 1
  • ~<number> – this is for a rollback from this version down, e.g. ~1

The rollback is mostly liked in dev environments when you want to change already defined update statements and test them.

For example, System.Data.SqlClient.resx will have such view:

Have a good luck with your DCI

Advertisements
This entry was posted in .NET, ASP .NET MVC, T-SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s