Disabling Prepared statements

[TheOtherBrian1]

This is a copy of a troubleshooting article on Supabase's docs site. It may be missing some details from the original. View the original article.

It is important to note that although the direct connections and Supavisor in session mode support prepared statements, Supavisor in transaction mode does not.

How to disable prepared statements for Supavisor in transaction mode

Each ORM or library configures prepared statements differently. Here are settings for some common ones. If you don't see yours, make a comment

Prisma:

add ?pgbouncer=true to end of connection string:

postgres://[db-user].[project-ref]:[db-password]@aws-0-[aws-region].pooler.supabase.com:6543/[db-name]?pgbouncer=true

Drizzle:

Add a prepared false flag to the client:

export const client = postgres(connectionString, { prepare: false })

Node Postgres

Omit the "name" value in a query definition:

const query = {
  name: 'fetch-user', // <--------- DO NOT INCLUDE
  text: 'SELECT * FROM user WHERE id = $1',
  values: [1],
}

Psycopg

set the prepare_threshold to None.

asyncpg

Follow the recommendation in the asyncpg docs

disable automatic use of prepared statements by passing statement_cache_size=0 to asyncpg.connect() and asyncpg.create_pool() (and, obviously, avoid the use of Connection.prepare());

Rust's Deadpool or tokio-postgres:

• Check GitHub Discussion

[mirusky]

I'm having issues with GORM I've setted PrepareStmt to false as docs suggests.

I'll try the 'pgbouncer=true' at end of connection string to see if it takes in.

[TheOtherBrian1]

pgbouncer=true is how Prisma reconfigures prepared statements, but it would not have an impact on GORM. As the doc you shared pointed out, modifying the global and/or session configs to PrepareStmt: false should be sufficient.

Are you certain that the error is prepared statement related?

As an aside, I'm curious to know where you're deploying to: AWS Lambda, Render, Fly.io? Depending on whether the service auto-scales or is predominantly stationary, the direct connection or Supavisor in session mode may be better

[mirusky]

My logs show that it is related to the prepared stmt, but even disabling it explicitly continues to give an error. Especially after the app has restarted.

Maybe something related to GORM itself? I'll try to contact them.

[TheOtherBrian1]

I'll try to contact them.

@mirusky, I'd love if you could share their feedback. If we can add it to the list, that could be very helpful for others

[2bashbrothers]

What about postgres.js? I've tried ?pgbouncer=true but no luck

[2bashbrothers]

actually think I just found it:

[TheOtherBrian1]

I'll add it to the list. Thanks for the help

[tedsecretsource]

What happens if we don't use prepared statements and also don't disable them?

[TheOtherBrian1]

@tedsecretsource, I think it may help if I provide a more complete breakdown of what prepared statements are and why they need to be disabled

What happens when Postgres receives a query

When Postgres receives a query, it first:

• Parses the SQL into a lexical tree
• Replaces query placeholders with their actual arguments
• Selects the algorithms used to retrieve the underlying data

These stages together are known as the planning phase. It is usually very quick (under 10ms, but often sub-ms). You can actually see how long it takes relative to fetching the data (execution phase), by running the following SQL:

EXPLAIN ANALYZE <your_query>;

Even though planning is fast, it still takes time. ORMs like Prisma reduce its overhead by using Prepared Statements (P.S.), cached plans that can be reused, so the database doesn’t replan each time.

Why prepared statements break with some poolers

The issue is that P.S. exists only within the session/connection they were defined in. If a query refers to a P.S. that doesn't exist in its session or tries to create one that already exists, Postgres will throw an error:

• ERROR: prepared statement "SOME_NAME" already exists
• ERROR: prepared statement "SOME_NAME" does not exist

Supavisor, when running in transaction mode (port 6543), acts as a Postgres connection load balancer. It dynamically reassigns database connections to clients as needed. So, there is no guarantee that when the query references a P.S., it will be able to find it in the session, causing the above errors.

What happens if we don't use prepared statements and also don't disable them?

Certain ORMs and drivers will only create prepared statements if explicitly programmed to. For instance, Node Postgres does not make P.S. by default. With these types of libraries, you can use Supavisor in transaction mode without any configurations.

Some ORMs, such as Prisma, will create them in the background by default. Even if you have no desire to use them, you'll still have to manually disable them in your code.