Recently I’ve come across three attitudes towards unit testing database functions and stored procedures etc that I’ve found rather odd, confusing or downright infuriating. I thought I’d share the things people said and the attitudes they expressed, before explaining why they are stupid.

Here goes…

 “The QA team write the tests”

You’re doing it wrong.

Developers should write their own unit tests. QA teams should have no business writing unit tests. If you have a QA team (and I’m not sold on the idea of separate QA teams ever being a good idea in the first place) I’m all for them automating various types of acceptance tests. These tests should be based on use cases and what the software is designed to do. These tests, however, should not concern themselves with how the software does it.

Unit tests are designed to test a single unit of code. (The clue is in the name.) Typically for databases this means a single stored procedure or function etc. This means unit tests are tightly coupled with the code they are written for. Therefore designing unit tests needs to be done hand in hand with designing the code. TDD argues that unit tests should be written before the actual code.

Creating separate teams to manage the unit tests and the actual code is downright inefficient. Unit tests are a developer tool that helps the developer to ensure they have built the thing that they intended to build correctly. Unit tests are not the right tool for a QA team to check that the software does the job it is intended to do. Unit tests are a tool for developers to help them design the code correctly.

“It doesn’t work”

You’re lazy.

I met a man recently who said he had tried unit testing SQL Server but it didn’t work. It’s not the first time I’d heard someone say something like this. I asked him for more information. “I tried tSQLt but it didn’t support [some particular type of object in SQL Server 2014/2016]”.

I have two things to say about this:

1. Great, you tried one unit test framework and it didn’t work for you. Have a biscuit. But tSQLt is not the only way to unit test SQL Server. If tSQLt didn’t work for you did you consider any of the alternatives? A very quick Google suggests the following options: SSDT, tSQLUnit, SQL Server Unit, Slacker, TST, DbFit. I’m sure there are others. A lot of people also write unit tests from the application layer (although this feels a bit more like integration testing to me). Did you try any of these?
2. So tSQLt doesn’t support one particular type of object in a new edition of SQL Server. Did it occur to you that tSQLt is open source? Look, it’s here, on GitHub. Did you try to fix the problem for yourself? (And in the process for the rest of the SQL community.) Unit testing is important. As a community of SQL Server professionals we should try to contribute toward making SQL Server better. If organisations decide to use SQL Server they’ll want to employ people with your skills, but if they decide to use Oracle or Postgres or MySQL or anything else instead they’ll employ people with CVs that are different from yours. It’s up to everyone in the #SQLFamily to make SQL Server the best relational database on the market.

Next time you want to do a thing (unit testing or otherwise) and you try an open source tool and it has a particular limitation don’t give up on it until you have tried some alternative solutions and/or at least considered whether you could fix the problem yourself.

Apathy

You don’t care. (At least not enough to do anything about it.) Which means you are exceptionally stupid.

You can test your code in two ways:

1. Every time you change the code you manually check that you haven’t broken anything.
2. Every time you change the code you automate a check that you haven’t broken anything.

If you do neither of these things you’ll break stuff. Tell me you have never written some code and later discovered that you broke something that you thought was unrelated. There’s a reason we all have stories about legacy databases that were impossible to work with because every time you changed something you broke something else. Imagine how much time you would save or how much better you could write code for that legacy database if you had decent test coverage.

Out of the two options above, the first is impractical to the point that it’s basically impossible. It’s slow, mind-numbingly boring and prone to human error.

There is a reason you wouldn’t hire a .NET developer that didn’t believe in unit testing. Our functions and stored procedures are much closer to our production data than our application code. Why on earth do you think it’s OK not to do some sort of automated testing for them?

If I had stored procedures or functions in my database I wouldn’t hire anyone who didn’t believe that automated testing was an essential part of development. If you are apathetic about unit testing I wouldn’t hire you.

What do you think?

Perhaps you’ve seen these attitudes before? Perhaps you’ve experienced other odd attitudes toward database unit testing? Perhaps you think I’m wrong? (I’m not. J)

If you have an opinion please share it.

Image source/licence:

https://commons.wikimedia.org/wiki/File:Anger2.jpg