Transcript Unit Testing with tSQLt

Unit Testing with tSQLt
Or, “How I
˄
Became a Better SQL Developer...”
by Mark Fugel
[email protected]
Course Abstract: Unit Testing with
 This session is for anyone interested in getting started in setting up an
environment where you wish to establish unit testing for SQL.
 It will include how to install, setup, create, run and even come to
depend on (& maybe even enjoy!) having your unit tests in place to
"have your back"!
 Real examples will used to show how unit tests can be used to test
stored procedures and functions.
 We will discuss how tSQLt can even be used to enforce development
standards in your team.
 We will show how it can even be integrated into your automated build
and release processes (such as Team City).
2 | 5/14/2015 | Unit Testing with tSQLt by Mark Fugel
About me…
Mark Fugel Rutgers ’77 St Peter’s ‘86
Database Developer/Development DBA
Application Developer
Old fart: After leaving teaching in 1984, worked
with various companies including 17 years with
AT&T, 3M, & currently in a TnwDevLabs agile
team @ The Network
 Email: [email protected]
 http://blog.tnwdevlabs.com/




3 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
My Story starts when…
• Returning to my old company after 6 years, I found a different
company & culture – developer-driven & agile focused using all
the “latest” languages, tools, techniques and practices.
• Assigned project to establish data transfer from RavenDb
(NoSQL) front-end application to reporting back-end system in
SQL Server using MS Service Broker messaging architecture.
• Created a multitude of “shredder” stored procedures” as
Service Broker Queue Activation procedures to transfer data
from JSON to XML and populate SQL tables.
4 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
My Challenge…
Developer Leads’ Questions to me:
My Feeble Initial Responses to them:
• If you’re implementing code in sp’s,
triggers, functions – are you testing
your code as we do the app code?
• I test all my code.
• Our tests are part of our application
and in source control. Will yours be?
• I can check-in test scripts as part of
my code too if that’s what you want.
• Can your tests be run by others?
Can they be automated?
• Uh…sure. I guess so. Huh?
• We practice TDD. Will you be doing
the same with your project?
• TDD…hmm… I guess so. If that’s
what our shop does, then by golly, I
will do it too!
5 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
My “Real” Initial Reaction…
•
I do quality work. They just don’t
know me.
•
I do test. Ok…they are manual
scripts but I make sure
everything I do works.
•
This is DB work. It’s different.
•
I don’t have the same tools they
are lucky to have.
•
It’s just me. They have a lot
more people. It will take way too
long for me to setup and do all
that testing alone, even if I had
a way.
6 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
Their “Real” Belief
7 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
Their “Real” Thoughts of My Methods
8 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
Their Expectation & Demands
If it's worth building, it's worth testing.
If it's not worth testing, why are you
wasting your time working on it?
Test as often and early as
possible, and better yet,
test first.
Test with a purpose!
9 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
What exactly were they looking for…
What Is Test-Driven Development?
What Test-Driven Development Is NOT?
• Not a panacea. Not bug-free code.
• Not integration, load or acceptance testing.
10 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
My “Conversion”…
My Initial Reservations
•
•
•
•
•
My Actual Experience
•
Provides Earlier Bug Notification
•
Leads to Better Design, Cleaner & More
Extensible Code, & more Stable product
•
Gives me Confidence to Refactor
•
Contributes to Team Work &
Documentation
•
Leads to Greater Developer Satisfaction
Too much legacy code – setting up unit
testing would be overwhelming.
•
Easier than I anticipated
Lack of experience & no one does it
•
Eat the elephant one byte at a time!
No time or desire to, yet again, learn
something new - just to write tests!
Writing tests takes much too long and slows
down the development process. We have
real deadlines.
DB Unit testing is too hard because it
involves setting up lots of test data and
dependencies.
11 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
The Quest Began…
Searching for a SQL Unit Testing Framework
 Similar to xUnit, NUnit frameworks used in C#
 Test Setup &Teardown should be easy
 Arrange, Act, Assert (AAA) Model for testing
 No new language to learn/same IDE
preferably
 Robust Assertion Engine
 Standard human readable output
 No special debugging code in our prod code
 Has Mocking/Faking & should be simple
 Run tests singly, in groups, or all & in any
order
 No dependencies between tests
12 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
 Can be automated & output redirected to
reporting
 Good documentation & industry support
The Quest Continued…
DB Unit Testing Candidates:
 DbFit
 SQLUnit
 TSQLUnit
 Visual Studio Unit Testing
 tSQLt
