Using Working Folder to Source Control Database

This article is a walkthrough of how to use the working folder option of source control for managing SQL Server databases.

In this article, I am also underlining some of the benefits and limitations of using a working folder as compared to other available options to use with source control.

Let us discuss some key concepts before delving into the technical details of this article.

Key Concepts

What is Source Control

Source control is a system or (part of software best) practice which keeps track of all the changes in the code done by developers.

Why Source Control is needed

The application code written by the developers need to be saved from time to time in such a way that all the changes done by any developer can be not only traced back but also reverted if required.

For this reason, the application code is put under source control to make sure that the history of all the changes along with comments is well kept, plus there are many other benefits of using source control, which are beyond the scope of this article.

Source Control vs Version Control

There is no difference between Source Control and Version Control and mostly these two terms are used freely interchangeably.

Do We Put Databases under Source Control

Just like application code, the database objects such as tables, views, stored procedures etc. also need to be version controlled. However, the method of putting database objects under source control is slightly and in some cases entirely different from when application code is put under source control.

Version Controlling Database Example

Suppose you create a sample database called “Cars” as per business requirement.

Then you create a table called “Car” with the CarId and CarName columns to meet another requirement.

In your absence, another developer is assigned a task to add the CarType column to the “Car” table.

He decides to remove the CarName column thinking it is not required and replace it with the CarType column.

You come back after a long time and are surprised to see that your CarName column is not only missing but also is replaced with the CarType column.

Now, you don’t remember the original data type and length you chose for CarName unless you go through the whole set of business requirements.

Wait a minute! This problem can easily be resolved if you have considered using source control for your database in the first place. Then you can easily see the first ever change you’ve made, which contains the column definition and then the second change done by another developer.

So, you and the other developer sit together and run through the historical changes made to the database (object) using source control which is keeping track of every single change made by any developer on the database.

This is illustrated as follows:

Most Important Use of Source Control

One of the main reasons to use source control is to be able to maintain multiple versions of the code at the same time by creating the following branches of the code:

1. Dev (Development branch)
2. Test (Test branch)
3. QA (QA branch)
4. Prod (Production branch)

The technical details of creating Dev, Test, QA and Production branches from source control are beyond the scope of this article.

Prerequisites

This article is best suited for those readers who fulfill the following requirements:

Basic Knowledge of T-SQL

You must have some basic T-SQL knowledge to create, query and modify database objects such as tables, views, and stored procedure.

Database Development Tools

You must have SSMS (SQL Server Management Studio) or dbForge Studio for SQL Server installed on your machine to create and manage databases and their objects.

Working Folder Data Source Availability

Although any source control which offers the working folder option is fine, it is recommended to use dbForge Source Control to follow all the steps of the walkthrough in this article.

Working Folder Source Control

Working Folder with limited functionality to version control database objects can be used just like other source control systems such as TFS, Git etc.

A working folder simply contains SQL script files used to create and manage database objects.

When to use Working Folder

Suppose you want to create a database and its related objects from scratch but not yet sure which source control your team is going to use ultimately. Then it is better to begin with the working folder source control option.

Another reason might be when you simply want to store the current state of the database and are not interested to keep track of the historical changes then working folder is a good candidate to be used as a source control.

Working Folder Limitation

Working Folder to version control database objects is limited in terms of keeping the most recent version of the database and its objects and there is no way to trace back the changes or revert them.

So, you have to be careful when using Working Folder as your source control option because it cannot show you all the changes made to the database and its objects from time to time.

Walkthrough: Linking Database to Working Folder

Let us go through the steps to link your database to a working folder using source control.

Requirements to Add Book and BookType

You have received internal requirements to create a test database called “SQLBookShopV2” which contains the following two tables:

1. Book
2. BookType

The database does not necessarily require a source control at this point and it is not important to keep track of all the changes made.

Check the Requirements

It is often a good practice to double-check the requirements before using a working folder. A working folder is best suited if you are asked to create a database with the following requirements:

1. Test database or database prototype is required
2. Database change(s) history is not mandatory
3. The decision which source control is ultimately going to be used is not yet decided

Setup Working Folder

The first step is to set aside a folder where your test database scripts will reside after you start checking your database code into the working folder.

Create a new folder called “SQLBookShopV2 Scripts” under C Drive.

Setup SQLBookShopV2 Sample Database

Open dbForge Studio for SQL Server and from the Database menu click “New Database”:

Type “SQLBookShopV2” in the name of the database and click “Apply Changes” button at the bottom of the window:

Link Database to Working Folder

It is better to get the database linked with the source control right after creating it.

Right-click the database (SQLBookShopV2) and select Source Control à Link Database to Source Control menu item:

Locate the working folder created earlier to link it with the database:

You can choose your desired database development model. We are choosing the shared database development model:

Check the tiny source control icon next to the database which confirms that the database has been successfully linked with the working folder source control:

Create Book Table

Right-click Tables and then click New Table and create book table using the following code and apply changes:

CREATE TABLE SQLBookShopV2.dbo.Book (
  BookId INT IDENTITY
 ,BookTitle VARCHAR(50) NOT NULL
 ,Notes VARCHAR(200)
 ,CONSTRAINT PK_Book_BookId PRIMARY KEY CLUSTERED (BookId)
)
GO

Commit Database Code Changes

Since we have created a new table in the database, these local changes must be picked up by source control to be saved.

Open Source Control Manager (which shows the latest changes to be saved) by right-clicking the database and then click Source à Show Source Control Manager

Click Commit to check into the Working Folder Source Control:

Working Folder Check

Go to your working folder and see the table object saved there as a result of the latest commit:

Create BookType Table

Create another table BookType using the following code:

CREATE TABLE SQLBookShopV2.dbo.BooKType (
  BookTypeId INT IDENTITY
 ,Name VARCHAR(50) NULL
 ,Detail VARCHAR(200) NULL
 ,CONSTRAINT PK_BooKType_BookTypeId PRIMARY KEY CLUSTERED (BookTypeId)
)
GO

Add Table to Source Control

Add the newly created table to the source control using the same method as mentioned earlier and then check the working folder to see that both tables are there:

Congratulations! You have successfully linked your database to the working folder source control.

Working Folder Precautions

Please remember a working folder in its pure form as a source control is just like an ordinary Windows folder and if it gets modified externally, it can no longer remember its latest state.

For example, if we delete the Book.sql code from the working folder and then check for any changes using Source Control Manager, we would have to add Book table code again into the Working Folder.

The responsibility of securing the working folder lies on the shoulders of developers rather than source code (in its original form) unless you strictly follow the workaround which has proven to be successful.

Things to Do

You can now easily put your database objects using the Working Folder source control option:

1. Please try to create another database by linking the Book table with the BookType table in such a way that the BookTypeId primary key of the BookType table is passed as the BookTypeId foreign key column in the Book table after using working folder source control.
2. Please try creating a stored procedure called AddBook to add a new book to the Book table after linking your database with working folder source control.
3. Please try creating a database view Books to see the list of all the books with their types and check all the changes into the Working Folder source control.

Further reading:

Tracking Database Changes Using Working Folder Source Control

Useful tool:

dbForge Source Control – powerful SSMS add-in for managing SQL Server database changes in source control.