Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing April 19, 2019 by Haroon Ashraf This article talks about core concepts of test-driven database development followed by creating simple SQL unit tests with tSQLt based on this approach. The conventional SQL unit testing has been around since long while test-driven database development has not also been introduced yesterday, however, switching to this testing methodology offers pure unit testing experience with a lot more features and flexibility as compared to its counterpart. Let us get introduced with test-driven database development first and please be prepared to switch to it if you are not already using it and you are happy to do so. About test-driven database development It is better to define test-driven database development first, so that it becomes easier to explore it further. Simple Definition The method in which unit tests drive the database development process is called test-driven database development or TDDD. Alternative Definition The method of creating database objects based on creating and running their unit tests first is called test-driven database development or TDDD. TDDD: Development or testing? I know, for the first time learners, the above definitions do not give much information about test-driven database development and on the top of that it is not still not clear whether we are talking about database development or database unit testing? The short answer is, both! Yes, it is basically SQL unit testing followed by database development unlike database development followed by SQL unit testing. I may have added more confusion now. So it’s time to clear the confusion now. Test first approach TDDD is basically a test first approach in which database development rely on unit testing to begin and take the control. The best way to understand this is to compare conventional unit testing with test-driven database development. Conventional vs Test-driven SQL development Please see a simple comparison between conventional SQL unit testing and TDDD Conventional SQL unit testing Test-driven database development Create database object Write database unit test to check object exists or not Run database unit test Create database unit test to check if potential object exists or not Run the unit test which will fail first Add database object Run the unit test which will pass now A more comprehensive comparison between both methodologies is as follows: Conventional SQL unit testing Test-driven database development Create database object Add desired functionality to the object Create unit test to check if object functions properly Run the database unit test to see it passing or failing Create unit test to check if potential object exists or not Run unit test which fails since the object does not exist Create object and rerun the unit test which should pass now Create another unit test to check if object functions properly Run the unit test which will fail now Modify the database object to function properly and rerun the unit test which should pass now The above points become more understandable as you move to the next sections of this article. To know more about conventional SQL unit testing please refer to the article, “Conventional SQL Unit Testing with tSQLt in Simple Words” Benefits of test-driven database development There are quite a number of benefits of using test-driven database development but we are only going to mention some major benefits of TDDD to highlight its importance. Business Logic Encapsulation One of the most outstanding benefits of test-driven database development is the encapsulation of business logic in the unit tests rather than database objects. For example, a business requirement for end user to be able to add new book to the library system should be met by writing a unit test(s) to ensure the database object responsible for adding the new book to the library system does its job properly. In simple words we look for the unit tests rather than database objects to see if the objects are doing their job properly or not and in this way business logic is concealed in unit tests rather than objects. Full Test Coverage Test-driven database development gives you full test coverage as compared to conventional database unit testing. Full test coverage means all our unit tests are covering all the database objects which are responsible for meeting business or technical requirements. Early Bug Detection Test-driven database development helps in early bug detection since SQL unit tests are in the front line of writing database object code. In other words, since all the unit tests are written first and are not considered correct until they pass so it helps in early bug detection. Requirements Focussed Development Another very handy benefit of test-driven database development is that, it only requires you to develop what is required by business. Since your database objects are totally based on unit tests and your unit tests are only written to meet the business requirements so in this way un-necessary database coding can be avoided and this saves a lot of time and effort. For example in order to meet a business requirement to add new book to the library system does not require you to provide search functionality as well since you are only concerned with what is required. Implementing Test-driven database development Let us now jump to implement test-driven database development with tSQLt unit testing taking into account a simple scenario. Why tSQLt with test-driven database development? tSQLt is one of the most advanced SQL unit testing frameworks and it supports test-driven database development by default. tSQLt has been written in such a way that it is by design, facilitates test first approach and this is what you are going to experience in this section. Note: To get better understand of tSQLt please read the following article tSQLt – A Forgotten Treasure in Database Unit Testing Pre-requisites This article assumes that readers have general understanding of database unit testing and T-SQL and can comfortably write simple database scripts. This article also assumes that readers are familiar with tSQLt unit testing framework. Setup sample database We are creating a sample database which consists of the following tables: Author Article Let us create a sample database SQLDevArticlesV3 by running the following script: 123456789101112131415161718192021222324252627282930313233343536373839 -- 1 Create SQLDevArticlesV3 databaseCREATE DATABASE SQLDevArticlesV3;GO USE SQLDevArticlesV3;GO -- 2 Create author tableCREATE TABLE [dbo].[Author] ( [AuthorId] INT IDENTITY (1, 1) NOT NULL, [Name] VARCHAR (40) NOT NULL, [RegistrationDate] DATETIME2 (7) NULL); -- 3 Create article tablesCREATE TABLE [dbo].[Article] ( [ArticleId] INT IDENTITY (1, 1) NOT NULL, [Title] VARCHAR (300) NOT NULL, [Published_Date] DATETIME2 (7) NOT NULL); -- 4 Populate author tableSET IDENTITY_INSERT [dbo].[Author] ONINSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (1, N'Asif', N'2018-01-01 00:00:00')INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (2, N'Peter', N'2018-02-01 00:00:00')INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (3, N'Sarah', N'2018-03-02 00:00:00')INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (4, N'Adil', N'2018-04-02 00:00:00')INSERT INTO [dbo].[Author] ([AuthorId], [Name], [RegistrationDate]) VALUES (5, N'Sam', N'2019-01-01 00:00:00')SET IDENTITY_INSERT [dbo].[Author] OFF -- 5 Populate article tableSET IDENTITY_INSERT [dbo].[Article] ONINSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (1, N'Fundamentals of Database Programming', N'2018-01-02 00:00:00')INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (2, N'Advanced Database Programming', N'2018-01-03 00:00:00')INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (3, N'Understanding SQL Stored Procedures ', N'2018-02-02 00:00:00')INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (4, N'Database Design Concepts', N'2018-03-02 00:00:00')INSERT INTO [dbo].[Article] ([ArticleId], [Title], [Published_Date]) VALUES (5, N'Power BI Desktop Fundamentals', N'2019-01-02 00:00:00')SET IDENTITY_INSERT [dbo].[Article] OFF;GO As a result of running the above script you see the sample database getting created. Install tSQLt unit testing framework Please download the tSQLt unit testing framework form the tSQLt.org. Please refer to Conventional SQL Unit Testing with tSQLt in Simple Words article for better understanding of how to install the tSQLt unit testing framework if you have not installed it before. Open tSQLt.class.sql file in SSMS (SQL Server Management Studio) and Run tSQL.class.sql script against the sample database SQLDevArticlesV3. So, sample database has been setup and the tSQLt unit testing framework has also been added to it which means we are good to go. Business requirement A business requirement stating that the end user should be able to add a new article to the database has just arrived. Create test class (ArticleTests) In order to start writing tSQLt unit tests you have to create a suitable test class which can be easily created a by creating a schema in the database under test. Create ArticleTests schema (test class) as follows: 12345678 USE SQLDevArticlesV3;GO -- Creating unit test calss ArticleTestsCREATE SCHEMA [ArticleTests]Authorization dboGOEXECUTE sp_addextendedproperty @name='tSQLt.TestClass',@value=1,@level0type='SCHEMA',@level0name='ArticleTests' Be requirements focus The first step in test-driven database development is to just focus on meeting the requirement only by relying on unit tests more than the objects themselves. This means we have to come up with a potential database object AddArticle. Create SQL unit test to check object exists Test-driven database development demands first to write a unit test to check if potential object exists or not. Write first unit test to see object exists or not as follows: 1234567891011 CREATE PROCEDURE ArticleTests.[test to check AddArticle exists]ASBEGIN --Assemble --Act --Assert EXEC tSQLt.AssertObjectExists @ObjectName = N'AddArticle'END;GO Run unit test to check object exists This is the tricky bit we know the object does not exist then why we are running the unit test? The answer is to comply with test-driven database development in which unit tests drive the process and they must pass to proceed further. Run all the unit test by running all the unit tests for the test class ArticleTests as follows: 12 -- Run all unit tests related to ArticleTest test classEXEC tsqlt.RunTestClass "ArticleTests" It is obvious from the test class output that the unit test to check object exists has failed: Create database object (ArticleTests) Now create the database object as a stub which means just create a database object with parameters but without any functionality because we are only interested to create it at the moment. Type the following code to create the object: 12345678910 -- Creating database object (stored procedure) AddArticle stub (placeholder)CREATE PROCEDURE AddArticle (@Name VARCHAR(40),@Published_Date DATETIME2) ASBEGIN SET NOCOUNT ON; ENDGO Rerun the unit test to check object exists After creating object stub please rerun the unit test class: 12 -- Rerun ArticleTest test classEXEC tsqlt.RunTestClass "ArticleTests" Your unit test has passed now, so the object which is meant to meet the requirement exists, however, please bear in mind it does not mean that the object necessarily meets the business requirement. Create unit test to check object functions properly In test-driven database development unit test is written first to check if object functions properly or not, which in turn triggers the database object to be developed to meet the specification only. The recommended way to check AddArticle object functions properly is to write SQL unit test which adds new article to the database by using the potential object and then results are compared with the expected results. Write the unit test to check object works properly as follows: 12345678910111213141516171819202122232425 --Create unit test to check AddArticle worksCREATE PROCEDURE [ArticleTests].[test to check AddArticle adds article to the table]AS-- AssembleEXEC tSQLt.FakeTable @TableName='dbo.Article',@Identity=1 -- Fake Article table Create TABLE [ArticleTests].[Expected] -- Create expected table( [ArticleId] INT NOT NULL, [Title] VARCHAR(40) NOT NULL, [Published_Date] DATETIME2 NOT NULL ) INSERT INTO ArticleTests.Expected -- Insert data into exepcted table(ArticleId,Title,Published_Date)VALUES(1,'Reporting Fundamentals','10 Nov 2018') -- ActEXEC dbo.AddArticle 'Reporting Fundamentals','10 Nov 2018' -- Run AddArticle procedure which adds new article to the Article tableSELECT * INTO ArticleTests.Actual FROM dbo.Article -- Put the records from Article table into Actual table -- Assert (compare expected table with actual table results)EXEC tSQLt.AssertEqualsTable @Expected='ArticleTests.Expected',@Actual='ArticleTests.Actual' Run unit tests to check object exists and works properly Now running all the unit tests should partially pass now because the test to check if object functions properly is going to fail. 12 -- Rerun ArticleTest test classEXEC tsqlt.RunTestClass "ArticleTests" Refactor object and rerun unit tests Modify AddArticle stored procedure with correct insert statement such that all the unit tests pass now. 12345678910111213 -- Alter database object (stored procedure) AddArticle to properly add new articleALTER PROCEDURE AddArticle(@Title VARCHAR(40),@Published_Date DATETIME2) ASBEGIN SET NOCOUNT ON; INSERT INTO dbo.Article (Title, Published_Date) VALUES (@Title,@Published_Date) ENDGO Run the unit tests: 12 -- Run unit tests related to ArticleTestsEXEC tsqlt.RunTestClass "ArticleTests" Congratulations, the fact that all the unit tests have passed now ensures that the object is capable of meeting the business requirement to add new article to the database now. Summary After going through this article and following the walkthrough you have familiarised yourself with test-driven database development methodology which is not only feature-rich, but to the point and also offers a lot of flexibility in handling business requirements ranging from simple to complex scenarios. About Latest Posts Haroon AshrafHaroon’s deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional.He holds BSc and MSc Degrees in Computer Science and also received the OPF merit award.He began his professional life as a computer programmer more than a decade ago, working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).He has also received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular interest and potential.View all posts by Haroon Ashraf Latest posts by Haroon Ashraf (see all) 10 Most Common SQL Unit Testing Mistakes - May 7, 2019 Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing - April 19, 2019 Conventional SQL Unit Testing with tSQLt in Simple Words - March 28, 2019 Related posts: tSQLt – A Forgotten Treasure in Database Unit Testing Conventional SQL Unit Testing with tSQLt in Simple Words SQL Unit testing with the tSQLt framework and SQL Server Database Project integration SQL unit testing with the tSQLt framework for beginners How to use fake tables in SQL unit testing?