Docs: Add note on connection URLs for PgBouncer and Prisma Migrate

[sauntimo]

Describe this PR

• it might just be me being dense but I didn't realise that the datasource url in schema.prisma could be different than the datasources.db.url used when the PrismaClient is instantiated - and in fact, when using PgBouncer and Prisma Migrate, should be different.
• My situation was slightly complicated by Google Cloud Run (see this Stack Overflow Post) but fundamentally I hadn't grasped that I needed to use two different connection URLs in these two places

Changes

• added a note to explain the need to use postgres connection URLs for Prisma Migrate and PgBouncer connection urls for PrismaClient instantiation

What issue does this fix?

• I don't think this is explained elsewhere in the docks and was a pain point I encountered when setting up Prisma and PgBouncer

Any other relevant information

• n/a

@all-contributors please add @sauntimo for doc

[vercel[bot]]

@sauntimo is attempting to deploy a commit to the Prisma Team on Vercel.

A member of the Team first needs to authorize it.

[janpio]

That is one way to do things ,but you can also just have a different DATABASE_URL values depending on if you are using Migrations, or Prisma Client.

[sauntimo]

That is one way to do things ,but you can also just have a different DATABASE_URL values depending on if you are using Migrations, or Prisma Client.

@janpio absolutely, should we mention that approach too? For me, the confusion came from the assumption that both values always had to be the same. Maybe no one else assumed that! I just thought it might help to clarify that the different situations call for different values, however one might choose to manage them.

[janpio]

Which values did you assume would need to be the same?

[sauntimo]

@janpio the connection URL used in schema.prisma and the the connection URL used when instantiating the PrismaClient

[janpio]

Ah. The URL you can provide in PrismaClient is explicitly meant to override the one from the schema file - that is the idea behind that feature.

Now you confusion makes sense to me.

Is there maybe a location where we can make this clearer?

[sauntimo]

@janpio I suppose it does mention that in the PrismaClient docs already, however until I was using pgBouncer, it didn't matter that I had not read that and had no understanding of it, because I only had one database connection URL which was used for both migrations and queries. It only became an issue when I started using pgBouncer, because then I had two different connection URLs. So, I think it makes sense to clarify this in the docs which discuss using pgBouncer with Prisma, which is what I've attempted to do in this PR 😁

[janpio]

Yep, that makes sense. I am not convinced about the exact headline and wording of it, but I now understand why it makes sense to highlight that information on this page specifically. Linking to the PrismaClient docs about being able to overwrite makes a lot of sense already - we do not really give concrete help how these two different URLs even can be handled.

[andrew-walford-prisma]

Thanks @sauntimo for your contribution, and @janpio for your investigation. I'll review your PR and suggest some changes.

[sauntimo]

Thanks @andrew-walford-prisma, very happy to make any changes if you can provide some steer 😁 Excited to be able to contribute something!

[janpio]

Please ping me for review when these changes are applied. Thanks.

[vercel[bot]]

The latest updates on your projects. Learn more about Vercel for Git ↗︎

Name	Status	Preview	Updated
docs	✅ Ready (Inspect)	Visit Preview	Sep 14, 2022 at 6:29PM (UTC)

[sauntimo]

@andrew-walford-prisma thanks for your suggestions, they all made sense to me and I've applied them now (GitHub's new batch commit for suggestions from code review is neat, huh!?). @janpio this should be ready for you to review now. Thank you both for your time and consideration with this 🙏

[sauntimo]

@janpio that's very reasonable criticism and I totally support making this information as inclusive as possible, and applicable to all users and situations. However, I feel a bit out of my depth - I only have experience in the particular situation I have described, so I may not be the best person to resolve those concerns. I think what we've got is better than nothing; I'm happy to make any changes myself if someone can describe what those changes should be, but I am also happy for someone with a broader understanding to take this forward and improve it if that's easier. I'll leave it to your to decide how best to proceed 😄

[janpio]

Yes, this is up to @andrew-walford-prisma to take over now.

[andrew-walford-prisma]

We're researching the various connection possibilities when using a connection pool and Prisma Migrate. I'll come back to this PR when we've decided on the best solution to recommend.

[andrew-walford-prisma]

Prisma's internal discussion on connection strings and connection pooling tools is here: https://www.notion.so/prismaio/How-to-use-separate-connection-strings-for-Prisma-Client-and-Prisma-Migrate-58baf70095594f8fa421104e68524a49

[andrew-walford-prisma]

Note for docs: new directUrl feature in 4.9.0 might help with this issue

[janpio]

Indeed, you can then have the PgBouncer url in url, and the direct one for Migration and Introspection in directUrl. We should replace this PR with a mention of directUrl once it is out and confirmed working.

[janpio]

This is nowobsolete with directUrl: https://www.prisma.io/docs/reference/api-reference/prisma-schema-reference#fields