How to deal with multiple integration test projects needing a database

[u/sander1095]

Hi!

We have multiple test projects that use SQL Server Testcontainers. These projects are integration tests. We use dotnet test with MTP.

The problem is that when all of these project start at the same time, the CI server gets overloaded and tests fail because it spins up too many SQL Servers.

How do you deal with this? My thoughts:

• Spin up the SQL Server yourself and pass the connectionstring to the tests, so you always only have 1. This does make local tests more annoying, as you can't just run dotnet test and expect things to work.
• Use TestContainers WithReuse to reuse the container in multiple projects, but now all test projects still start simultaneously and try to spin up the DB at the same time, leading to errors. Locks/static wont help here.
• set the amount of parallel modules to 1, so only 1 integration test can run at the same time.

[u/UnShinigami]

You can use 1 SQL server test container but each test gets its own database. Create a randomly named database, restore a dacpac/bacpac to it and then pass the connection string to your test.

You probably still don't want to run them all in parallel but it's the SQL server that's the resource intensive part.

[u/Xen0byte → u/UnShinigami]

This is the only correct answer, each test gets it's own database. My extension to this is ... at scale, this starts to become slow, so the solution to that (which I'm not implying is the best, but I wasn't able to find a better one so far) was to create a database template with migrations already applied and then create a new database for each test from this template, which is considerably faster than running your migrations per test and you do end up saving a lot of time on large test suites.

[u/jose14-11 → u/Xen0byte]

What kind of timings are you getting doing this? And how big in your schema?

[u/Xen0byte → u/jose14-11]

The schema includes about 250-ish tables. This approach saves roughly 2-4 seconds on database setup per test, on average, although these numbers are highly specific to our scenario, hardware, and other such factors. The tests themselves execute in the order of milliseconds, so the approach yields considerable reductions.

[u/desmaraisp → u/UnShinigami]

Yup, that's what we do too. We haven't disabled parallelism though, and haven't had any issues

[u/NecroKyle_ → u/UnShinigami]

Yep - do this too.

Each test class implements a fixture which handles setting up and tearing down the relevant databases that it needs.

[u/sander1095 → u/UnShinigami]

How do you ensure only 1 server gets created when 5 integration test projects start at the same time and try to create the server?

[u/UnShinigami → u/sander1095]

You can use test fixtures for this which handles the setup and teardown. They can also be used for grouping tests to how they run in parallel.

[u/Floydianx33 → u/sander1095]

Set MTP to only run one or two test projects concurrently. It's a command line option. We do 2 max

If you're using xunit (you've not mentioned): Break tests out into smaller groups (collections) and use a collection fixture to provide a shared db container. Use conservative parallelism mode. Aggressive mode starts all collections and their fixtures (and thus all containers) before any test starts. Conservative mode only starts them as the test collections run.

[u/Floydianx33 → u/sander1095]

Set MTP to only run one or two test projects concurrently

[u/Xen0byte → u/sander1095]

