Unit Testing and what to do with the database?

[Daissy538]

I'm digging into the world of unit testing and TDD. I'm reading the book "Unit Testing - Principles, Practices and Patterns" by Vladimir Khorikov. I came across the statement that you shouldn't stub/mock a database.

For a project, we use an in-memory SQLite db. The db is getting cleaned up for each unit test.
I don't have any experience with doing something different with the database than this.

I was looking into the GitHub project (banking-kat for .NET) and saw the use of a fake repository. I also read a previous discussion about fake repositories. There was also mentioned that it is a bad practice to use SQLite db.

2 things I like to know:

• I like to know why it is bad practice to use a SQLite in-memory db. I see only that practice around me in my company. We don't do a lot of unit testing and I want to improve our way of work.
• I also like to know what is a better option for replacement of the SQL db in the unit tests and why.

I'm a practical person. So if you have an example or situation description, that would be great!

[valentinacupac]

Great question @Daissy538! Databases are definitely a controversial topic.

BACKGROUND

Currently, there are three approaches:

1. Mockist school - they mock all collaborators (i.e. mocking out any collaborators for some class)
2. Classicist school - they mock out only shared dependencies (like access to DB, file system, etc.)
3. Vladimir Khorikov - he wrote "Both schools are wrong in their treatment of mocks" and describes to NOT mock out the DB https://enterprisecraftsmanship.com/posts/when-to-mock/ --> so with this his approach is different compared to the 2 schools above

Currently, there is no general consensus regarding this, what is better?

Why I'm illustrating the usage of test doubles in the banking-data:

• The usage of test doubles (test doubles for repositories, message queues, notifications, etc.) is currently the mainstream approach - in both London and Classicist TDD.
• The usage of test doubles is traceable even to hexagonal architecture whereby the rule is to replace any driven ports by test doubles, because the idea is to "protect" the hexagon from any infrastructural concerns (we separate our application from any I/O)
• I write unit tests which target driver ports (use cases) so as to maximize testability. Testing use cases is also illustrated by Uncle Bob in the Clean Coders video - London vs Chicago series
• I want to be able to swap out

Now when I look at code examples in "Unit Testing - Principles, Practices and Patterns" by Vladimir Khorikov.

In Section 10.2.1 Managing database transactions in production code, in listing 10.3 he shows that UserRepository is a concrete class (there's no interface!) - and it accepts Transaction (though later he shows a DBContext - CrmContext being passed in). He also mentions in 10.3.3. Avoid in-memory databases and he describes the reason why he doesn't recommend in-memory databases like SQLite.

So you will see the following differences between the code samples which I show versus the book:

• Currently, in practice, what I've been doing for the past several years and what I show on GitHub is the Classicist TDD approach, applied to Hexagonal/Clean Architecture. In Classicist TDD approach, DB is replaced by test double. Furthermore, in Hexagonal Architecture, the tests are targeting the user-side API / the driver ports (and NOT the domain model inside!). Read more here https://jmgarridopaz.github.io/content/hexagonalarchitecture.html and sample code https://github.com/jmgarridopaz/bluezone (I came across (Juan Manuel Garrido de Paz because Alistair Cockburn recommended his materials). The other reason I write tests targeting use cases is due to Clean Architecture - Uncle Bob. Also, influence from Ian Cooper - TDD and Clean Architecture, multiple YouTube videos. The third reason is, I've found tests coupled to use cases to be most robust and readable as requirements (use cases are requirements). Also you can watch this video by @mica16 https://www.youtube.com/watch?v=HbF4MAr0kk8

• In Khorikov's book, as far as I can see, he is mainly recommending unit testing the domain model (the domain logic) ... but NOT unit testing the controllers (the application logic / workflow logic).... P.S. Here controllers, as far as I see is the only place where the application logic is contained, thus they are akin to use cases in Uncle Bob's terminology... Also I can see Khorikov's approach is similar as people who follow primarily domain-driven rather than use-case driven approach, because in domain-driven someone is starting with the domain (hence unit testing it)

I currently cannot say that either approach is good or bad.

The approach I illustrated is the approach I'm using in practice with teams, it allowed me to maximize the area of unit testability (I can test application logic and transitively domain logic).

YOUR QUESTIONS

To answer your questions:

For a project, we use an in-memory SQLite db. The db is getting cleaned up for each unit test.

Ok I find that approach somewhere in-between unit testing versus integration testing.

• On one hand, these tests are in-memory fast running, which is needed for unit tests.
• On the other hand, there is shared state (you mentioned DB clean up for each test), so then these tests can't run effectively run in parallel because they're not isolated. This is the case with migration testing.

I was looking into the GitHub project (banking-kat for .NET) and saw the use of a fake repository. I also read a previous discussion about fake repositories.

Correct, I showcase usage with a test double (here fake repository) as per Classicist TDD style.

There was also mentioned that it is a bad practice to use SQLite db.

Could you send the link?

I like to know why it is bad practice to use a SQLite in-memory db. I see only that practice around me in my company. We don't do a lot of unit testing and I want to improve our way of work.

I don't see SQLite in-memory DB per se, but I do see the following issues:

• Isolation problem. If you have to clean up data on each test run, then that means tests can't run in parallel, this means slower feedback loop. (unless you have a solution that they can be isolated, I hadn't checked SQLlite in detail)
• What happens to the tests later when the team might need to switch to another database e.g. MongoDB, or when there's some breaking change in EF...? if we used test doubles, nothing would happen. But if we're bound to EF, then sure SQlite is fine for now but what happens later?

