Writing Tests for Data Access Code - Unit Tests Are Waste

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:

  1. Is the correct data stored to the used database?
  2. 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:

  1. Every piece of code must be tested in isolation.
  2. 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?

29 comments… add one
  • cc Jul 6, 2014 @ 22:26

    great post.

    it also raises a question: is testing a dao with junit and h2 actually an integration test?

    • Petri Jul 7, 2014 @ 0:09

      Thanks!

      it also raises a question: is testing a dao with junit and h2 actually an integration test?

      I would say that it is an integration test because the H2 database is used to simulate the behavior of the production database. However, I must admit that I think it is fine if someone thinks that this is a unit test. Naming is mostly semantics anyway, and the most important thing is that someone wrote that test.

      • Alexandre Kieling Jul 7, 2014 @ 16:17

        I think testing the data acess code using junit and H2 is a great solution to test complex queries and the Hibernate mapping. Unfortunately this is not an option if you use your database's proprietary SQL syntax. It would be great if all databases provided an in-memory option for testing.

        • Petri Jul 8, 2014 @ 0:32

          I agree with you. One of the biggest benefits of using the H2 database is that you get "fast" feedback. Of course it cannot guarantee that your application works when you deploy it to production environment, but I haven't missed any bugs because I used H2 database in my integration tests.

          By the way, have you checked out jOOQ? It could provide a solution to your problem. You can use its fluent APIs to write database queries which works on virtually every database. I have also written a small tutorial about it.

  • Magnus Lassi Jul 6, 2014 @ 22:36

    Good post! I agree that it doesn't make sense to unit test external system resources such as databases. How often do you add integration tests for the application database interactions in your projects?

    My opinion is that in large applications, often times the integration tests just takes longer and longer to run over time and eventually have less value because you don't want to wait on getting the feedback. I think automated end-to-end tests which tests the critical paths from the UI throughout the application and to the database is the most useful in at least large applications.

    What's your thoughts?

    • Petri Jul 7, 2014 @ 0:22

      How often do you add integration tests for the application database interactions in your projects?

      I do this all the time. I write integration tests for every repository method which I have created. Also, I write end-to-end tests for each feature as well.

      My opinion is that in large applications, often times the integration tests just takes longer and longer to run over time and eventually have less value because you don’t want to wait on getting the feedback.

      If you use the H2 memory database when you run the integration tests in the development environment, integration tests aren't really very slow. For example, I have an integration test suite which has about 1600 integration tests and it takes about 90 seconds to run the full test suite (these tests use JUnit, Spring MVC Test, DbUnit, and H2 in-memory database).

      On the other hand, you should also run your test suite against the same database server which is used in production. Because running integration tests against a real database is often slower than running them against an in-memory database, you should let the CI server do the heavy lifting for you.

      I think automated end-to-end tests which tests the critical paths from the UI throughout the application and to the database is the most useful in at least large applications.

      This is probably good enough if you only want to ensure that your application is working correctly. I want that my tests document the behavior of each class and help me to find the problem ASAP if a test fails. That is why I tend write more tests than an average developer.

      This raises an interesting question: are my tests waste?

      At the moment I think that they aren't waste, but the odds are that if you ask this question again two years from now, I will give you a different answer.

  • Gregor Jul 8, 2014 @ 0:22

    Hi Petri,

    good post! i like it, and i totally agree with it!

    i think the goal of a unit test is

    1. to make sure that the part of the program does what it is supposed to do
    2. to not contain implementation details, but be written from a blackbox view
    3. to test only a small portion of the program

    which completely suit your post, since the first test was not written from a blackbox view at all. and even though it tested only a small portion of the program, it would not make sure that the part of the program did what it was supposed to do.

    However one question:

    How do you deal with transactions. For example, imagine you'd use hibernate, and you'd open a transaction in the ServiceLayer which talks to the dao. Your dao is tested, and works as expected. But the transaction which was started outside the dao would have a negative effect on what was happening inside the dao. Maybe there was a detached entity, or an unassigned persistent bag. Just anything that damaged what the dao would do, but happened outside? How do you deal with these in tests

    • Petri Jul 8, 2014 @ 0:55

      Hi Gregor,

      Thank you for asking such an important question!

      It is extremely important that we test transactions as well. Typically I do this by writing end-to-end tests with the Spring MVC Test framework. A single end-to-end test tests a specific business requirement and ensures that:

      • The correct changes are made to the database if the transaction is committed.
      • No changes are made to the database if the transaction is rollbacked.

      Of course some features only read information from the database, but if a feature writes something to the database, I will write end-to-end tests which ensure that the feature works correctly in "every" situation.

      Sometimes I write integration tests which calls the tested service method and ensures that the transactions are working, but typically I do this only if the entry point of the feature is hard to test.

      I hope that this answered to your question. If not, please ask more questions! :)

  • Lukas Eder Jul 9, 2014 @ 10:04

    Petri,

    Nice assessment:

    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.

    :)

    One of the issues with unit vs integration testing seems to be that people often do not agree on what the terms actually mean. I've recently written a similar post: "Stop Unit-Testing Database Code". It appeared that this triggered a bit of confusion over on Reddit where people were actually inline with my (and your) line of thoughts, but mis-read the "unit" part in "unit testing".

    I think the important aspect here is that "unit" testing is most often used to test algorithms that have no side-effects on external state, whereas integration testing is the only sane and reasonable thing to do when testing a system in the context of its surrounding systems with all state that is distributed across such systems. Databases being one example of such external state-providing systems.

    Does that make sense?

    • Petri Jul 9, 2014 @ 10:34

      Nice assessment

      Yeah. I cannot figure out what I was thinking when I wrote that test. Probably I was not thinking at all.

      I think the important aspect here is that “unit” testing is most often used to test algorithms that have no side-effects on external state

      I have actually spend a lot of time thinking about unit tests, and more specifically, when it makes sense to write them. At the moment I am writing a lot of unit tests because of two reasons:

      • I want to document my in a way which ensures that the documentation is always up-to-date.
      • I want to ensure that if a test fails, I will find the problem as soon as possible.

      This means that I will write unit tests for components which alter the external state (e.g. application and domain services). But I agree that the "purest" form of unit testing takes place when you are writing tests for components which don't have side-effects on external state.

      whereas integration testing is the only sane and reasonable thing to do when testing a system in the context of its surrounding systems with all state that is distributed across such systems. Databases being one example of such external state-providing systems.

      Yes, I wouldn't write software without writing integration tests. If you have 100% unit test coverage (which you don't have), you cannot be sure that the components of your software work together (unless you write integration tests).

      • Lukas Eder Jul 9, 2014 @ 14:58

        This means that I will write unit tests for components which alter the external state (e.g. application and domain services). But I agree that the “purest” form of unit testing takes place when you are writing tests for components which don’t have side-effects on external state.

        Yep, that's probably where people's confusion about the terms comes from. To me, you're already integration testing stuff up there. But maybe that distinction is not very helpful as the border lines aren't very clear.

        • Petri Jul 9, 2014 @ 15:13

          To me, you’re already integration testing stuff up there.

          Interesting (and not totally against my beliefs either). Typically I have three different setups for "unit tests" which test either an application or a domain service:

          1. The component is tested in isolation (e.g. dependencies are replaced with mocks or stubs). I would say that this is a unit test.
          2. The component is tested together with its dependencies. This setup is often useful if the dependencies do not touch external resources such as database. I would say that this test is an integration test.
          3. Some dependencies are mocks/stubs and some are actual objects. Again, I would say that this is an integration test.

          This is indeed a bit confusing... ;) But typically I run all of these tests in my unit test suite because they don't use resources or components which aren't part of the my application's code base (e.g. database, a third party REST API, and so on).

          But maybe that distinction is not very helpful as the border lines aren’t very clear.

          I agree. I think that most flame wars are started because people use different definitions and never bother to find out what definition the other person use (or they just ignore it as stupid).

          • Lukas Eder Jul 9, 2014 @ 15:30

            and never bother to find out what definition the other person use

            Or they simply care about 100% test coverage and mere testability instead of added value. I think that's the most important bottom line :)

  • Manjunath Jul 14, 2014 @ 6:19

    Hi Petri,

    Thanks for sharing this informative topic. I totally agree with you that mocking database is something which might prove unnecessary in several cases . I could think of one case where in we might require to do so. Say we have a scenario wherein we want to test our service layer which is going to interact with the data layer and we want to see how our service layer responds to any exceptions being thrown from the data layer. Say in a concurrent system with hibernate as our JPA provider we want to see how our service layer behaves when the data layer throws an OptimisticLockException or any other exceptions then can we simulate it by mocking the data layer to throw exception (which I feel would be easier than using real database and trying for the exception to be thrown).

    I would like to apologise here if whatever I stated here doesnot make much sense as my coding experience is limited and I havent got much chance to write extensive test cases that I can comment with confidence.

    But for rest of the cases I totally agree with your write up.

    Regards,
    Manjunath Anand

    • Petri Jul 14, 2014 @ 10:50

      Hi Manjunath,

      I could think of one case where in we might require to do so. Say we have a scenario wherein we want to test our service layer which is going to interact with the data layer and we want to see how our service layer responds to any exceptions being thrown from the data layer.

      I think that this makes sense, and I mock my repositories (or DAOs) when I write unit tests for service classes.

      Say in a concurrent system with hibernate as our JPA provider we want to see how our service layer behaves when the data layer throws an OptimisticLockException or any other exceptions then can we simulate it by mocking the data layer to throw exception (which I feel would be easier than using real database and trying for the exception to be thrown).

      This is a very good example. If something makes your life easier, it is definitely the right thing to do.

      I would like to apologise here if whatever I stated here doesnot make much sense as my coding experience is limited and I havent got much chance to write extensive test cases that I can comment with confidence.

      It makes the perfect sense. Also, remember that even if someone has written a lot of tests, it doesn't necessarily mean that this person knows everything. Open discussion and asking questions is the best way to learn and often learning requires that the student questions so called authorities. :)

  • Manu PK Jul 15, 2014 @ 10:23

    Hi Petri,
    You have given good logic to an idea that I have always felt right. When I have started writing tests for an legacy application there were hell lot of tests which made no sence. I wrote an article about 3 years back on the similar lines http://blog.manupk.com/2011/11/when-to-replace-unit-tests-with.html over this frustration.

    Thanks for sharing.

    • Petri Jul 15, 2014 @ 10:50

      Hi Manu,

      Thank you for your kind words. I really appreciate them.

      Also, I read your blog post, and your examples reminded me that if we write unit tests for data access code, sometimes we have to mock so many things that the test cannot fail. And if a test cannot fail, does it really have any value?

      Nope.

      In other words, we end up with a test suite which contains tests that are very brittle and don't really test anything relevant, or test which cannot fail.

      It seems that we cannot win.

  • Arun Feb 24, 2016 @ 8:59

    I am subscribing to ur blog... The explanation is simple and funny.... Keep going

    • Petri Feb 24, 2016 @ 18:33

      Hi Arun,

      Thank you for your kind words. I really appreciate them!

  • aseem Sep 22, 2016 @ 23:59

    Excellent post, thanks for sharing. In a world, sometimes driven by jargons and excitement of newer technologies, someone is needed who can call spade a spade - this article does that !!

    • Petri Sep 27, 2016 @ 18:06

      Thank you for your kind words. I really appreciate them.

  • Pascal Oct 4, 2017 @ 21:58

    I recently found MariaDB4J, its awesome! https://github.com/vorburger/MariaDB4j

    It runs a completely embedded MySQL server. Finally I can rely on my integration tests.

    So long H2 DB !

    • Petri Oct 10, 2017 @ 21:53

      That looks interesting. I will take a closer look at it. Thank you for sharing.

  • Shekhar Apr 6, 2018 @ 22:15

    Hi Petri.
    Its great article and rightly said, but incomplete.
    Could you please post an update with examples how you advice to do integration tests replacing unit tests?
    Biggest problem with writing integration tests is we can't use real production database so have to rely on in-memory database, right?
    As many DAO to test require inserting data in database and we don't wanna do that during build time test runs. If we do should have a reliable way to do total roll back after tests are complete.
    I once wrote lot of integration tests where I was testing DAO's and writing records to real database in DB but after tests rolling them back.
    Problem was in case test breaks may skip rollback and records may stay in database.
    What is best way to use real database for integration tests with reliable rollback?

    • Petri Apr 8, 2018 @ 19:50

      Hi, and thank you for an interesting comment.

      Its great article and rightly said, but incomplete.

      You are right. This article (or this tutorial) won't help you if you are looking for instructions how you can write the actual test classes. The goal of this tutorial is to simply identify the techniques which you should use when you write tests for data access code.

      Could you please post an update with examples how you advice to do integration tests replacing unit tests?

      Are you using Spring Framework or Spring Boot? If so, what version?

      Biggest problem with writing integration tests is we can’t use real production database so have to rely on in-memory database, right?

      Actually, you can also use a real database (such as PostgreSQL) when you run your integration tests. I guess the only reason why people use an in-memory database is that it is fast and it doesn't require any setup after it's configured for the first time.

      As many DAO to test require inserting data in database and we don’t wanna do that during build time test runs. If we do should have a reliable way to do total roll back after tests are complete

      All my integration tests initialize the used database into a known state before an integration test method is run. This way I will know what data is found from the database when my tests are run. And yes, I know that my tests are slower and I cannot use large data sets but I can live with it because I don't need to roll back the transaction after each test.

      What is best way to use real database for integration tests with reliable rollback?

      This really depends from the framework you are using. Spring Test (and especially Spring Boot) has good support for this kind of behavior, but I haven't really used this behavior because I won't typically write transactional integration tests.

      Again, thank you for your comment! If you have any additional questions, don't hesitate to ask them.

  • Anonymous Jun 19, 2023 @ 11:25

    Nice Blog Petri!
    I want to know then how can we do integration testing of the mapper and dao classes. Is it important or we can skip it?

    • Petri Jun 21, 2023 @ 8:26

      Hi,

      Thank you about your kind words. I really appreciate them. About integration testing: you should have at least some integration tests which test your mapper and dao classes, but these tests don't have to (necessarily) invoke these components. For example, you can write tests which send HTTP requests to your REST API endpoint. This way you can write tests which cover your entire stack. That being said, the amount required tests depends on the implemented application and your risk tolerance level.

      If you have any additional questions, don't hesitate to ask them.

Leave a Reply