Connect directly to DB when "pooling" (pgbouncer) mode is enabled

Nick_Randall · January 30, 2023, 9:36pm

I use the pooling mode on my db endpoint but the tool I use to manage db migrations does not work with pgbouncer.

It would be great to have a method of connecting directly to DB, even when pooling is enabled. According to the Prisma docs, several other DB providers have this feature.

See: Configure Prisma Client with PgBouncer

psugihara · February 9, 2023, 12:34am

My dirty solution is setting DATABASE_MIGRATE_URL in my env to the url without pgbouncer=true.

Then a yarn task like this:
"db:migrate": "DATABASE_URL=$DATABASE_MIGRATE_URL yarn prisma generate && DATABASE_URL=$DATABASE_MIGRATE_URL yarn prisma migrate deploy",

Not pretty but it works.

Mahmoud · February 9, 2023, 12:42am

@Nick_Randall @psugihara This is something we’re actively working on and will ship very soon! We will expose two connection strings (pooled and non-pooled) so you will be able to run migrations when you have connection pooling enabled.

Krystian · February 10, 2023, 6:32am

Any recommendation on how this could be done in Vercel, using @psugihara 's temp solution? I’m trying to switch to neon right now and this is the only thing holding me up as I do migrations with Prisma and host on Vercel

Mahmoud · February 10, 2023, 12:35pm

Hey @Nick_Randall @psugihara @Krystian!

We shipped a solution for this but haven’t exposed it in our UI yet. First, make sure to disable the “pooler enabled” option in the endpoint settings.

To access the compute directly (without pooler), use the connection string & host values from the UI.

Example: postgres://casey@ep-square-sea-260584.us-east-2.aws.neon.tech/neondb

On the other hand, to access the compute via pooler, modify the connection string and add the -pooler suffix to the endpoint id just before the region part of the hostname.

Example: postgres://casey@ep-square-sea-260584-pooler.us-east-2.aws.neon.tech/neondb

You can use the pooled connection string when deploying your application and the non-pooled connection to run migrations.

Let us know if you try this workaround and run into any issues.

Krystian · February 10, 2023, 5:37pm

Thanks Mahmoud.

I assume then that this work-around won’t work for the neon-vercel integration just yet? I.e. when I open a new pull request and neon creates a db branch of the production database for that particular preview enviornment, neon won’t yet automatically update the respective preview DATABASE_URL to the new pooling URL, correct? Or would it right now just default to the non-pooling URL with the integration enabled?

Mahmoud · February 10, 2023, 7:19pm

Yes, this workaround won’t work for the Neon integration on Vercel yet, but we’re working on it.

Krystian · February 13, 2023, 4:13pm

Awesome. Any chance it will be available this month?

Mahmoud · February 15, 2023, 8:09pm

No specific ETA at the moment. It shouldn’t be a blocker from using the integration though, since previews generally don’t have the amount of traffic that requires connection pooling.

@Krystian

Krystian · December 1, 2023, 11:21pm

Any update on this @Mahmoud ?

Mahmoud · December 4, 2023, 1:16pm

Hey @Krystian! We will soon make the pooled connection the default one for the integration and provide the direct connection as well. No exact ETA yet

We’re also working directly with the Prisma team so that migrations can be applied when using the pooled connection. This way, you will only need one connection string

millar · December 22, 2023, 3:39pm

Hello @Mahmoud,

We are trying to switch to Neon with Prisma on Vercel (using the integration) but the DATABASE_URL value that is set for preview branches, despite containing the pooling hostname, does not contain the pgbouncer=true flag at the end of the URL.

Because of this, queries fail with errors like:

prepared statement \"s41\" does not exist

Is there anything that can be done to add this value to the end of the URL? There is no way for us to do this in Prisma via env AFAICT.

Kind regards,
Sam

Mahmoud · December 22, 2023, 4:30pm

Hey! Here’s an example of how you can append the pgbouncer flag to the connection string. Basically you do it while instantiating Prisma Client

github.com

neondatabase/naturesnap/blob/main/lib/prisma.ts

import { PrismaClient } from '@prisma/client';

const globalForPrisma = global as unknown as { prisma: PrismaClient };

export const prisma =
  globalForPrisma.prisma ||
  new PrismaClient({
    log: ['query'],
    datasources: {
      db: {
        // when using a pooled database connection with prisma, you need to append`?pgbouncer=true` to the connection string.
        // The reason this is done here rather than in the .env file is because the Neon Vercel integration doesn't include it.
        url: `${process.env.DATABASE_URL}?pgbouncer=true&connect_timeout=10&pool_timeout=10`,
      },
    },
  });

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;