I also like to know what is a better option for replacement of the SQL db in the unit tests and why.

There's two alternatives:

• Classicist TDD style as I wrote above, we would have interface for repositories and use test doubles. For implementing test doubles, I tend to use fakes. Inside the fakes I have lists/hashmaps (but perhaps I could have had an in-memory DB, if it allowed isolation with no need of cleanup)...
• Khorikov's approach, writing controllers as what he shows which are directly bound to DB technology and running those integration tests with the real DB (as he says, 10.3.3 Avoid in-memory databases such as SQLite

My recommendation is - you can play around with both of these options on your GitHub, maybe make some examples, and read the material above, to understand both views.... after all that, present to your colleagues.

The other point is - is the team experiencing any points with SQLite in-memory? If no pain points are felt, then there generally won't be interest in change.

[Daissy538]

Thanks @valentinacupac

For a project, we use an in-memory SQLite db. The db is getting cleaned up for each unit test.

Ok I find that approach somewhere in-between unit testing versus integration testing.

• On one hand, these tests are in-memory fast running, which is needed for unit tests.
• On the other hand, there is shared state (you mentioned DB clean up for each test), so then these tests can't run effectively run in parallel because they're not isolated. This is the case with migration testing.

Now you mentione it, it sound like a gray spot in between those types of tests. I always thought that an integration test would be connected to a hosted DB and that a In-memory db won't count.

By the way, I mentioned it wrong how the in-memory db is setup for our Unit Tests. Each Unit Tests gets their own instance. So they are tested in isolation and run async. But still the seed for setting up the in-memory db is shared. So changing the seed for 1 unit test can affect another unit test. So, that is a thing I see as a problem when the amount of unit tests grow.

There was also mentioned that it is a bad practice to use SQLite db.

Could you send the link?

I read it over here:
#13 (reply in thread)

It can be a community opinion. I'am just searching approaches for Unit Testsing. I want to know the why's behind the statements/idea's/approaches, so I can learn from it and come to my own conclusions.

I like to know why it is bad practice to use a SQLite in-memory db. I see only that practice around me in my company. We don't do a lot of unit testing and I want to improve our way of work.

I don't see SQLite in-memory DB per se, but I do see the following issues:

• Isolation problem. If you have to clean up data on each test run, then that means tests can't run in parallel, this means slower feedback loop. (unless you have a solution that they can be isolated, I hadn't checked SQLlite in detail)
• What happens to the tests later when the team might need to switch to another database e.g. MongoDB, or when there's some breaking change in EF...? if we used test doubles, nothing would happen. But if we're bound to EF, then sure SQlite is fine for now but what happens later?

For the current project. We are bound to the framework Asp .Net Boilerplate https://aspnetboilerplate.com/Pages/Documents/Articles/Unit-Testing-with-Entity-Framework,-xUnit-Effort/index.html?searchKey=Unit%20test.
In that framework changing to a different approach is not an option, because framework's test part is build around the in-memory sqlite db. My goal is not to change that approache for the project, I just want to learn from it.

I also like to know what is a better option for replacement of the SQL db in the unit tests and why.

There's two alternatives:

Classicist TDD style as I wrote above, we would have interface for repositories and use test doubles. For implementing test doubles, I tend to use fakes. Inside the fakes I have lists/hashmaps (but perhaps I could have had an in-memory DB, if it allowed isolation with no need of cleanup)...
Khorikov's approach, writing controllers as what he shows which are directly bound to DB technology and running those integration tests with the real DB (as he says, 10.3.3 Avoid in-memory databases such as SQLite

Thanks for the description of the alternatives!
I was wondering. For our project we use Link2SQL. Sometimes the framework can not translate the Link expresion to a SQL stamement. When you are not connected to some sort of DB (like a fakerepostiory), can you still evaluate if those expresions can be translated to SQL? Maybe it is something that is more part of an integration test then a unit test.

My recommendation is - you can play around with both of these options on your GitHub, maybe make some examples, and read the material above, to understand both views.... after all that, present to your colleagues.

The other point is - is the team experiencing any points with SQLite in-memory? If no pain points are felt, then there generally won't be interest in change.

So changing it to a different setup is not an option. Because we are bound to a framework that we use. Maybe I'm going to look for a better solution for the seed. I understand that to much sharing between tests can give problems in te long term!

I do want to try out the different approaces in my own projects as a learning oppertionity for myself.

Thanks for your view on the situation and the extra information you gave me!