Are (database) integration tests bad?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

source: p.470 in Code Complete 2 by McConnell

Answer 4

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:

IRespository

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.

Answer 5

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.

Conclusion

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?

Answer 6

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.

Answer 7

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)

Answer 8

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.

Answer 9

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.

Answer 10

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.

https://cucumber.io/

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.

https://stackoverflow.com/questions/2665812/what-is-mocking

"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.

https://www.joelonsoftware.com/2009/01/31/from-podcast-38/

Answer 11

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.