Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / SQL

SQL Unit Testing

Rate me:
Please Sign up or sign in to vote.
5.00/5 (5 votes)
15 Feb 2015CPOL5 min read 14.9K   10   1
SQL Unit testing

Most people in software agree that unit testing is a good thing. It is certainly one of the biggest developments in the way we code to occur in the last 20 years. Whether you unit test or not, you probably acknowledge the benefits of unit testing. On some projects, I follow a TDD approach, on others I write unit tests after writing my code, and on others still I don’t bother with unit tests at all. A good starting point for thinking about the nature, and therefore the requirements, of your project is to consider Joel Spotsky’s Five Worlds of Software Development.

Whilst unit testing has in general become more prevalent in the software industry, there are two very important types of programming which are generally less likely to be subject to unit testing if you were to pick a software project at random. These are JavaScript programming, and SQL programming. However, JavaScript and SQL can often (but not always) benefit from unit testing.

JavaScript is now supported by a growing number of unit testing frameworks (I use Jasmine), which seek to address the challenges posed by client-side unit testing. This trend is due largely to an increase in demand for single page applications and responsive user interfaces in general, as well as the growth in popularity of Node.js as a server-side language.

For SQL programmers, there are certainly fewer frameworks to choose from. One reason for this is that whilst client-side logic has become more complex, SQL logic has become less complex. ORM frameworks such as nHibernate and Entity Framework are removing the need for complicated stored procedures and functions in many applications.

There are of course exceptions. Sometimes, for one reason or another, there is no escaping the need for non-trivial SQL programming. In these cases, SQL unit tests can help. One SQL unit testing framework which I have been using lately is tSQLt, which is a simple open-source framework.

Unit testing SQL with tSQLt consists of the same four steps which typically comprise unit tests:

  1. Set up
  2. Execute
  3. Verify
  4. Tear down

One potential difficulty with applying this process to SQL is that any tables you create or manipulate during step 1 will naturally be persisted and therefore left over after your unit test completes. The obvious solution to this would be to clean up your data in the ‘tear down’ step, but this would quickly lead to overly complex unit tests. tSQLt addresses this problem by implicitly wrapping every unit test in a transaction, which is rolled back after it completes. This essentially removes the need to worry about the tear down step, as it is automatically taken care of.

Another common difficulty with SQL unit testing is that the ‘set up’ stage can quickly become a significant task. Foreign key constraints often mean that in order to insert some test data into one table, you have to insert into a number of other tables upon which it depends. One possible solution to this would be to use a fully populated database for unit testing purposes. However, this is undesirable as it leads to tests being run an inconsistent environment, and a consequent cost in predictability. A preferable approach is to always run unit tests on an empty database, and revert to that empty state after each test, with the possible exception of static tables containing lookup data. tSQLt helps us to achieve this goal with ‘fake’ tables. For each table into which we need to insert test data, we can run the FakeTable procedure, which effectively removes any constraints on that table for the duration of the test. In order to further isolate the unit we are testing, we can also fake any called functions, by defining simple mock versions of them which return whatever we want them to.

Verification is performed by using one of the many Assert functions defined in tSQLt. These include AssertEmptyTable, AssertEquals, and so on.

Let’s consider a simple example of a scenario where we might want to write a SQL unit test.

The database we are testing is part of an e-commerce application. Users add items to a basket, and then check out to complete their purchase. The unit we will test is a stored procedure named CompletePurchase. Our database includes User, Basket and CompletedPurchase tables, along with an IsValidBasket function.

SQL
CREATE TABLE [dbo].[User](
	[UserID] [int] NOT NULL,
	[Username] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Usera] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
))

CREATE TABLE [dbo].[Basket](
	[BasketID] [int] NOT NULL,
	[UserID] [int] NULL,
 CONSTRAINT [PK_Basket] PRIMARY KEY CLUSTERED 
(
	[BasketID] ASC
))
GO

ALTER TABLE [dbo].[Basket]  WITH CHECK ADD  CONSTRAINT [FK_Basket_User] FOREIGN KEY([UserID])
REFERENCES [dbo].[User] ([UserID])
GO

