DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
What's in store for DevOps in 2023? Hear from the experts in our "DZone 2023 Preview: DevOps Edition" on Fri, Jan 27!
Save your seat
  1. DZone
  2. Data Engineering
  3. Databases
  4. Database Testing With tSQLt

Database Testing With tSQLt

This article provides a hands-on examples of how to follow best practices in unit testing your database using tSQLt.

Halima Koundi user avatar by
Halima Koundi
·
Oct. 08, 16 · Tutorial
Like (4)
Save
Tweet
Share
7.62K Views

Join the DZone community and get the full member experience.

Join For Free

Evolutionary Database Development, as coined in Martin Fowler’s book Refactoring Databases, is a concept whereby the design of the database is not done upfront, but instead is done in a way that evolves as the software that is relying on the data store does. In order to have flexibility and to allow changes in the database to be made safely; you need to have a test suite to run and check for regressions. In this post we will have a look at a testing framework for Microsoft SQL Server databases.

Environment

Installing MS SQL Server

Get started installing SQL Server Express. SQL Server Express is a free lightweight version of Microsoft’s SQL Server database.

Installing MSSQL Server Manager

SQL Server Management studio provides us with a powerful user interface to access, manage and configure our MS SQL database server. If you don’t already have it, install it. Please note this will take some time.

Downloading tSQLt

We are going to use the tSQLt unit testing framework to test our Microsoft SQL Server database. We first need to get the download from the official website.

The tSQLt open source framework allows us to run our tests within transactions which makes our tests independant and takes care of cleaning up after it runs. The framework also gives us the ability to isolate our tests through fake tables and stored procedure (SP) spies.

Running the Test Sample

Once you have SQL Server Express up and running, and MSSQL Management Studio installed, open the latter and connect to your server.

The tSQLt team provides a quick start example database and a set of tests to help developers get started quickly on how the framework works. Let’s try the example together. First unzip the tSQLt folder you downloaded, and make sure that CLRs are enabled on your development server. Enabling CLR integration allows us to run managed code, such as C#, on our SQL Server database.

Open the Example.sql file that you will find within the unzipped folder, and execute it in your database server. This will create a test database named tSQLt_Example.

Once your database is created, open a new query window on that DB and run the following command: EXEC tSQLt.RunAll This is a command to run all the tests. You should see a failing test in the result screen:

Test case number 11, named test ready for experimentation if 2 particles, is failing. Let’s open the file and see what’s wrong with it; we will need to navigate to the stored procedures folder to find the test.

The failing test is calling IsExperimentReady and checks that it returns 1 when the particle table has two particles. Looking at the function, it seems that it is not counting the number of rows in the Particle table, and it is not doing the right checks.

Once we’ve solved the issue, we can run the tests again and see that they are all passing now.

What is This Test Doing and How is the Fake Table Working?

Tests in the tSQLt framework are run within transactions. The function we are testing relies on the Particle table to determine whether the system is ready for experimentation. Looking at our test here, we can see that we are creating a fake table of the Accelerator.Particle in which we will insert two rows.

If we have closer at look at the tSQLt.FakeTable SP, it is renaming the original table we are faking, and copying its structure into a new table of the original name without applying any of the constraints. Then we run the test by calling the function under test, knowing that it should use the table we just faked. After the test has run wrapped inside the transaction, this transaction is rolled back. This will revert all the changes made to the mocked table. The test results that were stored in temporary fields are then saved in a test result table.

In this first blog post we learned about database testing, and how the tSQLt testing framework works. In the next post, we will go beyond the quick start example and see how to approach legacy transact-SQL, implementing tests into an existing database.

This article was first published on the Codurance blog.

Database unit test Microsoft SQL Server sql

Published at DZone with permission of Halima Koundi, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Top Five Tools for AI-based Test Automation
  • Unleashing the Power of JavaScript Modules: A Beginner’s Guide
  • What Java Version Are You Running? Let’s Take a Look Under the Hood of the JDK!
  • Using QuestDB to Collect Infrastructure Metrics

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: