Unit Testing Your Transactional Database

Dan Mallott

We all have business logic in our databases, whether simple foreign keys, unique constraints, or default constraints, or more complex stored procedures, functions, or views. However, we often do not test that business logic outside of the initial development, when we test as we write. Unlike our application development brethren, we database developers do not have a tradition of continuous testing, or easy-to-reach-for tools to help us on the journey.

Join this session to learn what unit testing is, how to unit test T-SQL code, and how to ensure the most important part – that the tests are run frequently. You will learn how to safely test T-SQL and how to use tSQLt to automate repetitive setup steps. You will learn how to structure database code to be more easily testable. Finally, we will review how to integrate our unit tests into the CI/CD pipeline to ensure our tests are run regularly.

Database code can suffer from unexpected bugs when others change column names, table names, or even database settings, but we can insulate ourselves from some of those issues by unit testing!