Tag Archives: schema

A self-contained, roll-forward schema updater

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:

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.