Teatime: SQL Testing

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; 

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 🙂

Teatime: Deployment Pipelines

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: An old standby, Twinings Ceylon Orange Pekoe. There’s no orange flavor in it; the Orange refers to the size of the leaves. It’s a good staple tea I can find in my local supermarkets, solid and dependable — just like a deployment pipeline should be. 

Deployment Pipelines

Today’s topic is a little more afield from last week’s discussion of testing types, but I feel it firmly falls under the umbrella of quality. A good deployment pipeline, as you will see shortly, improves the maintainability of code and prevents unwanted regressions.

Like last week, much of this talk touches on concepts laid out in  Continuous Delivery by Jez Humble and David Farley. If your company isn’t already performing continuous delivery, I highly recommend the book, as it talks through the benefits and how to get there in small increments. In the book, they lay out a simple goal:

›“Our goal as software professionals is to deliver useful, working software to users as quickly as possible”

Note that they said “software professionals”, not developers. After all, isn’t that the ultimate goal of SQA as well? And of the BAs and project managers?

Feedback Loops

In order to achieve the goal — delivering software that is both useful and working — Humble and Farley suggest that there needs to be a tight feedback loop of information about how well the software works and how useful it is to the end user delivered back to the development team so they can adjust their course in response. In order to validate traditional software, one typically has to build it first; they advocate building the software after every change so that the build is always up to date and ready for validation. Automate this process, including the delivery of build results to the development team, and you have created a feedback loop — specifically, the first step in a deployment pipeline.

Automated Deployment

In order to validate software that builds correctly, it must be installed, either on an end-user-like testing machine or to a web server that will then serve up the content (depending on the type of software). This, too, can be automated — and now you’ve gained benefits for the development team (who get feedback right away when they make a change that breaks the installation) as well as the testing team (who always have a fresh build ready to test). Furthermore, your infrastructure and/or operations teams have benefits now as well; when they need to spin up a new instance for testing or for a developer to use, they now can deploy to it using the same automated script.

Automated deployment is a must for delivering working software. The first deploy is always the most painful; when it’s done by hand at 2am in production, you’ve already lost the war for quality. Not only should your deploys be automated, they should be against production-like systems, ideally created automatically as well (humans make mistakes, after all).

Continuous Testing

And now we see how this pipeline connects to QA’s more traditional role: testing. Once we have the basic structure in place, typically using a CI Server to automatically build on every commit, we can start adding automatic quality checks into the process to give development feedback on the quality of the code they’ve committed. This can include static checks like linting (automated maintainability checking) as well as simple dynamic tests like unit tests or performance tests. Ideally, however, you want to keep your feedback loop tight; don’t run an eight-hour automated regression suite on every commit. The key is to get information back to the developer before they get bored and wander off to get coffee 🙂

Essential Practices

In order to make this really work for your organization, there are a number of practices that must be upheld, according to the authors of Continuous Delivery. These are basic maintenance sort of things, required for code to keep the level of quality it has over time. They are:

  • Commit early, commit often. Uncommitted code can’t be built, and thus, can’t be analysed.
  • Don’t commit broken code. Developers love to “code first, test later”, and, if they’re not used to this principle, tend to commit code with broken unit tests, intending to go back and clean it up “later”. Over time, the broken windows of old failing tests inoculate people against the warning tests can give. They become complacent; “oh, that always fails, pay it no mind”, they say, and then you might as well not have tests at all.
  • Wait for feedback before moving on. If your brain’s on the next task already, you’ll file away a broken unit test under the “I’ll fix it later” category, and then the above will happen. Especially, never go home on a broken build!
  • Never comment out failing tests. Why are they failing? What needs to be fixed? Commenting them out means removing all their value. ‘


Do any of you use continuous testing and/or a deployment pipeline? Maybe with software like Jenkins, Travis CI, or Bamboo? Let’s chat in the comments!

Teatime: What kinds of testing should I do?

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: It’s been a stressful (and cold!) week as I pre-write this, so I’m chilling out with some Toasted Caramel Rooibos from Sub Rosa Teas


Today’s topic: What kind of testing should I do?

