Supabase Postgres Database Integration

[jacob-roling]

Hi, I am brand new when it comes to SQL databases as I'm coming from a heavy Firebase background. I assumed all I would have to do to set up Supabase as the Postgres DB would simply be changing the database type and replacing the connection string, e.g.:

// .env
DATABASE_TYPE=postgres
DATABASE_URL="postgresql://postgres:[password]@db.[cloudname].supabase.co:[port]/postgres"

However, when running either seed or medusa migrations run, I get the following error:

QueryFailedError: must be owner of event trigger pgsodium_trg_mask_update

I believe it has something to do with medusa not having access to certain records in the database, namely pgsodium, although I assumed by providing a connection string with the db password that medusa would have admin access. Can someone help me figure out where I am going wrong?

[pevey]

By default in Postgres installed locally, the postgres user is admin. But I think that is not the case for supabase. But you can do:

GRANT supabase_admin TO postgres;

That is a quick fix. For other options that give fewer permissions, check here:

https://supabase.com/docs/guides/cli/managing-environments#permission-denied-on-db-remote-commit

[LucasMatuszewski]

It looks like a connection to Supabase with direct DB access via IPv4 and pgBouncer doesn't work anymore (since January 26th 2024):
https://github.com/orgs/supabase/discussions/17817

I tried to install Medusa following this guide:
https://docs.medusajs.com/create-medusa-app#step-1-run-create-medusa-app

But the command from Medusa example doesn't work anymore:
npx create-medusa-app@latest --db-url postgresql://postgres:<password>@<host>.supabase.co:5432/postgres

The new URI format is:
postgres://postgres.<host>:<password>@aws-0-eu-central-1.pooler.supabase.com:5432/postgres

But it didn't work when I tried it like this:
npx create-medusa-app@latest --db-url postgres://postgres.<host>:<password>@aws-0-eu-central-1.pooler.supabase.com:5432/postgres

I tried some other formats but neither works. I've tested the connection with other DB tools and all works.

Any ideas?

I've created an issue: #6535

[Tdnshah]

Facing the same issue I get an error as below Couldn't connect to PostgreSQL using the database URL you passed. Make sure it's correct and try again.

[maidabarrientos]

Hello try to add your ip to network restrictions - then use the nodejs database ipv4 uri -> npx create-medusa-app@latest --db-url postgres://[username]:[password]@[hostname]:5432/postgres

[maidabarrientos]

It should work :)

[Hermitauge]

if you have weird characters in your password youve got to convert it to something, i forgot what its called, but for example I had ! in my password and instead of ! I had to write %21

[harish-github35]

@Hermitauge url encoding

[anajembaedwin]

I have found a solution to this problem.

It is URL Encoding.

A connection to Supabase with direct DB access via IPv4 and pgBouncer doesn't work anymore (since January 26th 2024).

The new URI format is:

postgres://postgres.<host>:<password>@aws-0-eu-central-1.pooler.supabase.com:5432/postgres

To spin up a Medusa backend use:

npx create-medusa-app@latest --db-url postgres://postgres.<host>:<password>@aws-0-eu-central-1.pooler.supabase.com:5432/postgres

NB: If your password has special characters, use an online url encoder to encode it.

Everything works fine from there.

[Tdnshah]

I think the db url should be like below
npx create-medusa-app@latest --db-url postgres://postgres.<user>:<password>@aws-0-eu-central-1.pooler.supabase.com:5432/postgres

rgt?