[dspillett]

This is one of those things that I would hope goes without saying, but obviously doesn't... Always test against what you expect to see in production. If you test against something else first (in this case mocking through an in-memory DB) to make the testing of other parts faster/easier then that is fine, but once those tests are done you still need to do a final full test against the real stack(s) you expect to see in production.

On the mocking thing: I thought the point of that was to completely avoid DB calls for speed when testing other logic layers, so even a fast in-memory DB isn't needed and anything (i.e. just some code that manufactures a response object) that produces a result set in the right format will do? In that case even using an in-memory DB is less efficient than the other option so is at best a "half optimisation". Am I missing a point here?

[frobaldo]

Yeah, when not hitting any DB, you're not testing runtime with ORMs. Got bit by that.

.net Entity Framework has this problematic limitation that you can't check if SQL queries will be properly built without DB - example situation is if you're trying to use not mapped .net method in your SQL-targeting queries. That's a runtime error you can't (yet) test without DB.

I'm cautious enough to believe other ORMs may have similar quirks, only testable with something to query on.

[plorkyeran]

If the in-memory DB has the same feature set as the real DB, using the in-memory DB for tests should be a single flip you switch, while writing code to generate fake result sets for every tests requires writing all of that code. 90% of the benefits for 1% of the cost.

[jeltz]

But they do not have the same feature sets. And PostgreSQL trivially be tuned to work almost like an in-memory database.