What is your Phoenix config for PostgreSQL on Supabase?

I hit this recently as well when Supabase suddenly dropped IPv4 support.

socket_options: [:inet6] also didn’t work for me- no connection could be made.

this issue suggests that IPv6 support may actually just work out of the box.

My app works with just updating the database URL and not changing any other field. It seems my app takes longer to connect via the IPv6 address, so I had a number of errors with a Fly app that had min machines set to 0.

btw you shouldn’t need to use the pooler url (...pooler.supabase.com...) if you’re on Fly (unless you have lots of nodes and need lots of db connections).

Fly and your db speak IPv6 so you should be able to connect directly.

@chasers I just recently played around with a fresh project (phoenix 1.7.11, elixir 1.16.1/OTP26) and created a vanilla project on Fly. I created a Fly postgres instance for it as well, it got connected, great stuff.

I then proceeded to go with the managed postgres option after reading some docs on Fly and wanted to try out supabase (first time, wanted to try it since you all released that open source elixir project ). My intent was to create a vanilla project on supabase, get the DATABASE_URL, connect, deploy the fly app and watch things succeed. Much like @rump13 , I get nxdomains and the app won’t even run migrations (I have none (fresh project as I mentioned).

I’m sharing this because you mentioned that folks didn’t need to use that ...pooler... option in the DATABASE_URL. I just wanted to share that this is what folks are seeing (I hope this is helpful, maybe unexpected - if not, please ignore ). All folks (including me) are doing is copy pasting the URL, this is why this is getting used.

image1200×441 23.9 KB

This kinda feels like it could be on Fly’s end though, or very likely me somehow

Thanks for the write up! Very helpful!

Well you should be able to use the pooler with Fly so we’ll have to look into this :nxdomain issue.

We show people the pooler URL first because typically you’d create a db and likely sign into it from your house. And in general right now IPv4 works from more places so we want to default to that (of course it isn’t working from Fly atm). And also, generally, more folks need a pooler than not.

Note: if you try to connect without the pooler, Supabase shows a big angry warning:

Captura de pantalla 2024-02-28 a la(s) 8.54.25 p.m.1624×402 42.6 KB

Edit: Supabase informed me that this warning is really just for the previous IPv4 URL, which has already gone away. The direct access URL will still work.

Did you ever have a chance to look into this? I’m experiencing the same issues connecting to Supabase on Fly. I’m moving an existing app from another service where it works fine, so this is definitely something Fly or IPv6-specific.

Using :verify_none for demonstration.

Supavisor, IPv4

database_url = "Supavisor DB connection string"

config :foundation, Foundation.Repo,
  ssl: true,
  ssl_opts: [
    verify: :verify_none
  ],
  url: database_url,
  pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10")
  # no ipv6 support enabled

Postgrex.Protocol (#PID<0.2411.0>) timed out because it was handshaking for longer than 15000ms
Postgrex.Protocol (#PID<0.2411.0>) failed to connect: ** (DBConnection.ConnectionError) ssl recv (idle): closed

Supavisor, IPv6

# export ECTO_IPV6="true" in env.sh.eex
database_url = "Supavisor DB connection string"
maybe_ipv6 = if System.get_env("ECTO_IPV6") in ~w(true 1), do: [:inet6], else: []

config :foundation, Foundation.Repo,
  ssl: true,
  ssl_opts: [
    verify: :verify_none
  ],
  url: database_url,
  pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10"),
  socket_options: maybe_ipv6

Postgrex.Protocol (#PID<0.2412.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (aws-0-us-west-1.pooler.supabase.com:5432): non-existing domain - :nxdomain

Direct IPv6 connection URL

# export ECTO_IPV6="true" in env.sh.eex
database_url = "IPv6 direct connection string"
maybe_ipv6 = if System.get_env("ECTO_IPV6") in ~w(true 1), do: [:inet6], else: []

config :foundation, Foundation.Repo,
  ssl: true,
  ssl_opts: [
    verify: :verify_none,
  ],
  url: database_url,
  pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10"),
  socket_options: maybe_ipv6

Works, but runs out of connections after a restart or two:

Postgrex.Protocol (#PID<0.2409.0>) failed to connect: ** (Postgrex.Error) FATAL 53300 (too_many_connections) remaining connection slots are reserved for non-replication superuser connections

I’m sorry, but I don’t have anything else to share about this issue. I’m in the prototyping phase of my project, so I moved the test environment database to AWS RDS using aws_rds_castore library to resolve the RDS certificates, then I’m using a local PostgreSQL for local development.

I reached out to Supabase when this was happening for me, and they suggested I up the pool size for my database in database settings. I bumped it from 15 to 25 and it solved it.