How to unit test the database code for Sql Server 2014

Question

I am working on developing a unit test framework for the db code in Sql Server 2014. My requirement is following:

• The tool should provide enough assertions to check the db schema, positive values, negative values, exceptions and constraints.
• The tool should provide mocking and stubbing for the db stored procs
• The tool should be able to generate .TRX reports for the passed/failed test cases as I want to pulbish these reports using VSTS/TFS build
• Code coverage if possible
• The tool should be able to integrate with the TFS build template for the CI purpose.

After doing some research, I think i have three options with the help of the tools SSDT with MSTest and tSqlT as:

• Using SSDT for test database initialization/cleanup and using MsTest for Sql unit testing but it has no mocking feature and the options for assertion are also limited. Cleanup is also manual
• Using tSqlT for unit test cases. it is a nice and powerful tool but it does not provide a GUI like SSDT. The redGate GUI is paid which is not possible in my case. Also it seems tougher to integrate it with TFS builds. And this does not produce reports in .TRX format but in .XML format.
• Using combination of SSDT and tSqlT as SSDT for db initialization, TFS integration and runnig tSqlT code from the MSTest project and tSqlT for the db unit testing.

Can anyone suggest a better approach on the db unit testing?

Thanks

Answer 1

In a similar situation here. I have yet to evaluate a "pure RedGate" solution - but for an MS-centric shop, I would recommend

• a SQLProject for PROD-ready schema control (ie anything you're going to ship, place here)
• a SQLProject that contains only tSQLt and tSQLt unit tests scripts
• MSTest data-driven test invokes tSQLt to execute tests

In CICD setup deploy PROD-ready SQLProject, then tSQLt SQLProject, then execute MSTest suite.

Details

Unfortunately, there is no easy way to enumerate each tSQLt unit test your developers may author as an individual item in TestExplorer, unless you force them to write a C# proxy test for each tSQLt test (which is error prone and duplicates work), or you leverage T4 templating (eg probe SQL for tSQLt test list, then use a template to dynamically generate MSTest source files), which seems hokey and overkill.

MSTest data-driven tests are a bit of a pain to set up, but it's documented, and once it's done, doesn't need to be touched again.

public TestContext TestContext { get; set; }

[TestCategory("Integration")]
[TestMethod]
// NOTE: DataSources should be configured as described here
// https://msdn.microsoft.com/en-us/library/ms182527.aspx 
// 
// NOTE: when configuring DataSource table, use tSQLt.Tests
[DataSource("MyDataSource")]
public void Test()
{
    // NOTE: tSQLt.Tests returns a multi-column table, index
    // as appropriate
    string name = $"{TestContext.DataRow[1]}.{TestContext.DataRow[3]}";
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.
        ConnectionStrings["MyConnectionString"].
        ConnectionString))
    {
        connection.Open();
        // NOTE: using Dapper to invoke
        try { connection.Query($"exec tSQLt.Run '{name}'"); }
        // NOTE: this is how we surface individual test names
        // on failure
        catch { Assert.Fail($"Failed tSQLt test '{name}'."); }
    }
}

Sample output

Alternatives and considerations

SSDT unit tests: I don't really see the value add here. The GUI is clumsy and completely unintuitive. If you are a power C# developer, the buttons and designer are a hindrance. If you are a power SQL developer, the code-behind and new IDE (VS vs SSMS) is a pain.

NUnit: In terms of presentation, NUnit's TestCase attributes would be optimal, as having the tSQLt test name surface directly in TestExplorer without prefix as an individual test would be ideal. Unfortunately, we cannot data-drive TestCase. Instead, we may only use TestCaseSource, which seems to format every test with [TestMethodName]("[tSQLtTestName]").

Answer 2

Steph locke pretty much has this wrapped up for you:

https://itsalocke.com/unit-testing-in-ssdt-a-quick-intro/

Highlights:

• use tSQLt for mocking (it is awesome)
• grab my test runner for runnint tSQLt tests (no trx format but its a start) https://the.agilesql.club/Projects/tSQLt-Test-Runner/About - this lets you run the tests from within visual studio / test explorer window
• For code coverage use SQLCover (https://the.agilesql.club/blogs/Ed-Elliott/2016-04-08/SQLCover-Code-Coverage-For-SQL-Server-T-SQL)

ok?

ed