Neon•2y ago

ugly-tan

DB URL w/ Vercel Integration

Hey all - really loving what Neon is doing. I saw recently that there is now a DATABASE_URL_UNPOOLED environment variable included in the Vercel integration. Is that intended for use with migrations, etc, and something we can rely on?

20 Replies

flat-fuchsia•2y ago

Yes, I believe that's the idea. The DATABASE_URL will be pooled and you'll need to opt-out by using UNPOOLED. Until recently we were always injecting the unpooled URL which was not always ideal on Vercel

ugly-tanOP•2y ago

Great - thanks!

flat-fuchsia•2y ago

You’re welcome! I asked internally for confirmation. If my understanding is incorrect I’ll let you know.

passive-yellow•2y ago

Hey @chase . We're rolling out some improvements to the integration this week. You're correct: for most uses, you probably want DATABASE_URL, which is pooled. For some scenarios (and notably for some Prisma operations), then you don't want the pooled endpoint and this is what DATABASE_URL_UNPOOLED gives you. In Prisma terminology, it's the "Direct URL".

ugly-tanOP•2y ago

Yeah, that makes sense - I really wanted this feature and appreciate you guys rolling it out!

provincial-silver•2y ago

Unfortunately, if you're using Prisma, it seems like we have to add ?pgbouncer=true to the DATABASE_URL, or else the pooled endpoint won't work. And, to make things difficult, prisma doesn't allow a convenient way to concatenate strings in the schema file, AFAIK. This change broke our db connections today, with a quick workaround to swapover to DATABASE_URL_UNPOOLED. That said, I'd love to use the connection pooling endpoint by default!

modern-teal•2y ago

That's how you can append params when instantiating Prisma Client https://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;

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;

GitHub

naturesnap/lib/prisma.ts at main · neondatabase/naturesnap

Contribute to neondatabase/naturesnap development by creating an account on GitHub.

modern-teal•2y ago

@shree

like-gold•2y ago

this broke preview deployments for us, migrations fail now:

Error querying the database: db error: ERROR: prepared statement "s0" already exists

We should use the unpooled connection for migrations and set up the pgbouncer=true param for the regular connection now right?

passive-yellow•2y ago

Hey @Dennis Paagman and @shree - apologies this broke your flow. For Prisma, you likely want to use DATABASE_URL with ?pgbouncer=true as Mahmoud has suggested above. The timeout params aren't strictly necessary as our cold starts are pretty quick these days. Early next year, we're enabling prepared statements: when that arrives the pgbouncer param won't be needed either. When you're running migrations, you'll want to configure DATABASE_URL_UNPOOLED to be what Prisma referrs to as the "Direct URL".

provincial-silver•2y ago

Awesome, thank you Mahmoud + Mike!!

passive-yellow•2y ago

Hi again @shree @Dennis Paagman - sincere apologies for the back and forth but we're reverting this change now. DATABASE_URL will continue to point to an unpooled endpoint. The reason for reverting it is that it's a breaking change and we should have given everyone more warning before making a change that could cause deployments to fail. We still intend to make the change but we're going to roll it out a little differently: - We're first going to roll out support for prepared statements. With this in place, Prisma clients won't need to append any additional params e.g. pgbouncher=true so it should "just work" without any configuration changes to your apps. 🎉 - We'll send out a notice before the change is made. - We'll make sure our docs are updated, including a section on our recommended setup for Prisma for url and directUrl values. cc @Mahmoud

like-gold•2y ago

I already merged the necessary changes, will that keep working as well or should I revert those? So keeping bouncer=true in place and using the directUrl to _UNPOOLED

absent-sapphire•2y ago

Ha, we started using DATABASE_URL_UNPOOLED and now our builds fail 😆 guess you can't win 'em all

modern-teal•2y ago

Really sorry about 🙏

absent-sapphire•2y ago

so the code snippet above is the recommended way to add pgbouncer to prisma? afaik you can't do it directly in their config file

modern-teal•2y ago

Yes, but in the future it won't be necessary

absent-sapphire•2y ago

ok cool, we'll update, easy enough!

continuing-cyan•2y ago

Hi Mike, How far along are you on the updated docs? I'm getting a bit confused if the DATABASE_URL_UNPOOLED is actually available now? I just tried installing the integration through Vercel, and it looks like only the DATABASE_URL variable is set by the Neon integration?

passive-yellow•2y ago

Hey @MariusVB - as of today, DATABASE_URL points to an unpooled endpoint. Rolling this out had a few more steps than first anticipated. This week and next, we are upgrading and enabling prepared statements across our entire pgbouncer fleet which is a prerequiste for this. I'm hopeful that within the next couple of weeks DATABASE_URL will be a pooled endpoint and we'll include DATABASE_URL_UNPOOLED by default too. We will update our docs and post an announcement here when it's ready. Thank you for your patience 🙏

Gaming

Programming

DB URL w/ Vercel Integration

Did you find this page helpful?