LLI Wiki — My Digital Knowledge Base

Testing SQL-facing PowerShell code with Pester, without a database

Mocking the data access layer, testing SQL templates and config files, and running the tests in CI without a database.

PowerShell that talks to SQL Server is the code people are most afraid to test: it needs a server, credentials, data in a known state, and a failed test may be a failed network. My approach is to stop testing the database and test everything around it. In practice that covers about 90% of the bugs I ever ship.

Where the bugs actually are

Look at the defects in a DBA tooling module: a wrong return value on the failure path, a placeholder never replaced, a config key missing in one environment, a destructive call made when it should have been skipped, a retry loop that never stops. None of these need a real database. They need the code to run with a fake data access layer.

Pester 5 only

Windows PowerShell ships with Pester 3.4, which has a different syntax and scoping. Install Pester 5 explicitly and import it with a minimum version before running:

Install-Module Pester -MinimumVersion 5.5 -Scope CurrentUser -SkipPublisherCheck
Import-Module Pester -MinimumVersion 5.5
Invoke-Pester -Path .\Tests\ -Output Detailed

Tests live in a Tests/ folder at the root of the module or application, never inside folders that are dot-sourced at start-up.

Mock the data access layer

The key design decision is to have one thin function that is the only place where SQL is actually executed. Every other function calls it. In tests, that function is mocked and the test asserts on what it was asked to do.

BeforeAll {
    Import-Module "$PSScriptRoot\..\Contoso.Dba.psd1" -Force
}

Describe 'Get-PoolUsage' {
    Context 'success' {
        BeforeAll {
            Mock Invoke-SqlQuery -ModuleName Contoso.Dba {
                [pscustomobject]@{ PoolName = 'pool-elp-01'; CpuPercent = 42 }
            }
        }
        It 'returns the rows with IsValid true' {
            $r = Get-PoolUsage -ServerName 'sql-contoso-prd' -LogFile (Join-Path $TestDrive 't.log')
            $r.IsValid | Should -BeTrue
            $r.Data[0].CpuPercent | Should -Be 42
        }
    }
    Context 'failure' {
        BeforeAll { Mock Invoke-SqlQuery -ModuleName Contoso.Dba { throw 'login timeout' } }
        It 'returns IsValid false with a reason' {
            $r = Get-PoolUsage -ServerName 'sql-contoso-prd' -LogFile (Join-Path $TestDrive 't.log')
            $r.IsValid | Should -BeFalse
            $r.Reason  | Should -Match 'login timeout'
        }
    }
}

Three habits matter here:

Destructive operations get the opposite assertion. For a function that must not drop anything in dry-run mode:

Should -Invoke Remove-Database -Times 0 -ModuleName Contoso.Dba

Testing SQL templates

If the SQL lives in template files with placeholders, test the template expansion rather than the result of the query:

It 'replaces every placeholder' {
    $sql = Expand-SqlTemplate -Name 'PoolUsage' -Values @{ DatabaseName = 'AppDb' }
    $sql | Should -Match 'AppDb'
    $sql | Should -Not -Match '\{\{'
}
It 'fails on a missing value' {
    { Expand-SqlTemplate -Name 'PoolUsage' -Values @{} } | Should -Throw
}

Cheap extras worth adding: every .sql file has no unreplaced placeholder left after expansion with the full value set, and every placeholder used in a template is documented. If you need syntax validation without a server, a T-SQL parser library can parse the expanded text and report errors; that is optional and worth it only for critical scripts.

Testing config files

Config files are code in disguise: a missing key in one environment fails at 3 a.m. Test them in discovery:

BeforeDiscovery {
    $files = Get-ChildItem "$PSScriptRoot\..\config\config-*.json" |
             Where-Object Name -ne 'config-common.json'
}
Describe 'config <_.Name>' -ForEach $files {
    It 'is valid JSON' { { Get-Content $_.FullName -Raw | ConvertFrom-Json } | Should -Not -Throw }
    It 'has the required keys' {
        $c = Get-Content $_.FullName -Raw | ConvertFrom-Json
        $c.sqlServer | Should -Not -BeNullOrEmpty
    }
}

BeforeDiscovery matters in Pester 5: data used to generate tests must exist at discovery time, not in BeforeAll.

Running in CI without a database

Because nothing real is touched, the same tests run on a hosted build agent with no network access to SQL and no Azure login:

steps:
  - pwsh: |
      Install-Module Pester -MinimumVersion 5.5 -Scope CurrentUser -Force -SkipPublisherCheck
      $cfg = New-PesterConfiguration
      $cfg.Run.Path = './Tests'
      $cfg.Run.Exit = $true
      $cfg.TestResult.Enabled = $true
      Invoke-Pester -Configuration $cfg
    displayName: Pester

Publish the result file so failures show in the pipeline UI. A test that needs a real server is an integration test: keep it in a separate folder, tag it, and run it on demand.

Lessons learned

Mastodon LLIMastodon SocialFosstodon