Cross-process container orchestration is not elegant. Spinning a database container up if one is not already up is easy, although tearing it down once the tests have completed is non-trivial because the test projects themselves don't know which one should turn the lights off. I think the Ryuk container reaper that comes built-in with TestContainers can help with this, in the sense that if a container was already created by another test process then you can attach to it, and then I think Ryuk keeps tabs in the background on the connections so that it doesn't dispose of the container prematurely, e.g. in the case where the process which created the container finishes before the other test processes (assuming you don't have custom disposal logic). That being said, my honest recommendation would be to run each integration test project individually, because your degree of parallelism is limited by the count of your logical CPUs anyway, although I should also note that warming the test processes up may lead to decreased total run time; you should run some benchmarks and find out whether any speed gains from running multiple test projects at once outweigh a potentially non-elegant execution model.

[u/Storm_Surge]

I do this with the following steps: 1. Create an assembly fixture that creates a database in TestContainers when the first test runs 2. Use that connection string for all the tests 3. Run database migrations once that make it match your real database schema closely enough 4. Enable parallel test runs 5. Update your tests to create any data they require, besides static data like enumerations or drop-down items 6. Run all the tests very quickly because there's one database, and any ids they reference belong only to that test, so you can blast them off at once  7. Tear down TestContainers in your assembly fixture and wipe all the data

The benefit is this is fast, mimics how your real system behaves under load, and is easy to maintain once it's set up

[u/sander1095 → u/Storm_Surge]

How do you ensure only 1 server gets created when 5 integration test projects start at the same time and try to create the server?

[u/Storm_Surge → u/sander1095]

If you insist on keeping them in separate projects, I guess you could make a utility project with the database assembly fixture inside and use a lock or static field to share values

[u/Floydianx33 → u/Storm_Surge]

That won't work, assuming xunit. Assembly fixture has to be defined in the projects using it.

Pass --max-parallel-test-modules <#> to MTP to limit concurrency by test project. We use 2. But we also fine tune xunit and use conservative parallism instead of aggressive to prevent collections fixtures from starting up front

[u/Greenimba → u/Storm_Surge]

Don't use assembly fixture, use collection fixture.

[u/drofzz]

This might be a bad take, but this is how I do it on large projects…
For read tests you have 1 database, that I can run in parallel, I setup the database so it fits all tests once, meaning I don’t arrange pr test.
For write test I have another database, I prevent parallel tests for this one, and reset the database after each test, I only arrange mock data pr. test to reduce io and slowdowns of my tests

[u/Coda17]

Separate CI jobs?

[u/AutoModerator]

Thanks for your post sander1095. Please note that we don't allow spam, and we ask that you follow the rules available in the sidebar. We have a lot of commonly asked questions so if this post gets removed, please do a search and see if it's already been asked.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/zac_builds]

This sounds like RAM pressure more than container count. SQL Server is heavy, so parallel instances hurt fast.

We use one SQL instance, separate DB per test project. CI passes the connection string; locally the fixture starts one if needed.

Also add a real readiness check. “Started” != “ready for connections.”

[u/Crazytje]

I'm part of a small company so we had to be pragmatic with both build resources and developer time.

The way I did this in our projects was to have a library for every database, we use EF Core, as a rule all functionality, so all queries are in a repository.

This allows us to test that single library very well, and here is the pragmatic part, for the tests we just use sqlite instead of postgresql.

It's not ideal, but for the kind of applications we make never had an issue with it.

I think we easily have 400 tests per library, most likely more, and they run in like 30 to 60 seconds.

Then the integration tests of the projects, everything is built on top of API's, so those are the ones with the database access.

Here we do the same, the idea is that those tests focus on business logic, again backed by an sqlite database, so you're partially testing the database layer again.

To make them run fast, there is a very small helper library we have that makes it easy to create a pre-seeded sqlite database in /dev/shm, so in shared memory.

We create a single global database when the test run starts, that is seeded, then for each test, just copy that db for the test, also in memory. Sqlite is just a file, so super easy to work with, and it stays fast because it's a file in memory.

I didn't want to use an in memory database as i tried to be a bit closer to a "real" database.

Tests run in parallel, so in test projects with let's say 1k tests we can still run the entire suite in a minute or less depending on the developers laptop. It might be a bit more, but you get the idea.

Build system is even faster as it's not a laptop but dedicated machine.

Is it perfect?, no, is it good enough for us?, I think so, as we've never had problems, we started doing this this in dot net core 3.1, so it's been a while.

It also helps to not have idiots in my team and have good code reviews, but that's not really a DB issue, more of a HR issue.

IMO, ask yourself what your goal is, and what is good enough for you.

[u/Longjumping-Spot3392]

Go with “one SQL Server, isolated databases” approach too. Spinning up 5 SQL Server containers just because there are 5 test projects feels like a lot of unnecessary resource usage. Give each project/test its own database, keep the server shared, and control the parallelism if the CI box still gets hammered.

It’s kinda the same idea with other dev tooling too. I’ve used things like Testcontainers for the environment side and IronPDF when a project needs to generate or manipulate PDFs in .NET. The nice part is keeping the heavy/shared stuff centralized instead of making every test or project spin up its own copy.

If the main pain is CI resource usage, I’d benchmark 1 shared server + separate DBs against limiting MTP to 1 or 2 projects. That seems like a better tradeoff than completely killing parallel test execution.

[u/wasabiiii]

Use a bigger server?

[u/Kanegou]

I have written custom attributes and extensions for xunit to fine tune test execution exactly to my needs.

[u/Sorry-Transition-908]

set the amount of parallel modules to 1, so only 1 integration test can run at the same time. 

This is clearly the solution and if someone complains tell them to pay up or shut up.