Unit testing on code that uses the Database

Question

I'm interested to know what approach people are taking in developing automated unit tests that exercise the database

Do you Install a QA database (known-starting point) before the test suite is run.

OR

Do you build database stub that stand-in whenever a database call occurs?

EDIT: Related question, but not a duplicate, though quite important for the matter at hand: How do I unit-test persistence?

Answer 1

We do a few tricks:

edit: We do have seperate databases per user. Our buildserver also has it's own database. The cost of extra harddisks is much less than the cost of developers influencing eachother's tests.

1. Our application can generate tables and do upgrade steps itself (no seperate db scripts). This helps our customer, because he only needs to drop in a WAR file and he's done. The application looks at the database and carries out any DDL statements needed before starting up the rest of the Spring context.
2. Our test-suite has some code which unloads the Spring context, drops the database, and restarts the context with a clean database. We can optionally switch this off if we like
3. All our database/SQL unittests are Spring transactional integration tests. This means that after the test is done, the transactions are rolled back, and other unittests will again look at a clean database.

In addition to this all, we try to do mock testing as much as possible, because unittests are really not intended to be integration tests. So try to seperate your servicelayer from the DAO layer. This also helps you find problems more easily because you isolated everything.

At some point I do believe you need to access the database, because it's not an ideal/academic world we live in :-)

Answer 2

Depends on the case, but on legacy systems where I wan't to stub out the database I often introduce an interface, lets say IFooDBManager that has methods that return ADO.Net entities, like data tables or data sets. Of course it doesn't have to be an interface, but it could be a virtual method for example. Then, in my tests, I use a small API with a fluent interface that I built myself a long time ago, I use this for creating data sets and tables and filling them with test values so that I can return them from my fakes.

The fluent interface looks something like this:

return DataTableBuilder.Create()
    .DefineColumns("a, b")
    .AddRow().SetValue("a", 1).SetValue("b", 2).DoneWithRow()
    .AddRow().SetValue("a", 10).SetValue("b", 20).DoneWithRow()
.Table

As I said, this is just one of the approaches I use, and this is mainly for legacy systems where I don't want to introduce new dependencies on frame works and such. This is however a technique I haven't seen a lot of other people use so I thought it'd be worth mentioning.

EDIT: I forgot to clarify that this is for stubbing out the database, so the interaction with the database is not tested in this case. The actual interaction will take place in the concrete implementation of IFooDBManager, to test that something else completely is needed.

Also, not all methods on such an interface returns things of course, there are also methods for writing to the database and these I usually test by interaction testing in RhinoMocks where I set expectations on these methods.

Answer 3

We are moving towards using mock frameworks - they mock resources like databases, configuration files etc. Using these frameworks is a practical way to run your unit tests effectively.

One of the frameworks that we are working on is Rhino Mocks. You can read about it in the link below. It also has a good introduction of why you need such frameworks.

http://weblogs.asp.net/stephenwalther/archive/2008/03/22/tdd-introduction-to-rhino-mocks.aspx

Answer 4

The "database stub" that stands in is usually referred to as a "fake repository" or "mock repository". They are a good idea. You can code them by hand (not hard for simple cases) or use a framework like Rhino Mocks to generate them. You don't mention what language you are working in. Rhino mocks is for .Net .

If you use mock repositories then you can run tests against code that works with data, without actually using a database for the data. This makes the tests run very fast, which is a good thing.

Of course, you still have to test the real repository at some stage, and this is more of an issue. These tests will run slower, because they use a real database. Some would classify then as "integration tests" not unit tests because of the speed and dependency issues.

I don't mind what you call them so much, but it's a good idea to keep these tests separate.

A good idea here for data consistency is to begin a db transaction before your test, and roll it back afterwards. That way the database state is reverted to what it was before the test.

Answer 5

If you're using NHibernate, you can very easily test against an in-memory SQLite database, which is very fast.

Answer 6

In reality, you should do both. When you say "build database stub", that hints at mocking in unit tests. And when you talk about "Install a QA database (known-starting point)", that hints at integration tests where you actually hit the database. Unit tests come much earlier in the game and are where mocking comes into play. Mocking is much faster than actually hitting the database. So when you run many unit tests, mocking will save much time. Rhino Mocks is a great framework that I've used personally. But there are several mocking frameworks, find the one that works best for you.

At some point you should put your full database through integration tests, and the best way I've found for that is to have a script that completely creates and populates the database with a known set of data. Then make sure that nothing is going to touch your newly created database except for the integration tests, and test away.

Answer 7

In my database testing practice I used NUnit and before whole sequence of tests is executed database was installed and populated with test data. Certainly this process was not really fast but what prevents you from doing other work while tests are running.

Answer 8

I usually use two approaches.

The code that depends on the database abstraction layer works with IoC and is therefore easily testable with mocks/stubs. You have an IDataAccess or IRepository interface and you test the interactions of your code against it.

The code that actually works against the database (say a class implementing IDataAccess) is tested on its own, usually performing round-trips to the database (insert-retrieve-update-retrieve-delete). Before running every test case the database is recreated or emptied to avoid test cross-talk. This leads to tests requiring a few minutes to run instead of a few seconds, but in my opinion it's important to test the actual database you will use in production. Using a replacement such as in-memory SQLite is not a good choice because it's too different from, for instance, SQL Server.

Answer 9

here is a good screen cast on this topic called The Value of Small Tests

Answer 10

Spring has test classes that are transactional. Seed the test data, run the tests, roll them back. It's as if you were never there.

Answer 11

If you're trying to test the data access code, you'll need a mocking framework. In the following link you can see a unit testing databases video tutorial