prisma: mainnet.db.explorer.indexer.near.dev: `db error: ERROR: prepared statement "s0" does not exist`

Someone else somewhere might have this issue after doing what I did… Here’s how to fix this if you did what I did:

The way that the Supabase Settings > Database page is laid out, if you scroll a bit too fast you’ll skip the Connection string section completely and see a Connection string field inside the Connection Pooling Custom Configuration section. This is not the connection string you are looking for. Make the Obi-Wan motion with you hand now

Connection Pooling connection string is a DIFFERENT connection string. Head to the right section, click on Nodejs and copy the hidden connection string that you ARE looking for.

You’re welcome. 😉

The one who is struggling with Supabase connection pooling. Please use this way your database url

DATABASE_URL="postgres://postgres.[reference-id]:[Your Password]@aws-0-ap-southeast-1.pooler.supabase.com"

Thank you

Configure your schema.prisma this way:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  directUrl = env("DIRECT_DATABASE_URL")
  schemas  = ["auth", "public"]
}

With this setup directUrl is used for migrations and push, and url is used for queries. Then set the environment variables like this:

DIRECT_DATABASE_URL="connection type session"
DATABASE_URL="connection type transaction"

Here is the supabase UI for generating the URLs.

Is this a connection pooled connection string (e.g. PgBouncer)? If so, you might need to enable PgBouncer mode by adding &pgbouncer=true to it.

Using Prisma’s directUrl option solved this issue for me (docs).

Example:

// schema.prisma

datasource db {
    provider  = "postgresql"
    url           = env("DATABASE_URL") // connection string with pgBouncer/pooling enabled
    directUrl = env("DIRECT_URL") // plain connection with no pgBouncer (used for push & migrations)
}

Prisma will use the directUrl connections for push and migrations (docs), and the url for queries.

Note for anyone using Supabase: there are 2 connection string URLs on the settings page - one with connection pooling, and one without.

I use supabase and got this error after running prisma migration. For me worked using regular database url, not the one for connection pooling.

Noob here,first time using prisma for a tutorial,but in my case i had to connect a Supabase project with a Postgress table created in Prisma , and i received the same error: Error: Migration engine error: db error: ERROR: prepared statement “s0” does not exist

The solution for me was changing the port in my DATABASE_URL from 6534 to the default 5432 , for example:

DATABASE_URL=“postgres://postgres.qbpjsltnktlwrvpjlmbe:Ndy2SbkwvwqwfqfqwdqfEjLOiU@aws-0-eu-central-1.pooler.supabase.com:5432/postgres”

Someone else somewhere might have this issue after doing what I did… Here’s how to fix this if you did what I did:

The way that the Supabase Settings > Database page is laid out, if you scroll a bit too fast you’ll skip the Connection string section completely and see a Connection string field inside the Connection Pooling Custom Configuration section. This is not the connection string you are looking for. Make the Obi-Wan motion with you hand now

Connection Pooling connection string is a DIFFERENT connection string. Head to the right section, click on Nodejs and copy the hidden connection string that you ARE looking for.

You’re welcome. 😉

thanks

It’s strange, after generate schema and client from near-indexer-for-explorer, it throw error when use rawQuery.

But it’s fine when use the db of the testnet postgresql://public_readonly:nearprotocol@testnet.db.explorer.indexer.near.dev/testnet_explorer?schema=public. &pgbouncer=true doesn’t help.

I was having the same issue. Just search for “Prepared statements error” on this link. That explains why you will need to add ?pgbouncer=true&connection_limit=1 at the end of the connection string

@cdgn-coding For me I have to use connection type session for both otherwise it does not work and I get the same error as OP. Have you faced the same issue or does the Transaction connection type work for you for queries?

Someone else somewhere might have this issue after doing what I did… Here’s how to fix this if you did what I did:

The way that the Supabase Settings > Database page is laid out, if you scroll a bit too fast you’ll skip the Connection string section completely and see a Connection string field inside the Connection Pooling Custom Configuration section. This is not the connection string you are looking for. Make the Obi-Wan motion with you hand now

Connection Pooling connection string is a DIFFERENT connection string. Head to the right section, click on Nodejs and copy the hidden connection string that you ARE looking for.

You’re welcome. 😉

I don’t think this is accurate https://supabase.com/docs/guides/database/connecting-to-postgres#supavisor-vs-pgbouncer

Supavisor vs PgBouncer# Supabase previously used PgBouncer for connection pooling. We have now deprecated PgBouncer in favor of Supavisor. Supavisor is available on all new and existing projects. Supavisor is a new connection pooler by Supabase that runs on a high-availability cluster, segregated from your database. This means more resources are available for your database. No Application changes are required to switch from PgBouncer to Supavisor, you simply need to choose the new connection string from the “Connection Pooling” section on Database settings. On 15th January 2024 PgBouncer will be disabled. Additionally, your Supabase database domain (db.projectref.supabase.co) will start resolving to an IPv6 address. No changes are required if your network supports IPv6. Otherwise, update your applications to use Supavisor which will continue to support IPv4 connections.

just got the same error but made it work with these env variables:

# Connect to Supabase via connection pooling with Supavisor.
DATABASE_URL="postgres://postgres.[your-supabase-project]:[password]@aws-0-eu-central-1.pooler.supabase.com:6543/postgres?pgbouncer=true"

# Direct connection to the database. Used for migrations.
DIRECT_URL="postgres://postgres:[password]@db.[your-supabase-project].supabase.co:5432/postgres"

please note that port is different in the DIRECT_URL

Interesting, when you point to the actual instance the pgbouncer=true should not be needed for Migrations. But good it works for you.

@janpio Thank you for the quick response. Unfortunately, &pgbouncer=true does not help.

On https://supabase.com/partners/integrations/prisma:

Prepared statements error

PostgreSQL supports prepared statements. They are pre-parsed queries that can be quickly modified for re-use. So, if you were executing the same query over-and-over, but only changing the arguments or some other small aspect, prepared statements offer performance benefits. Prisma will try to create prepared statements in the background, but Supavisor does not support them and leads to the following error

“prepared statement \“s#\” does not exist”

To prevent Supavisor from erroring, it is necessary to add the following query parameter to your connection strings: pgbouncer=true to the Transaction connection pooler string as mentioned in Step 1.

Prisma uses the parameters for adjusting its configuration settings. The pgbouncer parameter tells Prisma to not attempt to create prepared statements. The parameter is called pgbouncer because the most popular pooler for PostgreSQL is PGbouncer and it does not support prepared statements. The Prisma Team specifically made the configuration to reflect that, but the parameter works for Supavisor, too.

Supabase tells you that Pgbouncer is being deprecated…which makes you think that you should remove this line.

But Prisma is looking for ?pgbouncer=true to let it know it should not use prepared statements. This is where the confusion comes from. ?pgbouncer=true is not being used by the Supabase database, its just being read by Prisma.

I also had success using Supabase’s non-connection-pooling connection string on port :5432.

I had same issue. Just select Nodejs option in Connection string menu (for all those who are dealing with js or ts.

Similar to before @Papillon6814, if you get this error it means you are probably doing something to a connection pooled (behind PgBouncer) connection string that does not work like - it seems - using db push. To run db push you need to provide Prisma a direct connection string, for example via directUrl: https://www.prisma.io/docs/guides/performance-and-optimization/connection-management#external-connection-poolers Vercel Postgres for example provides different connection strings for usage in directUrl and url - where the url one already has pgbouncer=true at the end.