For the first QA-content-filled teatime, I wanted to start at the beginning and touch briefly on what kinds of testing there are, and when to use each of them. This is not intended to be an exhaustive list, more of a gentle overview while we sip tea and meditate on our own projects and the testing each one needs.

Things to consider

When you’re putting together a test plan, and you’re considering performing various types of tests, you should consider the following:

  • What is the purpose of this kind of test?
  • In what situations is this test most suited?
  • What differentiates this test type from others?
  • Why are we performing this test?

Testing without purpose is not so much testing as playing; if there’s no business need, and no reason to perform the test, all you’re doing is wasting your time. Furthermore, if you already have your needs covered, adding more types of tests won’t help anything, by definition: your needs are already covered, and further testing is just wasting time.

The Testing Quadrants

test quadrants

I came across a version of this diagram in the book Continuous Delivery by Jez Humble and David Farley, and I really liked it, so I’ve recreated my own version here. There are two axes represented above, both equally important. On the horizontal axis, testing can either support development efforts, which is to say, it can provide input into the ongoing effort of building the software to correct the course by small degrees; or, it can critique a finished product, producing a feedback loop for development of future enhancements and/or future products. On the vertical axis, tests can be developer-facing, giving feedback to the development team on the project, or they can be user-facing, giving feedback to the BA from a user’s perspective of the software.

Therefore, in this model, acceptance tests are user-oriented tests; do not write acceptance tests for things that are invisible to the user! On the other hand, they support development, so we want to run them as early as possible to tighten the feedback loop so the devs can course-correct. Which argues for an Agile approach 🙂

Unit tests are similar to Acceptance Tests in that they support development and so should be written and executed as early as possible,  but they are developer-facing, so they should absolutely test for things that only developers can see, like method signatures and code organization.

Exploratory tests are like Acceptance Tests in that they are user-facing, and thus should only focus on things the end user can see; however, they are a critique, intended to find things when the product is in a stable, “release candidate” state. They serve to prove to the business that what we built meets their expectations, not to aid developers in building it.

And finally, Nonfunctional acceptance tests allow us to critique the product from a development standpoint: now that we know it works (because of Unit tests), we need to see if it’s performant, secure, et cetera.

What about regression testing?

You may have noticed that regression testing isn’t in any of the four quadrants. It doesn’t really fit into this graph; it sort of lies orthogonal to the graph, or envelops the entire thing. Regression testing is simply the act of verifying that requirements which were previously tested to work have  not been broken since the test was run. Without qualifiers, we typically mean Functional Regression Testing, which is simply running old acceptance or unit tests over again to ensure that the functionality was not broken. You can also, however, perform nonfunctional regression testing, say, to verify that software that was previously fast has not gotten slower, or software that previous installed on Windows still does after being enhanced to run on Linux.  Exploratory regression testing would be exploratory testing of areas that have not changed in the most recent version.

So what kinds should I do?

All of them 🙂

Honestly, only you can answer the question of what types of testing are right for your business needs, your product, your development team, your release cycle, et cetera. Hopefully, however, you have some ideas now that you’ve paused to consider the types available and what goals they fulfill. So, you tell me: what kinds of testing will you do?

Teatime: Introduction

Welcome to the first installment of Teatime! At my day job, Teatime has become a tradition, growing out of one of the webteam’s lead’s weekly team meeting to become a department-wide celebration of learning and knowledge… or something like that 🙂  Every week, in one of our larger conference rooms, I set up tea and cookies and a presentation on some aspect of quality. I take the term pretty loosely; my audience is mostly technical folks, so I tend to dig into topics that, when properly understood, can lead to better quality code as well as topics about testing and the art of writing tests.

For today, however, I’m going to touch on a topic near and dear to my heart: How to make a good cup of tea.


QA Teatime presents: How to make tea

Step one: Gather ingredients

First of all, some terminology: if it doesn’t contain leaves from the tea plant, for these purposes, it’s not tea. Tisanes (known as “herbal teas” in America) are quite lovely, but out of scope for this guide. That leaves three major categories: Black tea, Green tea, and White tea.[1]

