CODEWITHSUNDEEP
postgresqlgoogle-cloud-runprismasupabasepgbouncer
sauntimo
sauntimo

Reputation: 1591

Connecting to postgres on supabase via pgBouncer from prisma

Problem

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

Attempt to diagnose

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.

Upvotes: 4

Views: 3035

Answers (1)

sauntimo
sauntimo

Reputation: 1591

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

Upvotes: 2

Related Questions