Update 2023-03-16: Use the JEST_WORKER_ID instead of the test path for generating PG schema names. It results in fewer migrations on a fresh DB, and is less complicated to implement. See the unique-schema-jest-worker commit for details.

You like writing tests that interact with a real database, but hate waiting forever for them to run? You also happen to use Typescript (or Javascript, but why would you), Prisma as your ORM, and PostgreSQL as your database? Then this article is for you!

How much of a speedup are we talking about? Jump to Anecdata for numbers. To see some code, head over to the fast-prisma-tests Github repo.

Sidenote: Testing Against a Live Database

This article isn't about convincing you that:

• testing against a real database has many benefits over mocking,

• testing against SQLite isn't the same,

• it doesn't matter if they're not "true unit tests",

• writing tests isn't a waste of time.

No, there's already plenty of information on those topics. However, if the main argument were to be "DB tests are slow", then hopefully this post will demonstrate that you can have your cake and eat it too!

All I'll say for now is this: I personally get much more confidence out of tests that interact with a real database.

Background: The Official Solution

Prisma provides an example application with real DB tests: the testing-express example. The example has the following characteristics:

1. Uses a custom Jest test environment (prisma-test-environment.mjs).

2. Uses SQLite.

3. Creates a unique and random SQLite database file for each test file.

4. Runs prisma db push at the start of each test file.

5. Deletes the database at the end of each test file.

Using unique SQLite databases for each test file is a great idea, since it easily lets you run tests in parallel, and because SQLite is so lightweight this all runs pretty quickly.

However, if you try to adopt this solution with PostgreSQL, or anything besides SQLite, you might run into a few issues, as I did:

• Setting up a fresh postgres database (or schema) for each test file, more specifically running the migrations, is slow.

• Wiping the database is also slow.

Additionally:

• Because the database is only wiped after each test file, all tests in a given file share the same state, which means that tests may depend on each other to run successfully. In fact, the example tests do rely on this. To me, that's simply unacceptable; if you are OK with this behavior, I'd be interested to understand why!

• The use of a custom Jest test environment is clever, but as far as I know it prevents test-specific configuration (such as whether to wrap tests in transactions or not, as discussed in the next sections) and means a database is setup for each test file, regardless of whether they actually need to interact with the database.

Consequently, there a quite a few testing-related issues and discussions in the Prisma repository [1] [2] [3] [4] [5].

There Must Be a Better Way

There's a few ways to speed up database tests:

1. Wrapping tests inside a transaction.

2. Using unique a schema for each test file.

3. Speeding up migrations.

These methods are further described in the following sections.

Note that these are orthogonal to each other, so you can pick and choose depending on your use case; transactional tests definitely give you a lot of bang for your buck though.

You'll find all of those methods implemented in the fast-prisma-tests repo, more specifically in tests/helpers/fast-prisma-tests. The repo builds on Prisma's testing-express example.

Wrapping Tests in a Transaction

Truncating tables from the database after each test is slow. Painfully slow. One solution is to wrap each test in a database transaction and ROLLBACK the transaction at the end of the test, which effectively reverts any changes made by the test. This is a pretty popular technique, and I personally first came across it in Django. Wrapping tests in transactions also means your tests can run in parallel!

Up until recently, this was impossible to implement with Prisma. However, with the recent(ish) arrival of the interactiveTransactions feature, which is still in preview, it is doable, although it's not exactly straight-forward either.

In fast-prisma-tests, the core transaction wrapping logic is implemented in transaction-wrapper.ts#TransactionWrapper; most of the complexity comes from the fact that Prisma does not (yet) support imperative transactions nor savepoints. TransactionWrapper can be used to wrap your tests using beforeEach/afterEach roughly like so.

const db = new PrismaClient(...);

const txWrapper = new TransactionWrapper(db);

jest.mock('../../../src/db', () => {
  return { getDb: () => txWrapper.getProxyClient() }
})

beforeEach(async () => {
  await txWrapper.startNewTransaction();
})

afterEach(async () => {
  await txWrapper.rollbackCurrentTransaction();
})

txWrapper behaves like a PrismaClient, and must be used wherever the system under test would use a PrismaClient. Applying this to your own codebase mostly depends on how your code accesses the database. In the fast-prisma-tests repo, this is done by mocking getDb with jest.mock (see fast-prisma/tests/index.ts.

All credit for the integration with Prisma goes to Valerionn and their transactional-prisma-testing package, which I learned about in #12898.

Pitfalls of Transactional Tests

Transactional tests have a few caveats.

First, the code being tested can't actually start a transaction. Instead, such nested transactions should be turned into a SAVEPOINT. Unfortunately, Prisma doesn't yet support that at the time of this writing. However, that can be implemented (see wrapInSavepoint).

Second, you can't test code that expects to be able to run two or more transactions in parallel, e.g. in separate Promises. Because all database calls within a transaction use the same connection, all operations are sequential.

Finally, transactions have a property that is sometimes undesirable in a testing environment: if any operation fails, such as a unique constraint violation, then the transaction is automatically rolled back, and any further operations will result in an error. In Prisma's case, you might see something like the following.

Error occurred during query execution:
    ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E25P02), message: "current transaction is aborted, commands ignored until end of transaction block", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("postgres.c"), line: Some(1453), routine: Some("exec_parse_message") }) }) })

