Manage Database Migrations

For various reasons you might need to manage database migrations for example if you:

The database tables and columns needs to be in order before Ucommerce starts requesting the database. This article will describe how you can manage migrations for your extensions easily.

How does Ucommerce keep track of Migrations?

Migrations can be handled by using the component UCommerce.Installer.AppsDatabaseInstaller. It will keep track of the scripts that needs to be executed by saving a state for scripts that has already been run. It will store the state in the table "Ucommerce_AppSystemVersion".

Scripts should be named in the following format:

"SOMENAME.001.sql" where the number 001 determines the order and the last executed step.

An entrance in the table will be made with MigrationName: "SOMENAME" and Schema version "2". Next time you upgrade it will run all scripts with a higher number than what's supplied in the table for your migrations named "SOMENAME".

Scripts should be Idempotent

Idempotence means that an operation, in this case a database migration script, can be applied multiple times without changing the result. This prevents the application from errors if for some reason the same scripts will be applied multiple times. A typical error could be a table or column already exists.

Utilizing the Initialize Pipeline

A great way to hook in your migration runner is by using the initialize pipeline. It will run in the beginning of the application before the session factory for Ucommerce is created. This means that you have the ability to get your database schemas up to date so the application will function without errors in the database layer.

Creating a PipelineTask

In this example, we'll show how migrations can be integrated as a pipeline task. We'll start of by creating the task that needs to be hooked into the Initialize pipeline.

    
    
    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Web.Hosting;
    using UCommerce.Infrastructure.Configuration;
    using UCommerce.Infrastructure.Logging;
    using UCommerce.Infrastructure.Runtime;
    using UCommerce.Installer;
    using UCommerce.Pipelines;
    using UCommerce.Pipelines.Initialization;
    
    public class MigrateAppDatabase : UCommerce.Pipelines.IPipelineTask<UCommerce.Pipelines.Initialization.InitializeArgs>
    { 
    	public MigrateAppDatabase() {
    	
    	}
    
    	public PipelineExecutionResult Execute(InitializeArgs subject)
    	{
    
    	}
    }

Before writing any code, I need to inject a few things in the constructor that we'll need for Ucommerce to run our migrations:

UCommerce.Infrastructure.Runtime.IPathService
- The path service interface will be able to tell me the base path to the Ucommerce folder. Since Ucommerce integrates into multiple CMSes we can't rely on a fixed path to the base Ucommerce folder.
UCommerce.Infrastructure.Logging.ILoggingService
- A loggingService needs to be supplied for the component that runs the migrations.

Our constructor will end up looking like this:

    
    
    public class MigrateAppDatabase : UCommerce.Pipelines.IPipelineTask<UCommerce.Pipelines.Initialization.InitializeArgs>
    { 
    	private readonly IPathService _pathService;
    	private readonly ILoggingService _loggingService;
    	public MigrateAppDatabase(UCommerce.Infrastructure.Runtime.IPathService pathService, UCommerce.Infrastructure.Logging.ILoggingService loggingService) {
    		_pathService = pathService;
    		_loggingService = loggingService;
    	}
    }

To be able to run the scripts that are shipped with our app or extension we need to point our DatabaseInstaller to the correct path. We already have the path service that points us to the Ucommerce folder. Now we just need to supply the path underneath Ucommerce where our migrations are located. Say we are shipping an app that requires migrations to run. In that case, the migration scripts are extracted under the app folder. It is now safe to point our application to that path.:

    
    
    public class MigrateAppDatabase : UCommerce.Pipelines.IPipelineTask<UCommerce.Pipelines.Initialization.InitializeArgs>
    { 
    	public PipelineExecutionResult Execute(InitializeArgs subject)
    	{
    		//Find the virtual path to the Ucommerce root folder	
    		string webpathToUCommerceRoot = _pathService.GetPath();
    		
    		//Map to the physical path
    		string physicalPathToUCommerce = HostingEnvironment.MapPath(webpathToUCommerceRoot);
    
    		//Join the sub folder where our migration scripts are located
    		string pathToApp = Path.Combine(physicalPathToUCommerce, @"Apps\UCommerce.GiftCards\Database");
    	}
    }

Now that we have a physical path to the migrations, we can use the MigrationsLoader in Ucommerce to grab all the migrations in the right order in which they need to be loaded.

    
    
    public class MigrateAppDatabase : UCommerce.Pipelines.IPipelineTask<UCommerce.Pipelines.Initialization.InitializeArgs>
    { 
    	public PipelineExecutionResult Execute(InitializeArgs subject)
    	{
    		//Find the virtual path to the Ucommerce root folder	
    		string webpathToUCommerceRoot = _pathService.GetPath();
    		
    		//Map to the physical path
    		string physicalPathToUCommerce = HostingEnvironment.MapPath(webpathToUCommerceRoot);
    
    		//Join the sub folder where our migration scripts are located
    		string pathToApp = Path.Combine(physicalPathToUCommerce, @"Apps\UCommerce.GiftCards\Database");
    		
    		//Use MigrationLoader to get the migrations
    		IList<UCommerce.Installer.Migration> migrations = new UCommerce.Installer.MigrationLoader().GetDatabaseMigrations(new DirectoryInfo(pathToApp));
    	}
    }

With the scripts in the hand, we can now safely create a new AppsDatabaseInstaller and run the migrations:

    
    
    		//Find the virtual path to the Ucommerce root folder	
    		string webpathToUCommerceRoot = _pathService.GetPath();
    		
    		//Map to the physical path
    		string physicalPathToUCommerce = HostingEnvironment.MapPath(webpathToUCommerceRoot);
    
    		//Join the sub folder where our migration scripts are located
    		string pathToApp = Path.Combine(physicalPathToUCommerce, @"Apps\UCommerce.GiftCards\Database");
    		
    		//Use MigrationLoader to get the migrations
    		IList<UCommerce.Installer.Migration> migrations = new UCommerce.Installer.MigrationLoader().GetDatabaseMigrations(new DirectoryInfo(pathToApp));
    
    		//Create a new instance of the database installer
    		var appsDatabaseInstaller = new UCommerce.Installer.AppsDatabaseInstaller(
    				new UCommerce.Infrastructure.Configuration.CommerceConfigurationConnectionStringLocator(), 
    				migrations, 
    				new UCommerce.Infrastructure.Logging.InstallerLoggingServiceAdapter(_loggingService));
    
    		//Run the actual migration scripts
    		appsDatabaseInstaller.InstallDatabase();

Configuring Initialize pipeline