Testcontainers and Real Databases

You may recall that I mentioned that the EF Core in-memory database is not recommended to be used by Microsoft, Jon Skeet, your aunt, your dog, or literally anyone. However, if you had me cornered, I'd admit to you that I'd rather you use that with tests than never right tests at all.

Let's talk about what I would call the (not) good-better-best model for testing databases.

• The (not) good - the in-memory database. Just trust me and Microsoft, don't use it.
• The better - SQLite's in-memory database. It's actually a real database unlike the in-memory option, but it's still not ideal because you probably aren't using SQLite in production. Not to mention, you'd have to have a completely separate set of migrations for something like PostgresSQL.
• The best - a real database, possibly in a container, that you can connect to. Wouldn't it be easy if you could have a test that spins up a REAL production-like database and tears it down cleanly?

Testcontainers

Testcontainers is a library that allows you to spin up real databases in containers for your tests. It's what we'll use to spin up a real database that we can use for our tests.

If you want to run this solution for yourself, you can find it under the 4-z-final code folder. You will need to install Docker.

I already completed the solution for you, so let's just go through the major particulars:

• We remove all references to SQLite from our project.
• We install the Postgres SQL provider for EF Core.
• We install Testcontainers and set it up to spin up a PostgresSQL container in our app and tear it down after.
• I hooked Testcontainers up to our app.
• I removed all of the old migrations and set us up with a new set of migrations.

There were a few gotchas along the way. I actually found some bugs in my previous code that I hadn't previously seen:

• One test was creating a John Doe records and adding it to the database, then another test ended up finding two of those records. Why this worked under SQLite and not Postgres I can't say without further digging. Subtle behaviors change when you change things like your test database provider!
• Our seed method wasn't properly adding the EmployeeBenefits records when I switched to Postgres (and probably with my track changes behavior change in a previous lesson) - I was only able to discover this with that failed test.

The lesson is, I'd recommend starting with your target database with your tests so you can get as close to a real-world scenario as possible. Testcontainers makes it dead simple to do!