As a SQL Server developer, it is common to encounter situations where you need to generate mock test data for your modules or scripts. In this blog post, we will explore a simple approach to generate mock test data using SQL Server and PowerShell.

Let’s say you are writing Pester tests for a module and you need some sample data. One way to obtain this data is by querying a database. You can use the dbatools module to run the query against the database and retrieve the desired data.

Here is an example of how you can retrieve data from a SQL Server database using dbatools:

$db = Get-DbaDatabase -SqlInstance $Instance -Database $Database
$variable = $db.Query($Query)

Now, let’s say you want to mock the $variable in your tests. To achieve this, you can wrap the code above in a function, such as Run-Query:

function Run-Query {
    Param($query)
    $db = Get-DbaDatabase -SqlInstance $Instance -Database $Database
    $variable = $db.Query($Query)
}

By encapsulating the code in a function, you can easily separate it for mocking in your tests.

Once you have retrieved the data, you may want to convert it into JSON format for easier manipulation and storage. However, you may encounter an issue where the JSON output contains unnecessary properties and is not in the desired format.

Here is an example of how you can convert the retrieved data into JSON:

$variable | ConvertTo-Json

In some cases, the JSON output may contain additional properties that you don’t need. To exclude these properties, you can use the Select-Object cmdlet with the -ExcludeProperty parameter:

$variable | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors | ConvertTo-Json

By excluding the unnecessary properties, you can obtain the desired JSON output that can be used as mock test data.

Remember, sharing what you learn not only helps others but also aids in your own learning and retention. So, don’t hesitate to share your knowledge and experiences with others.

That’s it for this quick post on generating mock test data in SQL Server. I hope you found this information helpful. Stay tuned for more SQL Server tips and tricks!