Database unit testing: Setting your team up for valued software delivery

There is constant pressure in software delivery to release at speed and often. To take an idea or fix and deliver it into the hands of customers in as little time as possible. However, releasing faster isn’t beneficial if what you’ve developed is of no value to the customer or business or, worse, contains errors.

The changes being made need to be tested both in business sense and in functionality. Without appropriate testing in place you could, for example, risk code smells creeping into production, which will either result in costly downtime or errors which are not easily spotted at first but impact all code created afterwards, jeopardizing business performance. This means teams are spending more time on rework rather than adding value.

This is why you should consider implementing database unit testing as part of your company-wide standardized testing practices.

Introducing good habits – test-driven development

Testing code as a part of continuous integration in application development is fast becoming standard practice, as noted in The ROI of Compliant Database DevOps. Yet, while unit testing is regarded as a best practice, it is not something that is commonly carried out in database development. Unit testing isn’t just an extension of DevOps, however, but a practice that should be introduced everywhere as standard.

This behavior changes the way developers approach coding in general, improving the overall quality of what is being delivered before automation is even introduced. Errors and breaking changes can be caught early, preferably in sand-boxed development environments, before making it further up the release pipeline.

Test-driven development (TDD) is about teams fully exploring the problem they are trying to solve before they write the code itself. How are they approaching the problem, and structuring the solution? What are they trying to achieve and, importantly, what does success look like? These changes are validated in development, both in business sense and functionality, and are ready for more rigorous testing in the appropriate test and QA environments. As a result, releases to production are more likely to be valuable and less likely to cause any immediate or underlying issues.

When we talk about introducing a company-wide standardized testing practice, this doesn’t mean a generic set of tests to be run for every change. Rather, it’s a standardized method of testing and providing the appropriate tools to development teams to write their own tests.

Feedback loops

Having dedicated testing or QA environments within the development pipeline is a common approach to testing database code before it reaches production. Development happens in dev. Testing happens in test. By removing the segmentation, however, vital feedback can be made immediately available, proving the value of any prospective change or allowing teams to make the necessary amendments there and then.

With no delay, developers are working more effectively and not putting new tasks on hold while waiting on validation of work they deem ‘completed’.

Unit testing is not a replacement for test or QA, but rather another layer of testing to work alongside the existing practices for more rigorous or specialized testing that occurs further up the pipeline as normal.

Shifting left – the foundation for automation

According to the 2019 State of Database DevOps Report, just 19% of participants practice test automation in their database development. This could be because moving to full automation can be too big a jump if the proper foundations are not laid first.

Creating a standardized approach to testing will allow for a smaller and smoother move to automation when ready. Without having appropriate standards in place, errors can slip through into production at a faster, more frequent rate. More time would be spent bug hunting, unpicking code or troubleshooting, and the benefits of automation can be lost in reactive rework.

It’s important to note that when reaching the state of automating tests, multiple disparate methodologies are not overlapping when approaching the same problem. Take a step back to review and put processes in place upfront.

Unit testing is an important part of a standardized, reliable process because it aids developers to ‘shift left’. It introduces known, quantifiable, and repeatable practices in order to catch errors and improve the quality of the code they are delivering. When automation is introduced, outputs are readable, so catching and fixing errors are much easier.

Changing behaviors is challenging but if teams are supplied with the correct tooling, it is easier to introduce good habits and make that shift. For example, SQL Test equips developers with the ability to write their own tests without the need to learn new technologies, and work in a language familiar and comfortable to them.


Database unit testing can be implemented alongside other standardized development practices such as version control, defined company-wide coding styles, and dedicated development environments. In conjunction with these practices, development teams are turning out higher quality code, reducing the risk of errors and subsequent downtime, and, importantly, collaborating to produce innovative valuable releases for the customer.

If you’d like to know more, this blog post explains the four steps to laying the foundations for standardized database development, and outlines the best practices for doing so.

Ready to discuss how to standardize your development processes? Take a look at our solution pages or get in touch to discuss your business challenges and requirements.

Tools in this post

SQL Test

Unit test add-in for SQL Server Management Studio

Find out more