Database Versioning and Source Control

• Downoad solution

Situation

When a team of developers is working on a project, every developer makes changes to both source code and database. Every time a developer checks in a new feature, it changes both code and the database.

Problem

How to exchange database schema changes via source control so that it takes minimum amount of time to apply changes on every individual workstation? How to apply all accumulated database changes to production database when it is time to release?

Solution at a Glance

Create a project (command line app) and add it to the solution, add it to the source control. We will share database changes by checking in/out this project. The simplest solution is to put every change into a new .SQL file, give it a number and add it to the project. Keep the current version number in database; to update database to latest version, we will have to execute all .SQL files with number greater than current version. This solution can be simplified: we can add update statements only for tables (and indexes, foreign keys). Views, stored procs, and other objects can be saved in their canonical “CREATE…AS” form, the application will recreate them every time it is executed. This significantly reduces number of files in the solution.

Project Structure

The following is the screenshot of one of my experimental projects:

1. As you see, DbVersion project is just one of the projects in the solution. You check it in/out same as your other projects.
2. “Changes” directory is the place where we put our updates to tables, this includes ALTER TABLE, UPDATE, all modifications to indexes and foreign keys. Every file here starts with a number – it is the version number that will be compared against the values saved in database.
3. You can easily end up with hundreds of files like this, so we group them in subfolders. For next release/iteration, we would add subfolder like “12 – Christmas release” and put all new changes in that subfolder.
4. “Functions”, “Stored Procs”, “Types” and “Views” are subfolders for specific object types. If you want objects to be created in a particular order, you can add numbers to the files. You may need this for example if some views depend on other views. If order does not matter, you don't need numbers. In this example, views are ordered but stored procs are not, this is because stored procs don't depend on each other. Each file contains single “CREATE…AS” statement. If you want to change a view, you don’t add “ALTER VIEW” file, instead you go and edit existing “CREATE VIEW” file.
5. This is the logic that compares database version with .SQL files and executes the update.

How It Works

This is the central function of the project. As you see, it does the following:

• Loads .SQL files from resources
• Loads current version number from database
• Drops all stored procs, views, type and functions
• Executes all update scripts from current version number
• Recreates all functions, types, views and stored procs
• Updates version number in database

public void Update()
{
    using (var trans = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 30, 0)))
    {
        _connection = new SqlConnection(_connectionString);
        _connection.Open();

        LoadFiles();

        if (VersionTablesExist()) LoadDbVersion();

        DropStoredProcedures();
        DropTypes();
        DropViews();
        DropFunctions();

        ApplyChanges();

        if (!VersionTablesExist()) CreateVersionTable();

        CreateFunctions();
        CreateViews();
        CreateTypes();
        CreateStoredProcedures();

        UpdateVersion();
        trans.Complete();
    }
} 

Typical Scenario

Here is an example of how the database is updated along with the code and how this database change is shared within the team.

Joe is working on the following task: add currency field to the order list and show currency next to the price. Joe has to do the following:

• To add "currency" field to the table, he will add new file to "Changes" subfolder, he will call it something like "12 - Add Currency field". The file will have "ALTER TABLE ADD COLUMN" statement.
• Since the list is built based on select from view rather than from a table, Joe will have to go and edit "vOrderDetails" view.
• Joe will change the code in controller and in view to show this new column.
• Joe will write/modify unit tests to make sure it all works fine.
• Then he will checkin. Affected files are: new file with alter table; modified file for the view; modified files for view/controller.

This way, database changes are checked in along with the code changes. Next time Jane gets the latest version, she will have to run the DbVersion app and it will automatically upgrade database to new version.

Production can be updated in the same way, obviously just with different connection string.

A Couple of Things to Remember

• I had to simplify the solution and remove external dependencies. In production, you would see cleaner/more modular code.
• All .SQL files must be "embedded resources". The code loads .SQL from resources, not from hard drive. In production, we had it compiled as .DLL and deployed as a part of the bigger solution so I wanted to minimize the number of files.
• This approach works if every developer has their own local copy of database.
• This approach may fail if database objects have complex inter-dependencies. Objects are created in the following order: Functions, Types, Views, Stored Procs, order within each subtime can be configured. There may be scenarios when this is not flexible enough, it did not happen in our practice though.