Using a different database engine for testing

Question

This was inspired by the Speeding up Django unit tests with SQLite, keepdb and /dev/shm article that demonstrates how switching from PostgreSQL to an in memory SQLite helps to speed up testing during the development phase. But, the article warns the readers to make sure on the CI there is a real database used:

But I'm only advocating this for use in development! Ultimately, Postgres or whichever database you're using will behave differently from SQLite. Django does a good job of abstracting away 90% of those differences, but that still leaves plenty of strange edge case behaviours to do with default values, ordering and transactions that can easily trip you up.

Now, I am curious, what are these possible edge cases, what can potentially go wrong if one would continue to use SQLite for testing on a CI server as well? Is there any evidence or existing historical examples of this kind of issues?

Answer 1

I'm a little out of date using sqllite but data types and stored procedures come to mind as feature gaps between sqllite and something like PostgreSQL. Beyond features, configuration settings (ex: threads in sqllite) could cause further irritation while trying to speed up your tests if used incorrectly.

More generally though, you should understand that what you're proposing moves you further away from how your app stack (and data) works in production. Each of these differences can be a challenge to the determinism and usefulness of the tests you write and the results they produce (or even for manual testing).

Answer 2

Using a different database engine makes sense only if you use just a bland simple SQL, avoiding any performance-enhancing tricks your chosen database provides.

IMHO that would be sub-optimal: you are using certain DB engine for a reason, and you might as well use it to the fullest - unlikely you will be switching to different engine anytime soon.

What we do is that for testing, we use the same engine but separate, much smaller dataset (which fits in RAM, with caching), which makes tests to run somewhat faster than full production database.