Prisma Error Management

[TheOtherBrian1]

Addressing Specific Errors:

Prisma, unlike other libraries, uses query parameters for configurations.

Some can be used to address specific errors and can be appended to end of your connection string like so:

.../postgres?KEY1=VALUE&KEY2=VALUE&KEY3=VALUE

Can't reach database server at:

Increase connect_timeout to 30s and check to make sure you are using a valid connection string.

.../postgres?connect_timeout=30

Timed out fetching a new connection from the connection pool:

Increase pool_timeout to 30s .

.../postgres?pool_timeout=30

... prepared statement "" already exists

Add pgbouncer=true to the connection string.

.../postgres?pgbouncer=true

Max client connections reached

Checkout this guide for managing this error

Server has closed the connection

According to this GitHub Issue for Prisma, it may be related to large return values for queries. Try to limit the total amount of rows returned for particularly large requests.

Drift detected: Your database schema is not in sync with your migration history

Prisma will try to act as the source of truth for your database structures. If you CREATE, DROP, or ALTER database objects outside of a Prisma Migration, it is likely to detect drift and may offer to correct the situation by purging your schemas. To circumvent this issue, try baselining your migrations.

Some users have discussed how they managed this problem in a GitHub Discussion.

Management Suggestions

Make a custom role for Prisma to increase observability

Imagine your database as a house, and users as the people with keys.

• By default, most developers use the "master key" (the postgres role) to access everything. But it's safer to give Prisma its own key! This way, it can only access the rooms (tables) it needs.
• it's usually safer to give Prisma its own key! This way, it can only access the rooms (tables) it needs.
• Plus, with separate keys, it's easier to see what Prisma is doing in your house with monitoring tools, such as PGAudit and pg_stat_activity.

Creating the Prisma User:

create user "prisma" with password 'secret_password' bypassrls createdb; 

Prisma requires the createdb modifier to create shadow databases. It uses them to help manage migrations.

Give Postgres Ownership of the New User:

This allows you to view Prisma migration changes in the Dashboard

grant "prisma" to "postgres";

Keep it safe!

Use a strong password for Prisma. Bitwarden provides a free and simple password generator that can make one for you.

If you need to change it later, you can use the below SQL:

alter user "prisma" with password 'new_password';

Grant Prisma Access

The below example gives Prisma full authority over all database objects in the public schema:

  -- Grant it necessary permissions over the relevant schemas (public)
  grant usage on schema public to prisma;
  grant create on schema public to prisma;
  grant all on all tables in schema public to prisma;
  grant all on all routines in schema public to prisma;
  grant all on all sequences in schema public to prisma;
  alter default privileges for role postgres in schema public grant all on tables to prisma;
  alter default privileges for role postgres in schema public grant all on routines to prisma;
  alter default privileges for role postgres in schema public grant all on sequences to prisma;

For more guidance on specifying access, check out this article on privileges

Optimize Prisma Queries:

In the Query Performance Advisor, you can view long-running or frequently accessed queries by role:

Selecting a query can reveal suggestions to improve its performance

Configuring Connections

Useful Links:

• How to Monitor Connections and Find the Correct Pool Size.
• Supavisor FAQ

Supabase provides 3 connection strings in the Database Settings. You can use all three or just the ones most relevant to your project.

Direct Connection:

Best used with stationary servers, such as VMs and long-standing containers, but it only works in IPv6 environments unless the IPv4 Add-On is enabled. If you are unsure if your network is IPv6 compatible, check here.

# Example Connection
postgresql://postgres:[PASSWORD]@db.[PROJECT REF].supabase.co:5432/postgres

Supavisor in Session Mode (port 5432):

# Example Connection
postgres://[DB-USER].[PROJECT REF]:[PASSWORD]@aws-0-[REGION].pooler.supabase.com:5432/postgres

An alternative to direct connections when working in IPv4-only environments.

Session mode is a good option for migrations

Supavisor in Transaction Mode (port 6543):

# Example Connection
postgres://[DB-USER].[PROJECT REF]:[PASSWORD]@aws-0-[REGION].pooler.supabase.com:6543/postgres

Should be used when deploying to:

• Horizontally auto-scaling servers
• Edge/Serverless deployments

When working in serverless/edge environments, it is recommended to set the connection_limit=1 and then gradually increase it if necessary.