[feike]

We do this too for PostgreSQL: to ensure the tests are really fast:

    - we create a template database using the migrations
    - for *every* integration test we do `CREATE DATABASE test123 TEMPLATE test_template;`
    - we tune the PostgreSQL instance inside Docker to speed up things, for exampling disabling synchronous_commit

On a successful test, we drop the test123 database. On a failed test, we keep the database around, so we can inspect it a bit.

The really great thing about this approach (IMHO), is that you can validate certain constraint violations.

For example, exclusion constraints are great for modelling certain use cases where overlapping ranges should be avoided. In our (go) code, the test cases can use the sqlstate code, or the constraint name to figure out if we hit the error we expect to hit.

This approach is pretty much as fast as our unit tests (your mileage may vary), but it prevents way more bugs from being merged into our codebase.

[neonate]

Out of curiosity, how fast is really fast?

[feike]

Just did a sequential run (to get some better measurements), and this is an excerpt of the things happening in the PostgreSQL instance inside the Docker container, for creating and dropping the databases:

    08:25:37.114 UTC [1456] LOG:  statement: CREATE DATABASE "test_1675239937111796557" WITH template = test_template
    [noise]
    08:25:48.002 UTC [1486] LOG:  statement: DROP DATABASE "test_1675239947937354435"

Start time of first test: 2023-02-01 08:25:03.633 UTC

Finish time of last test: 2023-02-01 08:26:13.861 UTC

82 tests, or 0.856 seconds per test (sequentially).

In parallel, we take 6.941 seconds for 82 tests, or 0.085 seconds per test.

[kakwa_]

We apply the overall same strategy (individual DB for each test created from a template).

Our whole test suite (integration + unit tests) takes ~80 seconds to run for ~800 integration tests (each with their own DB) and 300 unit tests. And that's on my dev laptop (T14s, cpu: i7-1185G7) without much optimization (mainly fsync = off in postgresql.conf).

In fact, I just ran a quick test, and just putting the DB on a tmpfs cuts that time to ~40 seconds.

So overall 0.1 to 0.05 second per test on average, same ballpark as parent (and it's kind of an over estimation actually since we have a dozen or so of slow tests taking 5 to 10 seconds).

Note that the tests are run in parallel however.