Some people maintain that integration tests are all kinds of bad and wrong - everything must be unit-tested, which means you have to mock dependencies; an option which, for various reasons, I'm not always fond of.

I find that, in some cases, a unit-test simply doesn't prove anything.

Let's take the following (trivial, naive) repository implementation (in PHP) as an example:

class ProductRepository
    private $db;

    public function __construct(ConnectionInterface $db) {
        $this->db = $db;

    public function findByKeyword($keyword) {
        // this might have a query builder, keyword processing, etc. - this is
        // a totally naive example just to illustrate the DB dependency, mkay?

        return $this->db->fetch("SELECT * FROM products p"
            . " WHERE p.name LIKE :keyword", ['keyword' => $keyword]);

Let's say I want to prove in a test that this repository can actually find products matching various given keywords.

Short of integration testing with a real connection object, how can I know that this is actually generating real queries - and that those queries actually do what I think they do?

If I have to mock the connection object in a unit-test, I can only prove things like "it generates the expected query" - but that doesn't mean it's actually going to work... that is, maybe it's generating the query I expected, but maybe that query doesn't do what I think it does.

In other words, I feel like a test that makes assertions about the generated query, is essentially without value, because it's testing how the findByKeyword() method was implemented, but that doesn't prove that it actually works.

This problem isn't limited to repositories or database integration - it seems to apply in a lot of cases, where making assertions about the use of a mock (test-double) only proves how things are implemented, not whether they're going to actually work.

How do you deal with situations like these?

Are integration tests really "bad" in a case like this?

I get the point that it's better to test one thing, and I also understand why integration testing leads to myriad code-paths, all of which cannot be tested - but in the case of a service (such as a repository) whose only purpose is to interact with another component, how can you really test anything without integration testing?

  • 7
    Read agitar.com/downloads/TheWayOfTestivus.pdf, especially page 6 "The test is more important than the unit".
    – Doc Brown
    Commented Nov 2, 2015 at 21:20
  • 2
    @user61852 it says "naive" in the description, yes? Commented Nov 2, 2015 at 21:37
  • 7
    How would your coworker be absolutely certain that his mocked database behaves as the real thing? Commented Nov 2, 2015 at 22:11
  • 14
    You are trying to be realistic. Your coworker is trying to adhere to rules. Always write tests that produce value. Don't waste time writing tests that will be unmaintable, and don't write tests that don't do one of the following: increase the probability your code is correct or force you to write more maintainable code.
    – jpmc26
    Commented Nov 2, 2015 at 23:26
  • 4
    @mindplay.dk: the key sentence in that paragraph is "But don’t get stuck on any dogma. Write the test that needs to be written.” Your co-worker seems be stuck in a dogma. And you do not need someone explaining you what the test is that needs to be written in your example - you already know that. It is pretty obvious that for testing if your database understands the query, you have to run the query against the real database - no mock can tell you this.
    – Doc Brown
    Commented Nov 3, 2015 at 4:51

11 Answers 11


Write the smallest useful test you can. For this particular case, an in-memory database might help with that.

It is generally true that everything that can be unit-tested should be unit-tested, and you're right that unit tests will take you only so far and no further—particularly when writing simple wrappers around complex external services.

A common way of thinking about testing is as a testing pyramid. It's a concept frequently connected with Agile, and many have written about it, including Martin Fowler (who attributes it to Mike Cohn in Succeeding with Agile), Alistair Scott, and the Google Testing Blog.

        /\                           --------------
       /  \        UI / End-to-End    \          /
      /----\                           \--------/
     /      \     Integration/System    \      /
    /--------\                           \----/
   /          \          Unit             \  /
  --------------                           \/
  Pyramid (good)                   Ice cream cone (bad)

The notion is that fast-running, resilient unit tests are the foundation of the testing process. There should be more focused unit tests than system/integration tests, and more system/integration tests than end-to-end tests. As you get closer to the top, tests tend to take more time/resources to run, tend to be subject to more brittleness and flakiness, and are less-specific in identifying which system or file is broken; naturally, it's preferable to avoid being "top-heavy".

To that point, integration tests aren't bad, but heavy reliance on them may indicate that you haven't designed your individual components to be easy to test. Remember, the goal here is to test that your unit is performing to its spec while involving a minimum of other breakable systems: You may want to try an in-memory database (which I count as a unit-test-friendly test double alongside mocks) for heavy edge-case testing, for instance, and then write a couple of integration tests with the real database engine to establish that the main cases work when the system is assembled.

As you noted, it's possible for tests to be too narrow: you mentioned that the mocks you write simply test how something is implemented, not whether it works. That's something of an antipattern: A test that is a perfect mirror of its implementation isn't really testing anything at all. Instead, test that every class or method behaves according to its own spec, at whatever level of abstraction or realism that requires.

In that sense your method's spec might be one of the following:

  1. Issue some arbitrary SQL or RPC and return the results exactly (mock-friendly, but doesn't actually test the query you care about)
  2. Issue exactly the SQL query or RPC and return the results exactly (mock-friendly, but brittle, and assumes SQL is OK without testing it)
  3. Issue an SQL command to a similar database engine and check that it returns the right results (in-memory-database-friendly, probably the best solution on balance)
  4. Issue an SQL command to a staging copy of your exact DB engine and check that it returns the right results (probably a good integration test, but may be prone to infrastructure flakiness or difficult-to-pinpoint errors)
  5. Issue an SQL command to your real production DB engine and check that it returns the right results (may be useful to check deployed behavior, same issues as #4 plus the dangers of modifying production data or overwhelming your server)

Use your judgment: Pick the quickest and most resilient solution that will fail when you need it to and give you confidence that your solution is correct.

  • 17
    +1 for "A test that is a perfect mirror of its implementation isn't really testing anything at all." All too common. I call this the Doppelganger Antipattern. Commented Nov 3, 2015 at 22:26
  • 8
    A contrarian school of software QA, the context-driven testing movement, is in part devoted to disputing that there exist any such useful general rules of thumb as the Testing Pyramid. In particular, the seminal texts of the movement give many examples where integration tests are far more valuable than other kinds of tests (because they test the system in context, as a system).... Commented Nov 3, 2015 at 22:32
  • 10
    ... consequently, Fowler et al., in arguing that less effort should be spent on integration tests and user acceptance tests because they are too difficult to write in a robust and maintainable way, are really just providing an ex post facto excuse for why they haven't figured out how to test well at higher levels. Commented Nov 3, 2015 at 22:38
  • 1
    @dodgethesteamroller In-depth discussions of a "contrarian school" like that are probably best suited to their own answer. Personally, I find that the Google Testing Blog does a pretty good job describing the virtues of fast, tightly-scoped automated tests alongside system-in-context tests. In case you found it unclear, I list the test pyramid here as a useful model or starting point, not as an excuse to stop thinking as an engineer. Commented Nov 3, 2015 at 23:01
  • 2
    Highly recommended presentation on the hierarchy and ratio of unittests to integration tests: vimeo.com/80533536 Very well explained.
    – rszalski
    Commented Nov 6, 2015 at 19:38

One of my co-workers maintains that integration tests are all kinds of bad and wrong - everything must be unit-tested,

That's a little like saying that antibiotics are bad - everything should be cured with vitamins.

Unit tests can't catch everything - they only test how a component works in a controlled environment. Integration tests verify that everything works together, which is harder to do but more meaningful in the end.

A good, comprehensive testing process uses both types of tests - unit tests to verify business rules and other things that can be tested independently, and integration tests to make sure everything works together.

Short of integration testing with a real connection object, how can I know that this is actually generating real queries - and that those queries actually do what I think they do?

You could unit test it at the database level. Run the query with various parameters and see if you get the results you expect. Granted it means copying/pasting any changes back into the "true" code. but it does allow you to test the query independent of any other dependencies.

  • Wouldn't you be testing whether or not the database contains certain data? Commented Nov 2, 2015 at 20:04
  • Possibly - but you could also be testing that your filters, complex joins, etc. work. The example query is probably not the best candidate for "unit tests" but one with complex joins and/or aggregations might.
    – D Stanley
    Commented Nov 2, 2015 at 21:45
  • Yes - the example I used, as pointed out, is trivial; a real repository could have all manner of complex search and sorting options, e.g. using a query builder, etc. Commented Nov 2, 2015 at 22:02
  • 2
    Good answer, but I would add that the DB should be in memory, to make sure that unit tests are fast. Commented Nov 3, 2015 at 13:03
  • 4
    @BЈовић: Unfortunately, that might not always be possible, since unfortunately there are no two compatible DBs out there and not all of them work in memory. There are also licensing issues for commercial DBs (you might not have the license to run it on any machine), ... Commented Nov 3, 2015 at 14:21

Unit tests don't catch all defects. But they are cheaper to set up and (re)run compared that other kinds of tests. The unit tests are justified by combination of moderate value and low-to-moderate cost.

Here's a table showing defect detection rates for different kinds of testing.

enter image description here

source: p.470 in Code Complete 2 by McConnell

  • 9
    That data was gathered in 1986. That was thirty years ago. Unit tests in 1986 were not what they are today. I'd be skeptical of this data. Not to mention, unit tests detect the bug before its ever committed, so it's doubtful that they would be reported.
    – RubberDuck
    Commented Jun 18, 2016 at 12:11
  • 6
    @RubberDuck This chart is from a 2006 book, and it's based on data from 1986, 1996, 2002 (if you look carefully). I haven't looked into the data gathering protocols in the sources, I can't say when they started to track a defect and how it was reported. Could this chart be somewhat out of date? It could. Last December I was at a seminar, and the instructor mentioned that integration tests find more bugs than unit tests (by a factor of 2, iirc). This chart says that they are about the same. Commented Jun 18, 2016 at 21:02
  • My dad did a lot of research in this area, back when IBM was doing research on how to better reduce software defects (a long time ago). The reason that most of these numbers are low (below 40%) is because most people find the same issues. The "other 60%" is the stuff that they don't look for, because they found "something" Formal code review and modeling get higher numbers because people are looking for issues while the solution is being designed. High volume tests have more people trying to find that first-reported issue, and gets a wider statistical spread.
    – Edwin Buck
    Commented Apr 16 at 14:30
  • But what this table fails to account for is the cost of the testing. Unit tests are wildly popular because, if they are written properly, they have nearly no maintenance costs. Designing software is often seen as an avoidable expense, which means verification becomes "verify the code against the author" which is also cheaper having two people, instead of the more robust "three people" pattern of "designer, implementer, verifier" pattern. And a high volume beta test is quite expensive, as it requires a large deployment, with real data.
    – Edwin Buck
    Commented Apr 16 at 14:34
  • @Edwin Another thing that the table doesn't account for is the toughness of the defects and how early they are found by each kind of test. I agree with you that unit tests are popular because they are easy to code and maintain, not because they find tough bugs early. Commented Apr 16 at 17:14

No, they are not bad. Hopefully, one should have unit and integration tests. They are used and run at different stages in the development cycle.

Unit Tests

Unit tests should be run on the build server and locally, after the code has been compiled. If any unit tests fail, one should fail the build or not commit the code update until the tests are fixed. The reason why we want unit tests isolated is that we want the build server to be able to run all the tests without all the dependencies. Then we could run the build without all the complex dependencies required and have a lot of tests that run very fast.

So, for a database, one should have something like:


List<Product> GetProducts<String Size, String Color);

Now the real implementation of IRepository will go to the database to get the products, but for unit testing, one can mock IRepository with a fake one to run all the tests as needed without an actaul database as we can simulate all sorts of lists of products being returned from the mock instance and test any business logic with the mocked data.

Integration Tests

Integration tests are typically boundary crossing tests. We want to run these tests on the deployment server (the real environment), sandbox, or even locally (pointed to sandbox). They are not run on the build server. After the software has been deployed to environment, typically these would be run as post deployment activity. They can be automated via command line utilities. For example, we can run nUnit from the command line if we categorize all the integration test we want to invoke. These actually call the real repository with the real database call. These type of tests help with:

  • Environment Health Stability Readiness
  • Testing the real thing

These tests are sometimes harder to run as we may need to set up and/or tear down as well. Consider adding a product. We probably want to add the product, query it to see if it was added, and then after we are done, remove it. We don't want to add 100s or 1000s of "integration" products, so additional set-up is required.

The integration tests can prove to be quite valuable to validate an environment and making sure the real thing works.

One should have both.

  • Run the unit tests for every build.
  • Run the integration tests for every deployment.
  • I would recommend running integration tests for every build, rather than having to commit and push. Depends how long they take, but it's also a good idea to keep them fast for many reasons.
    – artbristol
    Commented Jan 7, 2017 at 23:25
  • @ArtBristol - Typically our build server does not have the full environment dependency configured, so we can't run our integration tests there. But if one can run the integration tests there, go for it. We have a deployment sandbox set up after the build which we use for integration testing to verify the deployment. But each situation is different.
    – Jon Raynor
    Commented Jan 9, 2017 at 15:19

Database integration tests are not bad. Even more, they are necessary.

You probably have your application split into layers, and it's a good thing. You can test each layer in isolation by mocking neighbouring layers, and that's a good thing too. But no matter how many abstraction layers you do create, at some point there has to be layer that does the dirty work - actually talk to the database. Unless you test it, you don't test at all. If you test layer n by mocking layer n-1 you are evaluating assumption that layer n works on condition that layer n-1 works. In order for this to work, you must somehow prove that layer 0 works.

While in theory you could unit test database, by parsing and interpreting generated SQL, it's way much easier and more reliable to create test database on the fly and talk to it.


What's the confidence geined from unit testing your Abstract Repository, Ethereal Object-Relational-Mapper, Generic Active Record, Theoretic Persistence layers, when in the end your generated SQL contains syntax error?

  • I was thinking of adding a reply similar to yours but you've said it better! In my experience having some tests on the layer that gets and stores data has saved me a lot of grief. Commented Nov 4, 2015 at 14:37
  • Database integration tests are bad though. Do you have a database available in your ci/cd line? That seems pretty complex to me. It is much easier to mock the database stuff and build a layer of abstraction to use that. Not only is that a much more elegant solution, it is as fast as can be. Unit tests need to be fast. Testing your database slows down your unittests significantly to a level that it not acceptable. Unittests shouldn't take > 10 minutes to be done, even when you have thousands of them.
    – David
    Commented Nov 12, 2018 at 8:46
  • @David Do you have a database available in your ci/cd line? Sure, that's pretty standard feature. BTW, I'm not advocating integration tests instead of unit tests - I'm advocating integration tests in conjunction with unit tests. Fast unit tests are essential, but databases are too complex to rely on unit tests with mocked interactions. Commented Nov 13, 2018 at 8:24
  • @el.pescado I have to disagree. If your database communication is behind a layer of abstraction, it is really easy to mock. You can just decide which object to return. Also, the fact that something is standard doesn't make it a good thing.
    – David
    Commented Nov 13, 2018 at 21:26
  • @David I think it depends on how do you approach database. Is it implementation detail or vital part of system? (I lean towards the latter). If you treat database as a dumb storage of data, then yes, you might do without integration tests. However, if there's any logic in database - constraints, triggers, foreign keys, transactions, or your data layer uses custom SQL instead of plain ORM methods, I feel unit tests alone won't be enough. Commented Nov 14, 2018 at 6:59

The author of the blog article you refer to is mainly concerned with the potential complexity that can arise from integrated tests (although it is written in a very opinionated and categorical way). However, integrated tests are not necessarily bad, and some are actually more useful than pure unit tests. It really depends on the context of your application and what you're trying to test.

Many applications today would simply not work at all if their database server went down. At least, think of it in the context of the feature you're trying to test.

On the one hand, if what you're trying to test doesn't depend, or can be made not to depend at all, on the database, then write your test in such a way that it doesn't even try to use the database (just provide mock data as required). For example, if you're trying to test some authentication logic when serving a web page (for example), it's probably a good thing to detach that from the DB altogether (assuming you don't rely on the DB for authentication, or that you can mock it reasonably easily).

On the other hand, if it's a feature that directly relies on your database and that wouldn't work in a real environment at all should the database be unavailable, then mocking what the DB does in your DB client code (i.e. the layer using that DB) doesn't necessarily make sense.

For example, if you know that your application is going to rely on a database (and possibly on a specific database system), mocking the database behaviour for the sake of it often will be a waste of time. Database engines (especially RDBMS) are complex systems. A few lines of SQL can actually perform a lot of work, which would be difficult to simulate (in fact, if your SQL query is a few lines long, chances are you'll need many more lines of Java/PHP/C#/Python code to produce the same result internally): duplicating the logic you've already implemented in the DB doesn't make sense, and checking that test code would then become a problem in itself.

I wouldn't necessarily treat this as a problem of unit test v.s. integrated test, but rather look at the scope of what is being tested. The overall problems of unit and integration testing remain: you need a reasonably realistic set of test data and test cases, but something that is also sufficiently small for the tests to be executed quickly.

The time to reset the database and repopulate with test data is an aspect to consider; you would generally evaluate this against the time it takes to write that mock code (which you would have to maintain too, eventually).

Another point to consider is the degree of dependency your application has with the database.

  • If your application simply follows a CRUD model, where you have a layer of abstraction that lets you swap between any RDBMS by the simple means of a configuration setting, chances are you'll be able to work with a mock system quite easily (possibly blurring the line between unit and integrated testing using an in-memory RDBMS).
  • If your application uses more complex logic, something that would be specific to one of SQL Server, MySQL, PostgreSQL (for example), then it would generally make more sense to have a test that use that specific system.
  • "Many applications today would simply not work at all if their database server went down" - that's really important point! Commented Nov 4, 2015 at 14:15
  • Good explanation of the limits of complex mocks, like using another language to mock SQL. When test code gets complicated enough that it seems like it needs to be tested itself, that's a QA smell. Commented Nov 12, 2015 at 18:49

You need both.

In your example if you were testing that a database in a certain condition, when the findByKeyword method is run you get the data back you expect this is a fine integration test.

In any other code that is using that findByKeyword method you want to control what is being fed in to the test, so you can return nulls or the right words for your test or whatever then you mock the database dependency so you know exactly what your test will receive (and you lose the overhead of connecting to a database and ensuring the data within is correct)


You are right to think of such a unit test as incomplete. The incompleteness is in the database interface being mocked. Such naive mock's expectation or assertions are incomplete.

To make it complete, you'd have to spare enough time and resources to write or integrate a SQL rules engine that would guarantee that SQL statement being emitted by subject under test, would result in expected operations.

However, the often forgotten and somewhat expensive alternative/companion to mocking is "virtualization".

Can you spin up a temporary, in-memory but "real" DB instance for testing a single function ? yes ? there, you have a better test, the one that does check actual data saved and retrieved.

Now, one might say, you turned a unit test into an integration test. There are varying views about where to draws the line to classify between unit tests and integration tests. IMHO, "unit" is an arbitrary definition and should fit your needs.

  • 1
    this seems to merely repeat points made and explained in this prior answer that was posted several hours ago
    – gnat
    Commented Nov 3, 2015 at 21:15

Unit Tests and Integration Tests are orthgonal to each other. They offer a different view on the application you are building. Usually you want both. But the point in time differs, when you want which kind of tests.

The most often you want Unit Tests. Unit tests focus on a small portion of the code being tested - what exactly is called a unit is left to the reader. But te purpose is simple: getting fast feedback of when and where your code broke. That said, it should be clear, that calls to an actual DB is a nono.

On the other hand, there are things, that can only be unit tested under hard conditions without a database. Perhaps there is a race condition in your code and a call to a DB throws a violation of a unique constraint which could only be thrown if you actually use your system. But those kinds of tests are expensive you can not (and do not want to) run them as often as unit tests.


In the .Net world I have a habit of creating a testing project and creating tests as a method of coding / debugging / testing round trip minus the UI. This is an efficient way for me to develop. I wasn’t as interested in running all the tests for every build (because it does slow down my development work flow), but I understand the usefulness of this for a larger team. Nevertheless, you could make a rule that before committing code, all tests should be run and pass (if it takes longer for the tests to run because the database is actually being hit).

Mocking out the data access layer (DAO) and not actually hitting the database, not only does not allow me to code the way I like to and have become accustomed to, but it misses a large piece of the actual code base. If you’re not truly testing the data access layer and database and just pretending, and then spending a lot of time mocking things up, I fail to grasp the usefulness of this approach to actually test my code. I’m testing a small piece instead of a larger one with one test. I understand my approach might be more along the lines of an integration test, but it seems like the unit test with the mock is a redundant waste of time if you actually just write the integration test once and first. It’s also a good way to develop and debug.

In fact, for a while now I have been aware of TDD and Behavior Driven Design (BDD) and thinking about ways to use it, but it’s hard to add unit tests retroactively. Perhaps I am wrong, but writing a test that covers more code end to end with the database included, seems like a much more complete and higher priority test to write that covers more code and is a more efficient way to write tests.

In fact, I think something like Behavior Driven Design (BDD) that attempts to test end to end with domain specific language (DSL) should be the way to go. We have SpecFlow in .Net world, but it started as open source with Cucumber.


I’m just really not impressed with the true usefulness of the test I wrote mocking out the data access layer and not hitting the database. The object returned did not hit the database and was not populated with data. It was an entirely empty object that I had to mock up in an unnatural way. I just think it’s a waste of time.

According to Stack Overflow, mocking is used when real objects are impractical to incorporate into the unit test.


"Mocking is primarily used in unit testing. An object under test may have dependencies on other (complex) objects. To isolate the behaviour of the object you want to test you replace the other objects by mocks that simulate the behavior of the real objects. This is useful if the real objects are impractical to incorporate into the unit test."

My argument is that if I am coding anything end to end (web UI to business layer to data access layer to database, round trip), before I check anything in as a developer, I’m going to test this round trip flow. If I cut out the UI and debug and test this flow starting from a test, I am testing everything short of the UI and returning exactly what the UI expects. All I have left is to send the UI what it wants.

I have a more complete test that is part of my natural development workflow. To me, that should be the highest priority test that covers testing the actual user specification end to end as much as possible. If I never create any other more granular tests, at least I have this one more complete test that proves my desired functionality works.

A co-founder of Stack Exchange isn't convinced about the benefits of having 100% unit test coverage. I also am not. I would take a more complete "integration test" that hits the database over maintaining a bunch of database mocks any day.


  • it is so obvious that you do not understand the difference between unit and integration tests Commented Mar 10, 2017 at 10:55
  • I think it depends on the project. On smaller projects with fewer resources where a developer is more overall responsible for testing and regression testing because of a lack of testers and keeping documentation in synch with code, if I'm going to spend any time writing tests, it's going to be ones that gives me the most bang for my buck. I want to kill as many birds with one stone as possible. If most of my logic and bugs come from database stored procedures which are generating reports, or from the front end JavaScript, having full unit test coverage in the middle tier doesn't help much. Commented Mar 11, 2017 at 15:43

External dependencies should be mocked because you can't control them (they might pass during the integration testing phase but fail in production). Drives can fail, database connections could fail for any number of reasons, there could be network problems etc. Having integration tests doesn't give any extra confidence because they're all problems that could happen at runtime.

With true unit tests, you're testing within the limits of the sandbox and it should be clear. If a developer wrote an SQL query that failed in QA/PROD it means they didn't even test it once before that time.

  • +1 for "you can't control them (they might pass during the integration testing phase but fail in production)". Commented Nov 2, 2015 at 20:24
  • You can control them to satisfactionary degree. Commented Nov 2, 2015 at 21:29
  • I get your point, but I think this used to be more true than it is today? With automation and tools (like Docker) you actually can replicate and repeat the setup of all your binary/server dependencies accurately and reliably for integration test-suites. Of course, yes, physical hardware (and third-party services etc.) can fail. Commented Nov 2, 2015 at 22:15
  • 6
    I absolutely do not agree. You should write (additional) integration tests because extenal dependencies can fail. External dependencies may have their own quirks, which you'll most likely miss when you mock everything. Commented Nov 3, 2015 at 9:05
  • 1
    @PaulK still pondering which answer to mark as accepted, but I'm leaning towards the same conclusion. Commented Nov 3, 2015 at 11:23

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