Database

Troubleshooting prisma errors

This guide addresses common Prisma errors that you might encounter while using Supabase.

Understanding connection string parameters:

Unlike other libraries, Prisma lets you configure its settings through special options appended to your connection string.

These options, called "query parameters," can be used to address specific errors.


_10
# Example of query parameters
_10
_10
connection_string.../postgres?KEY1=VALUE&KEY2=VALUE&KEY3=VALUE

Errors

... prepared statement already exists

Supavisor in transaction mode (port 6543) does not support prepared statements, which Prisma will try to create in the background.

Solution:

  • Add pgbouncer=true to the connection string. This turns off prepared statements in Prisma.

_10
.../postgres?pgbouncer=true

Can't reach database server at:

Prisma couldn't establish a connection with Postgres or Supavisor before the timeout

Possible causes:

  • Database overload: The database server is under heavy load, causing Prisma to struggle to connect.
  • Malformed connection string: The connection string used by Prisma is incorrect or incomplete.
  • Transient network issues: Temporary network problems are disrupting the connection.

Solutions:

  • Check database health: Use the Reports Dashboard to monitor CPU, memory, and I/O usage. If the database is overloaded, consider increasing your compute size or optimizing your queries.
  • Verify connection string: Double-check the connection string in your Prisma configuration to ensure it matches one in your Database Settings.
  • Increase connection timeout: Try increasing the connect_timeout parameter in your Prisma configuration to give it more time to establish a connection.

_10
.../postgres?connect_timeout=30

Timed out fetching a new connection from the connection pool:

Prisma is unable to allocate connections to pending queries fast enough to meet demand.

Possible causes:

  • Overwhelmed server: The server hosting Prisma is under heavy load, limiting its ability to manage connections. By default, Prisma will create the default 2 * num_cpus / 2 worth of connections. A common cause for server strain is increasing the connection_limit significantly past the default.
  • Insufficient pool size: The Supavisor pooler does not have enough connections available to quickly satisfy Prisma's requests.
  • Slow queries: Prisma's queries are taking too long to execute, preventing it from releasing connections for reuse.

Solutions:

  • Increase the pool timeout: Increase the pool_timeout parameter in your Prisma configuration to give the pooler more time to allocate connections.
  • Reduce the connection limit: If you've explicitly increased the connection_limit parameter in your Prisma configuration, try reducing it to a more reasonable value.
  • Increase pool size: If you are connecting with Supavisor, try increasing the pool size in the Database Settings.
  • Optimize queries: Improve the efficiency of your queries to reduce execution time.
  • Increase compute size: Like the preceding option, this is a strategy to reduce query execution time.

Server has closed the connection

According to this GitHub Issue for Prisma, this error may be related to large return values for queries. It may also be caused by significant database strain.

Solutions:

  • Limit row return sizes: Try to limit the total amount of rows returned for particularly large requests.
  • Minimize database strain:Check the Reports Page for database strain. If there is obvious strain, consider optimizing or increasing compute size

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

Prisma relies on migration files to ensure your database aligns with Prisma's model. External schema changes are detected as "drift", which Prisma will try to overwrite, potentially causing data loss.

Possible causes:

  • Supabase Managed Schemas: Supabase may update managed schemas like auth and storage to introduce new features. Granting Prisma access to these schemas can lead to drift during updates.
  • External Schema Modifications: Your team or another tool might have modified the database schema outside of Prisma, causing drift.

Solution:

  • Baselining migrations: baselining re-syncs Prisma by capturing the current database schema as the starting point for future migrations.

Max client connections reached

Postgres or Supavisor rejected a request for more connections

Possible causes:

  • When working in transaction mode (port 6543): The error "Max client connections reached" occurs when clients try to form more connections with the pooler than it can support.
  • When working in session mode (port 5432): The max amount of clients is restricted to the "Pool Size" value in the Database Settings. If the "Pool Size" is set to 15, even if the pooler can handle 200 client connections, it will still be effectively capped at 15 for each unique "database-role+database" combination.
  • When working with direct connections: Postgres is already servicing the max amount of connections