In this case, one solution would be to wrap the offending call in a savepoint. This isn't a big deal, albeit a bit ugly, if said call is made right in the test, but can be tricky if the call is part of some complex function.

To avoid some of these problems, especially the second one, you'll notice that fast-prisma-tests' usingDb fixture lets you decide whether to use the transactional method or the regular truncate method.

There Be Dragons

If your test happens to both:

• use jest.useFakeTimers,

• use a Prisma transaction,

which is natural when you want to mock the date returned by something like new Date() in a DB test, then you might notice that the test hangs forever (or at least until it times out).

The solution is to pass {doNotFake: ["nextTick"]} to useFakeTimers -- if you actually need to mock nextTick, then you're out of luck!

I haven't investigated this too much, and just assume it's because Prisma's implementation relies on nextTick.

Parallel Schemas

Running tests in parallel (using the -w option if you're using Jest) is a great way to speed up your test runs, especially in local development. Unfortunately, this requires that your different test files do not step on each other's toes. A common source of contention is the database. If you wrap tests in transactions, as discussed previously, then your tests are already isolated! On the other hand, if you don't use transactions, or if you have just one test file that can't run inside a transaction (perhaps for one of the reasons mentioned in Pitfalls of Transactional Tests) there's an alternative method: using a different PostgreSQL schemas for each test file.

Out of all the methods described here, this is actually the easiest to implement. In short, you need to:

• Build a unique schema name for each Jest worker.

• Pass that schema name to the connection string's schema argument.

See unique-schema.ts and how it's used for an example implementation.

Caveats

Using parallel schemas requires that migrations be run for each schema. This may add significant overhead to each test file and even cancel out the benefits of running tests in parallel. This is where Fast Migrations come in!

Continuous Integration

Unless your CI servers are pretty beefy, you'll probably want to disable parallel schemas and just run tests serially on the default (public) schema.

Fast Migrations

Before your DB tests can run, the migrations need to be applied to the database. My preferred approach is to apply migrations within the test suite. In Prisma, migrations can be applied using prisma migrate reset or prisma db push. However, running either of those usually takes around 1 whole second. If you're running tests in parallel using parallel schemas, this can significantly affect your test times.

So what's the trick? Just don't apply the migrations! In local development, unless you very rarely run tests, your database will already be up-to-date with the migrations, which means you don't need to do anything. You could use prisma migrate status for that, although that's also not very fast: around 0.8 seconds.

Luckily, the prisma migrate status command is pretty easy to replicate. For the sake of brevity, I'll just refer you to fast-migrations.ts. As noted in the code comments, maybeRunMigrations only takes around 0.05 seconds (if migrations are indeed up-to-date of course).

Anecdata

The table below shows test times for various combinations of methods. These results were obtained from a real Next.js application, on my development machine. The test suite contains 495 tests across 79 test files; some of them interact with the database, some don't. There's nothing very scientific about this, and your mileage may obviously vary, but it's probably better than a toy example.

4 cores corresponds to --max-workers=25%, which is what I typically run: it's fast enough, and doesn't overwhelm the system too much.

Legend:

• Truncate means the database is truncated after every test, whereas Transaction means each test is wrapped in a transaction -- see Wrapping Tests in a Transaction.

• Single Schema means that tests all run against the same Postgres schema, which means that a single call to prisma migrate deploy can be made before running the tests, whereas Parallel Schemas means each test file runs against a unique schema -- see Parallel Schemas, which implies that migrations are run as part of the test suite.

• Fast Migrations means the Fast Migration strategy was used.

Obligatory info about the exact setup:

• For each case, an average of 10 separate runs was used.

• Except where ts-jest is explicitly mentioned, next/jest (which uses SWC) is used -- I haven't tested with babel-jest.

• Postgres is run in a Docker container (postgres:13.7-alpine)

• Node version: 16.15.1

• OS: PopOS 22.04

• Hardware:

  • Hardware Model: Dell Inc. Precision 3561

  • RAM: 32 GiB

  • Processor: 11th Gen i7-11800H @ 2.30GHz x 16

  • Disk: 512 GB, SSD

Conclusion

I hope this helps speed up your test suite!

Note that I do not plan on making an NPM package out of this, because I firmly believe most of this should be built into Prisma itself. A library's testability matters™, and most ORMs I've used or come across do implement this, e.g. Django, SQLAlchemy, Rails, Entity Framework, and Ecto.

If I've missed anything, don't hesitate to let me know in the comments below!