DevOps series : tSQLt Framework

Introduction

In one of my former projects I gained a lot of experience with building, testing and releasing with Azure DevOps and I noticed that unit testing in the database is a good thing to do. In my current project we are using tSQLt Framework for testing purposes. This blogpost is about the tSQLt framework, on how to install and how to use. The first basic steps in order to get you (and me) going.

Why is unittesting important? Well, I have noticed during my work as a consultant that releasing code can be very cumbersome and tricky when you're not convinced that everything still works, even the code you have not touched. May be something has changed in the data that will gives errors somewhere else. So running unittests before you release, is a very good thing to do!

tSQLt is free and is downloadable. There are a couple of steps that you have to do to make it work. You have to spend some time in using the stored procedure calls and scripting to understand to working of the tSQLt framework.

For this blogpost I've used some inspiration from sqlshack.

Installation of the tSQLt framework

First download the files from tsqlt.org and unzip it somewhere on your disk, like I've done below. There are a couple of sql files.

The next step is the installation of an example of the framework into SQL Server. That is the example.sql file. Open SSMS and execute the example.sql file.

Executing the tests scripts in the example file succeeds except one. The last unittest gives an error, unfortunately.

Below is the specific error of the test that is executed.

So the test is failed and therefore we need to check whether what went wrong. Is the test not good defined or something else has happened?

I changed to > into >= and execute the testexecution again and now it runs properly.

My first tSQLt testscript

Now the next step I've done is executing my own script (with a little help from sqlshack). I've created a database, a customer table and inserted a record and added a stored procedure that I would like to test.

CREATE DATABASE TestThetSQLtFramework
GO

USE TestThetSQLtFramework;
GO
 
CREATE TABLE [dbo].[Customer] (
    [CustomerId]       INT           IDENTITY (1, 1) NOT NULL,
    [Name]             VARCHAR (40)  NOT NULL,
);

SET IDENTITY_INSERT [dbo].[Customer] ON
INSERT INTO [dbo].[Customer] ([CustomerId], [Name]) VALUES (1, N'Hennie')
SET IDENTITY_INSERT [dbo].[Customer] OFF

SELECT * FROM [Customer]


CREATE PROCEDURE AddCustomer(@Name VARCHAR(40))
AS
BEGIN
  
  INSERT INTO dbo.[Customer] (Name)
  VALUES (@Name)
    
END
GO

The next step is to run the test framework, and it seems nothing is there, and the test process is executed properly. Off course that is because there is no test there.

EXEC tSQLt.RunAll

(0 rows affected)
 
+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name|Dur(ms)|Result|
+--+--------------+-------+------+
-----------------------------------------------------------------------------
Test Case Summary: 0 test case(s) executed, 0 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

The following step is creating a test class (aka a schema) in the database.

EXEC tSQLt.NewTestClass 'CustomerTests';
GO

Then, a test stored procedure has to be created in the database with three steps : Assemble, Act and Assert.

ALTER PROCEDURE [CustomerTests].[TestAddCustomer]
AS
BEGIN
 -- Assemble
        EXEC tSQLt.FakeTable 'dbo.Customer', @Identity = 1

    Create TABLE [CustomerTests].[Expected] 
 (
   [CustomerId] INT NOT NULL,
   [Name] VARCHAR(40) NOT NULL,
 )

 INSERT INTO [CustomerTests].[Expected] (CustomerId,Name)
 VALUES (1,'Hennie')

 -- Act
 EXEC dbo.AddCustomer 'Hennie' 
 SELECT * INTO CustomerTests.Actual FROM dbo.Customer 
 
  -- Assert (compare expected table with actual table results)
 EXEC tSQLt.AssertEqualsTable @Expected='CustomerTests.Expected', 
        @Actual='CustomerTests.Actual'
END;
GO

Executing the stored procedure will result in the following output. All is fine!

EXEC tSQLt.RunAll

(1 row affected)
 
+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name                   |Dur(ms)|Result |
+--+---------------------------------+-------+-------+
|1 |[CustomerTests].[TestAddCustomer]|    113|Success|
-----------------------------------------------------------------------------
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