CREATE TABLE [dbo].[CompletedPurchase](
[CompletedPurchaseID] [int] IDENTITY(1,1) NOT NULL,
[BasketID] [int] NOT NULL,
[PurchaseTimestamp] [datetime] NOT NULL	
)
GO

CREATE FUNCTION [dbo].[IsValidBasket](@BasketID int)
RETURNS bit
AS
BEGIN
 DECLARE @IsValid bit
 
 -- do stuff here to check values in various tables and set @IsValid
 
 RETURN @IsValid
END
GO

Our CompletePurchase stored procedure takes a @BasketID parameter, calls the IsValidBasket function to check the basket is valid, and then inserts a row into the CompletedPurchase table to complete the purchase.

SQL
CREATE PROCEDURE [dbo].[CompletePurchase]
@BasketID int
AS
BEGIN
	IF [dbo].[IsValidBasket](@BasketID) = 1
		INSERT INTO [dbo].[CompletedPurchase] _
		(BasketID, PurchaseTimestamp) VALUES (@BasketID, GetDate())
END
GO

Now we can start to write our unit test.

Assume we have created an empty database for our unit test suite to run against. We need to insert one row of test data into the Basket table. This table has a dependency on the User table, so we will use the FakeTable command to instruct tSQLt to ignore its constraints.

SQL
EXEC tSQLt.FakeTable 'dbo, 'Basket'
INSERT INTO [dbo].[Basket] (BasketID, UserID) VALUES (100, 123)

We will assume that the IsValidBasket function does a lot of stuff involving several other tables, therefore we will create a simple mock version which just returns true.

SQL
CREATE FUNCTION [dbo].[IsValidBasket_FakeReturnTrue](@BasketID int)
RETURNS bit
AS
BEGIN
  RETURN 1
END
GO

We can now use the FakeFunction command to tell tSQLt to use this function instead of IsValidBasket.

SQL
EXEC tSQLt.FakeFunction 'dbo.IsValidBasket','dbo.IsValidBasket_FakeReturnTrue'

Having set up our data, we can now execute our stored procedure, and verify the results. Altogether, then our unit test looks like this:

SQL
CREATE PROCEDURE [dbo].[test CompletePurchase]
AS
BEGIN
   EXEC tSQLt.FakeTable 'dbo', 'Basket'
   
   INSERT INTO [dbo].[Basket] (BasketID, UserID) VALUES (100, 123)

   EXEC tSQLt.FakeFunction 'dbo.IsValidBasket','dbo.IsValidBasket_FakeReturnTrue'

   EXEC tSQLt.AssertEmptyTable 'CompletedPurchase'; -- check the table is initially empty

   EXEC [dbo].[CompletePurchase] 100 -- run the stored procedure

   DECLARE @RowCount int
   SELECT @RowCount = COUNT(*) FROM CompletedPurchase
   EXEC tSQLt.assertEquals 1, @RowCount -- check the table contains exactly 1 row

   SELECT @RowCount = COUNT(*) FROM CompletedPurchase WHERE BasketID = 100 _
   AND PurchaseTimestamp > _
   DATEADD(s,-5,PurchaseTimestamp) -- check that the row contains the correct data
   EXEC tSQLt.assertEquals 1, @RowCount
END
GO

Running it, we get SQL output to tell us our test passed.

SQL
EXEC tSQLt.Run '[dbo].[test CompletePurchase]'
+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name               |Result |
+--+-----------------------------+-------+
|1 |[dbo].[test CompletePurchase]|Success|
-----------------------------------------------------------------------------
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

There are of course a number of other interesting features included in tSQLt, and I have not included installation details above. For more information, visit the tSQLt website.

The post SQL Unit Testing appeared first on The Proactive Programmer.

This article was originally posted at http://theproactiveprogrammer.com/design/sql-unit-testing

License

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


Written By
Software Developer
United Kingdom United Kingdom
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralInteresting! Pin
Jon_Boy16-Feb-15 8:09
Jon_Boy16-Feb-15 8:09 

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.