I need to play devils advocate on this question a bit because I cannot defend it well because of lack of experience. Here is the deal, I get conceptually the differences between unit testing and integration testing. When specifically focusing in on persistence methods and the repository, a unit test would use a mock possibly via a framework like Moq to assert that say an order searched for was returned as expected.

Let's say I have built the following unit test:

public void GetOrderByIDTest()
   //Uses Moq for dependency for getting order to make sure 
   //ID I set up in 'Arrange' is same one returned to test in 'Assertion'

So if I set up OrderIdExpected = 5 and my mock object returns 5 as the ID my test will pass. I get it. I unit tested the code to make sure what my code preforms returns the expected object and ID and not something else.

The argument I will get is this:

"Why not just skip the unit tests and do integration tests? It's testing the database stored procedure and your code together that's important. It seems like too much extra work to have unit tests and integration tests when ultimately I want to know if the database calls and the code work. I know the tests take longer, but they have to be run and tested regardless so it seems pointless to me to have both. Just test against what matters."

I could defend it with a text book definition like: "Well that's an integration test and we need to test the code separately as a unit test and, yada, yada, yada..." This is a case where a purist explanation of practices vs. reality is loosing out. I run into this sometimes and if I can't defend the reasoning behind unit testing code that ultimately relies on external dependencies, than I can't make a case for it.

