How I Solved Common Prisma ORM Errors: Debugging Tips and Best Practices

Struggling with Prisma ORM errors? Discover practical debugging tips, solutions to common issues, and best practices to optimize your development workflow.

Luca Restagno

 · 

7 min read

Debugging
Prisma ORM
Tips
notion-image

Table of Contents

The Prisma ORM is a powerful companion for every software engineer, indie maker, and SaaS founder who wants to add type safety to the queries executed on the database.
I have been using Prisma for the last 2 years, and I have experience on troubleshooting different type of errors.
 
In this article, I want to share it with you, so you don’t have to spend hours figuring out what I’ve already learned.
 

Why Prisma?

I have been using Prisma for all my SaaS products. So far, I’ve built six SaaS products, and I hope to build more in the future (some of them are BlackTwist, Wuf, Userdesk, Inboxs, Hivoe).
And the Prisma ORM is also part of the SaaS Boilerplate I’ve built, called Shipped.club.
The reason is simple, I want you to leverage the tools I have experience on, so that I can help the customers in the private Discord community, that’s available after the purchase.
 
The key goals of using Prisma are:
  • keep under control your database schema
    • define tables and types
    • apply migrations
    • query your database with type safety
    • avoid typing errors
  • protect from SQL injection
  • leverage pooling
 

Common issues

 
Despite being a stable product, sometimes you can encounter some issues.
Below I describe two common errors:
  • migration errors
  • query errors
 
Let’s start with the first one
 

Error with data migrations

Sometimes your migrations emit an error, that’s not very simple to debug, like this one.
JavaScript
Error: ERROR: prepared statement "s2" does not exist
 
You usually get this error when you run npx prisma db push (apply your schema changes to the database) or npx prisma generate (generate the types from your schema).
 
But what this means?
This error happens when using a relational database, like PostgreSQL (one of the most popular, and advanced open-source SQL databases on the market).
 
Specifically, a prepared statement in PostgreSQL is a feature that allows you to execute a query plan that is prepared and stored by the database server for reuse. It improves performance and security, especially when executing the same query multiple times with different parameter values.
Prisma ORM leverages the prepared statements as part of its database query execution process, under the hood (but you don’t have to know or master prepared statements to use Prisma).
 
The main issues arise with the confusion between the different database connections string configurations, pooling and PgBouncer.
 
Let me go straight to the solution, then I explain you the different parts.
The main solution I’ve found is to use two connection strings, one to run the migrations and the second for the execution of your web application / node.js server.
 
First, identify the correct connection strings for your database.
The connection string to run the migrations is the direct access to the database (no pooling or PgBouncer), like for instance
JavaScript
postgresql://username:password@host:5432/dbname
 
Save this connection string as an environment variable called DATABASE_DIRECT_URL.
JavaScript
DATABASE_DIRECT_URL="postgresql://username:password@host:5432/dbname"
Add it to your local .env file and to your hosting service (Vercel, Netlify, Render, and so on).
 
Secondly, get the connection string with the pool, for instance:
JavaScript
postgresql://user:password@host:post/poolName
Get this connection string from your database hosting provider, the parameters might be different.
 
Save this connection string as an environment variables called DATABASE_URL.
JavaScript
DATABASE_URL="postgresql://user:password@host:post/poolName"
Add it to your local .env file and to your hosting service (Vercel, Netlify, Render, and so on).
 
The second step is to update the Prisma configuration to use these two environment variables.
Open the file schema.prisma and update the section datasource db to include this:
JavaScript
datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DATABASE_DIRECT_URL")
}
 
Now, run the usual commands like npx prisma db push and npx prisma generate locally to ensure that everything is working correctly, and run your application locally.
If all is good, push the modifications to your hosting service and trigger a new deployment for the modifications to take effect.
 
Prisma is now correctly configured and you should not have the same issue again.
 

Query performance issues

Recently, I got the "stack depth limit exceeded" error message.
JavaScript
PrismaClientUnknownRequestError: Invalid `prisma.table.findMany()` invocation: Error occurred during query execution: ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(PostgresError { code: "54001", message: "stack depth limit exceeded", severity: "ERROR", detail: None, column: None, hint: Some("Increase the configuration parameter \"max_stack_depth\" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.") }), transient: false })
 
This means that the usage of memory exceeds the database configuration for a specific query.
I ran this SQL command on my database and found this value:
JavaScript
SHOW max_stack_depth;
---
2MB
 
I had a query that was returning 1900+ records to aggregate some values.
For each record I have to recalculate the sum of certain values.
 
When this happens you have two options:
  • increase the max_stack_depth value in your database
  • refactor the logic in the code to avoid to run that big query
  • optimize the query
 
I tried to increase max_stack_depth because it was the quickest solution (not the best one), but my database is hosted on DigitalOcean and I didn’t have the permissions to run the ALTER command:
JavaScript
ALTER SYSTEM SET max_stack_depth = '4MB';
---
permission denied to set parameter "max_stack_depth"
 
(I contacted DigitalOcean via a support ticket and they promptly provided a solution, I can alter the value using their web API).
 
Then, I started to analyze the problem.
The first thing I tried was to run the same query as plain SQL against the database using pgAdmin, and it perfectly worked.
This was suspicious, it means that probably Prisma was executing a query too complex and expensive.
 
Then, I refactored the code, using the Prisma method $queryRaw to run the same query, and it worked!
At this point, I didn’t want to manually define all the type for the values returned by the query, and I thought of using a new cool feature of Prisma called TypedSQL.
This way I can run a Raw Query against the database with the types automatically generated by Prisma for me.
 
So, in the end, I refactored to code, and avoided to increase the value max_stack_depth that would have increased the memory consumption in general.
 

Conclusion

I hope that this article was useful, and it avoids you from spending hours on troubleshooting these kind of errors with Prisma.
Overall my experience with the tool has been very good and exciting, and I see how they are releasing new versions, features and improvements.
 
You can find the documentation of Prisma at this link.
 
As a side note, I’m not affiliated with Prisma in any way, I’m just a happy user of the product.
 
Cheers,
Luca

Ship your startup in days and start selling.

Save weeks of coding with the #1 Next.js Startup and Chrome Extension Boilerplate. Made by a maker for makers.

Get Shipped.club
LR

Luca Restagno

View Profile