Black tea is your classic English tea: it’s strong, closer to coffee than the other teas, and generally popular. There are many varieties of tea, but for the most part you’re looking at Ceylon, Assam, and Darjeeling to start out with. If you’re new to the game, I suggest an English Breakfast, which is a blend of Ceylon and Assam designed to be bold and smooth. If you don’t like it bold and rugged, I’d suggest an Earl Grey or Lady Grey, which are a gentler blend with citrus oil or lavender mixed in, respectively.

Green tea is more popular in Japan than in England; it has a decidedly grassy flavor that’s very love-or-hate. I fall on the hate side of the spectrum, sadly, but my husband is rather more of a fan than I am. You typically see Gunpowder or Jasmine as your classic, unadulterated flavors, but my husband is quite fond of Genmaicha, which has toasted rice in it to enhance the flavor.

White tea is basically green tea, but more so. It’s less processed, so it’s got a more delicate, grassy flavor than even Green tea. I don’t personally drink it, so I can’t speak much to the best variety. If you like Green tea, though, it might be worth a shot.

The best cup of tea starts with the best leaves. However, for the budget-conscious, there are many options that don’t break the bank 🙂 First and foremost, however, I have to caution you: if you want a good cup, do not use teabags! These are made of scraps left over from the process of manufacturing loose-leaf tea, sometimes swept up off the floor of the factory. It’s reasonably inexpensive these days to get yourself set up for loose-leaf tea instead.

You will need:

  • kettle. This can be electric or stovetop; the purpose is to boil water quickly.
  • teapot. This does not go on the heat; the purpose is to steep the tea. I like cast iron because it holds heat longer, but it’s more traditional to use a porcelain one,  optionally with a cosy (which is like a sweater for the teapot). If your teapot does not have a filter, you may need to buy one separately.
  • mug. You want this to be fairly small, maybe 6 ounces at most. Otherwise your tea will get cold before you finish savoring it.
  • spoon. I like to re-use the one measuring spoon I use to measure my tea to also measure sugar (when it’s not in cube form) and stir the mug, but you can grab two if you prefer.
  • Milk or cream. I have had good success with soymilk, half-and-half, and liquid non-dairy creamer as well, though I’d steer clear of the powdered stuff.
  • Sugar or your preferred sweetener. I use honey in my tisanes, but I prefer sugar for my tea proper as it changes the flavor less.

Step 2: Prepare the pot

The first step here is to boil the water in the kettle. For black tea, you want just shy of a full boil; I like to bring the water to a boil and then let it cool a couple degrees, but purists will say not to let it boil in the first place, as it can affect the oxygen levels in the water. Green and white teas, however, need a much cooler temperature (around 170-18oF), or the leaves will cook slightly and ruin the delicate flavor. Oolong teas fall somewhere in between, depending how processed they are.

While the water boils, fill the pot with hot water from the tap. You never want to use water from the tap to make tea, as water heaters can impart off flavors to the water, but you do want the teapot to be nice and warm when you brew your leaves. Other solutions involve hotplates and warmers that involve suspending the pot over a small candle. Feel free to warm the cup as well.

When the water is ready, measure the tea into the basket; the packaging should tell you how much to use. Pour just a little water over the tea, to rinse the leaves, then dump it out the spout, taking the loose powdery bits with it. Then fill the teapot and set your timer — I like four minutes for my black teas, but people do as little as two and as much as five minutes.

Step 3: Enjoy!

When the tea is fully steeped, remove the leaves from the water and set aside. Now you can take the pot to your preferred sipping area. You’ll want to add milk to the tea, much like how coffee drinkers take cream in their coffee; drinking it black is only for those who really like bitter flavors. Similarly, add sugar to taste. Sip slowly and savor the flavors 🙂

Troubleshooting tips: If the tea is a hint too bitter, add more milk. If you’re not getting the full expression of flavor you expected, try a bit more sugar, sometimes it can bring out the other flavors. If your tea is too weak, you needed to have added more tea when steeping; steeping longer just makes it more bitter. If it’s excessively bitter with an odd after-taste, you over-steeped (or it’s bad tea).


[1]Rooibos tea, also known as Red Tea, is technically a tisane, but can be prepared as though it were a decaf Black Tea.