Introduction
In this article I demonstrate principles of test-driven database development (TD3) along with Try/Catch and SqlCmd functionality. The example database is called WeatherData. I actually built the WeatherData database to hold weather data collected by my weather station.
Build the Solution
Did you know you can use SSMS to build a database project? You can. I'm going to use SSMS 2005. To follow along, open SSMS and click File > New > Project:
When the New Project form displays, select the SQL Server Scripts template in the upper pane:
In the lower portion of the New Project form, name the project WeatherData:
When you click Ok, the database project is created. To view the Solution Explorer, click View > Solution Explorer:
Solution Explorer, for our empty WeatherData solution, looks like this:
Write the Test - Part 1: Create the Query File
Test-First Development is a popular variantof Test-Driven Development. You write and execute a test first, execute it, and it fails. This works very well for me, as most of my initial development efforts fail anyway! 😉
The first thing we'll need to do is create the database, so our first test will check for the database.
To create the test, right-click the Queries folder and click New Query:
When you create the first query, you'll be prompted to connect to a database:
Once you connect, the connection is added to the Connections folder:
Before we actually write the test, let's give SQLQuery1.sql a more descriptive name. I'd really like to be able to create a subfolder here and name it Tests. Alas, I cannot. So I am constrained to separate types of query files by their name. In this case, I choose to identify test queries by prefixing them with the text: "Test_".
Right-click SQLQuery1.sql and click Rename:
I chose to name my test "Test_CreateWeatherData.db.sql":
I like the file naming convention employed by Visual Studio Team System Database Edition. It's [ScriptName].[ObjectType].sql; so I use it here.
Write the Test - Part 2: Write Some Test T-Sql
It's important to stop here and talk some about test theory.
Let's talk first about test results. How will you know the test succeeded or failed? Do you want to return results that can be stored for posterity? If so, how? We'll start with test results that are only available at test time. We are finally ready to actually write some T-Sql!
The first test will be simple:
Use WeatherData
When this test is executed in SSMS, the result is an error message:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'WeatherData'. No entry found with that name. Make sure that the name is entered correctly.
The test failed, which is what we wanted, so Success! (Are you confused yet?)
Error Trapping
Another way to approach this test is to trap the error in a way that doesn't cause an error condition to occur. I can hear you asking: "Andy why is it important to not cause an error condition?" I'm glad you asked! Exceptions in the T-Sql will stop the T-Sql execution. The technical term for that is "bad." If the test code doesn't complete, our test results will be inconclusive.
Inconclusive is a valid test result. It's pretty obvious tests can either succeed or fail. Based on this, most assume there are two possible outcomes to a test: success or failure. There are actually four states to two-state logic:
- On (Success in testing)
- Off (Failure)
- Don't Know (Inconclusive)
- Don't Care (Ignored)
In SQL Server 2005 and beyond, T-Sql provides just the trick for this: Try/Catch. Also included are a couple functions to check for the expected error: Error_Number() and Error_Message(). Let's test this by executing the following T-Sql:
Begin Try
Select Convert(int,'One')
End Try
Begin Catch
Print Error_Number()
Print Error_Message()
End Catch
The results are:
-----------
(0 row(s) affected)
245
Conversion failed when converting the varchar value 'One' to data type int.
The cool part is the query completed successfully - without error:
Let's apply this to our database test query:
Begin Try
Use WeatherData
End Try
Begin Catch
Print Error_Number()
Print Error_Message()
End Catch
The results are the same as before:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'WeatherData'. No entry found with that name. Make sure that the name is entered correctly.
What happened? Some errors are not trapped by Try/Catch (see Books Online TRY...CATCH topic, Errors Unaffected by a TRY...CATCH Construct section). One category or error unaffected by Try/Catch is "Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution."
Let's TRY Again
It's obvious we need another approach, Try/Catch just won't help here. We can check master.sys.databases for the WeatherData database using the following query:
Use master
Select name
From sys.databases
Where name= 'WeatherData'
This is a better test. We want to test for the existence of the database on this instance of SQL Server, and this does just that. Let's wrap it in a conditional statement that reports the results of the test:
Use master
If Exists(Select name
From sys.databases
Where name = 'WeatherData')
Print 'Success: WeatherData exists.'
Else
Print 'Failure: WeatherData does not exist.'
Executing this script reports the result:
Failure: WeatherData does not exist.
Again, the test returns a Failure result, which is exactly what we want. I like this test. Let's keep it.
Next Step: Create the Database
Next, let's write a re-executable T-Sql statement to create the database. I like the test code so much, I'm going to re-use it to make the Create Database statement re-executable:
If Not Exists(Select name
From sys.databases
Where name= 'WeatherData')
Create Database WeatherData
go
Re-Test
Finally, we'll re-execute the test after creating the database. Why? Having the test fail is the first step in Test-Driven Database Development. After we meet the condition under test (or attempt to meet it), we need to re-execute the test to determine if we have accomplished the task under test.
When complete, my test-driven T-Sql looks like this:
Use master
If Exists(Select name
From sys.databases
Where name = 'WeatherData')
Print 'Success: WeatherData exists.'
Else
Print 'Failure: WeatherData does not exist.'
If Not Exists(Select name
From sys.databases
Where name= 'WeatherData')
Create Database WeatherData
go
Use master
If Exists(Select name
From sys.databases
Where name = 'WeatherData')
Print 'Success: WeatherData exists.'
Else
Print 'Failure: WeatherData does not exist.'
This returns the following results:
Failure: WeatherData does not exist.
Success: WeatherData exists.
Cool.
Kicking It Up Another Notch
This is good, but we can make it even better. First, save the test query alone as the file Test_CreateWeatherData.db.sql:
Use master
If Exists(Select name
From sys.databases
Where name = 'WeatherData')
Print 'Success: WeatherData exists.'
Else
Print 'Failure: WeatherData does not exist.'
Next, create a new query named CreateWeatherData.db.sql in Solution Explorer with the following T-Sql statements:
Use master
go
Print ''
Print 'Create WeatherData database'
If Not Exists(Selectname
From sys.databases
Where name = 'WeatherData')
begin
Print ' > Creating WeatherData database.'
Create Database WeatherData
end
Else
Print ' > WeatherData database exists.'
Save the CreateWeatherData.db.sql script.
Finally, create one more script in the database project named DeployV1.ver.sql containing the following T-Sql statements:
/*
Notes:
1. Turn on SQLCMD mode.
2. If you receive an error like "Incorrect syntax near ':'",
see Note 1.
3. Correct the file paths, replacing the default
with the full path to your files.
*/
Print ''
Print 'Deploying WeatherData v1.'
Print ''
Print 'Calling Test_CreateWeatherData.db.sql...'
:r "C:\Projects\WeatherData\WeatherData\Test_CreateWeatherData.db.sql"
Print 'Test_CreateWeatherData.db.sql called.'
Print ''
Print 'Calling CreateWeatherData.db.sql...'
:r "C:\Projects\WeatherData\WeatherData\CreateWeatherData.db.sql"
Print 'CreateWeatherData.db.sql called.'
Print ''
Print 'Calling Test_CreateWeatherData.db.sql...'
:r "C:\Projects\WeatherData\WeatherData\Test_CreateWeatherData.db.sql"
Print 'Test_CreateWeatherData.db.sql called.'
Print ''
Print 'WeatherData v1 deployed.'
Before executing this script, drop the WeatherData database (if you've created it). Also, follow the instructions in the comments. Turn on SQLCMD mode in SSMS. When you do this, the SqlCmd scripts will be highlighted in gray as shown:
Execute the DeployV1.ver.sql script. If all goes as planned, you should see the following results:
Deploying WeatherData v1.
Calling Test_CreateWeatherData.db.sql...
Failure: WeatherData does not exist.
Test_CreateWeatherData.db.sql called.
Calling CreateWeatherData.db.sql...
Create WeatherData database
> Creating WeatherData database.
CreateWeatherData.db.sql called.
Calling Test_CreateWeatherData.db.sql...
Success: WeatherData exists.
Test_CreateWeatherData.db.sql called.
WeatherData v1 deployed.
Q & A
I hear you thinking: "Andy, this was a lot more work than simply executing a Create Database statement." I agree. This is an example of Test-Driven Database Development. It's a good demonstration of the amount of additional work required to accomplish TD3.
"So why in the world would we do this?" I am so glad you asked that question!
While this is extra work for the first version of the database, the Unit Test developed here will live on as a Regression Test for the remainder of this database's lifecycle.
"Why not backup and restore the database?" You can do that. It works in many scenarios but not all: remote deployment, for instance. Scripting the database will work wherever restoring a backup will work, but the opposite doesn't always hold.
If you use source or version control, there is some value in comparing scripts. If you don't use source or version control, I predict you will one day.
Make no mistake, there are other ways to accomplish everything I've demonstrated here. There are some fantastic tools out there that help accomplish these tasks. I'll write about them soon.
:{> Andy
The Series
The articles in this series:
