What is your Phoenix config for PostgreSQL on Supabase?

rump13 · February 8, 2024, 6:11pm

Hi all,

I spent quite a few hours now trying to figure out how to connect a vanilla Phoenix app running on Fly.io to Supabase but I couldn’t make it work on IPV6.

When creating the Fly.io app, a file (/rel/env.sh.eex) gets created with the following configuration:

#!/bin/sh

# configure node for distributed erlang with IPV6 support
export ERL_AFLAGS="-proto_dist inet6_tcp"
export ECTO_IPV6="true"
export DNS_CLUSTER_QUERY="${FLY_APP_NAME}.internal"
export RELEASE_DISTRIBUTION="name"
export RELEASE_NODE="${FLY_APP_NAME}-${FLY_IMAGE_REF##*-}@${FLY_PRIVATE_IP}"

Then my connection string is configured with a secret in Fly.io:

fly secrets set DATABASE_URL=postgres://...

If I use the default config, then I get the following error connecting with session mode or transaction mode:

17:56:30.334 [error] Postgrex.Protocol (#PID<0.167.0>) failed to connect: ** (DBConnection.ConnectionError) tcp connect (aws-0-us-east-1.pooler.supabase.com:6543): non-existing domain - :nxdomain

Now, if I comment out the IPV6 config line, it works.

#export ECTO_IPV6="true"

I was wondering if any of you got it working under IPV6. It seems today it is supported by both, Fly.io and Supabase but I can’t figure out how to make it work.

How do you set up your database connection from Fly to Supabase?

dfalling · February 13, 2024, 10:12pm

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.

chasers · February 19, 2024, 8:12pm

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.

pdgonzalez872 · February 20, 2024, 7:23pm

@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.

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

chasers · February 20, 2024, 11:05pm

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.

dfalling · February 28, 2024, 7:55pm

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

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.

ntodd · May 21, 2024, 6:42pm

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

rump13 · May 23, 2024, 3:47am

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.

dfalling · May 23, 2024, 7:15am

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.