prisma: prepared statement \"s0\" already exists when connecting to pgbouncer with ssl

Bug description

I’m connecting to postgres on digital ocean with pgbouncer as guided here: https://www.prisma.io/docs/concepts/database-connectors/postgresql#configuring-an-ssl-connection. And also appended pgbouncer=true&statement_cache_size=0 as suggested by @ryands17

ConnectorError: prepared statement \"s0\" already exists when connecting to pgbouncer with ssl is thrown for every query.

Full error:

PrismaClientUnknownRequestError3 [PrismaClientUnknownRequestError]:
Invalid `prisma.siteMember.findMany()` invocation:


  Error occurred during query execution:
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(463), routine: Some("StorePreparedStatement") }) }) })
    at PrismaClientFetcher.request (/XXXXXXXXXXX/node_modules/@prisma/client/runtime/index.js:78125:15)
    at runMicrotasks (<anonymous>)
    at processTicksAndRejections (internal/process/task_queues.js:97:5) {
  clientVersion: '2.13.0'
}

How to reproduce

  1. Setup a managed database on digital ocean.
  2. Create pgbouncer pool.
  3. Connect with SSL config the above.
  4. Run any query

Expected behavior

Prisma queries should be executed normally when connected to a pgbouncer pool’d db.

Prisma information

Connection string: postgres://<XXXXXXXXXXXX>/default?sslmode=require&&sslcert=db-ssl.crt&sslpassword=<XXXXX>&sslidentity=db-ssl.p12&pgbouncer=true&statement_cache_size=0

Environment & setup

  • OS: Mac OS 10.15.4
  • Database: PostgreSQL 12
  • Node.js version: 14.4.0
  • Prisma version: 2.13.0
@prisma/cli          : 2.13.0
@prisma/client       : 2.13.0
Current platform     : darwin
Query Engine         : query-engine 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 (at node_modules/@prisma/engines/prisma-fmt-darwin)
Studio               : 0.329.0

