Topics

Supabase•3mo ago

Self-hosted connection to db?

I'm struggling with how to connect to my DB after setting up the self-hosted version. Anyone been through the same, or can guide one in the right direction on this? Fairly lost currently 🙃

129 Replies

inder•3mo ago

Are you using docker-compose or supabase cli to run self hosted instance? Also, when you say you want to connect to db do you mean via some client like pgadmin/dbeaver or from your app?

Tobias MadsenOP•3mo ago

docker-compose setup yes, and correct, running the connection from a next.js project, with prisma

Milou•3mo ago

Sorry for hijacking this, but does supabase-cli even support connecting to the self hosted instance? it suggests me to link a remote supabase project to run migrations

silentworks•3mo ago

You just use the --db-url flag when running most database related commands from the CLI. It's in the CLI docs.

inder•3mo ago

or use --local flag. cli will automatically use the local db. But this is only for containers started with cli

Tobias MadsenOP•3mo ago

From the guide: https://supabase.com/docs/guides/self-hosting/docker This connections tring is referenced:

postgres://postgres:[POSTGRES_PASSWORD]@[your-server-ip]:5432/[POSTGRES_DB]

postgres://postgres:[POSTGRES_PASSWORD]@[your-server-ip]:5432/[POSTGRES_DB]

However this format doesn't seem to work for me

Self-Hosting with Docker | Supabase Docs

Learn how to configure and deploy Supabase with Docker.

inder•3mo ago

Can you share the error you get

Tobias MadsenOP•3mo ago

>Error: P1001: Can't reach database server at and then my ip address, where the self-hosted is running

inder•3mo ago

Try this url. As it is. just add your own password and database. postgres://postgres.your-tenant-id:[POSTGRES_PASSWORD]@localhost:5432/[POSTGRES_DB] database by default is postgres

Tobias MadsenOP•3mo ago

Same, > Error: P1001: Can't reach database server at localhost:5432

inder•3mo ago

have you verified if all the containers are healthy?

Tobias MadsenOP•3mo ago

But this shouldn't work either as I understand. As I'm trying to connect outside from outside the server itself? Yes

Tobias MadsenOP•3mo ago

No description

inder•3mo ago

If you're using the default docker-compose.yml in supabase repo, supavisor is exposed on port 5432 You can run this cmd and verify supabase-pooler service's port

docker ps --format='table {{.Names}}\t {{.Ports}}'

docker ps --format='table {{.Names}}\t {{.Ports}}'

Tobias MadsenOP•3mo ago

Looks correct

inder•3mo ago

ya, so port 5432 is published on host so localhost:5432 should work

Tobias MadsenOP•3mo ago

Also when running on an external server, and running the next.js project locally?

inder•3mo ago

you mean running supabase self hosted on external server?

Tobias MadsenOP•3mo ago

Yes

inder•3mo ago

the hostname and port will have to be updated. that totally depends on you how you deploy the instance. Whether you want to make the database accessible on the internet or not

Tobias MadsenOP•3mo ago

Is has to be accessible, as it's a public forum format the DB controls Where to change the hostname and port, and to what in this case? 😅 hostname = server ip?

inder•3mo ago

correct, in the url localhost is hostname. you can use ip or a domain you've setup

Tobias MadsenOP•3mo ago

what about port?

inder•3mo ago

you'll have to open 5432 in your server firewall Are you going to deploy nextjs app and supabase on same server?

Tobias MadsenOP•3mo ago

6543 better for external? No

inder•3mo ago

It doesn't matter Whatever port you use you'll have to update it in docker-compose and your firewall

Tobias MadsenOP•3mo ago

Got it Thanks for the help, I'll give it a try 😊

inder•3mo ago

localhost still not working?

Tobias MadsenOP•3mo ago

nope

inder•3mo ago

let me run it locally. will get back to you

inder•3mo ago

Works fine for me

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

generator client {
  provider = "prisma-client-js"
}

model Task {
  id          Int      @id @default(autoincrement())
  title       String
  description String?
  isCompleted Boolean  @default(false)
  createdAt   DateTime @default(now())
}

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

generator client {
  provider = "prisma-client-js"
}

model Task {
  id          Int      @id @default(autoincrement())
  title       String
  description String?
  isCompleted Boolean  @default(false)
  createdAt   DateTime @default(now())
}

No description

No description

Tobias MadsenOP•3mo ago

You can actually connect to the db aswell? and push/pull data?

inder•3mo ago

No description

Tobias MadsenOP•3mo ago

hmmm

inder•3mo ago