13 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
And the Winner was…
Why tSQLt ?
Rich testing framework exclusively for SQL
Server
Reusable Setup functionality available.
Tests are written in T-SQL
Variety of Assertions for varied testing
scenarios.
Tests can be versioned & stored in source
control
Allows for testing of non-Happy path
exceptions
Tests are executed as stored procedures
Easy to separate test objects from
production code
No need to work in another tool – can work
in SSMS or Visual Studio. (or Redgate’s
SQLTest)
Tests can be run one at time or all at once
Test output in XML
Test Isolation. Each test is a transaction.
14 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
Installing tSQLt…
1. Download the latest version at SourceForge.
 http://sourceforge.net/projects/tsqlt/files/
2.
3.
4.
5.

Select a DB in SSMS to install it in.
Run SetClrEnabled.sql
Run tSQLt.class.sql
That’s it! You’re ready to test!
Optional: Download tSQLt.PrepareTableForFaking.sql at
http://harouny.com/2013/04/19/tsqlt-taketable-indexed-view

This stored proc is necessary when testing objects with dependencies; specifically,
objects with SCHEMABINDING (ex. Indexed Views).
15 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
What did we get with tSQLt…
All tSQLt commands
are tSQLt Stored
Procedures !
16 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
Setting up our first Unit Test…
1. Each test is a stored procedure.
2. Each test must have a name beginning with ‘test’.
3. Each test should only try to answer one question or
requirement
4. All together, your tests should cover all paths
17 | 5/14/2015 |
•
Happy path
•
Errors/Exceptions
•
Boundaries/Extremes
Unit Testing with tSQLt by Mark Fugel
Things to think about…
Remember the Definition of a Unit Test
A Method (Code)
that tests specific functionality,
has clear pass/fail criteria
and runs in isolation
3 Areas of Every Unit Test:
rrange
18 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
ct
ssert
First, create a tSQLt Class:
• tSQLt Class is nothing more than a “schema”.
• Provides way to categorize your tests into groups
• Make them meaningful
• I recommend starting them with “tSQLt_”
• You create a new Class by issuing the command:
exec tSQLt.NewTestClass ‘tSQLt_YourTestClassGoesHere’
19 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
Next, Create a Test Using a Template:
-- For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/
CREATE PROCEDURE [mytestclass].test_my_test_template AS
BEGIN
-- <<ARRANGE>> or Assemble
-- This section is for code that sets up the environment.
-- It often contains calls to methods such as tSQLt.FakeTable and tSQLt.SpyProcedure along with INSERTs of relevant data.
-- For more information, see http://tsqlt.org/user-guide/isolating-dependencies/
---------------------------------------------------------------------------------------------------------------------exec [tSQLt].[Private_Print] 'Running SQL Test: [mytestclass].[test_my_test_template]'
exec [tSQLt].[Private_Print] '
Purpose:<<<Test Purpose Goes Here>>'
exec [tSQLt].[Private_Print] ' '
--Test Exec SQL:
-exec tSQLt.Run '[mytestclss].[test_my_test_template]'
----------------------------------------------------------------------------------------------------------------------*/
-- Create new Expected table here
CREATE TABLE dbo.Expected (<<< DECLARE COLUMNS OF EXPECTED TABLE HERE>>>)
-- Populate Expected Table here
INSERT into dbo.Expected
SELECT
<<< POPULATE COLUMNS OF EXPECTED TABLE HERE>>>
-- Create empty Actual table
SELECT TOP 0 *
INTO dbo.Actual
FROM dbo.Expected
-- Fake any other stored procedures/functions here if needed
-- Fake tables here if needed
20 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
---------------------------------------------------------------------------------------------------- <<ACT>>
-- Execute the code under test like a stored procedure, function or view
-- and capture the results in variables or tables.
----------------------------------------------------------------------------------------------------*****FUNCTION/STORED PROCEDURE TO TEST GOES HERE************************
EXEC <<<dbo.myprocecuretotest ‘myparam1’>>>
--***************************************************************
-- Get the rows just affected by test and put into Actual table for comparison w/Expected
INSERT INTO dbo.Actual
SELECT <<<GET RESULTS INTO ACTUAL TABLE>>>
---------------------------------------------------------------------------------------------------- <<ASSERT>>
-- Compare the expected and actual values, or call tSQLt.Fail in an IF statement.
-- Available Asserts: tSQLt.AssertEquals, tSQLt.AssertEqualsString, tSQLt.AssertEqualsTable
-- For a complete list, see: http://tsqlt.org/user-guide/assertions/
---------------------------------------------------------------------------------------------------- EXEC tSQLt.Fail ‘<<<Test Not implemented>>>’;
EXEC tSQLt.AssertEqualsTable @Expected = N'[dbo].[Expected]',
@Actual
= N'[dbo].[Actual]',
@FailMsg = N'Tables do not match’
DECLARE @actual_count int = (SELECT COUNT(*) FROM dbo.Actual)
DECLARE @expected_count int = <<<PUT EXPECTED COUNT HERE>>>
EXEC tSQLt.AssertEquals @expected_count, @actual_count
END;
GO
21 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
Finally, Running Unit Test(s)…

