[Bug?]: Prisma View compatibility

[majimaccho]

What's not working?

Since View of Databases is being a feature of prisma, I think it should be supported by Redwood.js.
However, Redwood.js causes error with api test when using View.

The reason why it fails is that Redwood.js internally uses getDMMF method of prisma internal package to clean up db data and it does not distinguish table or view.

It's obviously issues of prisma but Redwood.js needs to handle it correctly.

I've already raised above it on prisma's preview feature feadback.
prisma/prisma#17335 (comment)

How do we reproduce the bug?

No response

What's your environment? (If it applies)

No response

Are you interested in working on this?

• I'm interested in working on this

[majimaccho]

For now we can probably solve this issue by catching error below.

Raw query failed. Code: 55000. Message: `db error: ERROR: cannot delete from view

[dac09]

Thanks @majimaccho for the issue, and ofcourse the analysis behind it - much appreciated!

I would say let's wait to hear back from the Prisma team as views are still a preview feature, before we implement anything.

@cannikin - could I hand this over to you to track?

[cannikin]

Hello! So your only concern at this point is the failing tests? Did you want to try and come up with a PR that addresses it? Until Prisma adds something to getDMMF() you could catch that error here: https://github.com/redwoodjs/redwood/blob/main/packages/testing/config/jest/api/jest.setup.js#L141-L156

You can see that we already expect some errors for foreign key relationships, you could add another catch block for the error you identified above.

[majimaccho]

@cannikin @dac09 Thank you for your kind and quick feedback.

Yeah, I already checked the jest.config.js and some error codes are catched and pushed to stack.

Of cause I can add something to catch the error.

However, the error code I got was below.

Raw query failed. Code: `55000`. Message: `db error: ERROR: cannot delete from view "pro_answer_all_values_view"
    DETAIL: Views that do not select from a single table or view 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.`

According to PostgreSQL's documentation The error code 55000 is object_not_in_prerequisite_state and not specifically for the error deleting views.

So the easiest way to handle this issue is that I just add scripts to check if the entire error message starts with Raw query failed. Code: 55000. Message: db error: ERROR: cannot delete from view `.

It's just works on PostgreSQL but maybe not on MySQL or others.

[cannikin]

Any solution we come up with will need to work with MySQL and SQLite as well...did you want to try applying your same schema to those two databases as well, running the tests, and adding those errors to the catch?

[majimaccho]

@cannikin
Yeah, I can take it if you give me some time (like in this year).

But for short term, I escaped from this problem by using INSTEAD OF DELETE trigger.