I don't use prisma. The schema file is just an example I used off the internet. As you were getting connection errors, that is my main concern What is your use case? Are you going to run any migrations with prisma? In prisma docs, it is mentioned that if you want to run migrations, only then prisma needs a direct connection to db. Otherwise you should use connection pooler https://www.prisma.io/docs/orm/overview/databases/supabase#specific-considerations

Tobias MadsenOP•3mo ago

But the connection pooler would require the same setup But yes, migrations are needed

inder•3mo ago

Got the same error, but retried and it succeeded. Also, added a direct url as mentioned in docs

No description

No description

No description

inder•3mo ago

I'm using this example: https://github.com/prisma/nextjs-prisma-postgres-demo

Tobias MadsenOP•3mo ago

hmm, just testing with my server currently But cool to see you got it working, has to be some server settings then

inder•3mo ago

supavisor service's ports are already exposed on 5432 and 6543 on your machine as well as you sent the picture above. You can try pinging the port

Tobias MadsenOP•3mo ago

Assume you've deployed to this an external server instead of running locally, wouldn't you just replace the localhost with the server IP?

inder•3mo ago

Yes, I mentioned it above

Tobias MadsenOP•3mo ago

Just double checking How did you get rid of that port :8000 from the dashboard URL you send in your example? Currently i have to visit it on {server_ip}:8000

inder•3mo ago

Can you tag the example you're referring to

Tobias MadsenOP•3mo ago

https://discord.com/channels/839993398554656828/1384131143644876831/1384144049572937760 i can see your dashboard url is just suapbase.example.com Mine is fixed to :8000 port

inder•3mo ago

Oh yeah, that's just I edited the hosts file and I'm proxying via caddy to supabase I'm listening on port 443

Tobias MadsenOP•3mo ago

hosts file? Where are these the docker-compose.yml setup file?

inder•3mo ago

This is a special file on an OS which is used for DNS resolution. 1. First the cache is checked for a new domain, if a mapping is found for a hostname, then that ip address is used. 2. Then, this hosts file is checked, if hostname is found then the ip address added in this file is found. This is how localhost is looped back. It is added in hosts file and mapped to 127.0.0.1 Are you on windows or linux?

Tobias MadsenOP•3mo ago

linux (talking the external server here)

inder•3mo ago

If you want to test this locally, then you edit hosts file on your local system For server, you setup a domain and a reverse proxy If you're looking for an automated solution https://github.com/singh-inder/supabase-automated-self-host This is what I use locally and on servers if your local system is linux as well I have a guide to setup hosts file https://github.com/singh-inder/supabase-automated-self-host/discussions/6 This guide will help for setting up https for kong only https://towardsdev.com/self-hosted-supabase-with-lets-encrypt-certificate-for-kong-2a6a8dd298db

Tobias MadsenOP•3mo ago

hmm, were just wondering whether the :8000 port did something with the self-hosted postgres url

inder•3mo ago

no, it shouldn't. kong is listening on this port. It shouldn't affect supavisor which is listening on ports 5432 and 6543

Tobias MadsenOP•3mo ago

Thes really should work then

DATABASE_URL="postgres://postgres.your-tenant-id:${DATABASE_PASSWORD}@${DATABASE_SERVER_IP}:6543/postgres" DIRECT_URL="postgres://postgres.your-tenant-id:${DATABASE_PASSWORD}@${DATABASE_SERVER_IP}:5432/postgres"

inder•3mo ago

Did you open the ports from firewall?

Tobias MadsenOP•3mo ago

Yea

inder•3mo ago

also in the cloud provider's firewall? if you're using aws ec2, you'll have to open ports in security group as well. vpc will have to opened if not using default vpc

Tobias MadsenOP•3mo ago

This is on Hetzner

No description

inder•3mo ago

I've never used hetzner. Does this also apply to outgoing?

Tobias MadsenOP•3mo ago

Woops, meant to set as outbound, applied them wrong

inder•3mo ago

no both has to be done

Tobias MadsenOP•3mo ago

oh Still the same, can't access it

inder•3mo ago

let me try it on aws ec2. will get back to you. you're trying to connect via prisma, right?

Tobias MadsenOP•3mo ago

yes

inder•3mo ago

works fine for me. This is my url You can try

DATABASE_URL="postgresql://postgres.your-tenant-id:75de6fb78ac35afdb2fd819a69da02b2@13.201.223.163:6543/postgres"
DIRECT_URL="postgresql://postgres.your-tenant-id:75de6fb78ac35afdb2fd819a69da02b2@13.201.223.163:5432/postgres"

DATABASE_URL="postgresql://postgres.your-tenant-id:75de6fb78ac35afdb2fd819a69da02b2@13.201.223.163:6543/postgres"
DIRECT_URL="postgresql://postgres.your-tenant-id:75de6fb78ac35afdb2fd819a69da02b2@13.201.223.163:5432/postgres"

