Unit Test in Jenkins

Question

I already have a SQL Server DB and Source control "Redgate" beside that I already connected my project to repo Github and this project also connected to Jenkins.

What I need is to do some kind of unit test on a the tables, like I already got the query and the test will pass values to this query , in away or another it is Unit test.

But I need it to done by Jenkins. Is there any possible way to do it !? in case of YES has that something to with POWERSHELL ?

I'm kind of confused because I know what I need to do, and I have the tools but the structure is not clear for me .

Where and how to do the Unit test and how to push it to everyone that they develop on the DB.

Answer 1

To do unit testing in databases, I'd recommend the tSQLt framework. Have a look at the quick start guide to see how to use it.

If you've already got a license for Redgate's SQL Source Control then you might also have a license for some of their other products - they have one called SQL Test which makes it much easier to use tSQLt within SQL Server Management Studio.

As for running the unit tests within Jenkins, check to see if you also have a license for Redgate's DLM Automation product - it's a set of Powershell cmdlets that you can use for automating database continuous integration and delivery, including running any tSQLt unit tests that are present. Luckily there is a Jenkins plugin available for it - https://plugins.jenkins.io/redgate-sql-ci

There are also some good videos here on how to use it - http://www.red-gate.com/products/dlm/dlm-automation/resources/

Answer 2

You've got a handful of questions over there, let me try and address most of them.

1. I'd use Pester Powershell module to create a test (that's using Invoke-SqlCmd) that would output result to xml, so the jenkins can parse them.
2. You can do db tests in any language you like, actually, like Jenkins can run whatever you feed into it, c# tests, python tests, powershell tests and so on, its just a matter of personal preference which language\stack are you going to use.
3. I'm not really sure what you mean by: "how to push it to everyone that they develop on the DB".

This is what I had to run the tests with powershell and have jenkins consume them:

$tests = Invoke-Pester -OutputFormat NUnitXml -OutputFile tests.xml -PassThru
if ($tests.FailedCount -eq 0) { Remove-AzureRmResourceGroup -ResourceGroupName $rgName -Force }