Best practices for integration testing against a real database

[royal pewter]

Hi,

We have an API which uses Postgres as a database and some other third party services like Stripe etc. Now we want to do some integration testing to cover the entire API

As per docs we can start mocking, but we also wanted to ensure queries are executed correctly so we decided to do testing against a real database

the problem is with each test suite, the database state can be polluted, so for each test suite we may wanna re-run migrations and start fresh. and to speed up testing we may wanna do parallel testing as well

are there any good guidelines to follow in this scenario?

[frozen garden]

I don't think there are guidelines, but we arrived in the same state as you - before each test, clean the database and run fresh migrations. And soon enough, the integration test suite took over an hour to run.

But, this is the only way to have non-interfering, isolated, tests.

If you're willing to trade some of the isolation for speed, you can follow our route, too ->

Redesign the tests that they don't expect a clean database. Have them each manage a unique records and then you can run multiple of those in parallel. Then you only need to clean and migrate the database once before you start the tests.

You can also split it into tests that can run in parallel and those that can't

I saw you mentioned testcontainers. Yes, we use them together with Jest's globalSetup and globalTeardown https://jestjs.io/docs/configuration#globalsetup-string

[royal pewter]

e2e with database is a really tough topic

thanks for shedding some light

i think this might be the way to do it

[frozen garden]

Another option (that I haven't personally looked into) is to run postgres in memory - see https://github.com/stack-auth/pgmock?tab=readme-ov-file

[thin valley]

I was going to ask if there isn't an in-memory version of pg. MongoDB has an in-memory version for testing too. You can install it via a package manager and it allows for much faster and simpler testing. In fact, the Typegoose maintainer (which I have a Nest module for - ehem - please excuse the self-promotion) is also the maintainer of the mongodb-memory-server too. A happy family. 😄

[royal pewter]

@frozen garden do you usually run database migrations on global setup?

[frozen garden]

Yes. It's not the application's concern to prepare the database

[frozen garden]

Yep, I've used this before, too, on projects that have been unfortunate enough to use mongodb as their main database 😭 (yes, I'm biased)

[thin valley]

Hehehe....yes. Switching to No-SQL is a paradigm shift from SQL, which one must fully accept and understand, in order for the benefits to be worth the change. 😛

[lethal basalt]

You could target a different schema per test suite that way you can run them in parallel. Or, what i personally prefer is testingcontainers but they come with their own set of problems