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

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

2 Likes

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.

2 Likes

@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.

4 Likes

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 :smiling_face_with_tear:

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.

1 Like

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?

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

1 Like

Awesome. Any chance it will be available this month?

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