One of the reasons why there aren't more unit testing solutions out there for SQL development is because proper unit testing is inherently harder with databases so people don't do it. This is because databases maintain state and also referential integrity. Imagine writing a unit test for a stored procedure (order_detail_update_status) that updates a status flag on an order_detail table. The order_detail table has a dependency on the order_header and product tables, order_header in turn has foreign keys to customer and employee whilst the product table may depend on product_category, product_type and supplier. That is at least seven tables (probably more) that need to be populated with valid data just to write one test and all but one of those tables have nothing to do with the code under test.
So what you should be looking for in a unit testing solution is exactly that - the ability to test discrete units of code - with the minimum of set-up. So ideally, you would be able to just set up the required test data in order_detail and ignore the rest of the tables - I am aware of only one testing framework that allows you to do that.
Additionally, unit tests should have minimal reasons to fail, in the above example, order_detail_update_status just updates a single row on the order_detail table. If a new not null column is added to the customer table, which is not handled by the test set-up then you have a scenario where our test could fail for a totally unrelated reason. This makes for very brittle tests and, under the pressure of tight delivery deadlines, developers will quickly give up writing and maintaining tests.
A suite of unit tests should be runnable in any order, with no interdependencies and a good test framework should support this along with set-up, tear down and support for mocking objects (which may or may not be part of the same framework). In the above scenario, the ability to mock the order_detail table to test a module that only touches the order_detail table is one of the most important features if you don't want to spend huge amounts of time fixing tests that are failing for no "good" reason.
So in terms of your requirements, and the above points, there is only one framework that I am aware of that does all of this - tSQLt. This is based on real-world experience - we had over 6,000 tSQLt unit tests on my last project. It includes the following feautures:
- Unit test stored procedures, functions and views
- Mock tables, views and functions
- Mock (or spy) stored procedures - either for isolation,
replay or pre-defined outcomes
- Suite set-up
- Automatic tear-down (as every test runs in it's own translation)
- Unit tests are completely isolated and can be executed in any order
It works very well with VSTS in a CI/CD and, as all the unit tests are written in T-SQL, it is very easy to use.
The best way to use tSQLt in Visual Studio is to make use of composite projects - where application database objects and modules are maintained in one project whilst the tSQLt framework and all unit tests are part of a second project. There is a good aticle to get you started on this here.
I wrote a more detailed article on the benefits of tSQLt for Simple-Talk a few year back which might also be helpful
