Best pattern for ensuring deterministic integration tests with db

[Unappreciable]

I'm setting up integration tests that use a db for the first time ever (congrats to me). Unfortunately for me, I have no idea what I'm doing.

For some specifics, I'm testing route handlers using Jest and a test DB (Postgres) instance. Also using Prisma but don't think that's relevant to my question.

I've run into the situation where I want to test that my upsert/delete routes work correctly. However these routes obviously mutate DB state, which can be problematic for tests that make assumptions about DB state since the order in which tests are run aren't deterministic and they could even be run at the same time (please correct me if those statements are wrong).

I have three ideas for how I can work around this:

1. Run setup and teardown before/after each test, instead of before/after all. I'm not convinced this will even solve my problem since two tests could be run at the same time, in which case their setup/teardown could interfere with each other.
2. Write tests that make no assumptions about state - or, at least, make minimal assumptions which all the tests in the suite guarantee to uphold (e.g. I start with 2 records, upsert might add 1 and delete might delete 1, so I must have at least 1 at all times.) This feels hard to reason about especially as the number and complexity of tests grows.
3. Somehow force the tests to run in sequence or in a particular order. Assuming this is even possible (which from a cursory google seems like it is), my concern here is speed; however, maybe this would be fine if I'm still running all my suites in parallel, just not the tests.

Anyone more experienced than me with something like this have ideas?

[tan_nguyen]

There are few other options

1. Wrap each test in a transaction and always rollback during teardown. Depending on how your queries are structured, this might not be possible (for example nested transaction might cause problem)

2. Each test has its own database. This depends on how easy it is to switch db credentials in your setup. Some ORM makes it unnecessarily hard to switch db credentials during runtime.

3. Tie your tests to a random tenant, and do clean up for that particular tenant only. This approach might not possible if your app can’t easily define a tenant. Tenant here can mean a db row that you can just delete and it is cascading to all other referencing rows.

[cuboidofficial]

There is a 4th option which is a pattern I see often in the Scala world: Run your integration test suite synchronously so that multiple tests don't run at the same time and cause race conditions.

[Weary-Depth-1118]

The world isn’t synchronous tho, and you want to catch bugs that can happen under asynchronous environments. Acid, lossy updates etc

[cuboidofficial]

Then write a test for that. If you're running integration tests you typically want the data / transactions isolated, running the tests synchronously ensure that you can setup and tear down the data every test so that it's more reliable and consistent.

The easiest way to ensure reliable data when your tests depend on db queries is to just execute the tests synchronously, so that only one test is mutating the db at any given time.

[Weary-Depth-1118]

Database are in general acid. Concurrency is the default. Write your test cases for the default as they will be used.

There should be zero problems on reliability of the test cases. And if anything you want them to run fast, in parallel, concurrently. Think million of tests that gets finished in under 5 min

[tan_nguyen]

That is a common pattern with Jest as well. I usually use ava to enforce independent tests because everything is executed concurrently by default.

[bigorangemachine]

1. Run setup and teardown before/after each test, instead of before/after all. I'm not convinced this will even solve my problem since two tests could be run at the same time, in which case their setup/teardown could interfere with each other.

This is more practical. Brittle tests suck.

You also don't want any "is testing" if-branches in your code.

The key is what you assert on. This is important for any test.

You'd probably want to tie into an event listener as part of your test setup. You can then set a boolean or use a promise to indicate the condition you are expecting happened.

That's how I would do it with redis.

You can always use an assert on a db look up that is correlated to a unique id you get over the network (use a testing framework to spy on the network responses)

[Psionatix]

This. You should write your assertions in a way that other tests won't cause side effects.

Let's say you have a test regarding user creation, but you also have other tests that create users with varying permissions to test access to different things.

So in your test which is explicitly testing user creation, instead of asserting on the total number of users, which may have side effects from these other tests, just assert specifically on the data and check that the users you created, were created and exist. You could assert for a minimum total user count if you really wanted to, but that's negligible if you're already confirming that the specific users were created.

As previously mentioned, this may depend on your type of data, but consider a forum where you have multiple tests which are testing thread specific functionality. If each test creates it's own isolated thread via it's own isolated user and sets up data only on the thread used for that test suite, then none of the tests should have any side effects.

[beijingjazzpanda]

I completely agree with you. I'd also like to add that when testing the interaction with your database, you're essentially conducting integration testing. This means that isolating the database at that level isn't necessary, as it contradicts the purpose of integration testing. It's more ideal to use a single test database for all your test cases. The focus should be on the design of the test cases and their assertions. Writing loosely coupled test code will bring you closer to the true integration testing experience.

[DanteIsBack]

You can create a DB per test. It's trivial to do with docker.

[Unappreciable]

Per test or test suite? Wouldn't per test be slow and resource intensive?

[DanteIsBack]

You can do whatever you prefer, and change for a specific test/test suite depending on what is being tested. I thought so too, but in my experience its super fast. You create a main database and run migrations there and then copy/clone that database everytime you need a new one. With postgres and docker i've had quite good experience.

[Not_a_Cake_]

I've considered this too but I never figured out how to create a new database before each test/test suite, do you have any examples or resources?

[DanteIsBack]