Solutions

  • Transaction Mode for serverless apps: If you are using serverless functions (Supabase Edge, Vercel, AWS Lambda), switch to transaction mode (port 6543). It handles more connections than session mode or direct connections.
  • Reduce the number of Prisma connections: A single client-server can establish multiple connections with a pooler. Typically, serverless setups do not need many connections. Starting with fewer, like five or three, or even just one, is often sufficient. In serverless setups, begin with connection_limit=1, increasing cautiously if needed to avoid maxing out connections.
  • Increase pool size: If you are connecting with Supavisor, try increasing the pool size in the Database Settings.
  • Disconnect appropriately: Close Prisma connections when they are no longer needed.
  • Decrease query time: Reduce query complexity or add strategic indexes to your tables to speed up queries.
  • Increase compute size: Sometimes the best option is to increase your compute size, which also increases your max client size and query execution speed

Cross schema references are only allowed when the target schema is listed in the schemas property of your data-source

A Prisma migration is referencing a schema it is not permitted to manage.

Possible causes:

  • A migration references a schema that Prisma is not permitted to manage

Solutions:

  • Multi-Schema support: If the external schema isn't Supabase managed, modify your prisma.schema file to enable the multi-Schema preview
prisma.schema

_11
generator client {
_11
provider = "prisma-client-js"
_11
previewFeatures = ["multiSchema"] //Add line
_11
}
_11
_11
datasource db {
_11
provider = "postgresql"
_11
url = env("DATABASE_URL")
_11
directUrl = env("DIRECT_URL")
_11
schemas = ["public", "other_schema"] //list out relevant schemas
_11
}

  • Supabase managed schemas: Schemas managed by Supabase, such as auth and storage, may be changed to support new features. Referencing these schemas directly will cause schema drift in the future. It is best to remove references to these schemas from your migrations.

An alternative strategy to reference these tables is to duplicate values into Prisma managed table with triggers. Below is an example for duplicating values from auth.users into a table called profiles.

Show/Hide Details
table_in_public

_10
-- Create the 'profiles' table in the 'public' schema
_10
create table public.profiles (
_10
id uuid primary key, -- 'id' is a UUID and the primary key for the table
_10
email varchar(256) -- 'email' is a variable character field with a maximum length of 256 characters
_10
);

trigger_on_insert

_15
-- Function to handle the insertion of a new user into the 'profiles' table
_15
create function public.handle_new_user()
_15
returns trigger
_15
language plpgsql
_15
security definer set search_path = ''
_15
as $$
_15
begin
_15
_15
-- Insert the new user's data into the 'profiles' table
_15
insert into public.profiles (id, email)
_15
values (new.id, new.email);
_15
_15
return new; -- Return the new record
_15
end;
_15
$$;

trigger_on_update

_16
-- Function to handle the updating of a user's information in the 'profiles' table
_16
create function public.update_user()
_16
returns trigger
_16
language plpgsql
_16
security definer set search_path = ''
_16
as
_16
$$
_16
begin
_16
-- Update the user's data in the 'profiles' table
_16
update public.profiles
_16
set email = new.email -- Update the 'email' field
_16
where id = new.id; -- Match the 'id' field with the new record
_16
_16
return new; -- Return the new record
_16
end;
_16
$$;

trigger_on_delete

_15
-- Function to handle the deletion of a user from the 'profiles' table
_15
create function public.delete_user()
_15
returns trigger
_15
language plpgsql
_15
security definer set search_path = ''
_15
as
_15
$$
_15
begin
_15
-- Delete the user's data from the 'profiles' table
_15
delete from public.profiles
_15
where id = old.id; -- Match the 'id' field with the old record
_15
_15
return old; -- Return the old record
_15
end;
_15
$$;

triggers_on_auth

_14
-- Trigger to run 'handle_new_user' function after a new user is inserted into 'auth.users' table
_14
create trigger on_auth_user_created
_14
after insert on auth.users
_14
for each row execute procedure public.handle_new_user();
_14
_14
-- Trigger to run 'update_user' function after a user is updated in the 'auth.users' table
_14
create trigger on_auth_user_updated
_14
after update on auth.users
_14
for each row execute procedure public.update_user();
_14
_14
-- Trigger to run 'delete_user' function after a user is deleted from the 'auth.users' table
_14
create trigger on_auth_user_deleted
_14
after delete on auth.users
_14
for each row execute procedure public.delete_user();