As a rule, we start to develop database solutions by creating database objects, such as tables, views, stored procedures, etc., based on business requirements. This approach is also known as Conventional Database Development. In this article, we are going to explore this approach and illustrate it with examples.
Conventional Database Development
The development style consists of the following steps:
- Receive the requirements
- Create database objects based on requirements
- Run unit tests for database objects to see if they meet requirements
- Receive new requirements
- Modify existing database objects or add new ones to meet the new requirements
- Create and run unit tests to check whether new requirements work accordingly and don’t conflict with the previous ones
To explore and illustrate the processes, let us start with setting up a sample database SQLDevBlog:
-- Create sample database (SQLDevBlog)
CREATE DATABASE SQLDevBlog
Use the following code to create tables in that sample database:
USE SQLDevBlog;
-- (1) Create Author table in the sample database
CREATE TABLE Author (
AuthorId INT PRIMARY KEY IDENTITY (1, 1)
,Name VARCHAR(40)
,RegistrationDate DATETIME2
,Notes VARCHAR(400)
)
-- (2) Create Article Category table in the sample database
CREATE TABLE Category (
CategoryId INT PRIMARY KEY IDENTITY (1, 1)
,Name VARCHAR(50)
,Notes VARCHAR(400)
)
-- (3) Create Article table in the sample database
CREATE TABLE Article (
ArticleId INT PRIMARY KEY IDENTITY (1, 1)
,CategoryId INT
,AuthorId INT
,Title VARCHAR(150)
,Published DATETIME2
,Notes VARCHAR(400)
)
-- Adding foreign keys for author and article category
ALTER TABLE Article ADD CONSTRAINT FK_Category_CategoryId FOREIGN KEY (CategoryId) REFERENCES Category (CategoryId)
ALTER TABLE Article ADD CONSTRAINT FK_Author_AuthorId FOREIGN KEY (AuthorId) REFERENCES Author (AuthorId)
GO
Review the database diagram with our newly created tables:
Note: I am using here dbForge Studio for SQL Server to do all tasks. The look of its output may differ from SSMS (SQL Server Management Studio), but the results are the same.
Next, we’ll populate our SQLDevBlog sample database to create a more realistic scenario:
-- (5) Populating Author table
INSERT INTO Author (Name, RegistrationDate, Notes)
VALUES ('Sam', '2017-01-01', 'Database Analyst'),
('Asif', '2017-01-02', 'Database and Business Intelligence Developer'),
('Sadaf', '2018-01-01', 'Database Analyst Programmer')
-- (6) Populating Category table
INSERT INTO Category (Name, Notes)
VALUES ('Development', 'Articles about database development'),
('Testing', 'Database testing related articles'),
('DLM', 'Database lifecycle management')
-- (7) Populating Article
INSERT INTO Article (CategoryId, AuthorId, Title, Published, Notes)
VALUES (1, 1, 'Fundamentals of SQL Database Development', '02-01-2018', ''),
(1, 2, 'Advanced Database Development', '02-01-2018', ''),
(2, 3, 'All About Database Testing', '03-01-2018', '');
GO
As a result, we have the following populated tables:
Now that we’ve done with database setup and table population, we face the next step. We have to imitate the scenario with a new requirement.
The Requirement to Add a New Category
A new requirement states that an administrator should be able to add a new category to the list of available categories. To meet this requirement, your development team has to come up with a stored procedure for adding a new requirement with ease. Or, we have to create an AddCategory Database Object.
To create the stored procedure, run the following script:
-- (8) This procedure meets a new requirement by adding a new category
CREATE PROCEDURE dbo.AddCategory @CategoryName VARCHAR(50),
@Notes VARCHAR(400)
AS
INSERT INTO Category (Name, Notes)
VALUES (@CategoryName, @Notes);
GO
The result is as follows:
Create Database Unit Test to Check if the Procedure Works Properly
The next step is to create the database unit test to check if the stored procedure meets the specification.
This tip works for dbForge Studio for SQL Server (or only dbForge Unit Test) and SSMS (SQL Server Management Studio). Note: When using SSMS (SQL Server Management Studio), make sure to install the tSQLt Framework to write the unit tests.
To create the first database unit test, right-click the SQLDevBlog database > Unit Test > Add New Test
The Add New Test window opens. Fill in all the required information and click Add Test.
Create the unit test as follows and save it:
-- Comments here are associated with the test.
-- For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/
CREATE PROCEDURE AddCategoryTests.[test to check if AddCategory procedure works]
AS
BEGIN
--Assemble
EXEC tSQLt.FakeTable @TableName = 'dbo.Category' -- create an empty dependency free Category table
CREATE TABLE AddCategoryTests.Expected ( -- create expected table
CategoryId INT
,Name VARCHAR(50) NULL
,Notes VARCHAR(400) NULL
)
INSERT INTO AddCategoryTests.Expected (CategoryId,Name, Notes) -- Insert data into expected table
VALUES (null,'Database Dummy Category', 'This is just a dummy category for testing');
--Act
EXEC AddCategory @CategoryName = 'Database Dummy Category'
,@Notes = 'This is just a dummay category for testing'
--Assert
EXEC tSQLt.AssertEqualsTable @Expected = 'AddCategoryTests.Expected'
,@Actual = 'dbo.Category'
END;
GO
Click the Database menu > Unit Test > View Test List and run the unit test as shown below:
We can see that the unit test is successful. Thus, a new category can be added to the database (table). The requirement has been satisfied.
Now, we are going to explore test-driven database development and describe how the process of writing unit tests can satisfy requirements.
Test-Driven Database Development (TDDD)
Test-driven database development (TDDD) begins with writing the unit test that will fail first. Then, we’ll modify it to pass, and then refine it.
Unit tests are written to meet requirements and unit tests that require database objects to be created and run properly.
To understand the difference between traditional database development and test-driven database development, let us create the SQLDevBlogTDD database. It is the same as SQLDevBlog.
-- Create sample database (SQLDevBlogTDD)
CREATE DATABASE SQLDevBlogTDD
Then, populate the sample database with tables:
USE SQLDevBlogTDD;
-- (1) Create Author table in the sample database
CREATE TABLE Author (
AuthorId INT PRIMARY KEY IDENTITY (1, 1)
,Name VARCHAR(40)
,RegistrationDate DATETIME2
,Notes VARCHAR(400)
)
-- (2) Create Article Category table in the sample database
CREATE TABLE Category (
CategoryId INT PRIMARY KEY IDENTITY (1, 1)
,Name VARCHAR(50)
,Notes VARCHAR(400)
)
-- (3) Create Article table in the sample database
CREATE TABLE Article (
ArticleId INT PRIMARY KEY IDENTITY (1, 1)
,CategoryId INT
,AuthorId INT
,Title VARCHAR(150)
,Published DATETIME2
,Notes VARCHAR(400)
)
-- Adding foreign keys for author and article category
ALTER TABLE Article ADD CONSTRAINT FK_Category_CategoryId FOREIGN KEY (CategoryId) REFERENCES Category (CategoryId)
ALTER TABLE Article ADD CONSTRAINT FK_Author_AuthorId FOREIGN KEY (AuthorId) REFERENCES Author (AuthorId)
GO
We have to populate our sample database to create a more realistic scenario as follows:
-- Use SQLDevBlogTDD
-- (5) Populating Author table
INSERT INTO Author (Name, RegistrationDate, Notes)
VALUES ('Sam', '2017-01-01', 'Database Analyst'),
('Asif', '2017-01-02', 'Database and Business Intelligence Developer'),
('Sadaf', '2018-01-01', 'Database Analyst Programmer')
-- (6) Populating Category table
INSERT INTO Category (Name, Notes)
VALUES ('Development', 'Articles about database development'),
('Testing', 'Database testing related articles'),
('DLM', 'Database lifecycle management')
-- (7) Populating Article
INSERT INTO Article (CategoryId, AuthorId, Title, Published, Notes)
VALUES (1, 1, 'Fundamentals of SQL Database Development', '02-01-2018', ''),
(1, 2, 'Advanced Database Development', '02-01-2018', ''),
(2, 3, 'All About Database Testing', '03-01-2018', '');
GO
Requirement to Add New Category (TDDD)
Now, we have the same requirement: the administrator should be able to add a new category to the list of available categories. To meet the requirement, we need first to write a database unit test that looks for a potential object.
That is how TDDD works: the unit test first fails since we assume that we are looking for the object which currently does not exist, but it is going to be there soon.
Create and Run the Database Unit Test to Check the Desired Object Exists
Although we know that it does not exist now, we think of this as a starting point.
In dbForge Studio for SQL Server, the database unit test that supports TDDD by default is created to fail first. Then, we are going to change it a little bit. If you are using a tSQLt database unit test framework directly, write the following unit test:
-- Comments here are associated with the test.
-- For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/
CREATE PROCEDURE CategoryTests.[test to check if routine to add new category exists]
AS
BEGIN
--Assemble
-- This section is for code that sets up the environment. It often
-- contains calls to methods such as tSQLt.FakeTable and tSQLt.SpyProcedure
-- along with INSERTs of relevant data.
-- For more information, see http://tsqlt.org/user-guide/isolating-dependencies/
--Act
-- Execute the code under tests like a stored procedure, function, or view
-- and capture the results in variables or tables.
--Assert
-- Compare the expected and actual values, or call tSQLt.Fail in an IF statement.
-- Available Asserts: tSQLt.AssertEquals, tSQLt.AssertEqualsString, tSQLt.AssertEqualsTable
-- For a complete list, see: http://tsqlt.org/user-guide/assertions/
EXEC tSQLt.AssertObjectExists @ObjectName = N'dbo.AddCategory'
END;
GO
After you run the database unit test, you can see that the test fails:
Create the Database Object and Rerun Unit Test
The next step is to create the required database object. In our case, it is a stored procedure.
-- (8) This procedure meets the new requirement by adding a new category
CREATE PROCEDURE dbo.AddCategory @CategoryName VARCHAR(50),
@Notes VARCHAR(400)
AS
-- Category Procedure Stub (template) in TDDD
GO
Run the unit test again – this time it succeeds:
But it is not enough to pass the unit test checking if the stored procedure exists. We also need to check if the stored procedure adds a new category.
Create the Database Unit Test to Check if Routine Functions Properly
Let’s create the new database unit test:
-- Comments here are associated with the test.
-- For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/
CREATE PROCEDURE CategoryTests.[test to check routine adds new category]
AS
BEGIN
--Assemble
EXEC tSQLt.FakeTable @TableName = 'dbo.Category' -- create an empty dependency free Category table
CREATE TABLE CategoryTests.Expected ( -- create expected table
CategoryId INT
,Name VARCHAR(50) NULL
,Notes VARCHAR(400) NULL
)
INSERT INTO CategoryTests.Expected (CategoryId,Name, Notes) -- Insert data into expected table
VALUES (null,'Database Dummy Category', 'This is just a dummy category for testing');
--Act
EXEC AddCategory @CategoryName = 'Database Dummy Category'
,@Notes = 'This is just a dummay category for testing'
--SELECT * INTO CategoryTests.Actual FROM Category -- put category table data into an actual table
--Assert
EXEC tSQLt.AssertEqualsTable @Expected = 'CategoryTests.Expected'
,@Actual = 'dbo.Category'
END;
GO
As you can see, the unit test fails for the first time and succeeds for the second time:
Add Functionality to the Routine and Rerun Unit Test
Modify stored procedure by adding the required functionality so that the test can succeed as shown below:
-- (8) This procedure meets the new requirement by adding a new category
ALTER PROCEDURE dbo.AddCategory @CategoryName VARCHAR(50),
@Notes VARCHAR(400)
AS
INSERT INTO Category (Name, Notes)
VALUES (@CategoryName, @Notes);
GO
Rerun the unit tests to check that they all succeed including the recently modified stored procedure:
This way, we have successfully implemented test-driven database development. Now we can focus on requirements only. The unit tests encapsulate requirements thereby demanding database objects to be created and run properly to meet the specification.
Let’s see how effective is TDDD when it comes to satisfying a business reporting requirement.
Satisfying Business Reporting Requirement through TDDD
We assume the database already got the necessary objects (such as tables) before receiving the new business reporting requirement.
Let’s create a sample database called SQLDevBlogReportTDD:
-- Create sample database (SQLDevBlogReportTDD)
CREATE DATABASE SQLDevBlogReportTDD;
GO
Then create and populate the tables for the sample database using the following code:
USE SQLDevBlogReportTDD;
-- (1) Create Author table in the sample database
CREATE TABLE Author (
AuthorId INT PRIMARY KEY IDENTITY (1, 1)
,Name VARCHAR(40)
,RegistrationDate DATETIME2
,Notes VARCHAR(400)
)
-- (2) Create an Article Category table in the sample database
CREATE TABLE Category (
CategoryId INT PRIMARY KEY IDENTITY (1, 1)
,Name VARCHAR(50)
,Notes VARCHAR(400)
)
-- (3) Create Article table in the sample database
CREATE TABLE Article (
ArticleId INT PRIMARY KEY IDENTITY (1, 1)
,CategoryId INT
,AuthorId INT
,Title VARCHAR(150)
,Published DATETIME2
,Notes VARCHAR(400)
)
-- Adding foreign keys for author and article category
ALTER TABLE Article ADD CONSTRAINT FK_Category_CategoryId FOREIGN KEY (CategoryId) REFERENCES Category (CategoryId)
ALTER TABLE Article ADD CONSTRAINT FK_Author_AuthorId FOREIGN KEY (AuthorId) REFERENCES Author (AuthorId)
GO
-- (4) Populating Author table
INSERT INTO Author (Name, RegistrationDate, Notes)
VALUES ('Peter', '2017-01-01', 'Database Analyst'),
('Adil', '2017-01-02', 'Database and Business Intelligence Developer'),
('Sarah', '2018-01-01', 'Database Analyst Programmer'),
('Asim', '2018-01-01', 'Database Analyst')
-- (5) Populating Category table
INSERT INTO Category (Name, Notes)
VALUES
('Analysis', 'Database Analysis'),
('Development', 'Articles about database development'),
('Testing', 'Database testing related articles'),
('DLM', 'Database lifecycle management')
-- (6) Populating Article
INSERT INTO Article (CategoryId, AuthorId, Title, Published, Notes)
VALUES (1, 1, 'Replicating a problem in SQL', '02-01-2018', ''),
(1, 2, 'Modern Database Development Tools', '02-01-2018', ''),
(3, 3, 'Test Driven Database Development (TDDD)', '03-01-2018', ''),
(3, 1, 'Database Unit Testing Fundamentals', '10-01-2018', ''),
(3, 3, 'Unit Testing with tSQLt', '10-01-2018', '')
GO
Create a view to see the list of all authors, articles, and articles’ categories:
-- (7) Create a view to see a list of authors, articles, and categories
CREATE VIEW dbo.vwAuthors
AS SELECT a.Name AS AuthorName,a1.Title AS ArticleTitle,c.Name AS CategoryName FROM Author a INNER JOIN Article a1 ON a.AuthorId = a1.AuthorId INNER JOIN Category c ON a1.CategoryId = c.CategoryId
GO
Run the view of the created table to see the results:
After processing the database setup and populating tables, the next step is to imitate the scenario where we received a new requirement.
Business Requirement: Total Number of Articles per Author Report
Consider a new business reporting requirement. It has to state a database report to view the total number of articles per author.
The first thing is to assign a database object which can meet business requirements. In our case, it is the ArticlesPerAuthorReport database object.
To meet the requirement, it is necessary to create a database unit test that looks for a potential appropriate object. As we know, this test will fail first because it will look for the object that is non-existing at the moment but will be there soon.
TDDD Implementation Plan
According to the standards of test-driven database unit testing, the following things must be there to meet the reporting requirement:
- Develop a single database object that will meet the reporting requirement.
- Create a unit test to check the object’s existence.
- Create an object stub (placeholder) to pass the first test.
- Create a second unit test to check if the object outputs correct data into the table with correct input.
- Modify the object definition to let the second test pass.
Create the Database Unit Test to Check Desired Object Exists
We’ll assign a database object which can meet the business requirement. In our case, it is the ArticlesPerAuthorReport database object. The next step is to create a database unit test.
To create the first database unit test, right-click the SQLDevBlogReport database > Unit Test > Add New Test
Write the following code to create the unit test that checks the existence or absence of the desired object:
CREATE PROCEDURE ArticlesPerAuthorReport.[test to check ArticlesPerAuthorReport exists]
AS
BEGIN
--Assemble
--Act
--Assert
EXEC tSQLt.AssertObjectExists @ObjectName = N'ArticlesPerAuthorReport'
END;
GO
The unit test must fail since it checks for the object created before. The object itself is created to comply with TDDD:
Create Object Stub and Run the Unit
Create an object stub with some hard-coded expected output, since we just want to create a database object with the expected result. Create the ArticlesPerAuthorReport object as a view stub (placeholder) at first:
-- (8) Create ArticlesPerAuthorReport view stub
CREATE VIEW ArticlesPerAuthorReport
AS
SELECT 'Adil' AS Author, 10 AS [Total Articles]
UNION ALL
SELECT 'Sam' AS Author, 5 AS [Total Articles]
Running the unit test should be successful:
Creating a stub serves as a kick-starter for TDDD. We create the object to pass the test and do not bother about the actual functioning of the object.
Create and Run the Unit Test to check whether Object Outputs Correct Data
It is time to check if the desired object is functioning properly. Create another unit test to check for the data output by the desired object (ArticlesPerAuthorReport). Let’s add a new unit test to the database:
Add the following unit test code:
CREATE PROCEDURE ArticlesPerAuthorReport.[test to check ArticlesPerAuthorReport outputs correct]
AS
BEGIN
--Assemble
-- Create mocked up tables (blank copies of original tables without constraints and data)
EXEC tSQLt.FakeTable @TableName = N'Author'
,@SchemaName = N'dbo'
EXEC tSQLt.FakeTable @TableName = N'Article'
,@SchemaName = N'dbo'
EXEC tSQLt.FakeTable @TableName = N'Category'
,@SchemaName = N'dbo'
-- Add rows to the mocked up tables
INSERT INTO Author (AuthorId,Name, RegistrationDate, Notes)
VALUES (1,'Zak', DATEFROMPARTS(2017,01,01), 'Database Author'),
(2,'Akeel',DATEFROMPARTS(2018,01,01),'Business Intelligence Author')
INSERT INTO Category (CategoryID,Name, Notes)
VALUES (1,'Database Development', '-'),
(2,'Business Intelligene','-');
INSERT INTO Article (ArticleId,CategoryId, AuthorId, Title, Published, Notes)
VALUES (1,1, 1, 'Advanced Database Development', DATEFROMPARTS(2017,02,01),'10K Views'),
(1,1, 1, 'Database Development with Cloud Technologies', DATEFROMPARTS(2017,02,01),'5K Views'),
(1,1, 1, 'Developing Databases with Modern Tools', DATEFROMPARTS(2017,03,01),'20K Views'),
(1,2, 2, 'Business Intelligence Fundamentals', DATEFROMPARTS(2017,02,01),'10K Views'),
(1,2, 2, 'Tabular Models', DATEFROMPARTS(2017,02,01),'50K Views')
-- Create an expected table
CREATE TABLE ArticlesPerAuthorReport.Expected
(Author VARCHAR(40),[Total Articles] int)
-- Add expected results into an expected table
INSERT INTO ArticlesPerAuthorReport.Expected (Author, [Total Articles])
VALUES ('Zak', 3), ('Akeel',2);
--Act
-- Run ArticlesPerAuthorReport object (view) and put results into an actual table
SELECT * INTO ArticlesPerAuthorReport.Actual FROM ArticlesPerAuthorReport apar
--Assert
-- Compare the expected and actual tables
EXEC TSQLT.AssertEqualsTable @Expected = N'ArticlesPerAuthorReport.Expected'
,@Actual = N'ArticlesPerAuthorReport.Actual'
END;
GO
Run the unit test that has to fail as well to comply with TDDD:
Add Required Functionality to the ArticlesPerAuthorReport object
The unit test that checks the functionality of the object requires a modified structure so that the test can pass.
Modify the ArticlesPerAuthorReport view to let the second unit test pass just like the first one:
ALTER VIEW ArticlesPerAuthorReport
AS
SELECT a.Name AS [Author],COUNT(a1.ArticleId) AS [Total Articles] FROM Author a
INNER JOIN Article a1 ON a.AuthorId = a1.AuthorId
GROUP BY a.Name
The database object has been successfully modified to output desired data. Run all the unit tests:
The ArticlesPerAuthorReport object is ready.
Our next task is to provide a walk-through of creating a report base on a database object developed and tested by using test-driven development (TDDD).
Implementing Reporting Requirement (ArticlesPerAuthorReport)
First, we are going to reset the SQLDevBlogReportTDD and add more data to it. Or, you can create a blank database for the first time.
To add enough data to our sample database, retrieve data from the vwAuthors view to see all the records:
Running the unit tests
Run the database unit tests that we created earlier:
Congratulations, both tests have passed which means the desired database object is capable of meeting the reporting requirement to view articles per author.
Create Database Report Based on ArticlesPerAuthorsReport Object
You can create a database report in many ways (such as using Report Builder, creating Report Server Project in Visual Studio Data Tools, or using dbForge Studio for SQL Server).
In this section, we are using dbForge Studio for SQL Server for report creating. To proceed, click New from the File Menu > Data Report:
Click Standard Report:
Select Simple Table\View as Data Type:
Add the base object (ArticlesPerAuthorReport), which is a view in our case:
Add the required fields:
We don’t need any grouping at this point, so proceed by clicking Next:
Select Layout and Orientation of the data report:
Finally, add title Articles per Author Report and click Finish:
Next, adjust the formatting of the report as per requirement:
Click Preview to see the database report:
Save the report as ArticlesPerAuthorReport. The database report has been created due to the business requirements.
Use of the Setup Procedure
When you write database unit tests using tSQLt, you will see that some test code is repeated often. Thus, you can define it in a setup procedure and reuse it afterward in other unit tests of that particular test class. Each test class can have only one setup procedure that runs automatically before the unit tests of that class processes.
We can put almost all test code written under Assemble (section) under the setup procedure to avoid code duplication.
For instance, we need to create mocked tables along with an expected table. Then we’ll add data to mocked tables, and then to the expected table. We can define it easily under the setup procedure and reuse it further.
Creating Setup Procedure to avoid test code duplication
Create a stored procedure in SQLDevBlogTDD as follows:
-- (12) Use of Setup Procedure to avoid repeating common test code
CREATE PROCEDURE ArticlesPerAuthorReport.Setup
AS
BEGIN
--Assemble
-- Create mocked up tables (blank copies of original tables without constraints and data)
EXEC tSQLt.FakeTable @TableName = N'Author'
,@SchemaName = N'dbo'
EXEC tSQLt.FakeTable @TableName = N'Article'
,@SchemaName = N'dbo'
EXEC tSQLt.FakeTable @TableName = N'Category'
,@SchemaName = N'dbo'
-- Add rows to the mocked up tables
INSERT INTO Author (AuthorId,Name, RegistrationDate, Notes)
VALUES (1,'Zak', DATEFROMPARTS(2017,01,01), 'Database Author'),
(2,'Akeel',DATEFROMPARTS(2018,01,01),'Business Intelligence Author')
INSERT INTO Category (CategoryID,Name, Notes)
VALUES (1,'Database Development', '-'),
(2,'Business Intelligene','-');
INSERT INTO Article (ArticleId,CategoryId, AuthorId, Title, Published, Notes)
VALUES (1,1, 1, 'Advanced Database Development', DATEFROMPARTS(2017,02,01),'10K Views'),
(1,1, 1, 'Database Development with Cloud Technologies', DATEFROMPARTS(2017,02,01),'5K Views'),
(1,1, 1, 'Developing Databases with Modern Tools', DATEFROMPARTS(2017,03,01),'20K Views'),
(1,2, 2, 'Business Intelligence Fundamentals', DATEFROMPARTS(2017,02,01),'10K Views'),
(1,2, 2, 'Tabular Models', DATEFROMPARTS(2017,02,01),'50K Views')
-- Create an expected table
CREATE TABLE ArticlesPerAuthorReport.Expected
(Author VARCHAR(40),[Total Articles] int)
-- Add expected results into an expected table
INSERT INTO ArticlesPerAuthorReport.Expected (Author, [Total Articles])
VALUES ('Zak', 3), ('Akeel',2)
END;
GO
Now, remove the test code we have written in the setup procedure from the previous unit test to check ArticlesPerAuthorReport outputs as follows:
-- (11) Create unit test check ArticlesPerAuthorReport outputs correct
ALTER PROCEDURE ArticlesPerAuthorReport.[test to check ArticlesPerAuthorReport outputs correct]
AS
BEGIN
--Assemble (Test Code written in Setup Procedure)
-- Create mocked up tables (blank copies of original tables without constraints and data)
-- Add rows to the mocked up tables
-- Create an expected table
-- Add expected results into an expected table
--Act
-- Run ArticlesPerAuthorReport object (view) and put results into an actual table
SELECT * INTO ArticlesPerAuthorReport.Actual FROM ArticlesPerAuthorReport apar
--Assert
-- Compare the expected and actual tables
EXEC TSQLT.AssertEqualsTable @Expected = N'ArticlesPerAuthorReport.Expected'
,@Actual = N'ArticlesPerAuthorReport.Actual'
END;
GO
Running All Unit Tests to Check Setup Procedure Working
Run the unit tests and see the results:
The unit tests have run successfully despite the fact we are using a setup procedure to run some parts of the test code before these unit tests are running.
Use of Stored Procedures
Next, we’ll focus on creating stored procedures through test-driven database development (TDDD) to meet specific requirements that cannot be fulfilled by using a database view.
Let’s assume that business users want to know the Total number of articles per author for a specified year. The database view can’t meet it because it is not defined at the time of writing the script (exactly the year is going to be desired by the business users).
Thus, it requires a database object with parameter(s) capability and it is exactly the stored procedure.
Let us consider a new business requirement to create the report that shows the total number of articles per author for a specified year. We’ll use the sample database called SQLDevBlogReportTDD that we created earlier.
Run the ArticlesPerAuthorReport view to see the results:
Select the Database Object (AuthorsPerArticleByYearReport)
Name the potential database object as AuthorsPerArticleForYearReport.
As we mentioned above, the database view can meet the reporting requirement despite the absence of the specified year. But this variable means that we need the stored procedure which will pass year as an argument to run the report and show the desired results.
Write and Run the Object Exists Unit Test
As we already know, we need to start with writing the basic unit test to check the existence or absence of the desired object.
To create the first database unit test, right-click the SQLDevBlogReport database > Unit Test > Add New Test
Write the following test code:
CREATE PROCEDURE ArticlesPerAuthorByYearReport.[test to check ArticlesPerAuthorByYearReport exists]
AS
BEGIN
--Assemble
--Act
--Assert
EXEC tSQLt.AssertObjectExists @ObjectName = N'ArticlesPerAuthorByYearReport'
,@Message = N''
END;
GO
Right-click on the database > click View Test List under Unit Test to see the Test List Manager:
Check the ArticlesPerAuthorByYearReport test class and click the run test icon:
This complies with TDDD – the unit test checking if object existence is written before the object is created. So, we expect the test to fail first.
Create Object Stub (dummy object)
We are going to create an object stub that mocks the object’s functionality. At this stage, we only need that object, the desired functionality is out of the question.
Create a stored procedure type object as the stub and call it ArticlesPerAuthorByYearReport by using the following code:
-- Create report object (stored procedure) stub
CREATE PROCEDURE dbo.ArticlesPerAuthorByYearReport
@Year INT
AS
SELECT 'Adil' AS Author, 10 AS [Total Articles], 0000 AS [Year]
UNION ALL
SELECT 'Sam' AS Author, 5 AS [Total Articles], 0000 AS [Year]
GO
After we created the object stub, the basic unit test that checks for the existence of the object will be successful:
Write and Run the Object Functionality Unit Test
To comply with TDDD, we need to write a unit test to check whether the desired object ArticlesPerAuthorByYearReport functions properly. Since the object was created as a stub (placeholder), this unit test is also going to fail first. The object has to function properly yet despite the fact it was created and passed the basic check of its existence.
Create a second unit test to check if the object outputs correct data by creating a setup procedure (which helps us to write shared test code within the same test class) that is followed by the unit test:
CREATE PROCEDURE ArticlesPerAuthorByYearReport. Setup
AS
BEGIN
--Assemble
-- Create mocked up tables (blank copies of original tables without constraints and data)
EXEC tSQLt.FakeTable @TableName = N'Author'
,@SchemaName = N'dbo'
EXEC tSQLt.FakeTable @TableName = N'Article'
,@SchemaName = N'dbo'
EXEC tSQLt.FakeTable @TableName = N'Category'
,@SchemaName = N'dbo'
-- Add rows to the mocked up tables
INSERT INTO Author (AuthorId,Name, RegistrationDate, Notes)
VALUES (1,'Zak', DATEFROMPARTS(2017,01,01), 'Database Author'),
(2,'Akeel',DATEFROMPARTS(2018,01,01),'Business Intelligence Author')
INSERT INTO Category (CategoryID,Name, Notes)
VALUES (1,'Database Development', '-'),
(2,'Business Intelligene','-');
INSERT INTO Article (ArticleId,CategoryId, AuthorId, Title, Published, Notes)
VALUES (1,1, 1, 'Advanced Database Development', DATEFROMPARTS(2017,02,01),'10K Views'),
(1,1, 1, 'Database Development with Cloud Technologies', DATEFROMPARTS(2017,02,01),'5K Views'),
(1,1, 1, 'Developing Databases with Modern Tools', DATEFROMPARTS(2017,03,01),'20K Views'),
(1,2, 2, 'Business Intelligence Fundamentals', DATEFROMPARTS(2016,02,01),'10K Views'),
(1,2, 2, 'Tabular Models', DATEFROMPARTS(2016,02,01),'50K Views')
-- Create an expected table
CREATE TABLE ArticlesPerAuthorByYearReport.Expected
(Author VARCHAR(40),[Total Articles] INT,[Year] INT)
-- Create an actual table
CREATE TABLE ArticlesPerAuthorByYearReport.Actual
(Author VARCHAR(40),[Total Articles] INT,[Year] INT)
-- Add expected results into an expected table for the year 2017
INSERT INTO ArticlesPerAuthorByYearReport.Expected (Author, [Total Articles],[Year])
VALUES ('Zak', 3,2017)
END;
GO
Write the unit test to check if the object functions properly:
-- Create unit test to check ArticlesPerAuthorByYearReport outputs correct data
CREATE PROCEDURE ArticlesPerAuthorByYearReport.[test to check ArticlesPerAuthorByYearReport outputs correct data]
AS
BEGIN
--Assemble (Test Code written in Setup Procedure)
-- Create mocked up tables (blank copies of original tables without constraints and data)
-- Add rows to the mocked up tables
-- Create an expected table
-- Create an actual table
-- Add expected results into an expected table
--Act
-- Call desired object (stored procedure) and put results into an actual table
INSERT INTO ArticlesPerAuthorByYearReport.Actual
EXEC dbo.ArticlesPerAuthorByYearReport @Year=2017
--Assert
-- Compare the expected and actual tables
EXEC TSQLT.AssertEqualsTable @Expected = N'ArticlesPerAuthorByYearReport.Expected'
,@Actual = N'ArticlesPerAuthorByYearReport.Actual'
END;
GO
Run the unit test. As demonstrated earlier, it will fail first since we have not added the desired functionality to the object yet:
Add Object Functionality and Rerun the Unit Test
Add the object functionality by modifying the stored procedure as follows:
-- Create report object (stored procedure) to show articles per author for a specified year
CREATE PROCEDURE dbo.ArticlesPerAuthorByYearReport
@Year INT
AS
SELECT
a.Name AS [Author],COUNT(a1.ArticleId) AS [Total Articles],YEAR(a.RegistrationDate) AS [Year]
FROM Author a
INNER JOIN Article a1
ON a.AuthorId = a1.AuthorId
WHERE YEAR(a.RegistrationDate) = @Year
GROUP BY a.Name,YEAR(a.RegistrationDate)
GO
Note: If you are using a declarative database development tool like dbForge Studio for SQL Server, you’ll use the Create Procedure statement to modify the object. For tools like SSMS (SQL Server Management Studio), you must use ALTER Procedure.
Rerunning the database unit test for checking the proper object functioning gives us the following results:
You have successfully unit tested the reporting procedure that is responsible for meeting the business requirement.
Conclusion
Test-driven database development (TDDD) is a specific approach. To meet the business requirement(s), potential database object(s) must pass the unit test(s) and satisfy the following conditions under normal circumstances:
- The database object must exist
- The database object must function properly to meet the business requirement
First, the unit tests have to fail because they are created before the creation of the object/defining the object functionality. After adding the necessary objects and ensuring their functionality, the unit tests succeed.
This article examined the basics of test-driven database development and illustrated it with practical examples. We hope that the article was helpful to you. Feel free to share your opinions and maybe some lifehacks in the Comments section, and stay tuned for the next materials!
Tags: ssms, tdd, tools, unit test Last modified: October 13, 2021