Any help on this question is greatly appreciated, thanks!

  • 5
    i say send it straight to user testing...they are going to change the requirements anyhow... Jan 30, 2013 at 23:56
  • +1 for the sarcasm to keep things light from time to time
    – atconway
    Jan 31, 2013 at 15:56
  • 2
    The simple answer is that each method may have x number of edge cases (let's say you want to test positive IDs, ID of 0 and negative IDs). If you wanted to test some functionality which uses this method, which itself has 3 edge cases, you would need to write 9 test cases to test each combination of edge cases. By isolating them, you only need to write 6. In addition, the tests give you a more specific idea of why something broke. Perhaps your repository returns null on failure, and the null exception is thrown several hundred lines down the code.
    – Rob
    Jun 28, 2015 at 23:55
  • 1
    I think you are being too strict on your definition of a "unit" test. What is a "unit of work" for a repository class?
    – Caleb
    Feb 23, 2016 at 9:51
  • 1
    And make sure you are considering this: if your unit test Mocks out everything you are trying to test, what are you really testing?
    – Caleb
    Feb 23, 2016 at 9:53

6 Answers 6


Unit tests and integration tests have different purposes.

Unit tests verify the functionality of your code... that you get what you expect back from the method when you call it. Integration tests test how the code behaves when combined together as a system. You wouldn't expect unit tests to evaluate system behavior, nor would you expect integration tests to verify specific outputs of a particular method.

Unit tests, when done correctly, are easier to set up than integration tests. If you rely solely on integration tests, your testing is going to:

  1. Be more difficult to write, overall,
  2. Be more brittle, due to all of the required dependencies, and
  3. Offer less code coverage.

Bottom line: Use integration testing to verify that the connections between objects are working properly, but lean on unit testing first to verify functional requirements.

All that said, you may not need unit testing for certain repository methods. Unit testing should not be done on trivial methods; if all you're doing is passing through a request for an object to ORM generated code and returning a result, you don't need to unit test that, in most cases; an integration test is adequate.

  • Yes thank you for the quick response I appreciate it. My only critique of the response is it falls into the concern of my last paragraph. My opposition will still just hear abstract explanations and definitions and not some deeper reasoning. For example, could you provide reasoning applied to my use case of testing the stored procedure/DB in relation to my code and why unit tests are still valuable in reference to this particular use case?
    – atconway
    Jan 30, 2013 at 20:32
  • 2
    If the SP is just returning a result from the database, an integration test may be adequate. If it has non-trivial logic in it, unit tests are indicated. See also stackoverflow.com/questions/1126614/… and msdn.microsoft.com/en-us/library/aa833169(v=vs.100).aspx (SQL Server specific). Jan 30, 2013 at 20:35

I am on the pragmatists' side. Don't test your code twice.

We only write integration tests for our repositories. These tests are only dependent on a simple test setup that run against an in-memory database. I think they provide everything a unit test does, and more.

  1. They can substitute for unit tests when doing TDD. While there is some more test code boilerplate to write before you can start with the real code, it works very well with the red/green/refactor approach once everything is in place.
  2. They test the real code of the repository - the code contained in SQL strings or ORM commands. It is more important to verify that the query is correct than it is to verify that you actually sent some string to a StatementExecutor.
  3. They are excellent as regression tests. If they fail, it's always due to a real problem, such as a change in the schema that has not been accounted for.
  4. They are indispensible when changing the database schema. You can be confident that you have not changed the schema in a way that breaks your application as long as the test pass. (The unit test is useless in this case, because when the stored procedure no longer exists, the unit test will still pass.)
  • 3
    Very pragmatic indeed. I have experienced the case that the in-memory database actually behaves different (not in terms of performance) to my real database, because of slightly different ORM. Take care of that in your integration tests.
    – Marcel
    Jan 30, 2013 at 22:43
  • 2
    @Marcel, I've run into that to. I solved it by sometimes running all my tests against a real database. Jan 31, 2013 at 0:41
  • @WinstonEwert for doing so I like to use mock databases in order to be deterministic and to not pollute the databases of a shared development environment or other. A simple docker compose would be already very useful. I have been using the TestContainer lib and it worth checking Aug 21, 2023 at 15:14

Tests are useful when they break: Proactively or Re-actively.

Unit Tests are proactive and may be a continuous functional verification while Integration Tests are reactive on a staged/fake data.

If the system under consideration is close to/dependent on data more than computation logic, Integration Tests should get more importance.

For example, if we are building an ETL system, the most relevant tests will be around data (staged, faked or live). The same system will have some Unit Tests, but only around validation, filtering etc.

Repository pattern shouldn't have computational or business logic. It is very close to the database. But repository is also close to the business logic that uses it. So it is a question of striking a BALANCE.

Unit Tests can test HOW the Repository behaves. Integration Tests can test WHAT REALLY HAPPENED when the repository was called.

Now, "WHAT REALLY HAPPENED" might seem to be very enticing. But this could be very expensive for running consistently and repeatedly. Classic definition of brittle tests applies here.

So most of the time, I just find it good enough to write Unit test on an object that uses the repository. This way we test if proper repository method got called and proper Mocked results are returned.

  • 1
    I like this: "Now, "WHAT REALLY HAPPENED" might seem to be very enticing. But this could be very expensive for running consistently and repeatedly."
    – atconway
    Jun 29, 2015 at 15:36
  • Excellent insight. Feb 21 at 21:32

Unit tests provide a level of modularity that integration tests (by design) cannot. When a system is refactored or recomposed, (and this will happen) unit tests can often be reused, whereas integration tests must often be re-written. Integration tests that try to do the work of something that should be in a unit test are often doing too much, which makes them hard to maintain.

Additionally, including unit testing has the following benefits:

  1. Unit tests allow you to quickly decompose a failing integration test (possibly due to a regression bug) and identify the cause. Moreover, this will communicate the problem to the entire team more quickly than diagrams or other documentation.
  2. Unit tests can serve as examples and documentation (a type of documentation that actually compiles) along with your code. Unlike other documentation you'll know instantly when it's out of date.
  3. Unit tests can serve as baseline performance indicators when trying to decompose larger performance issues, whereas integration tests tend to require lots of instrumentation to find the problem.

It is possible to split your work (and enforce a DRY approach) while using both Unit and Integration tests. Simply rely on unit tests for small units of functionality, and don't repeat any logic that is already in a unit test in an integration test. This will often lead to less work (and therefore less re-work).


There are 3 separate things that need testing: the stored procedure, the code that calls the stored procedure (i.e. your repository class), and the consumer. The repository's job is to generate a query and convert the returned data set into a domain object. There's enough code to support unit testing that separate from the actual execution of the query and the creation of the data set.

So (this is a very very simplified example):

interface IOrderRepository
    Order GetOrderByID(Guid id);

class OrderRepository : IOrderRepository
    private readonly ISqlExecutor sqlExecutor;
    public OrderRepository(ISqlExecutor sqlExecutor)
        this.sqlExecutor = sqlExecutor;

    public Order GetOrderByID(Guid id)
        var sql = "SELECT blah, blah FROM Order WHERE OrderId = @p0";
        var dataset = this.sqlExecutor.Execute(sql, p0 = id);
        var result = this.orderFromDataset(dataset);
        return result;

Then when testing OrderRepository, pass in a mocked ISqlExecutor and check that the object under test passes in the correct SQL (that's its job) and returns a proper Order object given some result dataset (also mocked). Likely the only way to test the concrete SqlExecutor class is with integration tests, fair enough, but that's a thin wrapper class and will rarely ever change, so big deal.

You still have to unit test your stored procedures too.

  • Very good point in the discussion, which changes many advices: what happen when we are using SQL Stored Procedure. I should say, that testing the repository in such a case should be more "integration" then "unit" to make a highly usable "unit" test. This is not so high usable in a simple repositories, when a database is a storage only. Feb 21 at 21:38

I can reduce this to a very simple line of thought: Favour unit tests because it helps locate bugs. And do this consistently because inconsistencies cause confusion.

Further reasons derive from the same rules that guide OO development since they also apply to tests. For example, the single responsibility principle.

If some unit tests seem like they're not worth doing, then perhaps it's a sign that the subject isn't actually worth having. Or perhaps its functionality is more abstract than its counterpart, and the tests for it (as well as its code) can be abstracted to a higher level.

As with anything, there are exceptions. And programming is still somewhat of an art form so many problems are different enough to warrant assessing each for the best approach.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.