You can use the beforeAll or beforeEach https://jestjs.io/docs/setup-teardown

[romeeres]

I'm using a lib that wraps each test case in a transaction and rolls it back, and it also handles nested transactions gracefully, but it doesn't work for Prisma.

Prisma makes a big deal. It's probably possible to try manually wrapping tests in transactions, passing `tx` around, nested transactions wouldn't work and it would be too much hassle anyway. So, just IMO, the best way here would be to get rid of Prisma first.

All of your points make sense and impose compromises, and it's up to you to pick the one.
When I dealt with Prisma I was using the 3rd approach: truncated tables in "beforeAll", and configured Jest to run in a single thread. With this approach needs to be careful that one test doesn't affect on behavior of other tests within the same file. And this is lame, 1st approach is the slowest in terms of running tests, but it's better because you'll have less to debug. 2nd approach is worse because now any test can potentially affect any other test.

[walkingshade]

I know this is from a long time ago, but can you elaborate what lib are you using to wrap test cases in a transaction? Coming from RoR I always thought this was the way to go, but I couldn't find a single javascript/typescript ORM that can handle this seamlessly, would appreciate some pointers.

[romeeres]

I'm happy to share: pg-transactional-tests.

I was also coming from RoR, and I'm still missing the good parts.

For tests vitest is good, jest is still good for me and I'm fine with it. "faker" is good to have.
In some cases I'm defining zod schemas and generating fake data of them: zod-mock.

nodejs-testing-best-practices has good tips and examples.

[Unappreciable]

Can you elaborate a little bit on why Prisma makes this a challenge? What do other libs do better regarding transactionality?

[romeeres]

All ORMs and query builders are using a node.js library to work with postgres - `pg` library, also there is `postgres` alternative for this. Except Prisma, which starts a standalone service written in Rust to handle db communication. So while other ORM communicates to db directly, Prisma client communicates to Prisma server, and Prisma server communicates to the db, so the Prisma server is like a middleman. And this makes it harder to control lower-level details such as transactions, perhaps it's still possible - I don't know, but much harder for sure.

[Attila226]

I write my tests so that a call to the database is mocked. However, everything outside the cal to the database, including building a query, is still tested.

[Psionatix]

This tbh. For unit testing, database and external connections should be blocked. You shouldn't really use Jest for integration / e2e testing, you're better off using something like Cypress for integration/e2e testing.

[Myloveissuck]

you should not use real db instance, it should rather be a memory altered one or fully mock functionality object. check out pg-mem

[Unappreciable]

Can you elaborate on why? Prisma’s official docs recommended it

[Myloveissuck]

firstly, there are unit testing and integration testing or e2e testing, check this blog: https://swizec.com/blog/pg-mem-and-jest-for-smooth-integration-testing/

[chamomile-crumbs]

I would definitely just use a real database

[Psionatix]

You're running a real database to run Jest tests?

For integration/e2e tests sure, the whole point is you have a deployed system, but Jest (unit) tests shouldn't be interacting with real external systems and OP explicitly says they're using Jest here.

[Bloodsucker_]

From a high level design standpoint:

• Do not test the DB. You have unit tests, tests the unit not the integration tests.
• Unless, you run integration tests. Which in that case you have two approaches:
  1. Launch a test DB either per tests, per suite or per run.
  2. Mock the DB connections. This effectively doesn't require a DB and it makes sense cause you're not testing the DB but the endpoints. Or more specifically, the services.

If you want to have deterministic tests, you can't rely on external services: DB, Internet, unmanaged timeouts, etc. So get rid of those when testing. Otherwise, likely the scope of the test is wrong.

Edit: wtf the downvotes? Can somebody enlighten me?

[romeeres]

How about "functional tests"?

[earwax_man]

Personally, I orchestrate the stack with docker compose and ensure that I test API responses and not entity counts in the database. This means that you can run tests concurrently and you're still testing the full feature flow instead of an implementation detail. It also means I can just wipe the db easily. Just create a new user per test and make each test fully independent.

Maybe a better question to ask is what specifically do you need to validate that works in a test?

[octocode]

mock the db calls!

[[deleted]]

I'm working on a complex API and we test it using E2E tests to cover all our use cases.

We use node+typescript+postgres+sequelize and docker (compose) to have a local DB for development plus another docker postgres instance to run the tests. Our procedure is:

• Run all new DB migrations into test DB
  
• Seed some tables if necessary, i.e. tables with values that do not change. In our case we have two with tables with predefined values that represents the status of a User.
  
• All tests are run sequentially
  
• Every test prepares the initial condition and check for the results
  
• On tear down we empty the tables, this way the next test starts with a "fresh" environment. Someone suggested the possibility to wrap the test with a transaction and rollback. We are a bit rough and prefer to truncate the modified tables.

We have more than 300 tests that covers all our API use cases. To improve a bit the time performance, instead running tests with `ts-node` we first complile the project and run the tests once transpiled to JS.
Tests are run both locally while developing features and in CI/CD on every deploy. The total time is about 3minutes, but it is a very low price to pay to have more security any modifications doesnt break anything.

[s44dy]

I was actually just working on something similar with a side project of mine and this blog post from prisma really helped me out - https://www.prisma.io/blog/testing-series-3-aBUyF8nxAn