Unit testing in database - Challenges

T

This part will be about challenges of unit testing in database world. Next parts will be about:

• Test patterns
• Organization tips
• Gamification

Challenges

Database unit testing is in very similar and sad position as database (application) security - often overlooked and unappreciated. From my experience developers just don't know where to start, they don't believe in the value of tests in database, they don't know how to make their spaghetti code testable and/or they fear about the amount of time required for proper testing. They get interested only when their issues on project are recurring but they give up quickly anyway.

Here is my approach and recommendation.

Why even bother with tests?

You can find a lot of theory elsewhere, but the main benefit I get from tests is an assurance (more like visibility) that if you change something you don't brake anything else. You can and you will brake something else eventually but if you have the safety net of unit tests you will know right away what you broke and where. You can fix it before it affects users and your business.

Main issue with code I see over and over again is that it is too long. Typical spaghetti code. I was on an interview recently and they asked me, how I will debug their 10000 (ten thousand) lines procedures. Well, at first I thought it was just hypothetical. Or they meant package, not procedure. Oh boy, I was wrong. They have many of 10 thousand lines procedures. It is a bad design. You can't seriously think you will be able to debug these, improve performance nor write tests for them. No refactoring would be enough. You need major redesign and most likely some process and mindset changes to avoid this in future.

I am a big advocate of readable code. I think it is the most important part of developers work. Even if I write crap code, if others can read it, they can (quickly) fix it. It doesn't matter how wast your procedure/function (modules) is, you can write tests for anything. But it certainly helps to keep your modules small and readable. Same goes for your tests. Keep them small and readable. Everybody after you and future yourself will thank you. Trust me.

Let's start with a simple procedure player.create_player, which creates record in Players table.

How do you test it works? What tests do you need?

Ideally you should think about this before you write your code. It is mantra of TDD but it is also like the extreme persona in agile. Of course you can do it later too, it just might lead to different results and you might miss some scenarios, specially the edge cases.

Don't forget about negative scenarios/tests. You should test also that something didn't happened. If you create user and you assign him default roles, you should test if he got these and also if he didn't got anything else.

So, how do you test player.create_player?

Possible options:

• check number of rows in Player table before and after call
• check existence of new row/player_id in Player table
• check if any exception was not raised during call

I would suggest to combine all of these in multiple scenarios.

Let's check the steps:

• ensure target user doesn't exist
• call procedure (create player)
• check if row exists and also columns are set to expected values

You have to think like a tester or QA. How can you brake this?

Then you will come up with related or edge scenarios:

• creating not unique player name
• checking possible errors during creation (like too long name, wrong email, age...)
• can be player deleted or disabled and how about related data?
• are there any default roles assigned (check related tables)?

Bonus tip

Don't spend too much time on edge cases. Remember the Pareto 80/20 rule. You don't want to spend too much time on tests. Your main goal should be to cover the main functionality and some specifics in reasonable time and effort. That should be enough so you can feel comfortable changing your code in the future. You don't need the perfect safety net. It will costs too much and probably won't be ever perfect. Also the more tests you write, the more tests you would have to change if/when you change the code.

Start. Start small. Don't give up. It will get easier quickly.

See you in the next part.