How would i unit test database logic?

Question

Hi there. I am still having a issue getting over a small issue when it comes to TDD.

I need a method that will get a certain record set of filtered data from the data layer (linq2SQL). Please note that i am using the linq generated classes from that are generated from the DBML. Now the problem is that i want to write a test for this.

do i:

a) first insert the records in the test and then execute the method and test the results

b) use data that might be in the database. Not to keen on this logic cause it could cause things to break.

c) what ever you suggest?

Answer 1

Use a testdatabase and clean it each time you run the tests. Or you might try to create a mock object.

Answer 2

You should choose option a).

A unit test should be repeatable and has to be fully under your control. So for the test to be meaningful it is absolutely necessary that the test itself prepares the data for its execution - only this way you can rely on the test outcome.

Thomas

Answer 3

When I run tests using a database, I usually use an in-memory SQLite database. Using an in memory db generally makes the tests quicker. Also it is easy to maintain, because the database is "gone" after you close the connection to it.

In the test setup, I set up the db connection and I create the database schema. In the test, I insert the data needed by the test. (your option a)) In the test teardown, I close the connection to the db.

I used this approach successfully for my NHibernate applications (howto 1 | howto 2 + nice summary), but I'm not that familiar with Linq2SQL.

Some pointers on running SQLite and Linq2SQL are on SO (link 1 | link 2).

Some people argue that a test using a database isn't a unit test. Regardless, I belief that there are situations where you want automated testing using a database:

• You can have an architecture / design, where the database is hard to mock out, for instance when using an ActiveRecord pattern, or when you're using Linq2SQL (although there is an interesting solution in one of the comments to Peter's answer)
• You want to run integration tests, with the complete system of application and database

Answer 4

What I have done in the past:

• Start a transaction
• Delete all data from all the tables in the database
• Setup the reference data all your tests need
• Setup the test data you need in database tables
• Run your test
• Abort the transaction

This works well provided your database does not have much data in it, otherwise it is slow. So you will wish to use a test database. If you have a test database that is well controlled, you could just run the test in the transaction without the need to delete all data first.

---

Try to design your system, so you get mock the data access layer for most of your tests. It is valid (and often useful) to unit test database code, however the unit tests for your other code should not need to touch the database.

You should consider if you would get more benefits from “end to end” system tests, with unit tests only for your “logic” code. This depend to an large extent on other factors within the project.