In reply to our recent tip on keeping your test database separate, I got a question about whether or not we use SQLite as a test database. It would still satisfy the goal of keeping the test database separate from your local development database.
Personally, I don't feel comfortable with this approach. We use MySQL in production, and I get nervous using different infrastructure for tests and CI compared to what is actually used in production.
This isn't some abstract fear either. I've bumped into more than one issue where a test against SQLite passed, despite the behavior being broken with MySQL in some subtle way, allowing a bug to creep into production.
It's undeniable that using SQLite in tests is faster and easier to set up than spinning up a production-like MySQL database, but for me the main value of tests is gaining confidence my code works as expected.
Anything that undermines that confidence, even if it comes with other advantages, is just not worth it to me.
Also, don't forget there are a lot of MySQL functions that just don't exist in SQLite either. So even without the subtle differences in behavior, if you ever use raw SQL in your application code, you're probably going to run into a wall with using SQLite for tests anyway.
Here to help,
Joel
P.S. There are better ways to make test suites faster than switching to an in-memory database. Let us know if you'd like help speeding your test suite up.