<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Jarek Beczek on Medium]]></title>
        <description><![CDATA[Stories by Jarek Beczek on Medium]]></description>
        <link>https://medium.com/@jb_63255?source=rss-606d0fa776d8------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*dmbNkD5D-u45r44go_cf0g.png</url>
            <title>Stories by Jarek Beczek on Medium</title>
            <link>https://medium.com/@jb_63255?source=rss-606d0fa776d8------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 14:41:21 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@jb_63255/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Unit testing the DB with tSQLt]]></title>
            <link>https://medium.com/juliusbaerengineering/unit-testing-the-db-with-tsqlt-07af06f6ee49?source=rss-606d0fa776d8------2</link>
            <guid isPermaLink="false">https://medium.com/p/07af06f6ee49</guid>
            <category><![CDATA[unit-testing]]></category>
            <category><![CDATA[tsqlt]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Jarek Beczek]]></dc:creator>
            <pubDate>Fri, 28 Feb 2025 07:14:25 GMT</pubDate>
            <atom:updated>2025-02-28T07:14:25.383Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Introduction</strong></h3><p>Unit testing is a fundamental practice in software development, as it ensures that individual components function as intended. However, when it comes to databases, testing often takes a backseat. Unit testing is either ignored or done as an ‘integration’ test from the consumer of the data. This is where tSQLt, a robust unit testing framework for SQL Server, comes into play. Designed specifically for database testing, tSQLt enables developers to write and execute automated tests directly within T-SQL. This simplifies the validation of database behaviour, facilitates the early identification of issues, separates the DB testing from consumer code, and guarantees the reliability of code deployment.</p><h3><strong>What is tSQLt?</strong></h3><p>tSQLt is an open-source unit testing framework designed specifically for SQL Server. It enables developers to write and execute tests for database objects such as stored procedures, functions, and triggers directly in T-SQL. Unlike traditional testing approaches, tSQLt provides a native way to isolate and mock dependencies (such as tables, stored procedures, functions, and views), making it easier to test individual components in a controlled environment.</p><p>Key features of tSQLt include:</p><ul><li>Test isolation: With tools like tSQLt.FakeTable, you can fake tables and avoid affecting production data.</li><li>Assertions: tSQLt provides built-in assertions to verify test outcomes, such as checking data values or ensuring certain actions are performed.</li><li>Integration: Tests live alongside your database code, enabling seamless integration with development workflows and CI/CD pipelines. Test results can be generated as JUnit XML and integrated in CI/CD pipelines.</li></ul><p>By offering these capabilities, tSQLt empowers teams to build more reliable databases, reduce bugs, and adopt agile practices in database development.</p><h3><strong>Writing your first test</strong></h3><h4><strong>Installation</strong></h4><p>tSQLt can be installed on an MS SQL Server running in a Docker container. I recommend this approach to get familiar with the tool. Installation is quite straightforward and can be described in four steps:</p><ol><li>Download tSQLt.</li><li>Unzip the file.</li><li>Run the PrepareServer.sql file (requires SA permission).</li><li>Run the tSQLt.class.sql file (requires SA permission).</li></ol><p>PrepareServer.sql automatically enables CLR and installs a server certificate that allows the installation of the tSQLt CLR. Note: tSQLt should never be installed in production.</p><p>After executing those first steps, you can run</p><pre>EXEC tSQLt.RunAll</pre><p>to test if it works correctly. If everything is running fine, you will get a similar response:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ji4lGk5DkOdKmxz9sHw14w.png" /></figure><p>I would highly recommend installing the examples (run the Examples.sql file from the zip file) — the script creates an example database (tSQLt_Example) and tSQLt test cases.</p><h4><strong>Simple test</strong></h4><p>We are going to write a simple test using the example database created by the Examples.sql script.</p><p>First we create a new test class:</p><pre>EXEC tSQLt.NewTestClass &#39;GetParticlesInRectangle&#39;;<br>GO</pre><p>After that we can create our first test:</p><pre>CREATE PROCEDURE GetParticlesInRectangle.[test returns particles included inside the boundaries of the rectangle]<br>AS<br>BEGIN<br>  -- Arrange <br>  EXEC tSQLt.FakeTable &#39;Accelerator.Particle&#39;; -- create a stub of Accelerator.Particle table<br><br>  -- Populate the Particle table with test data<br>  INSERT INTO Accelerator.Particle (Id, X, Y) VALUES ( 1, -0.01,  0.50);<br>  INSERT INTO Accelerator.Particle (Id, X, Y) VALUES ( 2,  0.00,  0.50);<br>  INSERT INTO Accelerator.Particle (Id, X, Y) VALUES ( 3,  0.01,  0.50);<br>  INSERT INTO Accelerator.Particle (Id, X, Y) VALUES ( 4,  0.99,  0.50);<br>  INSERT INTO Accelerator.Particle (Id, X, Y) VALUES ( 5,  1.00,  0.50);<br>  INSERT INTO Accelerator.Particle (Id, X, Y) VALUES ( 6,  1.01,  0.50);<br>  INSERT INTO Accelerator.Particle (Id, X, Y) VALUES ( 7,  0.50, -0.01);<br>  INSERT INTO Accelerator.Particle (Id, X, Y) VALUES ( 8,  0.50,  0.00);<br>  INSERT INTO Accelerator.Particle (Id, X, Y) VALUES ( 9,  0.50,  0.01);<br>  INSERT INTO Accelerator.Particle (Id, X, Y) VALUES (10,  0.50,  0.99);<br>  INSERT INTO Accelerator.Particle (Id, X, Y) VALUES (11,  0.50,  1.00);<br>  INSERT INTO Accelerator.Particle (Id, X, Y) VALUES (12,  0.50,  1.01);<br>  <br>  -- Act<br>  -- The function Accelerator.GetParticlesInRectangle will use fake table Accelerator.Particle from arrange<br>  SELECT Id, X, Y<br>    INTO #Actual<br>    FROM Accelerator.GetParticlesInRectangle(0.0, 0.0, 1.0, 1.0);<br>    <br>  -- Assert<br>  SELECT TOP(0) *<br>    INTO #Expected<br>    FROM #Actual;<br>    <br>  INSERT INTO #Expected (Id, X, Y) VALUES (3,  0.01, 0.50);<br>  INSERT INTO #Expected (Id, X, Y) VALUES (4,  0.99, 0.50);<br>  INSERT INTO #Expected (Id, X, Y) VALUES (9,  0.50, 0.01);<br>  INSERT INTO #Expected (Id, X, Y) VALUES (10, 0.50, 0.99);<br>    <br>  -- Compare the data in the #Expected and #Actual tables<br>  EXEC tSQLt.AssertEqualsTable &#39;#Expected&#39;, &#39;#Actual&#39;;<br>END;<br>GO</pre><p>In the arrange part, we are using the tSQLt.FakeTable stored procedure to create a stub of the Particle table. From that line onwards, every use of the Particle table will go to the created stub and not to the actual table.</p><p>The act part executes the function for testing, which internally uses our stubbed Particle table.</p><p>Finally, in the assert part, we compare the expected results with the actual results.</p><p>Please note that the name of the test stored procedure has to start with ‘test’.</p><p>To execute our newly written test we use the tSQLt.Run stored procedure:</p><pre>EXEC tSQLt.Run GetParticlesInRectangle;</pre><p>If all operations proceed without error, we anticipate an output that closely resembles the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2IU8MK2DNyzRYZT2K4k1iQ.png" /></figure><h3><strong>Faking and isolation in tSQLt</strong></h3><p>One of the standout features of tSQLt is its ability to fake and isolate database objects directly in the database during testing. In traditional database testing, dependencies like foreign keys, triggers, and existing data can make it difficult to test specific components in isolation. tSQLt addresses this challenge by providing tools that help you create a controlled testing environment.</p><p>The tSQLt.FakeTable procedure is a key tool for faking tables. It creates a lightweight, temporary version of a table that ignores constraints such as primary keys, foreign keys, and triggers. This lets you insert and manipulate test data without worrying about the underlying database schema.</p><p>Database objects often depend on each other, making unit testing tricky. With tSQLt, you can isolate dependencies to test one component at a time. For example, you can fake a stored procedure using tSQLt.SpyProcedure, which tracks calls to the procedure without executing its actual logic.</p><p>Benefits of tSQLt faking and isolation capabilities:</p><ul><li>Simplicity: Focus on testing the logic of one object without being blocked by dependencies.</li><li>Speed: Avoid running complex logic or large data transformations, reducing test execution time.</li><li>Safety: Ensure tests do not accidentally modify or rely on production data.</li></ul><p>By utilizing faking and isolation techniques, tSQLt enables developers to construct reliable and maintainable unit tests for even the most intricate database logic.</p><p>To sum up, tSQLt provides the following objects to help with isolating dependencies:</p><ul><li>SpyProcedure — to create independent tests, we can replace the functionality of a stored procedure with a spy. The spy will record the parameters that were passed to it. It can also return OUT parameters.</li><li>RemoveObjectIfExists/RemoveObject — utility provided by tSQLt to safely ‘remove’ an object (e.g. a table, procedure, or function) from the database if it exists. This is particularly useful in unit testing scenarios where you want to ensure a clean slate before creating or testing a database object.</li><li>FakeTable — a powerful feature in the tSQLt framework that allows you to create a ‘faked’ version of a database table for unit testing. It simplifies testing by removing dependencies on the table’s constraints, triggers, or existing data, enabling the isolated testing of logic that interacts with the table.</li><li>FakeFunction — used to fake or override the behaviour of a scalar user-defined function (UDF) during unit testing. It allows you to control the return values of the function, making it possible to isolate the logic you are testing from external dependencies.</li><li>ApplyTrigger — re-enables a database trigger that was previously disabled for testing purposes. This is especially useful when you’ve used tSQLt.FakeTable, which disables triggers by default, and you need to test how a specific trigger behaves when applied to a table.</li><li>ApplyConstraint — used to re-enable constraints on a table that were previously disabled, typically by using tSQLt.FakeTable. This allows you to selectively test the behaviour of specific constraints while keeping others inactive for isolation purposes.</li></ul><h3><strong>Integrating tSQLt with CI/CD</strong></h3><p>Integrating tSQLt with a CI/CD pipeline is a real game-changer for database development. It means you can automate testing at every step of the deployment process. The first thing you need to do is make sure your tSQLt test suite is version-controlled with your database code. This way, you can always run the tests in the same way, no matter what environment you’re in. Tools like Azure DevOps, Jenkins, or GitHub Actions can be set up to run tSQLt tests as part of the build or release process. This way, you can catch any problems early on and stop any bad changes from getting to production.</p><p>Exporting test results in formats compatible with CI/CD tools, such as XML (JUnit) or JSON, makes it easy to monitor outcomes and display reports in dashboards. If a test fails, the pipeline can halt the deployment, giving developers an opportunity to address issues before they escalate. To further streamline the process, consider using a dedicated testing database in the pipeline, ensuring your tests run in isolation without affecting production or staging environments.</p><p>Integrating tSQLt into CI/CD not only enhances the reliability of your deployments but also enforces a culture of continuous improvement. Automated database testing ensures that every change is thoroughly validated, reducing risks and making your development process more agile and efficient</p><h3><strong>Common challenges and solutions</strong></h3><p>Using tSQLt can revolutionize how you approach database testing, but like any tool, it comes with its own set of challenges.</p><p>One common hurdle is managing schema changes. As database schemas evolve, tests can break, requiring frequent updates. This can be mitigated by leveraging tSQLt.FakeTable to decouple tests from schema constraints and using version control to track changes systematically.</p><p>Another challenge is setting up test data, which can be time-consuming and prone to errors. By utilizing tSQLt.FakeTable and reusable test data scripts, you can streamline this process and ensure consistency across your tests.</p><p>Debugging test failures is another tricky area, especially when dealing with complex logic. Breaking down large tests into smaller, focused units and using tSQLt’s built-in assertions can help pinpoint issues more effectively, as debugging inside SQL Server is not possible (or not easy). Dependencies between database objects, such as foreign keys or triggers, can also complicate test isolation. Tools like tSQLt.ApplyTrigger and tSQLt.FakeFunction allow you to control these dependencies, enabling you to focus on the specific unit of logic being tested.</p><p>Performance can become a concern as your test suite grows. To address this, consider optimizing database queries, running tests in parallel, and pruning outdated or redundant tests to keep execution times manageable.</p><p>Finally, incorporating tSQLt into CI/CD pipelines may require extra effort, but tools like Azure DevOps or Jenkins can automate test execution, ensuring your database is validated with every deployment. By addressing these challenges with the right strategies, you can unlock the full potential of tSQLt and create a robust, efficient testing workflow.</p><p>However, the biggest challenge I’ve faced while trying to incorporate tSQLt in an existing and mature project was the amount of work to fake all the tables and move the logic contained in large and complicated stored procedures to smaller units that can be tested independently. I would definitely recommend tSQLt when starting a project from scratch as soon as the need to have logic in the database arises.</p><h3><strong>Conclusion</strong></h3><p>tSQLt is a unit testing framework for SQL Server that enables developers to write and execute automated tests directly in T-SQL. It provides test isolation, assertions, and integration with development workflows. tSQLt empowers teams to build reliable databases and adopt agile practices in database development.</p><p>tSQLt provides faking and isolation techniques to simplify database testing. It enables developers to create independent tests by replacing stored procedure functionality with spies, removing objects from the database, and creating faked tables. Integrating tSQLt with CI/CD pipelines automates testing and ensures that every change is thoroughly validated, reducing risks and making the development process more agile and efficient.</p><p>Another interesting aspect of tSQLt is the way it is implemented in MS SQL Server and the ease of installation.</p><p>Incorporating tSQLt into an existing project requires significant effort to refactor stored procedures. It is recommended for new projects to use tSQLt from the start.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=07af06f6ee49" width="1" height="1" alt=""><hr><p><a href="https://medium.com/juliusbaerengineering/unit-testing-the-db-with-tsqlt-07af06f6ee49">Unit testing the DB with tSQLt</a> was originally published in <a href="https://medium.com/juliusbaerengineering">Julius Baer Engineering</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>