In which cases do you test against an In-Memory Database instead of a Development Database?

Question

When do you test against an In-Memory Database vs. a Development Database?

Also, as a related side question, when you do use a Development Database, do you use an Individual Development Database, an Integration Development Database, or both?

Also++, for unit testing, when do you use an In-Memory Database over mocking out your Repository/DAL, etc.?

Answer 1

For my team, it's in-memory on developper machine, and the real-database on the continuous integration server.

Answer 2

In memory is an excellent choice for your unit-tests, when the data is easy to seed for your given test cases and a very particular operation is being tested. A real database is better for integration tests, where the data pre-requisites are more complex and there is value to having the base data remain after the tests complete.

For us, the only things we allow in our 'fast' test suite of JUnit tests are those that do not have any external dependencies (database, file, network, etc) so that the suite can be run quickly and efficiently by both developers and continuous integration on checkin. If there is a certain test that absolutely needs to go to the DB, then an in memory one is the only way to go.

A couple points to keep in mind:

• Think carefully if you need to use a database at all in a unit test. It may be indicative of a poor design in that the data access layer is coupled too tightly to the business logic you are trying to test and cannot be mocked out.
• If using a real database for integration testing, ensure that the tests always restore the data to a pristine state when finished. I've seen a lot of wasted time and failed integration tests because some other test messed up the data.

As for your other question, it really depends on your need. A good rule of thumb is one development database per code branch, since schema changes may be needed that are not relevant to another branch of code. Just having a dedicated development database is important; I'm surprised at how many development teams have to share a database with the QA team, etc. It is important to be able to make changes in a sandboxed environment that does not affect other teams or prevent others from doing their work, so if you've met those requirements you're doing well.