CODEWITHSUNDEEP
sqldatabaseunit-testingtestingdata-access-layer
Richiban
Richiban

Reputation: 5930

Is it worth testing data access queries themselves?

I know that proper SOLID principles combined with IOC mean you can unit test all your code without actually going to the database by mocking out a classes dependencies. My question, following a discussion at work, is whether it's worth actually testing your data access layer itself.

When you've properly separated out your code, your data access method will often be very small. Here's a contrived example:

public class InvoiceQueries
{
    public IEnumerable<Customer> GetAllCustomersWithOverdueInvoices(TimeSpan timeOverdue)
    {
        var results = 
            from invoice in Invoices
            where invoice.DateDue - timeOverdue > DateTime.Now
            select invoice.Customer;

        return results;
    }
}

Is it worth writing an automated test (it wouldn't be a unit test) to check that your query is properly written? Perhaps connect to a real database, or even an in-memory database, insert some test data and then check that your method returns those customers?

One colleague said that you absolutely should - everything should be tested. To me it seems like a huge amount of work to get some form of T-SQL database up and running on the build server, not to mention the fact that these tests will almost certainly be very slow, and of dubious usefulness.

Who has experience actually testing (in an isolated fashion) their data access layer?

Upvotes: 1

Views: 70

Answers (2)

Richiban
Richiban

Reputation: 5930

After some deliberation, my colleague and I came up with a way to separate the query from the persistence layer. I threw together a quick example below:

public class Invoice
{
    public int Id {get; set;}
    public DateTime DueOn {get; set;}
    public DateTime MadeOn {get; set;}
    public decimal Total {get; set;}
    public Customer Customer {get;set;}
}

public class Customer
{
    public int Id {get; set;}
    public string Name {get; set;}
}

public class OverdueInvoice
{
    public int CustomerId {get; set;}
    public int InvoiceId {get; set;}
    public string CustomerName {get; set;}
    public decimal Total {get; set;}
}

public class InvoiceQueries
{
    private readonly IReadRepository<Invoice> _invoices;

    public InvoiceQueries(IReadRepository<Invoice> invoices)
    {
        _invoices = invoices;
    }

    public IEnumerable<OverdueInvoice> GetAllOverdueInvoices(TimeSpan timeOverdue)
    {
        return _invoices
            .FindAll(invoice => invoice.DueOn - timeOverdue > DateTime.Now)
            .Select(Map);
    }

    private OverdueInvoice Map(Invoice invoice)
    {
        return new OverdueInvoice
        {
            CustomerId = invoice.Customer.Id,
            InvoiceId = invoice.Id,
            CustomerName = invoice.Customer.Name,
            Total = invoice.Total,
        };
    }
}

public class InMemoryInvoicesRepository : IReadRepository<Invoice>, IWriteRepository<Invoice>
{
    private List<Invoice> _backingStore;

    public InMemoryInvoicesRepository() : this(new List<Invoice>())
    {}

    public InMemoryInvoicesRepository(List<Invoice> backingStore)
    {
        _backingStore = backingStore;
    }

    public IEnumerable<Invoice> FindAll(Predicate<Invoice> predicate)
    {
        return _backingStore.FindAll(predicate);
    }

    public void Add(Invoice item)
    {
        _backingStore.Add(item);
    }
}

public interface IReadRepository<T>
{
    IEnumerable<T> FindAll(Predicate<T> predicate);
}

public interface IWriteRepository<T>
{
    void Add(T item);
}

Separating the data access into a 'query' object and a 'repository' object allows you to replace the repository at test time with an in-memory collection. This way, you can fill a List with whatever objects you want and then test your query layer by running it against that list. The repository is a really dumb object that takes your query and just passes it through to your ORM (to actually do this Func might have to be replaced by Expression>).

Upvotes: 0

piotrek
piotrek

Reputation: 14550

yes, it should be tested and it's worth spending time to get it work (otherwise you will waste time testing it manually). yes, it is much slower than unit testing but you can make it fast enough (in-memory db, one time db startup for all tests, groups of tests etc). and of course you need to isolate db layer from your business logic

Upvotes: 1

Related Questions