Run a single test:
Run
tSQLt
exec tSQLt.Run ‘YourTestGoesHere’

Run only unit tests that belong to the same test class:
exec tSQLt.Run ‘YourTestClassGoesHere’

Run All Unit Tests:
exec tSQLt.RunAll
22 | 5/14/2015 | Unit Testing with tSQLt by Mark Fugel
Run
tSQLt
Demo
Setup
Current application returns the Top 10 Most Expensive Products
Current application utilizes this Stored Procedure:
CREATE procedure [dbo].[Ten Most Expensive Products] AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts,
Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC
New requirements are delivered to you:
• Allow for variable number of products to be returned based on input
• Columns returned should be ProductName, CategoryName and UnitPrice
• Error if no input or zero is provided as input
23 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
Demo
24 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
Other tSQLt commands…
Assertions
Expectations
Isolating Dependencies
AssertEmptyTable
ExpectException
ApplyContraint
AssertEquals
ExpectNoException
FakeTable
AssertEqualsString
Test Creation/Execution SpyProcedure
AssertLike
NewTestClass
FakeFunction
AssertNotEquals
DropClass
RemoveObjectIfExists
AssertObjectDoesNotExist
RunAll
ApplyTrigger
AssertObjectExists
Run
RemoveObject
Fail
RenameClass
PrepareTableForFaking*
AssertResultSetsHaveSam
eMetaData
25 | 5/14/2015 Unit Testing with tSQLt by Mark Fugel
For full explanation of each with examples, see:
http://tsqlt.org/user-guide/
Using SQL Test
Redgate’s SQL Test
 Add-in for SSMS
 UI for tSQLt framework
 Easier to interface
with tSQLt tests
26 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
Using SQL Cop
 Series of tSQLt tests (by G. Mastros) to highlight
potential problems or violations of best practices.
 Detects “Issues” in:
• Code • Columns • Tables/Views • Indexes • Configuration • Db Health
 Download all 48 tests here:
dl.dropbox.com/u/58229865/SQLCop%20Tests.zip
 Use only the ones that make sense for your situation
 Demonstrates how you can create your own tests to
enforce your own shop’s DB standards and/or naming
conventions.
27 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
Integrating tSQLt with CI
•
Our team utilizes JetBrain’s
TeamCity to do our builds and
deployments
•
tSQLt runner plugin for TeamCity
•
Step by step instructions in
David Green’s Simple Talk
excellent article:
https://www.simpletalk.com/sql/sql-tools/using-sqltest-database-unit-testing-withteamcity-continuous-integration/
28 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
Uninstalling tSQLt…
 Easily uninstall all tSQLt framework objects by
running uninstall script from database:
EXEC tSQLt.Uninstall
29 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
References & Resources…
Topic
Link
tSQLt
Tsqlt.org
SQL Test
http://www.red-gate.com/products/sql-development/sql-test
SQL Cop
http://sqlcop.lessthandot.com/
Why tSQLt? By Greg Lucas
https://www.simple-talk.com/sql/t-sql-programming/test-driven-databasedevelopment-%E2%80%93-why-tsqlt/
Getting Started with tSQLt by Robert
Sheldon
https://www.simple-talk.com/sql/t-sql-programming/getting-started-testingdatabases-with-tsqlt/
Getting Started with Test Driven Design in
Sql Server by Sebastian Meine
http://sqlmag.com/t-sql/getting-started-test-driven-design-sql-server
Pluralsight tSQLt Course
http://www.pluralsight.com/courses/unit-testing-t-sql-tsqlt
Using SQL Test Database Unit Testing with
TeamCity Continuous Integration by David
Green
https://www.simple-talk.com/sql/sql-tools/using-sql-test-database-unit-testingwith-teamcity-continuous-integration/
Agile Sql Club – Ed Elliott’s blog
https://the.agilesql.club/taxonomy/term/1
DB Unit Testing for Sql Server Using tSQLt
Robert C Martin Series By Dennis Lloyd
http://www.bookdepository.com/Database-Unit-Testing-for-SQL-Server-UsingtSQLt-Dennis-Lloyd/9780133564327
30 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
Final thought…
31 | 5/14/2015 |
Unit Testing with tSQLt by Mark Fugel
Special thanks to our Sponsors!