Complete Debug Log

  tryLoadEnv Environment variables not found at null +0ms
  tryLoadEnv Environment variables not found at undefined +0ms
  tryLoadEnv No Environment variables loaded +1ms
  tryLoadEnv Environment variables not found at null +10ms
  tryLoadEnv Environment variables not found at undefined +0ms
  tryLoadEnv No Environment variables loaded +0ms
  prisma-client { clientVersion: '2.13.0' } +0ms
  prisma-client Prisma Client call: +77ms
  prisma-client prisma.siteMember.findMany({
  prisma-client   where: {
  prisma-client     userId: 1
  prisma-client   },
  prisma-client   include: {
  prisma-client     site: {
  prisma-client       include: {
  prisma-client         posts: {
  prisma-client           select: {
  prisma-client             title: true
  prisma-client           }
  prisma-client         }
  prisma-client       }
  prisma-client     }
  prisma-client   },
  prisma-client   orderBy: {
  prisma-client     createdAt: 'desc'
  prisma-client   }
  prisma-client }) +2ms
  prisma-client Generated request: +0ms
  prisma-client query {
  prisma-client   findManySiteMember(
  prisma-client     where: {
  prisma-client       userId: 1
  prisma-client     }
  prisma-client     orderBy: [
  prisma-client       {
  prisma-client         createdAt: desc
  prisma-client       }
  prisma-client     ]
  prisma-client   ) {
  prisma-client     id
  prisma-client     siteId
  prisma-client     userId
  prisma-client     role
  prisma-client     createdAt
  prisma-client     updatedAt
  prisma-client     addedBy
  prisma-client     site {
  prisma-client       id
  prisma-client       name
  prisma-client       createdAt
  prisma-client       updatedAt
  prisma-client       posts {
  prisma-client         title
  prisma-client       }
  prisma-client     }
  prisma-client   }
  prisma-client }
  prisma-client  +0ms
  engine {
  engine   cwd: '/Users/XXXXXXX/prisma'
  engine } +0ms
  engine Search for Query Engine in /Users/XXXXXXX/node_modules/.prisma/client +1ms
  plusX Execution permissions of /Users/XXXXXXX/node_modules/.prisma/client/query-engine-darwin are fine +0ms
  engine {
  engine   flags: [
  engine     '--enable-raw-queries',
  engine     '--unix-path',
  engine     '/tmp/prisma-ccda131f44c9c5e83960f619.sock'
  engine   ]
  engine } +1ms
  engine stdout {
  timestamp: 'Dec 21 16:17:46.726',
  level: 'INFO',
  fields: { message: 'Starting a postgresql pool with 1 connections.' },
  target: 'quaint::pooled'
} +48ms
  engine stdout {
  timestamp: 'Dec 21 16:17:48.659',
  level: 'INFO',
  fields: {
    message: 'Started http server on http+unix:///private/tmp/prisma-ccda131f44c9c5e83960f619.sock'
  },
  target: 'query_engine::server'
} +2s
  engine Search for Query Engine in Users/XXXXXXX/node_modules/.prisma/client +5ms
  plusX Execution permissions of /Users/XXXXXXX/node_modules/.prisma/client/query-engine-darwin are fine +2s
  engine Client Version 2.13.0 +25ms
  engine Engine Version query-engine 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 +0ms
  engine {
  engine   error: PrismaClientUnknownRequestError3 [PrismaClientUnknownRequestError]: Error occurred during query execution:
  engine   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(463), routine: Some("StorePreparedStatement") }) }) })
  engine       at NodeEngine.graphQLToJSError (/Users/XXXXXXX/node_modules/@prisma/client/runtime/index.js:27363:14)
  engine       at /Users/XXXXXXX/node_modules/@prisma/client/runtime/index.js:27285:24
  engine       at /Users/XXXXXXX/node_modules/@risingstack/react-easy-state/dist/cjs.es6.js:243:63
  engine       at Object.batchedUpdates$1 (/Users/XXXXXXX/node_modules/react-dom/cjs/react-dom.development.js:21856:12)
  engine       at batch (/Users/XXXXXXX/node_modules/@risingstack/react-easy-state/dist/cjs.es6.js:243:30)
  engine       at Object.apply (/Users/XXXXXXX/node_modules/@risingstack/react-easy-state/dist/cjs.es6.js:264:16)
  engine       at runMicrotasks (<anonymous>)
  engine       at processTicksAndRejections (internal/process/task_queues.js:97:5) {
  engine     clientVersion: '2.13.0'
  engine   }
  engine } +280ms
  prisma-client Error: Error occurred during query execution:
  prisma-client 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(463), routine: Some("StorePreparedStatement") }) }) })
  prisma-client     at NodeEngine.graphQLToJSError (/Users/XXXXXXXnode_modules/@prisma/client/runtime/index.js:27363:14)
  prisma-client     at /Users/XXXXXXX/node_modules/@prisma/client/runtime/index.js:27285:24
  prisma-client     at /Users/Users/XXXXXXX/node_modules/@risingstack/react-easy-state/dist/cjs.es6.js:243:63
  prisma-client     at Object.batchedUpdates$1 (/Users/XXXXXXX/node_modules/react-dom/cjs/react-dom.development.js:21856:12)
  prisma-client     at batch (/Users/XXXXXXX/node_modules/@risingstack/react-easy-state/dist/cjs.es6.js:243:30)
  prisma-client     at Object.apply (/Users/XXXXXXX/node_modules/@risingstack/react-easy-state/dist/cjs.es6.js:264:16)
  prisma-client     at runMicrotasks (<anonymous>)
  prisma-client     at processTicksAndRejections (internal/process/task_queues.js:97:5) +2s
PrismaClientUnknownRequestError3 [PrismaClientUnknownRequestError]:
Invalid `prisma.siteMember.findMany()` invocation:


  Error occurred during query execution:
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(463), routine: Some("StorePreparedStatement") }) }) })
    at PrismaClientFetcher.request (/Users/XXXXXXX/node_modules/@prisma/client/runtime/index.js:78125:15)
    at runMicrotasks (<anonymous>)
    at processTicksAndRejections (internal/process/task_queues.js:97:5) {
  clientVersion: '2.13.0'
}

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Reactions: 3
  • Comments: 32 (11 by maintainers)

Most upvoted comments

It’s working for me. I use prisma/client 2.12.1 and the following DATABASE_URL:

postgresql://username:password@db_host:5432/dbname?schema=public&pgbouncer=true&sslmode=require

(note the pgbouncer and sslmode options)

+34
AlessandroA on Dec 30, 2020

I was able to connect to pool with SSL. The change I did was to remove sslidentity and sslpassword from connection string. Was able connect to DO cluster using .crt file.

DATABASE_URL=postgres://XXXXXXXXXXXXXX?connection_limit=3&sslmode=require&&sslcert=ssl.crt&pgbouncer=true&connect_timeout=10&pool_timeout=30

+6
s-kris on Apr 2, 2021

