Unit tests and database

This question about unit tests sparked another thing that's been bothering me. I've gone back and forth on three ways to do unit tests when hitting a database.

1. Create mock objects and plug them in. This has the advantage of not needing a database, but it's time consuming and I'm not sure how much return on investment I'm getting. I've been getting into IOC and moq a开发者_Python百科 little bit, but it still seems painful.
2. Create a setup and teardown database scripts to create known cases, and test for those. Again, can be time intensive, but still easier to create than mock objects most of the time. And other people at work can still run it assuming they have SQL server on their localhost.
3. Manually check the dev database and modify unit tests. Intensively manual work, but if I have a "test set" that doesn't change, it seems to work OK. On my machine, at least :-).

I know option 1 is the "proper" way to do unit tests, but of the three, that's probably the option I've used the least (although the latest projects have been with IOC, so that door is open to me). I realize a lot of it depends on what exactly is being mocked and what is being tested, but what am I missing here?

If context helps, I'm in a C# shop, writing in-house applications, only a few developers.

The first one shouldn't be that hard if you've got a Data Access Layer that exposes just a handful of IQueryable<T> methods. There's a nice trick you can use for fake data: just store the entity objects in a List<T> and use AsQueryable. I find this easier than moq for the data parts.

Regarding IOC, I take the position that it's currently overused (be aware that my opinion represents the minority of programmers in this regard). You can use a tool like Moles during testing to mock without having to abuse your program's design. I don't use IOC unless the design actually calls for it.

I would definitely continue to use true unit tests (option 1). I like Stephen's advice for this.

You may also find integration tests with an actual database (option 2) necessary to use as well (option 2). Why? Because part of what you need to test (O/R mapping, compatability with the actual DB schema, etc.) are not covered by true unit tests.

As for setup and teardown logic, inheriting from this usually suffices (using .NET, NUnit, and SqlServer for the database):

using System.Transactions;
using NUnit.Framework;
namespace Crown.Util.TestUtil
{
    [TestFixture]
    public class PersistenceTestFixture
    {
        public TransactionScope TxScope { get; private set; }

        [SetUp]
        public void SetUp()
        {
            TxScope = new TransactionScope();
        }

        [TearDown]
        public void TearDown()
        {
            if (TxScope != null)
            {
                TxScope.Dispose();
                TxScope = null;
            }
        }
    }
}

Easy as pie.

There's a whole suite of answer to this. The first thing to do is to clearly articulate what you are testing. Is it the facade of an API that has a database behind it, DAO objects, your database structure?

Getting to this will also help you decide the best way to test things. There is also from what I can see an alternative to the ones you list. That is to start up in in memory database such as hsql and run your classes and tests against that. This means that you can create the database structure at the start of you tests. Because it's in memory you don't have to worry about having a database server, it's fast, and you can load it with data specific to your test.

I use mocks quite a bit and whilst they are great for unit testing a class, in some cases they are not. They can also miss lead quite easily. It's not uncommon for something that works with mocks, to not work when integrated. The reasonnfor this is that you are loading the mock with certain expectations and responses, which you may have wrongly intepreted from the thing that the mock represents.

Don't get me wrong, I like mocks and use them quite a bit. But in doing so you must never assume that because something is unit tested, it's correct. It does increases the chances, but on integration tests actually give you 100% assurance.

What are you testing really that makes you feel that this is difficult?

If you decouple your business and service layer logic from your persistance code, you should easilly be able to isolate the code you want to unit test without having to a DB.

One of the most important principles of unit testing is to decouple and test in isolation. When you have a clear understanding of how to do that, unit testing is easy. When you don't, unit testing becomes hard.