Document how to perform Live DB test support with savepoints

[revmischa]

Problem

I don't have a good solution for writing unit tests for the queries I'm writing with Prisma. I would like to be able to write tests that run real queries, each in an isolated transaction.

Suggested solution

There is a very stable and awesome implementation of this for flask-SQLAlchemy - see https://pypi.org/project/pytest-flask-sqlalchemy/#motivation

The actual implementation in there is only about 20-30 lines of python: https://github.com/jeancochrane/pytest-flask-sqlalchemy/blob/master/pytest_flask_sqlalchemy/fixtures.py#L40

The approach is pretty simple. Create a test DB and run migrations if one doesn't exist already. Then for each test:

• Begin a transaction
• Intercept all new transaction calls to create a SAVEPOINT (a nested transaction) instead
• At the end of the test, roll back the transaction

This is simple, elegant, fast, and works amazingly well. You can run tests in parallel and because they are all in uncommitted transactions they don't step on each others' toes. The actual state of the database is never affected.

Alternatives

Creating a database for each test?

[janpio]

Related: https://selimb.hashnode.dev/speedy-prisma-pg-tests https://github.com/chax-at/transactional-prisma-testing https://github.com/Quramy/jest-prisma

[revmischa]

I've been using https://github.com/chax-at/transactional-prisma-testing and it's a little tricky to set up if you're not using dependency injection but it does work mostly well otherwise, though everything goes to hell if one test raises a DB exception

I have a little lazy prisma client proxy wrapper and use vitest test content extending with something like this:

// create prisma test proxy
const { prismaProxy, prismaTestingHelper } = await vi.hoisted(async () => {
  const { PrismaTestingHelper } = await import("@chax-at/transactional-prisma-testing")
  const { PrismaClient } = await import("@prisma/client")
  const { setPrismaTestingHelper } = await import("@backend/repo/util/testProxy")

  // this is my hacky client wrapper, maybe you have some better way to lazy init prisma in your app or DI
  class PrismaClientProxy {
    constructor(private proxy?: PrismaClientType) {}
    get c() {
      return this.proxy
    }
  }

  // should be only one per test runner
  const prismaTestingHelper = new PrismaTestingHelper(new PrismaClient())
  setPrismaTestingHelper(prismaTestingHelper)

  return {
    prismaTestingHelper,
    prismaProxy: new PrismaClientProxy(prismaTestingHelper.getProxyClient()),
  }
})

// mock prisma client imports
vi.mock("@backend/repo/client", () => {
  return {
    prisma: prismaProxy,
  }
})

// wrap each test in a transaction
beforeEach<DBTestContext>(async (ctx) => {
  await prismaTestingHelper.startNewTransaction({ timeout: 20_000 })
})

// rollback transaction after each test
afterEach<DBTestContext>(async (ctx) => {
  prismaTestingHelper?.rollbackCurrentTransaction()
})

[jkomyno]

Hey there, I'm moving this issue to our Docs team.

[nikolasburk]

Hey @revmischa 👋 thanks for raising this! Do you still need support for this? It seems like new docs would be needed for this, could you elaborate what kind of documentation would be most helpful for you?

[revmischa]

Like if I'm starting a new project and want to run my tests isolated in transactions, what do I need to do with prisma?