prisma any good if i need mysql view and stored procedures in nextJS

user_9735599 0
Viewed 2920

I'm starting to learn prisma to replace my nodejs back-end in my nextJS app.

It seems that prisma is not made to work with views and stored procedures created in my mysql database.

Is there even a point in switching to prisma if I cannot just use views and stored procedures, which allows me to create a user in the database that can only access those two and nothing else.

any prisma pro's that could give confirm or deny what I am thinking

Thx

prisma next.js
1 Answers

While neither feature is directly supported by Prisma at the moment, workarounds exist that make it possible to use both with Prisma. For both you would need to perform some operations directly in SQL.

Stored Procedures

You can use the $executeRaw and $queryRaw command to perform raw SQL queries through Prisma. You can use this to execute your created procedures. For example, if you have a stored procedure called foobar you could execute it as follows:

  const rawSQL = `call foobar`;
  const result = await prisma.$executeRaw(rawSQL);

You could also use $executeRaw to generate the stored procedure or use the tool/CLI of your choice.

Check out the Raw database access article for more information on $executeRaw and $queryRaw, as well as the difference between the two.

MySQL View

A workflow exists to use views through Prisma. The workflow goes something like this:

  1. Create the view directly using SQL.
  2. Introspect database to update your Prisma Schema.
  3. Manually add the model for your view to the Prisma Schema.
  4. Generate the Prisma client and use the entity class created for the view to run queries.

You can find a indepth guide on how to use SQL Views in MySQL along with discussions and current limitations.

Suggestions

For both features, I would strongly suggest trying out your usecase first before adopting Prisma in a production application. For example, in the past users have faced some difficulties when running procedures that return data back to the client (this should be fixed now though).

Additionally, if you face any issues or bugs, always feel to create a issue on the Prisma github repo. We are super active and will try our best to help fix the issue.

user_88558440
Related