None of both - this just needs to be documented better on our side and possibly be caught as an error message. If you want to use pgBouncer, you need to supply that parameter in Prisma. No way around it right now.

+6
janpio on Jan 25, 2021

@lassegit You can probably skip the redirection to a js file, and just do "migrate": "DATABASE_URL=\"$DATABASE_URL_MIGRATE\" yarn prisma migrate deploy", instead (The yarn in the script command might even be not necessary - not sure).

+2
janpio on Aug 31, 2022

OK awesome. Just added that extra flag and it works for me too.

+2
viperfx on Dec 30, 2020

That sounds better, overriding prod values didn’t sit right. I’ll change my approach, thanks for the heads up.

+1
frontsideair on Feb 28, 2023

We have directUrl that overrides the url for the CLI commands: https://www.prisma.io/docs/data-platform/data-proxy/prisma-cli-with-data-proxy (This also applies for Pgbouncer, not only Data Proxy - we will have to update that docs page)

+1
janpio on Feb 28, 2023

@lassegit You can probably skip the redirection to a js file, and just do "migrate": "DATABASE_URL=\"$DATABASE_URL_MIGRATE\" yarn prisma migrate deploy", instead (The yarn in the script command might even be not necessary - not sure).

Specifically for me with NextJS/vercel & a managed digitalocean postgres database, in the package.json:

"vercel-build": "prisma generate && DATABASE_URL=\"$DATABASE_URL_MIGRATE\" prisma migrate deploy && next build",

Where DATABASE_URL_MIGRATE is in the form: postgresql://user:pass@host:25060/db?sslmode=require&prepareThreshold=0&connection_timeout=60&connection_limit=1

+1
leerobert on Sep 16, 2022

Having the same issue on Digitalocean using connection pool with NextJS and Postgres. To fix this I did the following:

  • added DATABASE_URL_MIGRATE environment variable with a regular database connection
  • added DATABASE_URL environment variable with database connection pool

Created a migrate.js script in the root:

#!/usr/bin/env node

'use strict';

const { execSync } = require('child_process');
const { DATABASE_URL_MIGRATE } = process.env;

try {
  // Run the migrate with database connection not using pooling
  // See: https://github.com/prisma/prisma/issues/4752
  execSync(`DATABASE_URL=${DATABASE_URL_MIGRATE} yarn prisma migrate deploy`);
} catch (error) {
  console.error(error);
  process.exit(1);
}

Invoke the script before every deployment from the package.json:

"scripts": {
  "start": "yarn migrate && next start",
  "migrate": "node ./migrate.js",
  }

This should solve it.

On Windows I had to use:

require('dotenv').config();
const { execSync } = require('child_process');
const { DATABASE_URL_MIGRATE } = process.env;

try {
  // Run the migrate with database connection not using pooling
  // See: https://github.com/prisma/prisma/issues/4752
  execSync(`cross-env DATABASE_URL=${DATABASE_URL_MIGRATE} npx prisma migrate deployl`, { stdio: 'inherit' });
} catch (error) {
  console.error(error);
  process.exit(1);
}

🤷‍♂️

+1
kno-raziel on Sep 4, 2022

Having the same issue on Digitalocean using connection pool with NextJS and Postgres. To fix this I did the following:

  • added DATABASE_URL_MIGRATE environment variable with a regular database connection
  • added DATABASE_URL environment variable with database connection pool

Created a migrate.js script in the root:

#!/usr/bin/env node

'use strict';

const { execSync } = require('child_process');
const { DATABASE_URL_MIGRATE } = process.env;

try {
  // Run the migrate with database connection not using pooling
  // See: https://github.com/prisma/prisma/issues/4752
  execSync(`DATABASE_URL=${DATABASE_URL_MIGRATE} yarn prisma migrate deploy`);
} catch (error) {
  console.error(error);
  process.exit(1);
}

Invoke the script before every deployment from the package.json:

"scripts": {
  "start": "yarn migrate && next start",
  "migrate": "node ./migrate.js",
  }

This should solve it.

+1
lassegit on Aug 31, 2022

I have had this exact same issue. Adding &pgbouncer=true to the end of the connection string fixed the issue.

Is this something that should be fixed on the DO side, or on the Prisma side?

+1
OriginalEXE on Jan 25, 2021

Yes, sample uri postgresql://user:pass@host:5432/mydb?schema=public&pgbouncer=true

+1
pantharshit00 on Jan 22, 2021
Read more comments on GitHub
← prisma: prepared statement \"s0\" already exists
prisma: Prisma 2 not working with Nestjs →