I'm really confused when I see a lot of in-memory database implementation used for testing, because I also heard a lot from integration testing best practices that the environment running the test should resemble as closely as possible the production environment, including operating system, library, database engine, etc.
What am I missing here?
In a typical software development situation, tests are used at two points: during development, and before moving the product along the development chain.
The first situation, running tests during development, serves short-term goals: defining tasks (as in TDD: write a failing test, then make it pass), preventing regressions, making sure your changes don't break anything else, etc. Such tests need to be extremely fast: ideally, your whole test suite runs in less than 5 seconds, and you can just run it in a loop next to your IDE or text editor while you code. Any regression you introduce will pop up within seconds. Speedy test runs are more important in this phase than catching 100% of regressions and bugs, and since it is impractical (or outright impossible) to develop on exact copies of the production systems, the effort required to achieve perfect testing here isn't worth it. Using in-memory databases is a trade-off: they are not exact copies of the production system, but they do help keep test runs below the 5-second limit; if the choice is between a slightly different database setup for my database-related testing, and no testing at all, I know what I pick.
The second situation, moving the code along the development chain, however, does require extensive testing. Since we can (and should) automate this part of the development process, we can afford much slower tests - even if a full test run takes hours, scheduling a nightly build still means we always have an accurate picture of yesterday's codebase. Simulating the production environment as accurately as possible is important now, but we can afford it. So we don't make the in-memory-database tradeoff: we install the exact same version of the exact same DBMS as the production systems, and if possible, we fill it with actual production data before the testing begins.
I guess it's a trade-off speed/matching the environnement. Tests should be run often, and that mean they got to be fast. Especially unit tests, which should not take more than a few seconds.
Integration tests will run slower, but when they're fast you can run them more often. For example, before every commit. Of course, it's not as complete as a full environment, but at least you're testing the mapping layer, the generated SQL, how the part talk to each other, etc. In the case of costly database you also ensure that you don't need to buy a licence for everyone. You may catch more error covering 90% of the code with tests ran once per hour than covering 100% of the code testing once per day or worst, week.
That being said, of course you need test with the real database and a fully integrated environment. You may not run those tests as often, but since your previous test already gave you confidence, all that is left is weird platform specific bug.
For doing simple tests, mocking the database access layer is perfectly acceptable. You call getName(), it calls the DAO thats been mocked and returns "John" for the first name and "Smith" for the last name, assembles them and everything is perfect. No need to actually unit test a database there.
Things becomes a bit more when logic becomes a bit more complex. What if you had a method "createOrUpdateUser(...)". If you mocked the database you can verify that a given method has been called once with a certain parameter when the mock returns no objects and a different method is invoked on the database when it returns back an existing object. This starts getting to that fuzzy line where it might be easier (especially if it was already there) to spin up a specialized in memory database and test that code with preconfigured data.
In some actual code I worked on (point of sales), we had a resumeSuspededTransaction(...) method. This would pull the transaction from the database into an object (and its components) and update the database. We had it mocked and a bug lurked in the code somewhere with the serialization and deserialization of the data going to the database (we changed a type which was serialized differently on the database).
The mock didn't show us the bug because it was returning its happy path - serialize the transaction, store it in the mock, deserialize it from the mock, test that they are equal. However, when you serialize an object with a leading zero to the database it was dropping them and then recombining it back to a string without the zeros. We caught the bug without the database through troubleshooting (it wasn't that hard to spot once we knew it was there).
Later, we put a database in there and realized that the bug would have never gotten through that junit test if we were instead going to an in memory database.
In memory databases have the advantages:
This depends much on the database system you are using. When your db system provides you with an in-memory alternative which is almost 100% API and behaviour compatible to a disk-based database configuration (except for speed and beeing failsafe, or course), then using the in-memory variant is obviously fine.
If, however, your DB system has significant differences between in-memory configuration and non-in-memory usage, you are right: in this case integration tests have a higher risk of shadowing a bug. But even then, you may be able to "abstract that differences away" by yourself, given you know your DB system well and the differences.
In layman's words:
The mocking of important parts of the architecture is OK (and a must) for unit testing.
But for integration testing, I strongly agree with you. Mocking shouldn't be done and an environment as similar as possible as the real one should be provided.
After all, integration tests are about testing how the different parts of the architecture behave together.
