Configure Prisma Client with PgBouncer

PgBouncer holds a connection pool to the database and proxies incoming client connections by sitting between Prisma Client and the database. This reduces the number of processes a database has to handle at any given time. PgBouncer passes on a limited number of connections to the database and queues additional connections for delivery when space becomes available.

Add pgbouncer to the connection URL

To use Prisma Client with PgBouncer from a serverless function, add the ?pgbouncer=true flag to the PostgreSQL connection URL:

postgresql://USER:PASSWORD@HOST:PORT/DATABASE?pgbouncer=true

Note: PORT specified for PgBouncer pooling sometimes different from the default 5432 port. Check database provider docs for the exact port number, otherwise adding ?pgbouncer=true won't work.

Set PgBouncer to transaction mode

Additionally, for Prisma Client to work reliably, PgBouncer must run in Transaction mode.

Transaction mode offers a connection for every transaction – a requirement for the Prisma query engine to work with PgBouncer.

Prisma Migrate and PgBouncer workaround

Prisma Migrate uses database transactions to check out the current state of the database and the migrations table. However, the Schema Engine is designed to use a single connection to the database, and does not support connection pooling with PgBouncer. If you attempt to run Prisma Migrate commands in any environment that uses PgBouncer for connection pooling, you might see the following error:

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

To work around this issue, you must connect directly to the database rather than going through PgBouncer. To achieve this, you can use the directUrl field in your datasource block.

For example, consider the following datasource block:

datasource db {
  provider  = "postgresql"
  url       = "postgres://USER:PASSWORD@HOST:PORT/DATABASE?pgbouncer=true"
  directUrl = "postgres://USER:PASSWORD@HOST:PORT/DATABASE"
}

The block above uses a PgBouncer connection string as the primary URL using url, allowing Prisma Client to take advantage of the PgBouncer connection pooler.

It also provides a connection string directly to the database, without PgBouncer, using the directUrl field. This connection string will be used when commands that require a single connection to the database, such as prisma migrate dev or prisma db push, are invoked.

PgBouncer with different database providers

There are sometimes minor differences in how to connect directly to a Postgres database that depend on the provider hosting the database.

Below are links to information on how to set up these connections with providers who have setup steps not covered here in our documentation:

• Connecting directly to a PostgreSQL database hosted on Digital Ocean
• Connecting directly to a PostgreSQL database hosted on ScaleGrid