When you are maintaining multiple web apps across environments it can be difficult to keep track of which scripts need to be run to upgrade the database when it comes time to deploy. If you’re maintaining different versions of web apps across environments when the versions can sometimes be significantly out of sync, the difficulty of determining which update scripts need to be run on deployment can explode.

While there are a ton of approaches to keeping your database under version control, if you want something simple and effective that you can implement with minimal time and effort, consider a DatabaseVersionHistory table in your database.

A database version history table will allow you to see at a glance the state the database is in and by comparing it with the update scripts in source control, you will quickly be able to determine which update scripts need to be run.

The folder structure

All of your change scripts must be under version control for this to work successfully and they must be named sequentially. Consider the folder structure:

App Root
	\ SQL-Scripts
		\ 1.0.0
			\ 01 - Create-Initial-Database.sql
			\ 02 - Create-Database-Version-History-Table.sql
			\ 03 - Add-Users-Table.sql

		\ 1.1.0
			\ 01 - Add-Timesheet-Tables.sql

etc

Under the SQL-Scripts folder, you have folders 1.0 and 1.1. These should correspond to your application version, and whenever it changes, you should create a new SQL-Scripts folder for any new scripts. If you don’t currently have a good system for versioning your application, consider Semantic Versioning.

The Database Version History table

Consider the following for your DatabaseVersionHistory table:

CREATE TABLE [dbo].DatabaseVersionHistory(
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [MajorReleaseNumber] integer NOT NULL,
   [MinorReleaseNumber] integer NOT NULL,
   [PatchReleaseNumber] integer NOT NULL,
   [ScriptName] [varchar](255) NOT NULL,
   [DateApplied] [datetime] NOT NULL,

    CONSTRAINT [PK_SchemaChangeLog]
        PRIMARY KEY CLUSTERED ([Id] ASC)
)

INSERT INTO DatabaseVersionHistory
       ([MajorReleaseNumber]
       ,[MinorReleaseNumber]
       ,[PatchReleaseNumber]
       ,[ScriptName]
       ,[DateApplied])
VALUES (1, 0, 0, '02 - Create-Database-Version-History-Table.sql', GETDATE())

The MajorReleaseNumber, MinorReleaseNumber, PatchReleaseNumber correspond to the conventions in Semantic Versioning i.e. 1.2.3 has a major release number of 1, a minor release number of 2, and a patch release number of 3.

Note the INSERT INTO after creating the table. This is how we track what script has been run. Every script should contain an INSERT INTO following the same convention.

For example, the 01 – Add-Timesheet-Tables.sql script would contain:

CREATE TABLE [dbo].Timesheet(
 ...etc...
)

INSERT INTO DatabaseVersionHistory
       ([MajorReleaseNumber]
       ,[MinorReleaseNumber]
       ,[PatchReleaseNumber]
       ,[ScriptName]
       ,[DateApplied])
VALUES (1, 1, 0, '01 - Add-Timesheet-Tables.sql', GETDATE())

Ok. It’s time to deploy a new version of our web app. Which database scripts do we need to run?

Execute a query such as:

SELECT *
FROM DatabaseVersionHistory
ORDER BY MajorReleaseNumber, MinorReleaseNumber, PatchReleaseNumber, ScriptName

This will give you a list of the scripts that have already been applied to the database. Based on the results of this query and comparing them to the scripts you have in source control, you will know exactly the state the database is in.

Where to next?

While this system is very quick and easy to get into place, there are many things that can be done to make the system more robust.

For example, all update scripts could start with a query to determine whether the script has already been executed or not. This could be done via querying the DatabaseVersionHistory for a match and stopping execution if a match was found. The system could then potentially be automated to execute all scripts as part of a deployment script.