Welcome back to Teatime! This is a weekly feature in which we sip tea and discuss some topic related to quality. Feel free to bring your tea and join in with questions in the comments section.
Tea of the week: I like a nice, spicy chai in the winter to warm me up, especially when I’m grappling with rough questions like today’s topic. Rather than subject myself to excessive caffeine on a cold afternoon, I’m sipping on Rooibos Chai from Sub Rosa Tea. Just the smell alone makes me more alert and ready to take on the day, and the flavor does not disappoint!
Today’s Topic: Testing SQL – How and why?
In today’s teatime, I wanted to touch on an often-overlooked part of the stack: the database. At my current company, we have a separate development team that focuses entirely on stored procedures and database development, above and beyond the usual operational DBAs. And yet, before I became the QA coordinator, all our database testing was still manual! I set about researching how we could test the business logic stored in our stored procedures.
Two main approaches
There are two overarching approaches to database testing. The one I chose not to focus on due to our company’s setup was to test the database from outside the database. This is particularly popular in .net shops, as Visual Studio includes many tools that make it easy to unit test your stored procedures in the same suite you’re using to unit test your code. I would imagine this would also be useful in a Node shop, as you could do much the same thing. Most of our database access comes from our Coldfusion API layer, which is a little more challenging to set tests up in; furthermore, the Coldfusion code was maintained by different people than the SQL, and the SQL team was not comfortable enough in Coldfusion (or .Net) to write their tests there.
The other approach, the one I will be focusing on in this talk, is to test the database from within the database: using something like the popular tSQLt framework to write tests in SQL that test your SQL. This is a similar approach to how unit testing in other layers work; it’s very rare to see unit tests written in a different language than the code under test. Furthermore, you can keep the unit tests right next to the code, just like you would in otehr layers. It provides less overhead in the form of context-switching between writing code and writing SQL, which is great when you specialize in SQL itself.
How to write unit tests
In any language, there’s basically three phases to a unit test:
- Arrange the environment by performing any setup steps or preconditions that are required,
- Act on the system, usually by invoking the item under test, and
- Assert that the result was within acceptable parameters
In this sample unit test (from the tSQLt documentation), you can see the steps in action:
CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given conversion rate] AS BEGIN DECLARE @actual MONEY;
DECLARE @rate DECIMAL(10,4);
SET @rate = 1.2;
DECLARE @amount MONEY;
SET @amount = 2.00;
SELECT @actual = FinancialApp.ConvertCurrency(@rate, @amount);
DECLARE @expected MONEY;
SET @expected = 2.4; --(rate * amount)
EXEC tSQLt.AssertEquals @expected, @actual;
END;
GO
First we arrange the environment by declaring some variables, setting them to the amounts needed for the test. We act by calling the procedure (FinancialApp.ConvertCurrency), and then we assert that the actual response was what we expected (with a comment about why we expected it to be that).
Note how the expected result is a solid number, not the result of doing some math. If the math were wrong in the procedure, duplicating the logic here wouldn’t help us test anything. Instead, work the algorithm by hand, coming up with the expected outcome, and hard-code it into the test. That ensures that no mistakes were made implementing the algorithm as it was on paper.
One of the things you’re not seeing is that when this is run, it’s wrapped in a transaction, which is automatically rolled back at the end of the execution. This prevents any side effects from affecting your data, such as insertion of records into a table. The library also provides functions for mocking out tables and stubbing other functions, which I can cover in a future teatime.
But Why?
But why would you want to test stored procedures and functions? To me, it’s pretty straightforward: if there’s business logic there, it needs to be tested. But if you’re not already convinced, here’s some talking points to mull over:
- Code that is unit tested ends up being cleaner, more efficient, and easier to refactor. This is well documented in terms of program code, but it’s also been examined for database code as well; for example, see this blog post about test-driven database development, or this one, or this one.
- Tests provide living documentation of the expectation of the code. This is also true of stored procedures, some of which can run into dozens or hundreds of lines, with dizzying amounts of table joins and complex branching. A simple suite of tests can easily tell a new developer what exactly they’re looking at — and ensure that they didn’t break anything.
- You can plug the tests into a development pipeline like we discussed last week for instant feedback upon committing your stored procedures. This of course only works if your procs are in version control, but of course they already are, right? 🙂
Do you test your database? Why or why not? Discuss in the comments 🙂