Click here to Skip to main content
14,542,361 members

Database Versioning and Source Control

Rate this:
4.00 (1 vote)
Please Sign up or sign in to vote.
4.00 (1 vote)
19 Jul 2013CPOL
Database versioning and source control

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:

Image 1
  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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

m_kramar
Australia Australia
No Biography provided

Comments and Discussions

 
SuggestionDatabase Versioning and Source Control Pin
Behtash Moradi21-Jul-13 16:02
MemberBehtash Moradi21-Jul-13 16:02 
GeneralRe: Database Versioning and Source Control Pin
m_kramar21-Jul-13 17:52
Memberm_kramar21-Jul-13 17:52 
GeneralRe: Database Versioning and Source Control Pin
Behtash Moradi21-Jul-13 19:47
MemberBehtash Moradi21-Jul-13 19:47 
QuestionSSDT Pin
Darek Danielewski20-Jul-13 1:59
MemberDarek Danielewski20-Jul-13 1:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Technical Blog
Posted 19 Jul 2013

Tagged as

Stats

9.2K views
5 bookmarked