Pester the Tester: PowerShell Bugs, Beware! – Notes from the Field #129

If you are like many SQL Server DBA’s, you’ve embraced the power that is PowerShell. By now, you’ve likely amassed a collection of scripts to aid in your work. Perhaps you’ve even assembled these into modules.

Over time, you’ve found the need to make changes to these scripts. How do you ensure your changes work correctly? What about new scripts? How do you ensure yourself, your coworkers, and management that your scripts function properly?

By now, you’ve probably guessed Pester is the answer. Pester is an open source testing framework, written in PowerShell, and designed to make the testing of your scripts and modules easy.

The first thing to know is where to get Pester? There are two ways in fact. The first is via the Pester GitHub site. ( https://github.com/pester/Pester ) Like installing any other module, simply download and install to your PowerShell Modules folder. This is the method people using PowerShell prior to version 5 will need to use.

If you are on version 5, there is a much simpler method. First, load the package management module.

Import-Module PackageManagement

Next, you can install Pester using the Install-Module cmdlet.

Install-Module Pester -Force 

Why the –Force switch? Well, there is actually a third method. Pester is actually included in Windows 10. However, that version is already out of date, so you’ll want to install using the Install-Module cmdlet to get the latest version.

Note that after it is installed, you can just use Update-Module Pester to keep it updated.

You can do a lot with Pester with just two basic commands. The first is Describe. Describe is used as a wrapper around your tests. Its syntax is simple, the function name of Describe, followed by a name you want to give it, then the opening tag for a script block.

Describe "See if DB Exists" {

}

Note the placement of the opening brace is important. Describe is actually a function. The text “See if DB Exists” is actually a parameter to the Describe function. The brace marks the beginning of a script block, which is the second parameter to the Describe function. Unlike built in PowerShell commands, such as foreach, the opening brace must be on the same line as the function call.

Within the Describe script block tests are placed within It functions. Inside the It script block, a comparison is done, then the output piped to a Should function.

Describe "See if DB Exists" {
  It "Should be true" {
    1 -eq 1 | Should Be $true
  }
} 

There are many variations on Should. You can pipe in a file name, and use Should Exist, or pipe in a file and use Should Contain ‘some text’ to see if that file contains ‘some text’.

You can do some useful things in the SQL Server world with tests. Let’s say you are writing a module, which contains a function “CreateNewTable”. You want to test that this function actually did its job, namely creating a table. Below is a sample from my recent Pluralsight course on Pester. There is a database called “PodcastSight” which accumulates data for Podcasts. Each podcast goes in its own table. The code here creates a new table for a podcast named NoAgenda.

Describe "Test CreateNewTable Function" {

  Push-Location 
  Import-Module SQLPS -DisableNameChecking
  Pop-Location

  # Place the table being created in a variable
  $tableName = 'NoAgenda'

  # Call the function we are testing
  CreateNewTable $tableName

  # Construct a query to run against the database
  $dbcmd = @"
    SELECT [Name] AS TableName 
      FROM [PodcastSight].[sys].[tables] 
     WHERE [Name] = '$($tableName)'
"@

  # Execute query to see if table exists (note line continuation characters)
  $result = Invoke-Sqlcmd -Query $dbcmd `
                          -ServerInstance $env:COMPUTERNAME `
                          -Database 'master' `
                          -SuppressProviderContextWarning 

  # Now run the test   
  It "$tableName should exist" {
   ($result.TableName -eq $tableName) | Should Be $true
  }

}

The test starts with the Describe block, its name, and the opening script brace. We then load the SQLPS module, so we can interact with SQL Server.

We then load the table name we are using for testing into a variable. This will make it easier to alter as time goes by.

Next is the call to our function, CreateNewTable, which in our demo we are passing a table name into.

After that, we construct a SQL Select statement to determine if the table name exists; following is the Invoke-SqlCmd cmdlet to run the query.

Now comes the key test, the It statement. Note the inclusion of the $tableName variable in the name we are giving to the It function. Using variables in this way can let you create dynamic tests.

Within the It script block we do a comparison between the results of the SQL query and the name of the table, and pipe that to the Should Be function.

So how to run the test? Pester only knows how to run tests stored in files. So the first thing is to save the test, and this is import, make sure the file name ends with .Tests.ps1. Pester will execute if the “.Tests” exists in the file name. We’ll name this one Blog-Demo.Tests.ps1.

Now open up an interactive window, and navigate to the folder with the test file in it. After doing Import-Pester to load the Pester module in memory, simply enter Invoke-Pester into the command window. It will then execute all tests in that folder that have .Tests in the file name. Here’s a sample of the output:

Note the output includes the name of the Describe block, followed by the name of the test. At the bottom are the number of tests passed, failed, etc. This would imply you could have many It blocks in the Describe, and indeed you can!

This is just the bare tip of the iceberg when it comes to Pester testing, but as you can see even with these simple commands you can construct reusable tests so each time you make code modification you can run automated tests against your changes.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.