Prisma-client-js: Prisma Client failing with PgBouncer Transaction Mode

[flybayer]

Bug description

I was having this error with a Next.js build while deploying to Zeit. It uses getStaticProps, but I doubt that is relevant here.

I'm using a brand new Digital Ocean Postgres instance with their connection pool.

17:53:37.265  PrismaClientUnknownRequestError: 
17:53:37.265  Invalid `prisma.product.findOne()` invocation:
17:53:37.265  Error occurred during query execution:
17:53:37.265  ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("42P05"), message: "prepared statement \"s0\" already exists", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("prepare.c"), line: Some(473), routine: Some("StorePreparedStatement") }) }) })

How to reproduce

Try to use Prisma client with with Digital Ocean Postgres and pgbouncer in transaction mode. It was failing every time for me.

Prisma migrate worked correctly.

Expected behavior

It should work

Prisma information

    "@prisma/cli": "2.0.0-beta.2",
    "@prisma/client": "2.0.0-beta.2",

[divyenduz]

Related issue with all the context of our pgBouncer exploration https://github.com/prisma/prisma-client-js/issues/503

[janpio]

Can you follow the content of https://github.com/prisma/prisma-client-js/issues/503 @flybayer and see if this solves your problem? (Note that this API is not officially released and might change before that happens.)

[janpio]

We implemented a new PgBouncer mode in https://github.com/prisma/prisma/issues/2520 that is activated by adding ?pgbouncer=true (or &pgbouncer=true of course) to your connection string. Available in the current alpha, and the next normal beta release 🚀

We have some e2e tests that confirm this is working on DO's Connection Pooling feature (https://github.com/prisma/e2e-tests/tree/master/databases) and also got reports from users that are using it successfully.

If this does not work for you, please let us know via a comment to this issue 👍

[aarshaw]

I'm receiving a very similar error using the pgbouncer=true query param in the connection / Digital Ocean - is this still the way to do it?

'Error occurred during query execution:\n' +
    'ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("26000"), message: "prepared statement \\"s19\\" does not exist", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("prepare.c"), line: Some(512), routine: Some("FetchPreparedStatement") }) }) })'

[janpio]

It is. Can you please open a new issue and provide some more information? I really want to look into that. (The more information on reproduction you can give the better.)

[janpio]

For the record: Transaction mode should work just fine. Just add &pgbouncer=true to your connection string to trigger that mode in Prisma itself.

[cball]

@janpio is there a recommended way to disable pgbouncer mode for migrations short of constructing a completely separate DATABASE_URL for it?

We have a script that runs migrations on the main branch and it currently fails when using the pgbouncer flag is set

[janpio]

Not that I am aware of. Best open a new issue about it though, then we can also consider this a feature request.