- Features
- Dependencies
- Setup
- Quick Use
- What is TSQLt
- TSQLt Test Adapter
- Configuring a Database Solution
- Appendix
Just a few tools to make using TSQLt easier for database unit tests in Visual Studio SQL Projects. The Goal is to make SQL unit testing as much like C# just in TSQL. No GUI with code behind and clicking just code.
This allow the developer test in isolation any structural change to tables or stored procedures refactors for correctness before committing code.
- Visual Studio Templates
- Solution
- Project
- Project Items
- Visual Studio 2017/2019 (VS)
- LocalDB (part of must VS installs)
- TSQLt Test Runner Adapter by Ed Elliott
- add the local extension feed to visual studio
- install the extension "TSQLt Tools"
- File > New Solution > SQL > TSQLt Solution
-
Solution > Add Project > SQL > Unit Test > TSQLt
-
Name "SomeProject.Test"
-
"SomeProject" > References > right-click > add "Database Reference"
-
In Projects
- Base project ("SomeProject")
- "Same Server, Same Database"
-
as needed "SomeProject" > database Item > Add Unit test
TSQL is a SQL Server CLR (database plug-in) that adds a unit testing framework to SQL Server database. This includes, but not limited to:
- Isolating Dependencies (Mocking)
- Assertions
- Expectations
All of this has been bundled up into a database reference (.dacpack file) and the unit testing can be added to you current database solution as a separate SQL Project.
node "tsqlt.dacpack"{
rectangle "tSQLtCLR.dll" <<CLR>>
rectangle "ExpectSomething" <<StoredProc>>
rectangle "MockSomething" <<StoredProc>>
}
To Perform database unit test with TSQLT we need Visual Studio to do the following:
- Visual Studio can create the project database, with all developer changes
- Publish to a database
- Run unit tests in the database as queries
- returned the result and displayed in query explorer.
Steps #1, #2 and #4 are part of Visual Studio. Step #3 is implemented by TSQLt Test Runner Adapter by Ed Elliott.
The extension provides the following:
- enumerates the test classes and tests in a Solution
- collects and results from the invoked sql stored procedures
actor ":Developer" as user
boundary "Visual Studio" as vs
control "Unit Test Adapter" as test
collections "Project Files" as files
database "localDB" as db
== Build ==
user -> vs : Build
vs -> files: Parse
files -> vs : model
== Publish ==
user -> vs : Publish
vs -> vs : diff against model
vs -> db : migrate script
== Run all Tests ==
user -> vs: Run all tests
vs -> test: Enumerate test suites
test -> files: scan for TSQL Tests
vs -> test: Enumerate tests in test suit
vs-> test: Run Suite1
test -> db: exec [Suite1].[Run all tests]
It is important to remember that:
- tests are enumerated from the source code and are current as of the last save
- tests are run from the database and are current as of the last publish
The diagram below illustrates the logical dependencies SQL Project SomeProject.Test
has on SomeProject
and TSQLt
.
database TSQLt as tsqlt
database "Master" as sys
database "//SomeProject//" as proj
database "//SomeProject//.test" as test
rectangle "TSQLt.dll" <<CLR>> as clr
clr <-- tsqlt : <<uses>>
sys <-- tsqlt: <<uses>>
sys <-- proj: <<uses>>
proj <|-- test: <<includes>>
tsqlt <|-- test: <<includes>>
caption: Logical Dependencies in a TSQLt SQL Project
The database reference to TSQLt
should be satisfied by a reference to
\\<build_server>\build\Reference\Dacpacs\tSQLt\$sql_target$\tSQLt.dacpacwhere
$sql_target
is target engine version for the project. Since the unit testing framework is included in the DACPAK there is no need to install the library local a rebuild will pull the latest CLR DLL with the DPACK at build time.
This project structure dependes on database refrences using same database mode. This allows
title Example Project:\n Detailed Logical Dependencies
database tsqlt {
rectangle tSQLt <<schema>> {
rectangle "ExpectSomething" <<StoredProc>>
rectangle "MockSomething" <<StoredProc>>
}
}
database "Master" as sys
database "MyProject" as proj {
rectangle "dbo" <<schema>> {
rectangle "SomeAction" <<Stored Proc>> as unit
}
}
database "MyProject.test" as test {
rectangle SomeAction <<schema>> as test_suit {
rectangle Test1 <<Stored Proc>>
rectangle Test2 <<Stored Proc>>
}
}
rectangle "TSQLt CLR" <<CLR>> as clr
sys <-- tsqlt: <<uses>>
clr <-- tsqlt : <<uses>>
sys <-- proj: <<uses>>
proj <|-- test: <<includes>>
tsqlt <|-- test: <<includes>>
After build all the same database references are in-lined into the top level project before compairing agins the active database for deployments.
title Example Project:\n Detailed Physical Deployment
database "MyProject.Test" as alias {
rectangle "tSQLt.dll" <<CLR>>
rectangle tSQLt <<schema>> {
rectangle "ExpectSomething" <<StoredProc>>
rectangle "MockSomething" <<StoredProc>>
}
rectangle dbo <<schema>> {
rectangle SomeBusinessAction<<Store Proc>>
}
rectangle SomeAction <<schema>> {
rectangle Test1 <<Stored Proc>>
rectangle Test2 <<Stored Proc>>
}
}