Using SQL Views on Prisma View

[Niedman]

Question

Hi Everyone,

I've followed the official documentation(https://www.prisma.io/docs/orm/prisma-schema/data-model/views) of Prisma in order to fetch data from a view, however I'm getting The table public.BalanceTransactionView does not exist in the current database.

If I use $queryraw i'm able to fetch the data and don't get that error but when i use the client like: prisma.BalanceTransacionView.findMany() it states that the The table public.BalanceTransactionView does not exist in the current database.

I've added the view to the prisma.schema and run prisma generate so I can use the prisma client to the new generated view and I can see that it recognizes it but when i deploy it it just gives the error above. From what i understood from the documentation we don't need to create a migrate so I'm a bit unsure why does it not get the view.

The view on the scheme is defined:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["views"]
}

view BalanceTransactionView {
  id                     String   @unique
...
}

Thanks in advance.

How to reproduce (optional)

Expected behavior (optional)

No response

Information about Prisma Schema, Client Queries and Environment (optional)

// Add your schema.prisma

// Add any relevant Prisma Client queries here

• OS:

• Database:

• Node.js version:

• Run prisma -v to see your Prisma version and paste it

[ludralph]

Hi @Niedman 👋

Thank you for raising this question.

Currently, to use Views in Prisma, you will need to add it as part of a migration using the migrate dev --create-only command and then manually add the SQL for views to your migration file. Alternatively, you can create views manually in the database. and then introspect it using npx prisma db pull.

Defining a View in the schema file and running prisma generate does not create or run any SQL related to views which is why you are receiving the error message does not exist in the current database.

[Niedman]

Thanks for the reply @ludralph !

I've made the second option which was creating the view on the database and then did a npx prisma db pull but it deletes the view from the prisma scheme. Is that supposed to happen?

I will give it a try with a migration and see how it goes :) I will post when it's fixed!

[nikolasburk]

Did it work in the end for you, @Niedman?