Tag Archives: schema

A self-contained, roll-forward schema updater

Programming, Software,

I use Dapper for most of my database interactions. I like it because it’s simple, and does exactly one thing: runs SQL queries, and returns the typed results.

I also like to deploy my schema changes as part of my application itself instead of doing it as a separate data deployment. On application startup, the scripts are loaded and executed in lexical order one by one, where each schema change is idempotent in isolation.

The problem you run into is making destructive changes to schema, which is a reasonable thing to want to do. If script 003 creates a column of UNIQUEIDENTIFIER, and you want to convert that column to NVARCHAR in script 008, you have to go back do some reconciliation between column types. Adding indexes into the mix makes it even hairier. Scripts that are idempotent in isolation are easy to write. Maintaining a series of scripts that can be safely applied in order from beginning to end every time an application starts up is not.

Unless you keep track of which schema alterations have already been applied, and only apply the changes that the application hasn’t seen before. Here’s a short, self-contained implementation:

public class SchemaUpdater
{
  private readonly string _connectionString;
  private readonly ILog _logger;
  private readonly string _environment;
 
  public SchemaUpdater(string connectionString, string environment)
    : this(connectionString, environment, LogManager.GetLogger(typeof(SchemaUpdater))) { }
 
  internal SchemaUpdater(string connectionString, string environment, ILog logger)
  {
    _connectionString = connectionString;
    _environment = environment;
    _logger = logger;
  }
 
  public void UpdateSchema()
  {
    MaybeCreateAuditTable();
    var previousUpdates = GetPreviousSchemaUpdates();
 
    var assemblyPath = Uri.UnescapeDataString(new UriBuilder(typeof(SchemaUpdater).GetTypeInfo().Assembly.CodeBase).Path);
    var schemaDirectory = Path.Combine(Path.GetDirectoryName(assemblyPath), "schema-updates");
 
    var schemaUpdates = Directory.EnumerateFiles(schemaDirectory, "*.sql", SearchOption.TopDirectoryOnly)
      .Select(fn => new { FullPath = fn, Filename = Path.GetFileName(fn) })
      .Where(file => !previousUpdates.Contains(file.Filename))
      .OrderBy(file => file.Filename)
      .Select(file => new { file.Filename, Query = File.ReadAllText(file.FullPath) })
      .ToList();
 
    foreach (var update in schemaUpdates)
    {
      using (var connection = new SqlConnection(_connectionString))
      {
        try
        {
          var splitOnGo = SplitOnGo(update.Query);
          foreach (var statement in splitOnGo)
          {
            try
            {
              connection.Execute(statement);
            }
            catch (Exception exception)
            {
              Console.WriteLine(exception);
              throw;
            }
          }
 
          connection.Execute("INSERT INTO SchemaRevision (Filename, FileContents) VALUES (@filename, @fileContent)",
            new { filename = update.Filename, fileContent = update.Query });
        }
        catch (Exception e)
        {
          _logger.Fatal(new { Message = "Unable to apply schema change", update.Filename, update.Query, Environment = _environment }, e);
          throw;
        }
      }
    }
  }
 
  public static ICollection<string> SplitOnGo(string sqlScript)
  {
    // Split by "GO" statements
    var statements = Regex.Split(
      sqlScript,
      @"^[\t\r\n]*GO[\t\r\n]*\d*[\t\r\n]*(?:--.*)?$",
      RegexOptions.Multiline |
      RegexOptions.IgnorePatternWhitespace |
      RegexOptions.IgnoreCase);
 
    // Remove empties, trim, and return
    var materialized = statements
      .Where(x => !string.IsNullOrWhiteSpace(x))
      .Select(x => x.Trim(' ', '\r', '\n'))
      .ToList();
 
    return materialized;
  }
 
  internal void MaybeCreateAuditTable()
  {
    const string createAuditTable =
@"IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SchemaRevision')
BEGIN
CREATE TABLE [dbo].[SchemaRevision]
(
[SchemaRevisionNbr] BIGINT IDENTITY(1,1),
[Filename] VARCHAR(256),
[FileContents] VARCHAR(MAX),
CONSTRAINT PK_SchemaRevision PRIMARY KEY (SchemaRevisionNbr),
)
END";
 
    using (var connection = new SqlConnection(_connectionString))
    {
      connection.Execute(createAuditTable);
    }
  }
 
  internal HashSet<string> GetPreviousSchemaUpdates()
  {
    using (var connection = new SqlConnection(_connectionString))
    {
      var results = connection.Query<string>(@"SELECT Filename FROM SchemaRevision");
      return new HashSet<string>(results, StringComparer.OrdinalIgnoreCase);
    }
  }
}

Update 2017-09-05: I added the SplitOnGo() method to support the GO delimiter, since I’ve had occasion to need it recently. It’s adapted from Matt Johnson’s answer on StackOverflow.