powershellvb/pstsqlttestgenerator

PowerShell module to generate tSQLt tests

Master Branch	Development Branch

PStSQLtTestGenerator

What does it do

Unit testing is fairly new to databases and more and more companies are implementing it into their development process. The downside is that the existing objects do not have any unit tests yet.

That's where this PowerShell module comes in. This module makes it possible for you to generate basic unit tests for database objects.

Tests like:

• Database Collation
• Objects Existence
• Function Parameters
• Stored Procedure Parameters
• Table Columns
• View Columns

How to install

Run the following to install the module from the PowerShell Gallery (to install on a server or for all users, remove the -Scope parameter and run in an elevated session):

Install-Module PStSQLtTestGenerator -Scope CurrentUser

How to run the module

The main command to get all the tests is Invoke-PSTGTestGenerator.

To get all the tests run the following command:

Invoke-PSTGTestGenerator -SqlInstance [yourinstance] -Database [yourdatabase] -OutputPath [testfolder]

That's all that is to it. The tests will all be written to the designated folder. You can then copy these to your SSDT project or run the scripts to create the the tests in your database

For more help and information about any particular command, run the Get-Help command, i.e.:

Get-Help Invoke-PSTGTestGenerator

How does it work

The modules works by iterating through database objects and create tests according to the type of object.

Based on a specific template for each test, it will create a ".sql" for each test with the correct content.

For instance, all the functions, stored procedures, tables and views will have a test to check if they exists the next time the tSQLt unit test runs.

Let's take the table "dbo.Customer". This table would get a test called "test If table dbo.Customer exists Expect Success.sql". This test file would contain content similar to this:

/*
Description:
Test if the table dbo.Customer exists

Changes:
Date		Who					Notes
----------	---					--------------------------------------------------------------
9/18/2019	sstad				Initial test
*/
CREATE PROCEDURE [TestBasic].[test If table dbo.Customer exists Expect Success]
AS
BEGIN
    SET NOCOUNT ON;

    ----- ASSERT -------------------------------------------------
    EXEC tSQLt.AssertObjectExists @ObjectName = N'dbo.Customer';
END;