How to do database unit testing?
I have heard that when developing application which uses a database you should do database unit testing.
What are the best practices in database unit testing? What are the primary concerns when doing DB unit testing and how to do it "right"?
What are the best practices in database unit testing?
The DbUnit framework (a testing framework allowing to put a database in a know state and to perform assertion against its content) has a page listing database testing best practices that, to my experience, are true.
What are the primary concerns when doing db unit testing
- Creating an up to date schema, managing schema changes
- Setting up data (reference data, test data) and maintaining test data
- Keeping tests independent
- Allowing developers to work concurrently
- Speed (tests involving database are typically slower and will make your whole build take more time)
and how to do it "right"?
As hinted, follow known good practices and use dedicated tools/frameworks:
- Prefer in memory database if possible (for speed)
- Use one schema per developer is a must (to allow concurrent work)
- Use a "database migration" tool (à la RoR) to manage schema changes and update a schema to the ultimate version
- Build or use a test harness allowing to put the database in a known state before each test and to perform asserts against the data after the execution (or to run tests inside a transaction that you rollback at the end of the test).
A list of items that should be reviewed and considered when staring with database unit testing
- Each tester needs a separate database, in order to avoid interfering with activities of other tester/developer
- To have an easy way of creating a database to be tested (this is related to having a SQL Server database under version control). This is specifically useful when trying to find what went wrong if some tests fail
- Focus on specific areas and creating tests for a single module instead of covering all at once. Adding tests granularly is a good way to be efficient
- Make sure to provide as many details as possible when a test fails, to allow easier debugging
- Use one and the same test data for all tests
If test are implemented using tSQLt framework, the unit testing process could be complicated when dealing with a lot of databases from multiple SQL Server instances. In order to maintain, execute and manage unit tests directly from SQL Server Management Studio, ApexSQL Unit Test can be used as a solution