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.

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.

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.

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.

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.

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:

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.

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.

CodeProject

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