Now, see what happens when I change the output from the desired output. I changed  Hennie into Hennie2 in the call of the stored procedure.

-- Act
EXEC dbo.AddCustomer 'Hennie2' 
SELECT * INTO CustomerTests.Actual FROM dbo.Customer 

Excuting the RunAll SP of the tSQLt framework will result in an error in the test framework.

EXEC tSQLt.RunAll

(1 row affected)
[CustomerTests].[TestAddCustomer] failed: (Failure) Unexpected/missing resultset rows!
|_m_|CustomerId|Name   |
+---+----------+-------+
|<  |1         |Hennie |
|>  |1         |Hennie2|
 
+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name                   |Dur(ms)|Result |
+--+---------------------------------+-------+-------+
|1 |[CustomerTests].[TestAddCustomer]|    127|Failure|
-----------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 29
Test Case Summary: 1 test case(s) executed, 0 succeeded, 1 failed, 0 errored.
-----------------------------------------------------------------------------

Final thoughts

Building tests is very simple in the tSQLt test framework

Hennie

Powerdesigner series : How to organize your workspace in Powerdesigner

Introduction

In this blogpost I'll outline the concept of workspaces in Powerdesigner. A workspace is a container of models that you want to organize in a logical manner. Although, a workspace in Powerdesigner is a concept that is comparable with Visual studio solutions, there are some differences between the concepts of workspaces and visual studio solutions.

Similarities between Powerdesigner and Visual Studio projects are that you can organize different files in a container, you can use folders and store files in a hierarchy of folders. It is possible to have multiple workspaces on a machine.

Now there is also the same concept of projects. In solution explorer of Visual Studio you can create projects as in Powerdesigner. Although, I haven't been investigate projects very thoroughly yet, projects seems very different in Powerdesigner than projects in Visual studio. In Visual Studio files are organized in (one or more) project(s) in a solution, but the concept of projects in Powerdesigner is a bit more different. Projects inPowerdesigner are supposed to show relationships between models and what the dependencies are.

Start with a workspace

Let's start looking at the concept of workspaces. When Powerdesigner is started a workspace is already there (in contrast with Visual Studio).

Now you can save the workspace at a certain place on your computer and give the file a proper name.

When the workspace file is saved, a .sws file is created with the a name that you gave.

Rename a workspace

Now saving the file with another name than the standard "workspace" does not change the name in Powerdesigner. You have to rename that too.

And when you have done this, the workspace in Powerdesigner is renamed to different name.

So starting Powerdesigner and saving a Workspace at a proper place and renaming the workspace with a same name is advisable. This way there is less confusion.

Add a data model to a workspace

A next step is adding a data model to the workspace. That is possible with the menu option New > Physical Data Model (for instance).

Now a Physical Data Model is added to the Workspace. Note that there are two levels in the Wokspace added. A Physical Model and a Physical Diagram. I'll show some more examples later.

Add a folder to a workspace

Yet another option to organize models in a workspace is the usage of folders. You can use the option New > Folder.

Folders are logical organized in your workspace file and not physical. If you want to organize your model in a physical folder structure you can simply create them in the window folder and save your models there.

Organize models in folders

Models can be organized in models and now you can simply drag models to the folder and drop it there. The model (and diagram) is now present in the folder.

And organize models in a physical folder on your file system.

Adding more models and diagrams

It's also possible to organize models and diagrams in different ways in a workspace. You can add diagrams to a model and you can add separate models and diagrams. Below an example of adding another diagram to a existing model.

And off course you can add a new model/diagrams to a workspace.

If you insert different types of workspace objects to the workspace you can have something like this.

Save your workspace

You can save the workspace to disk and it can be confusing saving the models to the disk and using logical and physical folder all together. In my opinion, use only logical folders in your workspace because aligning physical and logical folders can be very difficult and confusing.

How is the sws file organized?

I've added a screenshot of the workspace file (.sws) and this organized as a XML file. Notice that not all elements of the workspace are mentioned in the file. Only one model and one diagram is stored.

Final thoughts

This blogpost was about discovering the basic options of organizing a workspace in Powerdesigner. Using workspaces have some similarities and dissimilarities with Visual Studio projects. 

Hennie