Use SUPABASE_DB_URL instead of EDGE_WORKER_DB_URL

[mikz]

Supabase populates SUPABASE_DB_URL env variable by default in edge workers.
EDGE_WORKER_DB_URL needs to be manually created by the user, which makes it a hassle - I need to duplicate the password and in case I reset it, it needs to be regenerated.

This can't really by worked around from the user side as the edge workers can't call Deno.env.set.
Or at least allow passing it from options to EdgeWorker.start or have some global options like EdgeWorker.options that I can set before calling start.

[jumski]

Thanks for reporting this!

This is related to Discussion #280 where we've been discussing the same problem in the context of preview branches.

The short version:

1. There's a bug where connectionString passed to EdgeWorker.start() doesn't actually work - only the env var gets used. I'll be fixing this.

2. However, EDGE_WORKER_DB_URL requires a transaction pooler URL, not the direct connection that SUPABASE_DB_URL provides. This is important because Edge Functions can be killed at any time, and direct connections would be left hanging, exhausting your limit.

3. Unfortunately, auto-deriving the pooler URL from SUPABASE_DB_URL isn't feasible - they're fundamentally different formats and the database region info isn't available in any env var.

I'm planning to reach out to the Supabase team to see if there's a way to make the pooler URL available as a standard env var, similar to how SUPABASE_DB_URL is automatically populated.

See Discussion #280 for more technical details on why auto-derivation won't work.

[jumski]

I checked with the Supabase team and this is a known gap - they've discussed it internally but it's not yet prioritized. For now, manually setting the pooler URL is the only option.

[mikz]

Makes sense. I probably set it up in a way where I'm using the direct connection, as I'm reusing the SUPABASE_DB_URL. And sometimes I'm exhausting my connections :) It might be worth having this very visible in the documentation.

The setup is quite painful. Have you considered documenting how to set it up with a new user with own password for this? Is that feasible? Using the main user can be quite a hassle, especially since you can't see the password anywhere, so you either have to reset or store it somewhere else. And once it is duplicated in places it is painful to reset.

[jumski]

The transaction pooler requirement is in the docs - happy to make it more prominent if you have suggestions on where it tripped you up.

Re: password management - good point. Once I fix the connectionString bug from #280, you'll be able to build the pooler URL at runtime - store just the password as a separate env var, set your region once, and construct the connection string in code. That way you're only syncing the password, not the entire URL.

I'll prioritize that fix.

[jumski]

Hey @mikz I just released 0.9.1, it should address all the issues and provide workarounds, more info in the release post https://www.pgflow.dev/news/pgflow-0-9-1-unified-connection-configuration/

Thanks for reporting this. Would appreciate your feedback!