No description

Tobias MadsenOP•3mo ago

what, how 😄 Give me a second

inder•3mo ago

this is the schema.prism

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

model User {
  id            Int       @id @default(autoincrement())
  name          String?
  email         String    @unique
  emailVerified DateTime?
  image         String?
  posts         Post[]
  accounts      Account[]
  sessions      Session[]

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Account {
  userId            Int
  type              String
  provider          String
  providerAccountId String
  refresh_token     String?
  access_token      String?
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String?
  session_state     String?
  user              User    @relation(fields: [userId], references: [id], onDelete: Cascade)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@id([provider, providerAccountId])
}

model Session {
  id           Int      @id @default(autoincrement())
  sessionToken String   @unique
  userId       Int
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  authorId  Int
  author    User    @relation(fields: [authorId], references: [id])

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

// Looking for ways to speed up your queries, or scale easily with your serverless or edge functions?
// Try Prisma Accelerate: https://pris.ly/cli/accelerate-init

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

model User {
  id            Int       @id @default(autoincrement())
  name          String?
  email         String    @unique
  emailVerified DateTime?
  image         String?
  posts         Post[]
  accounts      Account[]
  sessions      Session[]

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Account {
  userId            Int
  type              String
  provider          String
  providerAccountId String
  refresh_token     String?
  access_token      String?
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String?
  session_state     String?
  user              User    @relation(fields: [userId], references: [id], onDelete: Cascade)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@id([provider, providerAccountId])
}

model Session {
  id           Int      @id @default(autoincrement())
  sessionToken String   @unique
  userId       Int
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  authorId  Int
  author    User    @relation(fields: [authorId], references: [id])

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

I'm running a spot instance, aws can terminate these instances at any time so try it as soon as possible

Tobias MadsenOP•3mo ago

No description

inder•3mo ago

what command do you run?

Tobias MadsenOP•3mo ago

npx prisma db push

npx prisma db push

Tobias MadsenOP•3mo ago

migrate doesn't work either

No description

inder•3mo ago

No description

Tobias MadsenOP•3mo ago

I'm running prisma client v.6.9.0 Can you try and upgrade?

inder•3mo ago

No description

inder•3mo ago

Are you behind some firewall which could be blocking some ip addresses?

Tobias MadsenOP•3mo ago

🤔 Shouldn't be no I can connect to my external server via ssh, and no firewall blocking anything there

inder•3mo ago

try with the same example I'm using. maybe something is wrong with your prisma setup (highly doubtful) but still you can try https://github.com/prisma/nextjs-prisma-postgres-demo

Tobias MadsenOP•3mo ago

Works with these settings in schema.prisma

generator client {
  provider               = "prisma-client"
  output                 = "../src/generated/prisma"
  previewFeatures        = ["queryCompiler"]
  engineType             = "library"
  generatedFileExtension = "ts"
  importFileExtension    = ""
  runtime                = "nodejs"
}

generator client {
  provider               = "prisma-client"
  output                 = "../src/generated/prisma"
  previewFeatures        = ["queryCompiler"]
  engineType             = "library"
  generatedFileExtension = "ts"
  importFileExtension    = ""
  runtime                = "nodejs"
}

inder•3mo ago

I only have this for generator client block in schema.prism

generator client {
  provider = "prisma-client-js"
}

generator client {
  provider = "prisma-client-js"
}

Tobias MadsenOP•3mo ago

Can you try the above? Not that I think it changes anything though

inder•3mo ago

wait

inder•3mo ago

No description

Tobias MadsenOP•3mo ago

What is going on man 😂

inder•3mo ago

Try with the hostname replace ip with supabase.servehttp.com port will remain

Tobias MadsenOP•3mo ago

Same issue

No description

inder•3mo ago

Can you setup another server and copy your prisma setup in that. I think this has something to do with your network You can try making a prisma push request from this another server or use your mobile network for internet use your mobile's internet on your pc and try making request. If this doesn't work then try with another server Hey just checking in, are you going to try to make a request to my instance or should I take it down?

Tobias MadsenOP•3mo ago

No just take it down Obviously something wrong in my end

Tobias MadsenOP•3mo ago

@inder you installed it 1:1 as this setup on the aws server? https://supabase.com/docs/guides/self-hosting/docker

Self-Hosting with Docker | Supabase Docs

Learn how to configure and deploy Supabase with Docker.

inder•3mo ago

I use supabase-automated-self-host. it simply adds a proxy in front of kong for dashboard, rest api, realtime and storage. Doesn't modify the default setup. When connecting db, it connects directly to published ports on the server.

Tobias MadsenOP•3mo ago

weird, just had a suspision on the docker-compose setup, if something were missing in that guide

inder•3mo ago

Did you try setting up another server?

Tobias MadsenOP•3mo ago

Not yet, will try next

inder•3mo ago

To rule out any issues with prisma, try connecting to db with a client with psql or pgadmin(use direct url here). Also try with mobile internet. Could be an issue with your ISP Before that make sure the ports on which you're connecting on your server are accessible from internet https://portchecker.io/

Tobias MadsenOP•3mo ago

tried mobile local docker works as expected trying with a new server now

inder•3mo ago

before that check if you can access port 5432 on the server you've deployed instance

Tobias MadsenOP•3mo ago

port is all good Got another workbuddy to test it, he can migrate all fine to the server i'm trying to do it to. So not quite sure what is going on

inder•3mo ago

Then its definitely your ISP

Tobias MadsenOP•3mo ago

@inder You've already been such a great help so far, can I maybe get you to try and follow this guide 1:1 https://supabase.com/docs/guides/self-hosting/docker on a AWS spot instance? Really need to ensure the github repo that is cloned isn't somewhat buggy with the setup in ports or ips allowed. My coworker can no longer access the db as well, it's so weird

inder•3mo ago

Let me setup an instance. Will ping you here you go

DATABASE_URL="postgresql://postgres.your-tenant-id:your-super-secret-and-long-postgres-password@35.154.5.194:6543/postgres"
DIRECT_URL="postgresql://postgres.your-tenant-id:your-super-secret-and-long-postgres-password@35.154.5.194:5432/postgres"

DATABASE_URL="postgresql://postgres.your-tenant-id:your-super-secret-and-long-postgres-password@35.154.5.194:6543/postgres"
DIRECT_URL="postgresql://postgres.your-tenant-id:your-super-secret-and-long-postgres-password@35.154.5.194:5432/postgres"

inder•3mo ago

works on my end

No description

Tobias MadsenOP•3mo ago

This is so weird

No description

inder•3mo ago

Try logging in from browser http://35.154.5.194:8000 user: supabase password: password

Tobias MadsenOP•3mo ago

And it even says it's open

No description

Tobias MadsenOP•3mo ago

Login via browser to dashboard works just fine But does it on my own instance as well

inder•3mo ago

Do you have some db client like pgadmin or dbeaver?

Tobias MadsenOP•3mo ago

Only the prisma commands that doesn't work No

inder•3mo ago

psql?

Tobias MadsenOP•3mo ago

Yea

inder•3mo ago

try logging in with psql

Tobias MadsenOP•3mo ago

psql connection works fine too tried it with my own as well

inder•3mo ago

can you run migrations with supabase cli? it can also take --db-url flag or is prisma migrations a requirement? try running migrations once with supabase cli

Tobias MadsenOP•3mo ago

Seems to work with the Supabase cli

inder•3mo ago

You tried on your own db? didn't update in my instance

Tobias MadsenOP•3mo ago

Or maybe not, it says it updated the remote instance, but nothing changed. Tried on my own external server

inder•3mo ago

can you ask your friend who was able to run migrations successfully yesterday

Tobias MadsenOP•3mo ago

Will have to try tomorrow, he is out of office

inder•3mo ago

And you tried with another server? what command did you run here?

Tobias MadsenOP•3mo ago

npx supabase db push --db-url ****

inder•3mo ago

?

Tobias MadsenOP•3mo ago

Yes Same issue Only works locally

inder•3mo ago

Not working even from on a separate server. I was going to ask you that I will try pushing migrations from my end but you mentioned that yesterday your friend was able to run migrations

Tobias MadsenOP•3mo ago

Yea... this is a real head scratcher But once again, much appreciate your help 🙏

inder•3mo ago

If this is a disposable instance, I can try running from my end if you want. you can always reset the instance by deleting volumes/db/data directory

Tobias MadsenOP•3mo ago

No need, this eliminated on of my main concerns

inder•3mo ago

Just wanted to ask, when you ran from another server did you allow inbound & outbound connections on this server? I don't know the default firewall settings of hetzner

Tobias MadsenOP•3mo ago

Yes I did But doesn't seem to affect anything, since I can connect fine without inbound and outbound port settings, via psql

inder•3mo ago

I am out of ideas on this one. If psql can connect, can't think of any reason why prisma can't

Tobias MadsenOP•3mo ago

I'm stumped too, will reach out once finding a solution. What... If I run prisma migrate dev works fine, it's the npx prisma migrate dev that fails, even though they are the same version installed

inder•3mo ago

is there any antivirus on your system which could be blocking DNS queries for processes running with npx?

Tobias MadsenOP•3mo ago

Have to dig deeper into this

40KMembers

View on Discord

Did you find this page helpful?