Unit Testing with SQLite3

Question

I am writing unit tests and use SQLite3 with his inmemory mode.

I am doing this because i need is a fresh database for each test so i can run tests in parallel without affecting each other.

But this gets, off course, slower and slower the more tests i have.

Are there any optimisations i can do with sqlite3, another better workflow (e.g. a good mocking library) or another solution do this problem? I need to run raw queries.

Currently i use knexjs but this is related to allmost every data access library.

Answer 1

You don't need to run raw queries against a database.

In the scope of unit tests, you can assume that your persistence layer works, i.e. that it is able to upsert and delete.

When writing unit test, mock away your database connection. In regards to unit tests, it's not important that the queries are performed against a database but that your business logic behaves in a specific way when getting certain inputs. (That could also mean having your mocked database connection to throw exceptions to ensure that your application behaves in a fail-safe manner.)

There is value in having integration tests that actually do run the queries against a test database no matter the environment (local, staging or even production).

Yet those are slower and harder to set up by definition and can only be parallelized to a certain degree. (If you rely too much on them, your test setup resembles an ice cone and not a pyramid.)

They still make sense for business-critical features (e.g. shopping cart click through). Yet if your focus is writing unit tests, make sure you unit test your application and not your persistence layer.