Unit-Testing Databases

Question

This past summer I was developing a basic ASP.NET/SQL Server CRUD app, and unit testing was one of the requirements. I ran into some trouble when I tried to test against the database. To my understanding, unit tests should be:

• stateless
• independent from each other
• repeatable with the same results i.e. no persisting changes

These requirements seem to be at odds with each other when developing for a database. For example, I can't test Insert() without making sure the rows to be inserted aren't there yet, thus I need to call the Delete() first. But, what if they aren't already there? Then I would need to call the Exists() function first.

My eventual solution involved very large setup functions (yuck!) and an empty test case which would run first and indicate that the setup ran without problems. This is sacrificing on the independence of the tests while maintaining their statelessness.

Another solution I found is to wrap the function calls in a transaction which can be easily rolled back, like Roy Osherove's XtUnit. This work, but it involves another library, another dependency, and it seems a little too heavy of a solution for the problem at hand.

So, what has the SO community done when confronted with this situation?

---

tgmdbm said:

You typically use your favourite automated unit testing framework to perform integration tests, which is why some people get confused, but they don't follow the same rules. You are allowed to involve the concrete implementation of many of your classes (because they've been unit tested). You are testing how your concrete classes interact with each other and with the database.

So if I read this correctly, there is really no way to effectively unit-test a Data Access Layer. Or, would a "unit test" of a Data Access Layer involve testing, say, the SQL/commands generated by the classes, independent of actual interaction with the database?

Answer 1

There's no real way to unit test a database other than asserting that the tables exist, contain the expected columns, and have the appropriate constraints. But that's usually not really worth doing.

You don't typically unit test the database. You usually involve the database in integration tests.

You typically use your favourite automated unit testing framework to perform integration tests, which is why some people get confused, but they don't follow the same rules. You are allowed to involve the concrete implementation of many of your classes (because they've been unit tested). You are testing how your concrete classes interact with each other and with the database.

Answer 2

DBunit

You can use this tool to export the state of a database at a given time, and then when you're unit testing, it can be rolled back to its previous state automatically at the beginning of the tests. We use it quite often where I work.