Writing Tests for Data Access Code – Unit Tests Are Waste

Petri KainulainenJuly 8th, 2014Last Updated: July 8th, 2014

1 1,170 5 minutes read

A few years ago I was one of those developers who write unit tests for my data access code. I was testing everything in isolation, and I was pretty pleased with myself. I honestly thought that I was doing a good job. Oh boy was I wrong! This blog post describes why we shouldn’t write unit tests for our data access code and explains why we should replace unit tests with integration tests. Let’s get started.

Unit Tests Answers to the Wrong Question

We write tests for our data access code because we want to know that it works as expected. In other words, we want to find the answers to these questions:

Is the correct data stored to the used database?
Does our database query return the correct data?

Can unit tests help us to find the answers we seek? Well, one of the most fundamental rules of unit testing is that unit tests shouldn’t use external systems such as a database. This rule isn’t a good fit for the situation at hand because the responsibility of storing correct information and returning correct query results is divided between our data access code and the used database. For example, when our application executes a single database query, the responsibility is divided as follows:

The data access code responsible of creating the executed database query.
The database is responsible of executing the database query and returning the query results back to the data access code.

The thing is that if we isolate our data access code from the database, we can test that our data access code creates the “correct” query, but we cannot ensure that the created query returns the correct query results. That is why unit tests cannot help us to find the answers we seek.

A Cautionary Tale: Mocks Are Part of the Problem

There was a time when I wrote unit tests for my data access code. At the time I had two rules:

Every piece of code must be tested in isolation.
Let’s use mocks.

I was working in a project which used Spring Data JPA, and dynamic queries were built by using JPA criteria queries. If you aren’t familiar with Spring Data JPA, you might want to read the fourth part of my Spring Data JPA tutorial which explains how you can create JPA criteria queries with Spring Data JPA. Anyway, I created a specification builder class which builds Specification<Person> objects. After I had created a Specification<Person> object, I passed it forward to my Spring Data JPA repository which executed the query and returns the query results. The source code of the specification builder class looks as follows:

import org.springframework.data.jpa.domain.Specification;
  
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
  
public class PersonSpecifications {
 
    public static Specification<Person> lastNameIsLike(final String searchTerm) {
          
        return new Specification<Person>() {
            @Override
            public Predicate toPredicate(Root<Person> personRoot, 
									CriteriaQuery<?> query, 
									CriteriaBuilder cb) {
                String likePattern = getLikePattern(searchTerm);              
                return cb.like(cb.lower(personRoot.<String>get(Person_.lastName)), likePattern);
            }
              
            private String getLikePattern(final String searchTerm) {
                return searchTerm.toLowerCase() + "%";
            }
        };
    }
}

Let’s take a look at the test code which “verifies” that the specification builder class creates “the correct” query. Remember that I wrote this test class by following my own rules which means that the result should be great. The source code of the PersonSpecificationsTest class looks as follows:

import org.junit.Before;
import org.junit.Test;
import org.springframework.data.jpa.domain.Specification;
 
import javax.persistence.criteria.*;
 
import static junit.framework.Assert.assertEquals;
import static org.mockito.Mockito.*;
 
public class PersonSpecificationsTest {
     
    private static final String SEARCH_TERM = "Foo";
    private static final String SEARCH_TERM_LIKE_PATTERN = "foo%";
     
    private CriteriaBuilder criteriaBuilderMock;
     
    private CriteriaQuery criteriaQueryMock;
     
    private Root<Person> personRootMock;
 
    @Before
    public void setUp() {
        criteriaBuilderMock = mock(CriteriaBuilder.class);
        criteriaQueryMock = mock(CriteriaQuery.class);
        personRootMock = mock(Root.class);
    }
 
    @Test
    public void lastNameIsLike() {
        Path lastNamePathMock = mock(Path.class);       
        when(personRootMock.get(Person_.lastName)).thenReturn(lastNamePathMock);
         
        Expression lastNameToLowerExpressionMock = mock(Expression.class);
        when(criteriaBuilderMock.lower(lastNamePathMock)).thenReturn(lastNameToLowerExpressionMock);
         
        Predicate lastNameIsLikePredicateMock = mock(Predicate.class);
        when(criteriaBuilderMock.like(lastNameToLowerExpressionMock, SEARCH_TERM_LIKE_PATTERN)).thenReturn(lastNameIsLikePredicateMock);
 
        Specification<Person> actual = PersonSpecifications.lastNameIsLike(SEARCH_TERM);
        Predicate actualPredicate = actual.toPredicate(personRootMock, criteriaQueryMock, criteriaBuilderMock);
         
        verify(personRootMock, times(1)).get(Person_.lastName);
        verifyNoMoreInteractions(personRootMock);
         
        verify(criteriaBuilderMock, times(1)).lower(lastNamePathMock);
        verify(criteriaBuilderMock, times(1)).like(lastNameToLowerExpressionMock, SEARCH_TERM_LIKE_PATTERN);
        verifyNoMoreInteractions(criteriaBuilderMock);
 
        verifyZeroInteractions(criteriaQueryMock, lastNamePathMock, lastNameIsLikePredicateMock);
 
        assertEquals(lastNameIsLikePredicateMock, actualPredicate);
    }
}

Does this make any sense? NO! I have to admit that this test is a piece of shit which has no value to anyone, and it should be deleted as soon as possible. This test has three major problems:

It doesn’t help us to ensure that the database query returns the correct results.
It is hard to read and to make matters worse, it describes how the query is build but it doesn’t describe what it should return.
Tests like this are hard to write and maintain.

The truth is that this unit test is a textbook example of a test that should have never been written. It has no value to us, but we still have to maintain it. Thus, it is waste! And yet, this is what happens if we write unit tests for our data access code. We end up with a test suite which doesn’t test the right things.

Data Access Tests Done Right

I am a big fan of unit testing but there are situations when it is not the best tool for the job. This is one of those situations. Data access code has a very strong relationship with the used data storage. That relationship is so strong that the data access code itself isn’t useful without the data storage. That is why it makes no sense to isolate our data access code from the used data storage. The solution to this problem is simple. If we want to write comprehensive tests for our data access code, we must test our data access code together with the used data storage. This means that we have to forget unit tests and start writing integration tests. We must understand that only integration tests can verify that

Our data access code creates the correct database queries.
Our database returns the correct query results.

If you want to know how you can write integration tests for Spring powered repositories, you should read my blog post titled Spring Data JPA Tutorial: Integration Testing. It describes how you can write integration tests for Spring Data JPA repositories. However, you can use the same technique when you are writing integration tests for any repository which uses a relational database. For example, the integration test written to test the example application of my Using jOOQ with Spring tutorial use the technique described in that blog post.

Summary

This blog post has taught us two things:

We learned that unit tests cannot help us to verify that our data access code working properly because we cannot ensure that the correct data is inserted to our data storage or that our queries return the correct results.
We learned that we should test our data access code by using integration tests because the relationship between our data access code and the used data storage is so tight that it makes no sense to separate them.

There is only one question left: Are you still writing unit tests for your data access code?

Reference:

Writing Tests for Data Access Code – Unit Tests Are Waste from our JCG partner Petri Kainulainen at the Petri Kainulainen blog.