Database applications can - and should - benefit from unit tests, but it is surprising how rarely database code is covered by such tests. One of the problems I suppose is with the fact that it can be pretty tricky to write an isolated test that does not modify the state of the system, so it is fully re-runnable and yields the same results each time. Martin Fowler wrote a good article discussing the unit tests concept - well worth having a look!
Even if you decide not to implement unit tests for your stored procedures or queries, you can still implement them to ensure that the coding standards are observed.
You can use unit tests to check
- naming conventions of your tables, columns, constraints and other objects - a database is much easier to manage if all developers follow certain agreed standards
- data types used across the database for common fields (user name, date created etc) - will help you to avoid awkward bugs with data not fitting into their table columns or performance problems that may occur if the database has to convert the datatypes back and forth in joins and WHERE clauses
- permissions applied to your database - developers have this ability to take shortcuts and grand all access to all objects, because their code works this way.
Tests like this will allow you to catch issues early, while you're developing the database - not in production.
There is a number of frameworks that allow you to write and manage unit tests for your database. Here are two of them:
DBUnit supports a number of DB technologies, including PostgreSQL, MySQL and Oracle
tSQLt supports SQL Server
I also wrote many, many years ago an article about unit testing in SQL Server. The approach is quite simple, but can help you with the use cases I listed above. The idea is based on a simple script that you can modify to suit your needs. You can write a client framework or use an existing one (I used XUnit for example) to execute these scripts automatically every few minutes or every day, as you see fit.
The script is quite simple. You just add your regular code in the begin try/end try section and you catch resulting error in the catch section of the script. You roll back any changes that might have been made by the testing script. Of course, if you only validate naming convention or data types, you don't have to roll back any transactions.
begin tran begin try raiserror('-- ======== Enter your unit test name here ========= --', 10, 1) with nowait; raiserror('Test 2: Add new feed, invalid category name', 10, 1) with nowait; insert FeedCategory (CategoryName) values('Maintenance feeds') --insert category exec AddNewFeed @FeedName = 'Feed 1', @FeedCategoryName = 'No such category', @FeedType='FS' raiserror('FAIL', 16, 1) --Assert.Fail. We shouldn't be here. end try begin catch declare @err varchar(2000) set @err = error_message() if @err like '%Invalid feed category name%' --is it expected exception? raiserror('Test succeeded.', 10, 1) with nowait; else raiserror('Test failed: %s', 16, 1, @err); end catch rollback
Image By Marcel Douwe Dekker [CC BY-SA 3.0], via Wikimedia Commons