2) It is at least an order of magnitude slower than not using a database at all.
Combined this makes for very slow tests. Certainly for larger applications with thousands of tests.
Having said that, I don't think there is a single right answer to the problem of testing applications that use a database and your suggestion still can be a valid solution.
- parallel sessions on one database just like multiple clients accessing your web app at the same time
- create multiple copies of a schema and run different independent testusite legs on each (you just have to pass X-Test-Schema in the HTTP header to select on which schema should the web app operate)
- I have an even better trick up my sleeve for testing an app with a real DB backend, and that's passing X-Test-Force-Time header that allows me to override time under which app and DB operates per-request, so I can test even time dependent behavior, like timeouts, timed queues and events, time dependent DB queries, etc. And in general just have a stable predicatble timestamps in the database for each test run and be able to compare database states at the end of the test run and check the diff for irregularities against the previous test runs, or known good state.
I have to patch postgresql to allow for output of now() current_timestamp, etc. to be overridable per session, but it's absolutely worth it to be able to simulate/control the flow of time across the whole stack.
You can actually do a lot of useful stuff with schemas and search_paths, without disrupting connected clients, and without having to re-create databases, all in a transactional/atomic manner, that is very useful for testing.
Parallelizes quite well when we test the engines at Prisma...