[Bug?]: Jest scenario cleanup fails when using Prisma multischemas preview feature

[ketang]

What's not working?

Cleaning up a scenario after a test will fail and thus fail the test if you are using the Prisma multiple schema preview. You'll get something like:

    PrismaClientKnownRequestError: 
    Invalid `prisma.$executeRawUnsafe()` invocation:

    Raw query failed. Code: `42P01`. Message: `relation "state" does not exist`

      at Rn.handleRequestError (node_modules/@prisma/client/runtime/library.js:174:7325)
      at Rn.handleAndLogRequestError (node_modules/@prisma/client/runtime/library.js:174:6754)
      at Rn.request (node_modules/@prisma/client/runtime/library.js:174:6344)
      at teardown (node_modules/@redwoodjs/testing/config/jest/api/jest.setup.js:143:7)
      at Object.<anonymous> (node_modules/@redwoodjs/testing/config/jest/api/jest.setup.js:237:5)

Your postgresql.log, if you have one, will show

2023-07-31 13:57:46.037 CDT [77866] calypso@calypso_test ERROR:  relation "state" does not exist at character 13
2023-07-31 13:57:46.037 CDT [77866] calypso@calypso_test STATEMENT:  DELETE FROM "state"

Since it's a preview this is half feature request half bug (it's a feature request because it's not supported; it's a bug because it fails gracelessly).

I'm pretty sure this is because of jest.setup.js:144

  for (const modelName of teardownOrder) {
    try {
      await getProjectDb().$executeRawUnsafe(
        `DELETE FROM ${quoteStyle}${modelName}${quoteStyle}`
      )
  ...
  }
}

I found this in 5.4.3, but the above code is from the 6.0.1 tag.

Prisma's info from the original issue and docs on the preview feature.

The workaround is pretty easy. In your .env set the schema search path via the connection string:

TEST_DATABASE_URL="postgresql://...?options=-c%20search_path=public,location,etc"

You need the %20 exactly as that otherwise the driver gets confused (no problem with the second = though ¯_(ツ)_/¯ )

How do we reproduce the bug?

In a schema.prisma like so:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas = ["location"]
}

...

model State {
  code   String @id @db.VarChar(2)
  name String @db.VarChar(100) @unique

  @@map("state")
  @@schema("location")
}

Create a jest test using a scenario with data using State, e.g.:

export const standard = defineScenario<Prisma.StateCreateArgs, 'state'>({
  state: {
    VT: { data: { name: 'Vermont', code: 'VT' } },
  }
})

Create a test using the scenario:

describe('nothing', () => {
  scenario('should do nothing', async (scenario) => {
    expect(true).toEqual(true)
  })
})

You will get the errors described above.

What's your environment? (If it applies)

System:
    OS: Linux 5.19 Ubuntu 22.04.2 LTS 22.04.2 LTS (Jammy Jellyfish)
    Shell: 5.1.16 - /bin/bash
  Binaries:
    Node: 18.17.0 - /tmp/xfs-96f07c85/node
    Yarn: 3.6.0 - /tmp/xfs-96f07c85/yarn
  npmPackages:
    @redwoodjs/auth-firebase-setup: 5.4.3 => 5.4.3 
    @redwoodjs/core: 5.4.3 => 5.4.3

Are you interested in working on this?

• I'm interested in working on this