The most useful functionality, in my opinion, is the ability to mock or fake objects. See Greg Lucas' post "A mock too far?" for some useful scenarios. There are many other tSQLt tutorials and examples on the DataCentricity web site for Test Driven Database Development (TD3) using mocks.
What do you mean, mock or fake objects?
When running a test or test suite with a SQL testing framework like tSQLt, you arrange your test, act on the item under test, and assert your results match expectations. The framework handles the assertions, teardown and rollback of any items changed by the test. What mocking allows for is the faking of dependent items not central to the purpose of the test. It provides a shell or template object that is a clone of an existing object without the clutter of reams of data, foreign keys or constraints unrelated to the test.
tSQLt allows you to fake tables. Behind the scenes, tables are renamed, constraints are removed and all columns are set to nullable. This allows for you to assert / check only the columns and rows of data required for your test. Instead of sifting through millions of rows looking for your changes, you can assert that your input row was added to the destination table with a simple count. You can create an expected table, and use tSQLt's AssertEqualsTable to compare that the columns you are testing contain your expected results. You can take your expected results from a table, fake the table, then ensure that the results are repeated after execution of the object under test.
It's kind of like Where's Waldo, with only a white page and Waldo sitting in the middle of it. It becomes very obvious when things are and aren't working, since the noise has been removed from your test.
tSQLt allows you to spy on procedures. Behind the scenes, procedures are emptied out, and only the shell or "contract" remains. The parameters of the procedure remain, with an insert into a logging table. This allows you to inspect what is being sent to procedures to ensure the item under test is passing along the expected parameters. It also allows you to inject code into dependent procedures, perhaps to test functionality or changes that would exist in the future once that other team gets finished with their deliverable.
These types of actions follow the Test Double pattern.
How can I use this when debugging SQL code?
Fake out and spy on all dependent objects to get a better understanding of how the procedure under test is acting against them.
I have found Faking and Spying are also useful when debugging and understanding poorly documented procedures, since you can interactively view your data during a test, roll back the changes, and visualize what occurred in the destination table. If you are working with a large, legacy procedure or set of procedures, this tool becomes invaluable for tracing data lineage and transformation logic. A side effect is you'll probably end up with some automated unit tests on your legacy code.
Martin Fowler describes these mocks or fakes as a kind of Stunt Double, taking the bombardment of your tests while your true actor is sitting on the sidelines.
In the past, I used synonyms for similar purposes, however creating fakes for testing rather than replacing synonyms is much a much easier task with tSQLt.
If you are using SQL Server for more than just a big dumb ORM storage or CRUD layer, it's worth looking at a unit testing framework such as tSQLt and the ecosystem around it.
- Seb Rose has a great article around Behavior Driven Database Development (BD3) including examples of Test Data Builder and Data Mother patterns for creating test data.
- Ed Elliott's SSDT & tSQLt - a Serious Double Act and Generating tSQLt Tests from a DACPAC shows how Visual Studio SQL Tests can incorporate the tSQLt framework, and how you can generate test stubs from procedures in a DACPAC.
- The tSQLt Google Group for questions on the framework
- Denis Gobo gets you setup with tSQLt and walks through the basics.
- Steve Jones has some good Youtube videos on the topic, and a recent article about hacking your way into tSQLt on Azure.
No comments:
Post a Comment