Connecting to postgres on supabase via pgBouncer from prisma

Question

Problem

• running out of database connections in prod leading to errors like this;

Error: 
Invalid `prisma.queryRaw()` invocation:
  Timed out fetching a new connection from the connection pool. More info: http://pris.ly/d/connection-pool (Current connection pool timeout: 10, connection limit: 5)
    at Object.request (/usr/src/app/node_modules/@prisma/client/runtime/index.js:45629:15)
    at async Proxy._request (/usr/src/app/node_modules/@prisma/client/runtime/index.js:46456:18)

Situation

• multiple API containers running in Google Cloud Run running node/express/prisma API
• using Supabase's hosted postgres. In supabase Settings > Database, Connection Pooling is enabled.
• in db connection string, using :6543/postgres?pgbouncer=true

Attempt to diagnose

• in supabase, Database > Roles, I can see a list of roles and the number of connections for each. pgBouncer has 0 and the role which my application uses has several.

• If I query pg_stat_activity, I can see connections for the usename which is used by my application, and client_addr values representing ip addresses for a couple of different container instances. Are these "forwarded on" from pgBouncer? or have they bypassed pgBouncer entirely?

I am not familiar with what either of these should look like if I were using pgBouncer correctly so it's hard for me to tell what's going on.

I assume this means that I either haven't configured pgBouncer correctly, or I'm not connecting to it properly, or both. I'd be really grateful if someone could point out how I could either check or fix my connection to pgBouncer and clarify what I should see in pg_stat_activity if I was correctly connected to pgBouncer. Thanks.

Answer 1

Figured out what's going wrong here, so writing out how I fixed it in case anyone else runs into this issue.

Better understanding of the problem

in my prisma schema file I'm getting my database url from the env

datasource db {
  provider = "postgresql"
  url      = env("SUPABASE_POSTGRES_URL")
}

and when I'm instantiating the prisma client I'm using the same variable

export const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.SUPABASE_POSTGRES_URL,
    },
  },
});

• I have a build trigger in Google Cloud Build that builds containers when branches that are merged into certain other branches, eg when PRs are merged in to master, build new containers and deploy them to prod.
• In the build trigger, the SUPABASE_POSTGRES_URL value is set in the env, using :5432 which connects directly to postgres, bypassing pgBouncer. This is a requirement for prisma migrations which can't be run through pgBouncer.
• The Google Cloud Run container env vars specify a different value for SUPABASE_POSTGRES_URL however it looks like the this not being used, and instead the direct-to-postgres :5432 value is used while the app is running, to connect to the db and run queries - so pgBouncer was permanently bypassed.

Solution

where the prisma client is instantiated, I'm using a second env var. It turns out that prisma uses the env var referenced in the schema file for the DB URL for migrations and the db url in the client instantiation for queries when the app is running, and you can happily have two completely separate values for these two URLs.

export const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.SUPABASE_PGBOUNCER_URL,
    },
  },
});

Now, SUPABASE_POSTGRES_URL is still populated from the build trigger, but it doesn't get used at runtime; instead I set SUPABASE_PGBOUNCER_URL in the Google Cloud Run env vars and that gets used during the prisma client instantiation, so queries a run through pgBouncer.

Result

• Effective Prisma migrations direct to postgres
• Effective connection pooling by running queries through pgBouncer