How to use a different `*.db` file during e2e tests?

[kettanaito]

Hi!

I think we should use a different SQLite database instance for e2e testing. That is to separate whatever state the local database has during development. Basically, each test run should start with a clean database (and likely even drop the db between test suites/cases as well) to ensure proper test isolation.

I've tried setting DATABASE_URL to file:./data.db? in here:

epic-stack/playwright.config.ts

Lines 37 to 40 in 97135fc

	env: {
	PORT,
	NODE_ENV: 'test',
	},

I've also tried DATABASE_PATH: './prisma/test.db' to no effect.

But that had no effect on the app under test; it still used ./prisma/data.db. I might be doing something wrong here... Any hints would be appreciated!

[kettanaito]

This also brings up the question of whether the locally running app instance should be reused during tests:

epic-stack/playwright.config.ts

Line 34 in 97135fc

reuseExistingServer: true,

I'd say that it's a bad idea, if you follow the database-level isolation. If we reuse a running app, the tests are going to use that app's database, and not the designated test database, chipping away at the said isolation.

Apps are fast to run. I see little reason to reuse them, to be honest.

[kettanaito]

Update

I figured out why my changes weren't taking effect. They were working! Just for the application, not my test setup. So a different DATABASE_URL has to be used both for the tested app (webServer.env) and for the process that runs Playwright (since the setup is run by Playwright).

Playwright loads the same .env as the app here:

epic-stack/playwright.config.ts

Line 2 in 97135fc

import 'dotenv/config'

[kentcdodds]

Can I safely assume that this isn't going to work based on our conversation on 𝕏?

[kettanaito]

Sadly, Playwright doesn't seem to acknowledge per test case setup as a viable thing. This severely limits what developers can reliably test and this makes me so sad.

@kentcdodds but we should still configure Playwright to run against test.db in Epic Stack! There's no excuse to run tests against the same db you use for local development. You'll be testing in a potentially pre-seeded context and that is likely to cause issues. This part isn't hard to do. It's "let's isolate context on a test case level" that's the hard part. I strongly recommend you reopen this. I will open pull requests after I'm done refining these setups in my workshop (as usual! I love the workshop > Epic Stack contribution journey).

[kentcdodds]

I intentionally made the tests run against the dev database because it helps find cleanup issues (when you go into develop after running the tests you find you've got a lot more users than you expected for example). That said. I don't feel strongly about this so I'm fine accepting a PR to isolate things.

[LukasPolak]

Hey!

I didn't like that my development database was getting filled with test data. Also, when running tests in parallel, I was getting weird failures because tests were interfering with each other.

I decided to give each Playwright worker its own database and its own application instance. So instead of all tests sharing one database, I have:

• Development database → only for local development (never touched by tests)
• Worker 1 → gets its own database + runs on port 3001
• Worker 2 → gets its own database + runs on port 3002
• And so on...

This means tests can run completely in parallel without any conflicts.

(Playwright setup takes quite some time to run all the apps/workers)

---

Before tests start:

• Create fresh database for each worker
• Start separate app instance for each worker
• Each worker connects to its own app on different port

During tests:

• Tests create the data they need
• Fixtures clean up automatically after each test - this gives test isolation
  without needing database resets
• No interaction between workers

After tests finish:

• Kill all the app instances
• Delete all the test databases

---

The main benefit is that tests are completely isolated at two levels:

1. Worker isolation: Workers can't interfere with each other (separate
   databases + app instances)
2. Test isolation: Tests within a worker don't interfere because fixtures
   clean up after each test runs

No more "this test fails when I run all tests but passes when I run it alone" situations. Also my development database stays clean.

The downside is it's more code to maintain compared to simple setup, and the initial Playwright setup takes longer because it needs to spawn multiple app instances.

I'm using fixture-based cleanup rather than database resets between tests, and this approach is working really well for me.

[kettanaito]

@LukasPolak, do you have your setup to look at somewhere? If not, could you post at least the parts you are mentioning above? I've spent two weeks fighting Playwright for proper app+db collocation and concluded that setup isn't worth it mostly because it's impossible due to current Playwright limitations (see microsoft/playwright#37237).

It's possible to point your entire test suite to a single test database, and that's what I'm proposing as an improvement to Epic Stack. But full, test case based isolation is, sadly, out of reach. I'm investing into alternative solutions at the moment but those will take time.

Anyhow, really curious to see how you achieved the setup above. Will be happy to give you feedback on it as well.

[kasperpeulen]

@kettanaito I love your desire to isolate things. I have tried a couple of different fully isolated testing patterns with remix/ the epic stack over the years. Using vitest browser mode and storybook (and running the backend/db of the epic stack on the browser).

It is a very different direction, but I never could gey playwright to work in the way I wanted. And running the frontend and backend in the same runtime has some adventages on its own.

FYI, being able to run remix/RR in the browser also inspired me to get RSC to run in the browser for isolated component testing, which we have talked about.

If you are interested, I could make an epic stack example. Most of the recent work I have done here is in private epic stack repos for commercial apps.

I have used prisma-mock to run the db in the browser. Ideally, prisma would have official
support for more battle tested in memory browser solutions, such as pglite and sqlite-wasm. I belief drizzle already had support for that.

[LukasPolak]

@kettanaito sorry for the late reply, I was quite busy. During my time off over the holiday, I will create a reproducible app with all the changes.

[kettanaito]

@kasperpeulen, appreciate the proposition! I believe I've seen what you have in store for RSC testing with VBM, it's really exciting! That's not your conventional E2E testing, though, and I'm looking forward to exploring testing RSC a bit later.