Views in prisma schema cause unit test scenarios to break

jonoc330 · August 1, 2024, 12:05am

Hey all, I’m using Redwood 6.6.4. I’ve got a test suite across our Redwood app that relies pretty heavily on scenarios. Recently, I used the “views” preview feature and added some db migrations with my Postgres views, and then added those views to the prisma schema.
However, when I run my unit test suite pretty much every test is breaking, here’s some examples:

  ● updateContact › should update contact, assign a project and not overwrite existing project assignments

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


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

      at si.handleRequestError (node_modules/@prisma/client/runtime/library.js:125:6817)
      at si.handleAndLogRequestError (node_modules/@prisma/client/runtime/library.js:125:6151)
      at si.request (node_modules/@prisma/client/runtime/library.js:125:5859)
      at l (node_modules/@prisma/client/runtime/library.js:130:10025)
      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:254:5)

  ● deleteContact › should throw error if logged out

    PrismaClientKnownRequestError: 
    Invalid `getProjectDb()[model].create()` invocation in
    node_modules/@redwoodjs/testing/config/jest/api/jest.setup.js:179:64

      176     createArgs(scenarios)
      177   )
      178 } else {
    → 179   scenarios[model][name] = await getProjectDb()[model].create(
    Unique constraint failed on the fields: (`id`)

      at si.handleRequestError (node_modules/@prisma/client/runtime/library.js:125:6817)
      at si.handleAndLogRequestError (node_modules/@prisma/client/runtime/library.js:125:6151)
      at si.request (node_modules/@prisma/client/runtime/library.js:125:5859)
      at l (node_modules/@prisma/client/runtime/library.js:130:10025)
      at seedScenario (node_modules/@redwoodjs/testing/config/jest/api/jest.setup.js:179:36)
      at Object.<anonymous> (node_modules/@redwoodjs/testing/config/jest/api/jest.setup.js:129:28)

  ● deleteContact › should throw error if logged out

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


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

      at si.handleRequestError (node_modules/@prisma/client/runtime/library.js:125:6817)
      at si.handleAndLogRequestError (node_modules/@prisma/client/runtime/library.js:125:6151)
      at si.request (node_modules/@prisma/client/runtime/library.js:125:5859)
      at l (node_modules/@prisma/client/runtime/library.js:130:10025)
      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:254:5)

  ● deleteContact › should throw if the contact doesnt exist

    PrismaClientKnownRequestError: 
    Invalid `getProjectDb()[model].create()` invocation in
    node_modules/@redwoodjs/testing/config/jest/api/jest.setup.js:179:64

      176     createArgs(scenarios)
      177   )
      178 } else {
    → 179   scenarios[model][name] = await getProjectDb()[model].create(
    Unique constraint failed on the fields: (`id`)

      at si.handleRequestError (node_modules/@prisma/client/runtime/library.js:125:6817)
      at si.handleAndLogRequestError (node_modules/@prisma/client/runtime/library.js:125:6151)
      at si.request (node_modules/@prisma/client/runtime/library.js:125:5859)
      at l (node_modules/@prisma/client/runtime/library.js:130:10025)
      at seedScenario (node_modules/@redwoodjs/testing/config/jest/api/jest.setup.js:179:36)
      at Object.<anonymous> (node_modules/@redwoodjs/testing/config/jest/api/jest.setup.js:129:28)

  ● deleteContact › should throw if the contact doesnt exist

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


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

      at si.handleRequestError (node_modules/@prisma/client/runtime/library.js:125:6817)
      at si.handleAndLogRequestError (node_modules/@prisma/client/runtime/library.js:125:6151)
      at si.request (node_modules/@prisma/client/runtime/library.js:125:5859)
      at l (node_modules/@prisma/client/runtime/library.js:130:10025)
      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:254:5

Simple example of one of my views:

generator client {
  provider        = "prisma-client-js"
  binaryTargets   = ["native", "rhel-openssl-1.0.x"]
  previewFeatures = ["fullTextSearch", "views"]
}

view SignOnHours {
  signOnId        String    @id @unique
  hours             Float?
}

When I remove the views, everything works fine.
I did notice that my test DB was not showing the new views I’d created, it’s as if the migrations weren’t run but my assumption was that Redwood ran all the migration scripts against the truncated db before it started.

jonoc330 · August 1, 2024, 12:09am

Quick follow up here - running yarn rw test --no-watch definitely doesn’t populate my test table with the views from my migration scripts, and I even ran those scripts manually and it still cleared them so it’s obviously truncating and then ignoring the views
Attempted this on Redwood v8 as well and same thing happened

jonoc330 · August 5, 2024, 11:04pm

I tried using TEST_DATABASE_STRATEGY=reset.
It looked like it actually populated the database with the views from my migrations, whereas if I don’t use the reset strategy it does not add the views in. Tests still didn’t work though.

Ran the tests but scoped to a single test just to see what it was doing:

{"level":50,"time":1722898486380,"pid":61107,"prisma":{"clientVersion":"5.7.0"},"timestamp":"2024-08-05T22:54:46.380Z","message":"\nInvalid `prisma.$executeRawUnsafe()` invocation:\n\n\nRaw query failed. Code: `55000`. Message: `ERROR: cannot delete from view \"SignOnHours\"\nDETAIL: Views containing GROUP BY are not automatically updatable.\nHINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.`","target":"$executeRawUnsafe","msg":"\nInvalid `prisma.$executeRawUnsafe()` invocation:\n\n\nRaw query failed. Code: `55000`. Message: `ERROR: cannot delete from view \"SignOnHours\"\nDETAIL: Views containing GROUP BY are not automatically updatable.\nHINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.`"}

For context, “SignOnHours” is one of my views. It looks like it’s trying to delete data from the views when they are of course not able to be deleted from.

My test and scenario is pretty basic - seed some basic user data with the standard scenario, mock user then call service.

dthyresson · August 8, 2024, 1:35pm

Hi @jonoc330 many apologies as this issue fell through our review process and got lost as many summer things do. I’ve mentioned it to the team and we’ll have a a think and get back.

dthyresson · August 8, 2024, 1:38pm

@jonoc330 Is there any way to get a small reproducible case? If nt actual code or repo, at least an idea of what we’d need into reproduce:

models (with and without views)?
a view?
tests for … ?
steps when things “do wrong”

Thanks - this will help us track down the issue.

Also - not sure it helps, but did you see the different test database strategies here:

WHAT IF I HAVE CUSTOM MIGRATION SQL?

The prisma db push command only restores a snapshot of the current database schema (so that it runs as fast as possible). It does not actually run migrations in sequence. This can cause a problem if you have certain database configuration that must occur as a result of the SQL statements inside the migration files.

Are you using this strategy or the default?

Thanks!

jonoc330 · August 12, 2024, 12:31am

No problem at all! @dthyresson !
I’ve tested both test strategies (reset & default) and both dont appear to work.

Here’s a Github repo: GitHub - jonoc330/redwood-view-bug

And full repro instructions:

Create a new redwood app
Spin up a new Postgres database (dev & unit test) and add to your .env

DATABASE_URL="postgresql://postgres@localhost:5432/viewtest_dev"
TEST_DATABASE_URL="postgres://postgres@localhost:5432/viewtest_test"

Use the following in your prisma schema:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider        = "prisma-client-js"
  binaryTargets   = ["native", "rhel-openssl-1.0.x"]
  previewFeatures = ["views"]
}

Create a random model that you can create an SQL view on

model User {
  id        String   @id @default(cuid())
  createdAt DateTime @default(now())
  updatedAt DateTime @default(now())

  firstName String
  lastName  String
}

Run yarn rw prisma migrate dev to create the user model
Run yarn rw g scaffold user.
Add a view to your prisma schema:

view UserView {
  id                  String
  createdAt     DateTime
  updatedAt    DateTime?
}

Run yarn rw prisma migrate dev --create-only and write a view that selects * on one of your new models:

CREATE OR REPLACE VIEW "UserView" AS
SELECT * FROM "public"."User";

Run yarn rw prisma migrate dev to create the view.
Run yarn rw test --no-watch and watch the user tests break: Raw query failed. Code: 42P01. Message: relation “UserView” does not exist``
Delete the view from schema.prisma and run yarn rw test --no-watch, and watch the unit tests pass.

dthyresson · August 15, 2024, 4:03am

@jonoc330 I raised this issue up with the team and will get back ASAP.

It’s midnight where I am so, I’ll try to reproduce in the morning but others on the team may get a chance to look at this before I do.

I did some digging into Prisma docs here: How to include views in your Prisma schema | Prisma Documentation

And sounds like:

You cannot yet apply views in your schema to your database with Prisma Migrate and db push unless the changes are added manually to your migration file using the --create-only flag.

your steps should be ok.

The teat command does:

      process.env.TEST_DATABASE_STRATEGY === 'reset'
        ? ['prisma', 'migrate', 'reset', '--force', '--skip-seed']
        : ['prisma', 'db', 'push', '--force-reset', '--accept-data-loss']

when running tests.

I did see this, however:

github.com

redwoodjs/redwood/blob/0c6000272ece75566786e2ca78e6c17e1b120321/packages/testing/config/jest/api/globalSetup.js#L13


      
          const { getPaths } = require('@redwoodjs/project-config')
          
          const {
            getDefaultDb,
            checkAndReplaceDirectUrl,
          } = require('../../../dist/api/directUrlHelpers')
          
          const rwjsPaths = getPaths()
          
          module.exports = async function () {
            if (process.env.SKIP_DB_PUSH !== '1') {
              const process = require('process')
              // Load dotenvs
              require('dotenv-defaults/config')
          
              const defaultDb = getDefaultDb(rwjsPaths.base)
          
              process.env.DATABASE_URL = process.env.TEST_DATABASE_URL || defaultDb
          
              // NOTE: This is a workaround to get the directUrl from the schema
              // Instead of using the schema, we can use the config file

module.exports = async function () {
  if (process.env.SKIP_DB_PUSH !== '1') {
    const process = require('process')

I wonder if setting the envar process.env.SKIP_DB_PUSH to 1 will skip this and if the test db has the very created (which may need to be done manually one time), and if the scheme is up to date, then the db won’t be reset or pushed to nd the view may remain.

May be a workaround for moment.

But, we’ll have a look.

Cheers.

–dt

dthyresson · August 15, 2024, 4:10am

@jonoc330 FYI - we do have this issue

github.com/redwoodjs/redwood

[Bug?]: Possible Data Corruption in Production DB When Using SKIP_DB_PUSH Option

opened 12:53PM - 22 Aug 23 UTC

bokukpark

bug/needs-info

### What's not working? I understand that SKIP_DB_PUSH=1 is used when testing… code that doesn't use the database. (https://github.com/redwoodjs/redwood/issues/5303) However, the issue lies in the fact that even when SKIP_DB_PUSH=1, the substitution of the DATABASE_URL variable with TEST_DATABASE_URL is also disregarded. This leads to the problem that, during every test, the teardown function deletes all records from the table. However, since it's executed on the DATABASE_URL instead of the TEST_DATABASE_URL, all data is lost. In other words, the original DATABASE_URL should not be used in jest tests. I think that regardless of the SKIP_DB_PUSH status, resolving the DATABASE_URL to TEST_DATABASE_URL would resolve the issue. <img width="804" alt="스크린샷 2023-08-22 오후 9 42 43" src="https://github.com/redwoodjs/redwood/assets/63600736/15b600cb-1e9e-4d7d-a6ae-ce3582750d38"> <img width="352" alt="스크린샷 2023-08-22 오후 9 48 51" src="https://github.com/redwoodjs/redwood/assets/63600736/88825a60-ad67-46ea-b851-9da6ff00873f"> <img width="674" alt="스크린샷 2023-08-22 오후 9 42 07" src="https://github.com/redwoodjs/redwood/assets/63600736/402d91a3-c728-4213-88d1-fee22707991b"> ### How do we reproduce the bug? 1. SET DATABASE_URL in .env 2. Add some records on DATABASE_URL db 3. Run Test with SKIP_DB_PUSH ``` SKIP_DB_PUSH=1 yarn rw test ``` ### What's your environment? (If it applies) _No response_ ### Are you interested in working on this? - [X] I'm interested in working on this

Skipping DB_PUSH might not set the database url to the test db:

In other words, the original DATABASE_URL should not be used in jest tests.
I think that regardless of the SKIP_DB_PUSH status, resolving the DATABASE_URL to TEST_DATABASE_URL would resolve the issue.

Though this [Bug?]: Possible Data Corruption in Production DB When Using SKIP_DB_PUSH Option · Issue #9055 · redwoodjs/redwood · GitHub says the opposite and the test command will set the db as expected.

In think this user had a production db url in local dev settings.

In case you see that behavior.

dthyresson · August 15, 2024, 4:14am

The last idea I have before I head to bed is to see if you manually create the view in the TEST_DATABASE before running tests.

jonoc330 · August 15, 2024, 5:59am

Ok so I manually created the views in the test database, used SKIP_DB_PUSH=1 yarn rw test and ran a single test:

● ticketsByWorkers › should return all tickets

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


    Raw query failed. Code: `55000`. Message: `ERROR: cannot delete from view "SignOnHours"
    DETAIL: Views containing GROUP BY are not automatically updatable.
    HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.`

      at In.handleRequestError (node_modules/@prisma/client/runtime/library.js:122:6877)
      at In.handleAndLogRequestError (node_modules/@prisma/client/runtime/library.js:122:6211)
      at In.request (node_modules/@prisma/client/runtime/library.js:122:5919)
      at l (node_modules/@prisma/client/runtime/library.js:127:11167)
      at teardown (node_modules/@redwoodjs/testing/config/jest/api/jest.setup.js:164:7)
      at Object.<anonymous> (node_modules/@redwoodjs/testing/config/jest/api/jest.setup.js:114:11)

It looks like now it’s trying to truncate the view at the end of the test?

jonoc330 · August 15, 2024, 6:01am

I get this on a single test run with a standard old yarn rw test. This removes the views cause it writes a snapshot instead

● ticketsByWorkers › should return all tickets

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


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

      at In.handleRequestError (node_modules/@prisma/client/runtime/library.js:122:6877)
      at In.handleAndLogRequestError (node_modules/@prisma/client/runtime/library.js:122:6211)
      at In.request (node_modules/@prisma/client/runtime/library.js:122:5919)
      at l (node_modules/@prisma/client/runtime/library.js:127:11167)
      at teardown (node_modules/@redwoodjs/testing/config/jest/api/jest.setup.js:164:7)
      at Object.<anonymous> (node_modules/@redwoodjs/testing/config/jest/api/jest.setup.js:114:11)

Running TEST_DATABASE_STRATEGY=reset yarn rw test on the single test gives me:
This seems to create the views but i get the same error as before cause its trying to truncate the views

● ticketsByWorkers › should return all tickets

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


    Raw query failed. Code: `55000`. Message: `ERROR: cannot delete from view "SignOnHours"
    DETAIL: Views containing GROUP BY are not automatically updatable.
    HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.`

      at In.handleRequestError (node_modules/@prisma/client/runtime/library.js:122:6877)
      at In.handleAndLogRequestError (node_modules/@prisma/client/runtime/library.js:122:6211)
      at In.request (node_modules/@prisma/client/runtime/library.js:122:5919)
      at l (node_modules/@prisma/client/runtime/library.js:127:11167)
      at teardown (node_modules/@redwoodjs/testing/config/jest/api/jest.setup.js:164:7)
      at Object.<anonymous> (node_modules/@redwoodjs/testing/config/jest/api/jest.setup.js:114:11)

dthyresson · August 15, 2024, 8:15am

Woke up and read message – ok, we’re getting closer here (again, a workaround just)… yes, you are correct – the Jest runner is now trying to teardown each table and delete from each model (ie a truncate).

github.com

redwoodjs/redwood/blob/0c6000272ece75566786e2ca78e6c17e1b120321/packages/testing/config/jest/api/jest.setup.js#L168


      
              })
            }
          
          const teardown = async () => {
            const fs = require('fs')
          
            const quoteStyle = await getQuoteStyle()
          
            for (const modelName of teardownOrder) {
              try {
                await getProjectDb().$executeRawUnsafe(
                  `DELETE FROM ${quoteStyle}${modelName}${quoteStyle}`,
                )
              } catch (e) {
                const match = e.message.match(/Code: `(\d+)`/)
                if (match && FOREIGN_KEY_ERRORS.includes(parseInt(match[1]))) {
                  const index = teardownOrder.indexOf(modelName)
                  teardownOrder[index] = null
                  teardownOrder.push(modelName)
                } else {
                  throw e

And it thinks your view is a normal table and attempts to delete from it.

And looks like we have an old issue: [Bug?]: the teardown in jest.setup is attempting to delete data from views · Issue #9824 · redwoodjs/redwood · GitHub

One think to try to to define the list of models manually that is kept here:

github.com

redwoodjs/redwood/blob/0c6000272ece75566786e2ca78e6c17e1b120321/packages/testing/config/jest/api/jest.setup.js#L43C1-L46C4


      
          // check if pre-defined delete order already exists and if so, use it to start
          if (fs.existsSync(TEARDOWN_CACHE_PATH)) {
            teardownOrder = JSON.parse(fs.readFileSync(TEARDOWN_CACHE_PATH).toString())
          }

Which is what this approach suggests: [Bug?]: the teardown in jest.setup is attempting to delete data from views · Issue #9824 · redwoodjs/redwood · GitHub

Or since, the tables to delete are cached and saved in the scenarioTeardown.json file in the .redwood folder, you can try to remove the views there and it should start cached for the test runs.

Hopefully that’s a workaround until we can fix the migrate and teardown model type determination.

Fingers crossed.

dthyresson · August 15, 2024, 9:03am

I couldn’t sleep so I did try and reproduced your example case.

I thought that workaround would work, but because of

  // check the number of models in case we've added/removed since cache was built
  if (teardownOrder.length !== schemaModels.length) {
    teardownOrder = schemaModels
  }

It tries to add back that view into the teardown list and delete from it.

I’ll try to find another possible workaround … again until we can fix properly.

dthyresson · August 15, 2024, 9:11am

Ok, a patch to the jest setup seems to work:

In node_modules/@redwoodjs/testing/config/jest/api/jest.setup.js in teardown

const teardown = async () => {
  const fs = require('fs')

  const quoteStyle = await getQuoteStyle()

  for (const modelName of teardownOrder) {
    try {
      if (modelName === 'UserView') {
        continue
      }
      await getProjectDb().$executeRawUnsafe(
        `DELETE FROM ${quoteStyle}${modelName}${quoteStyle}`
      )
    } catch (e) {
      const match = e.message.match(/Code: `(\d+)`/)
      if (match && FOREIGN_KEY_ERRORS.includes(parseInt(match[1]))) {
        const index = teardownOrder.indexOf(modelName)
        teardownOrder[index] = null
        teardownOrder.push(modelName)
      } else {
        throw e
      }
    }
  }

here I added a list of the views to skip:

      if (modelName === 'UserView') {
        continue
      }

and with that when I run the tests (no change to or no change to test strategy):

Environment variables loaded from .env
Prisma schema loaded from api/db/schema.prisma
Datasource "db": SQLite database "test.db" at "file:./test.db"

Applying migration `20240815084053_`
Applying migration `20240815084154_`

Database reset successful

The following migration(s) have been applied:

migrations/
  └─ 20240815084053_/
    └─ migration.sql
  └─ 20240815084154_/
    └─ migration.sql

✔ Generated Prisma Client (v5.14.0) to ./node_modules/@prisma/client in 66ms

 PASS   api  api/src/services/users/users.test.ts
 PASS   api  api/src/directives/requireAuth/requireAuth.test.ts
 PASS   api  api/src/directives/skipAuth/skipAuth.test.ts
 PASS   web  web/src/lib/formatters.test.tsx

Test Suites: 4 passed, 4 total
Tests:       25 passed, 25 total
Snapshots:   1 passed, 1 total
Time:        3.723 s
Ran all test suites in 2 projects.
rw-view-tests main %

Looks like we ran and the test db had the view:

Hopefully that can get you to run the suite for the moment.

jonoc330 · August 15, 2024, 10:26am

Patch did the trick!

This will unblock us for the meantime.

Thanks so much mate!

dthyresson · August 15, 2024, 12:09pm

Whew!

Great news.

I’ll grab those issues and work with the team to figure out a proper fix in an upcoming version.

Cheers

jonoc330 · October 1, 2024, 5:00am

Hey @dthyresson the unit tests breaking is still an issue for us in Redwood v8. Also, this is still an issue for us: Have to restart local devserver manually when we change code - #6 by callingmedic911

dthyresson · October 1, 2024, 1:52pm

Did the patch not work as you noted on Aug 15th?