Yesterday, I was looking for some help trying to have a clean way to roll back database transactions at the end of a test in a Python TestCase. So, naturally, I started searching and found some posts on Stack Overflow and started to read them. I was instantly mortified by what I read.
But in any case, an in-memory database (SQLite makes this easy) is the best choice for unit tests.Huh? I, at least, will listen and have a nice healthy debate when people suggest you should mock out every call to the database in the unit tests because if you are talking to the database it's not a unit test its a functional test. That argument is mainly one of semantics of what you call the test. In the case of using a different database engine in a testing environment than a production environment, why would you want to do that?
What's worse is the next stack overflow question I went to had almost the same answer by another person.
You can simply create an in-memory SQLite database and bind your session to that.
So, first, I wanted to stand on the rooftop and yell at the top of my lungs to anyone who would listen. This is a really bad idea. Here's why.
- SQLLite has different SQL semantics than Postgres, so a query written on SQLLite may not run in Postgres
- SQLLite has different bugs than Postgres, so just because it works in SQLLite does not mean it will work in Postgres. Also the reverse is true, just because you can't get it to work in SQLLite doesn't mean it won't work in Postgres
- Postgres has way more features that SQLLite. Try doing a CTE query in SQLLite. You can't use any of these features in your application if you test in a different database than you use for your production environment.
- How can you test deployments if your test environment uses a different database than your production environment?
- How can you test that your application works with the newest version of the database before you push it to production?
This sentiment about testing in a different database than you use in production is not just limited to stackoverflow it seems. I found a document on leading cloud service provider's site that said this:
When To Use SQLiteThis is even more strange coming from them since one of the valid reasons cloud service providers give for using their services is that you can have a test environment that mirrors your production environment as closely as possible.
It is an overkill for a large portion of applications to use an additional process for testing the business-logic (i.e. the application's main purpose: functionality).
I specifically mentioned SQLLite and Postgres, but in reality you could replace any two technologies and the arguments don't change. You wouldn't want to test on SQLLite and use MySQL in production, nor would you want to test on Postgres and use Oracle in production.
So remember don't develop and test against one database and release against a different database.