supavisor, pgbouncer and supabase

[ijkml]

Describe the Bug

Supabase deprecated pgbouncer in favor of supavisor, unfortunately simply changing the connection string isn't enough. This also affects new projects, see https://github.com/umami-software/umami/discussions/2462#discussion-6034248.

I'm also getting emails from Supabase that my [umami] project "will be unable to connect to supabase". Is there a workaround for this?

Database

PostgreSQL

Relevant log output

No response

Which browser are you using? (if relevant)

No response

How are you deploying your application? (if relevant)

Vercel + Supabase, Netlify + Supabase

[lawlow]

I'm also using Netlify + Supabase. I updated my connection string to use the "session" version instead of "transaction", and seems to work now.

[ijkml]

Thanks, @lawlow, that works!

(Leaving this open for now to see if there are other/official opinions on the matter, I could shoot a PR to update the docs :smile:)

[jawngee]

Switching to session works but has intermittent issues. We had to switch to direct DB connection (uncheck connection pooling in supabase dashboard).

[ijkml]

Hi, @jawngee, what issues did you come across?

[jawngee]

If I use the supavisor URL, I can't log into umami (the login button shows the spinner forever).

If I use the direct non-supavisor URL I can.

Weirdly, nothing shows in the vercel logs.

I haven't really investigated further because it's a production system.

I'm also certain this is a supabase issue but it might also be a prisma + supavisor thing.

On Jan 30, 2024, at 4:44 AM, ML @.***> wrote:

Hi, @jawngee https://github.com/jawngee, what issues did you come across?

— Reply to this email directly, view it on GitHub https://github.com/umami-software/umami/issues/2485#issuecomment-1915625655, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAK3IORNOF7UI25AWYIAB3YRAJ35AVCNFSM6AAAAABCMPUDI6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMJVGYZDKNRVGU. You are receiving this because you were mentioned.

[robcsaszar]

I'm also using Netlify + Supabase. I updated my connection string to use the "session" version instead of "transaction", and seems to work now.

I'm on Vercel, and it works for me with the new connection URL as well. Connection pooling is on, and the mode is set to Mode: Session.

[azat-io]

The same problem https://github.com/orgs/supabase/discussions/17817

[azat-io]

@lawlow Have you done anything else? I have updated the DATABASE_URL in Netlify. And now I get "500: Internal Server Error".

[chukwumaokere]

When I tried switching to use the non connection pooling method, it wouldnt even build I get an error with check_db...

If I use the supavisor URL, I can't log into umami (the login button shows the spinner forever). If I use the direct non-supavisor URL I can. Weirdly, nothing shows in the vercel logs. I haven't really investigated further because it's a production system. I'm also certain this is a supabase issue but it might also be a prisma + supavisor thing. … On Jan 30, 2024, at 4:44 AM, ML @.***> wrote: Hi, @jawngee https://github.com/jawngee, what issues did you come across? — Reply to this email directly, view it on GitHub <#2485 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAK3IORNOF7UI25AWYIAB3YRAJ35AVCNFSM6AAAAABCMPUDI6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMJVGYZDKNRVGU. You are receiving this because you were mentioned.

[nailuoGG]

We need to modify the database schema to Session and change the port to 5432. The final format should be as follows:

postgresql://[db-user]:[db-password]@db.[project-ref].supabase.co:5432/[db-name]

Please note that the port should be 5432. I was stuck for a long time because I missed this detail.

[azat-io]

Fix with "Mode: Session" not works for me 😢

[jawngee]

The new connection pooling works for me too for like 80% of the time and then the logs get spammed with Can't reach database server at aws-0-us-east-1.pooler.supabase.com:5432. Please make sure your database server is running at aws-0-us-east-1.pooler.supabase.com. And then it starts working again for a little bit.

I'm pretty sure the issue is specifically supabase and not umami, but prisma is a pos so maybe that's at play too. I know that our apps that don't use prisma don't have a problem but I also think if it was a prisma issue it wouldn't be a it works most of time, fails the rest kind of thing.

So that leaves me thinking supabase has a spotty load balanced instance or supavisor isn't ready for prime time.

At this point I'd gladly pay the $4 to get ipv4 back if it came with pgbouncer.

[lawlow]

@lawlow Have you done anything else? I have updated the DATABASE_URL in Netlify. And now I get "500: Internal Server Error".

I had to update my node version, as I was way behind on updates. Other than that, nothing else.

I did get some random errors after, but the next day it seemed to work fine again.

[jawngee]

This is what's working for me right now:

In umami, edit db/postgresql/migrations/schema.prisma and change this:

datasource db {
  provider     = "postgresql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

to this:

datasource db {
  provider     = "postgresql"
  url          = env("DATABASE_URL")
  directUrl    = env("DIRECT_DATABASE_URL")
  relationMode = "prisma"
}

And then for environment variables, I'm using the supabase transaction URL with &pgbouncer=true appended to it:

DATABASE_URL=postgres://postgres.YOURID:YOURPASSWORD@aws-0-us-east-1.pooler.supabase.com:6543/postgres?pgbouncer=true

And the direct URL:

DIRECT_DATABASE_URL=postgresql://postgres:YOURPASSWORD@db.YOURID.supabase.co:5432/postgres

Everything has been kosher for the last 24 hours on vercel.

[azat-io]

@mikecao Perhaps we should update the documentation on using Umami with Supabase?

[jpvalery]

Found this after solving the problem, just adding a comment with what I witnessed in the hopes it could help someone searching.

• I got an email from Supabase saying the project would be paused
• I tried logging in my umami instance (hosted on vercel) but it kept spinning
• I checked my browser network tab and saw that /login was returning a 405
• Tried redeploying on Vercel—got errors that ERROR: "check-db" exited with 1. error Command failed with exit code 1.
• Tried readding my supabase url as described on https://umami.is/docs/running-on-supabase
• Finally found this thread and used the new connection pooling URL and everything started working (deploys and login)

I've created a PR to update the docs with that information—reading the comments it seems to work for the majority of supabase users but obviously anyone should feel free to edit my PR 👍

Update: might have spoken too soon, I started getting errors again :|

Looking at the Supabase discussion thread:

What do I do if I am using Prisma?

If you are using Prisma, please check out our updated [Prisma Guide](https://supabase.com/partners/integrations/prisma) for instructions on how to configure your connections for both querying and migrations.
How do I update my Vercel Supabase integration?

The environment variables POSTGRES_URL and POSTGRES_PRISMA_URL point to Supavisor and POSTGRES_URL_NON_POOLING points to Supavisor in session mode. [Redeploy your Vercel application](https://vercel.com/docs/deployments/managing-deployments#redeploy-a-project) to pick up the latest environment variables. This is required since Vercel does not support IPv6.

From the Prisma doc, Transaction should be used.

[azat-io]

Not works for me.

"500 Internal Server Error" on api/auth/login Netlify + Supabase

[trentpeterson]

Here's how I got everything working again with Netlify + Supabase.

In my fork of Umami, I added directUrl as per this comment by @jawngee.

Then I used Supabase's Prisma guide. As the article recommends, I set DATABASE_URL to the transaction connection string:

postgres://[db-user].[project-ref]:[db-password]@aws-0-[aws-region].pooler.supabase.com:6543/[db-name]?pgbouncer=true&connection_limit=1

and DIRECT_DATABASE_URL to the session connection string:

postgres://[db-user].[project-ref]:[db-password]@aws-0-[aws-region].pooler.supabase.com:5432/[db-name]

Finally, and this is Netlify-specific, I had to update @netlify/plugin-nextjs to the latest (4.41.3) in package.json as per this Netlify support question.

[tairosonloa]

👆 What @trentpeterson suggests worked for me too, on Vercel cloud.

Initially, I was following @jawngee comment but I was always getting the following error:

Error: P1001: Can't reach database server at `db.[my-supabase-project-id].supabase.co`:`5432`
Please make sure your database server is running at `db.[my-supabase-project-id].supabase.co`:`5432`.

Then, I realized that @trentpeterson suggest a different value for DIRECT_DATABASE_URL

I changed from @jawngee suggestion:

DIRECT_DATABASE_URL=postgresql://postgres:[db-password]@db.[my-supabase-project-id].supabase.co:5432/postgres

to @trentpeterson one:

DIRECT_DATABASE_URL=postgres://postgres.[my-supabase-project-id]:[db-password]@aws-0-[aws-region].pooler.supabase.com:5432/postgres

And now everything is working

[jpvalery]

@tairosonloa did you make any other changes or was it just adding an additional env variable?

[tairosonloa]

@tairosonloa did you make any other changes or was it just adding an additional env variable?

I modified the file db/postgresql/schema.prisma to add directUrl = env("DIRECT_DATABASE_URL") and then just set the correct values to both, DATABASE_URL and DIRECT_DATABASE_URL as @trentpeterson suggests

Here is the commit in the umami repo I have connected with Vercel: https://github.com/tairosonloa/analytics.aalonso.dev/commit/12aea22e3b825cb88c7b79d0cc2e9a86d394d518

Note that if you make the changes and you did not updated the env vars yet, the build will fail. That happened to me, as committing to my repo triggered a deployment attempt in Vercel. Once I set both env vars on Vercel, I redeployed from Vercel dashboard and everything worked.

[lucasfcunha]

@tairosonloa followed your fix and things are not working. Locally I can log in to my Umami dashboard without any issues, however, when I try login in using the version deployed by Netlify I'm getting "500 Internal Error" on the login page.

This is a new setup and I haven't used Umami before today.

@azat-io our problems seem similar, have you found any other possible fixes for this?

[azat-io]

@lucasfcunha Nope 😢

[tairosonloa]

@tairosonloa followed your fix and things are not working. [...] when I try login in using the version deployed by Netlify I'm getting "500 Internal Error" on the login page.

I'm sorry to heard that. I'm not using Netlify, so I'm not sure how to help, but did you tried what @trentpeterson said about Netlify? You might need to bump a dependency on umami:

Finally, and this is Netlify-specific, I had to update @netlify/plugin-nextjs to the latest (4.41.3) in package.json as per this Netlify support question.

And as a last resource, it may not be ideal depending on your situation, but you can always move umami deploy from Netlify to Vercel or elsewhere. I mean, the database is in supabase, and you can always point the DNS to a new hosting. You'll keep your data, and you won't need to update any service, tracking script or browser bookmark if you keep using the same domain name, even if the hosting changes. The only work here is to configure the project in Vercel and perhaps your CI.

[oneminch]

I was experiencing this issue using the Vercel + Supabase combination, and it was resolved just by changing the port from 6543 to 5432 in my DATABASE_URL environment variable. It's a tiny change that could be missed.

[jpvalery]

I can confirm that @tairosonloa fix did the trick for me:

I modified the file db/postgresql/schema.prisma to add directUrl = env("DIRECT_DATABASE_URL") and then just set the correct values to both, DATABASE_URL and DIRECT_DATABASE_URL as @trentpeterson suggests

Here is the commit in the umami repo I have connected with Vercel: https://github.com/tairosonloa/analytics.aalonso.dev/commit/12aea22e3b825cb88c7b79d0cc2e9a86d394d518

Note that if you make the changes and you did not updated the env vars yet, the build will fail. That happened to me, as committing to my repo triggered a deployment attempt in Vercel. Once I set both env vars on Vercel, I redeployed from Vercel dashboard and everything worked.