Expose supavisor websocket port to localhost via kong

[batuhanbilginn]

Describe the bug I can't connect to the local database in serverless with drizzle. I follow the guide you guys provided here and it works for production however it doesn't locally. https://supabase.com/docs/guides/database/connecting-to-postgres/serverless-drivers

drizzle client

import { sql } from "@vercel/postgres";
import { drizzle  } from "drizzle-orm/vercel-postgres";

const db = drizzle(sql, { schema });

connection string POSTGRES_URL=postgresql://postgres:postgres@localhost:54329/postgres?workaround=supabase-pooler.vercel

To Reproduce Steps to reproduce the behavior:

1. Start Supabase locally
2. Install Drizzle and Vercel serverless packages
3. Try to connect your database from edge runtime(API ROUTE)
4. See error

Expected behavior It should be connected to the database as expected.

System information Rerun the failing command with --create-ticket flag.

• Version of OS: Mac OS 14.1.1 (23B81)
• Supabase CLI 1.178.2
• Version of Docker: [e.g. v25.0.3]
• supabase/postgres │ 15.1.1.61 │ - supabase/gotrue │ v2.151.0 │ - postgrest/postgrest │ v12.0.1 │ - supabase/realtime │ v2.28.32 │ - supabase/storage-api │ v1.0.6 │ - supabase/edge-runtime │ v1.54.6 │ - supabase/studio │ 20240422-5cf8f30 │ - supabase/postgres-meta │ v0.80.0 │ - supabase/logflare │ 1.4.0 │ - supabase/supavisor │ 1.1.56 │ - darthsim/imgproxy │ v3.8.0 │ -

[sweatybridge]

The connection string for local supavisor is postgresql://postgres.pooler-dev:postgres@localhost:54329/postgres

[batuhanbilginn]

@sweatybridge thanks for the clarification but this one doesn't work either.

[sweatybridge]

Oh ic, since you are connecting from edge runtime, you need to use a different host and port.

postgresql://postgres.pooler-dev:postgres@pooler:5432/postgres

[batuhanbilginn]

@sweatybridge thank you for helping me on Saturday but this doesn't work either.

1. postgresql://postgres.pooler-dev:postgres@localhost:54329/postgres I can confirm that we can connect to the database with this string in serverfull env. with Drizzle. It doesn't work on the edge runtime.

import { sql } from "@vercel/postgres";
import { drizzle  } from "drizzle-orm/vercel-postgres";

const db = drizzle(sql, { schema });

2. postgresql://postgres.pooler-dev:postgres@pooler:5432/postgres When I try this one it gives an error before trying to connect to the database in the same setup above. The error: _VercelPostgresError - 'invalid_connectionstring': This connection string is meant to be used with a direct connection. Make sure to use a pooled connection string or try createClient() instead.. Also, it doesn't work in serverfull env. too.

[sweatybridge]

2. That's a problem with vercel Postgres lib. You can try the workaround suggested here https://github.com/supabase/supabase/discussions/14165#discussioncomment-8880849

[batuhanbilginn]

Yeah I'm aware of this issue and when I use the connection string as postgresql://postgres.pooler-dev:postgres@pooler:5432/postgres?workaround=supabase-pooler.vercel it gives NOT FOUND POOLER error.

[sweatybridge]

@batuhanbilginn tbh I'm not super familiar with vercel/postgres but I've asked around to see if anyone solved it before.

Are you able to connect to the pooler url of the hosted project from a deployed function? Just wondering if this is only a local issue or affects hosted as well.

[batuhanbilginn]

Yes, I can connect to the hosted version with the pool URL.

The issue might be related to the Websocket connection. I tried several approaches from this solution (connecting local Supabase with Prisma client before Supavisor released in CLI) but couldn't make it so far...

Maybe you can reach out to the person who created this guide.

[sweatybridge]

Upon reviewing the guide, I realised it's specifically using transaction mode. The url I gave is for session mode. Could you try this instead

postgresql://postgres.pooler-dev:postgres@pooler:6543/postgres?workaround=supabase-pooler.vercel

[batuhanbilginn]

It's still the same...

Error Object

  ErrorEvent {
[Symbol(kTarget)]: WebSocket {
_events: [Object: null prototype] {
error: [Function],
message: [Function],
close: [Function],
open: [Function]
 },
  _eventsCount: 4,
 _maxListeners: undefined,
_binaryType: 'arraybuffer',
_closeCode: 1006,
_closeFrameReceived: false,
_closeFrameSent: false,
_closeMessage: <Buffer >,
_closeTimer: null,
_extensions: {},
_paused: false,
_protocol: '',
_readyState: 3,
_receiver: null,
_sender: null,
_socket: null,
_bufferedAmount: 0,
_isServer: false,
_redirects: 0,
_autoPong: true,
_url: 'wss://pooler/v2',
_req: null,
[Symbol(kCapture)]: false
},
[Symbol(kType)]: 'error',
[Symbol(kError)]: Error: getaddrinfo ENOTFOUND pooler
  at GetAddrInfoReqWrap.onlookup [as oncomplete] (node:dns:108:26)
  at GetAddrInfoReqWrap.callbackTrampoline (node:internal/async_hooks:130:17) {
errno: -3008,
code: 'ENOTFOUND',
syscall: 'getaddrinfo',
hostname: 'pooler'
},
[Symbol(kMessage)]: 'getaddrinfo ENOTFOUND pooler'
}

[sweatybridge]

I took a closer look at the link you shared and found the following modification to be working locally.

// Connect to Vercel Postgres
const url = new URL(Deno.env.get("POSTGRES_URL")!);
if (url.hostname === "pooler") {
  neonConfig.useSecureWebSocket = false;
  neonConfig.wsProxy = (host) => `${host}:4000/v2`;
}
export const db = drizzle(sql);

neonConfig is a global import from @neondatabase/serverless

[batuhanbilginn]

I took a closer look at the link you shared and found the following modification to be working locally.

// Connect to Vercel Postgres

const url = new URL(Deno.env.get("POSTGRES_URL")!);

if (url.hostname === "pooler") {

  neonConfig.useSecureWebSocket = false;

  neonConfig.wsProxy = (host) => `${host}:4000/v2`;

}

export const db = drizzle(sql);

neonConfig is a global import from @neondatabase/serverless

Could you please share which database url you used here?

[sweatybridge]

Sure, it's this one https://github.com/supabase/cli/issues/2474#issuecomment-2203507586

Also make sure local pooler is enabled in supabase/config.toml. It's disabled by default.

[db.pooler]
enabled = true

[batuhanbilginn]

I still can't connect to the database.

Pooler is enabled in the config and when I check the Docker I can see that Supavisor is running and it's healthy.

The issue is not related to any library or the code. I can't connect to the Websocket proxy running inside Docker. I couldn't connect to the Websocket via terminal too.

npx wscat -c ws://pooler:4000 -> Not Found npx wscat -c ws://localhost:4000 -> Can't Connect

When I checked the mapped ports of Supavisor with docker port 7466ecfd29f4 I only see this one: 6543/tcp -> 0.0.0.0:54329 There is no mapping for 4000.

[sweatybridge]

Are you sure you are connecting to pooler from edge runtime container, ie. functions serve then curl, and not from your local terminal with deno run?

There is no mapping for 4000.

There's no need to map port 4000. All containers in the same docker network can reach each other using their container name, id, or network alias.

pooler is the network alias that cli sets up automatically. You can also try using the container name of supavisor instead, which is obtainable from docker ps -a.

[batuhanbilginn]

Oh sorry about the misunderstanding.

I'm not trying to connect to the database from an Edge Function of Supabase. I'm trying to connect from Edge Runtime of Vercel.

[sweatybridge]

No worries, I've reopened this issue and updated the title.

Do you see any difficulty in migrating your code to use Supabase functions instead?

[batuhanbilginn]

It's great to see that we have found the root cause of this issue. Really appreciate your support man!

I don't think that I can export everything into Edge Functions since the whole logic is built on top of TRPC and Drizzle. Also, I think that this is a very common use case.

Don't you think that you can expose the port to the localhost or you are asking this to understand the severity of this issue?

[sweatybridge]

Don't you think that you can expose the port to the localhost or you are asking this to understand the severity of this issue?

Yes, I want to be careful with exposing new ports because once we start supporting this config, it will be difficult to take back later. So ideally, this needs to be a common enough use case with long term support to justify adding it.

I believe there's currently work in progress for vercel to move towards a generic library like postgres-js rather than being coupled with implementation details of the pooler. When that happens, cli may not need to expose websocket port anymore. So this doesn't really justify long term support.

An alternative is to route pooler's websocket port via the local api gateway, running on port 54321 by default. According to kong documentation, this should be supported but it will likely take some trial and error to figure out the right config.

If you want to take a crack at this yourself, you can modify our existing kong.yml with an additional route to pooler. For eg. something like this may work

  - name: pooler-v2-ws
    _comment: "Pooler: /pooler/v2/* -> ws://pooler:4000/v2/*"
    url: http://{{ .PoolerId }}:4000/v2
    protocol: ws
    routes:
      - name: pooler-v2-ws
        strip_path: true
        paths:
          - /pooler/v2/
    plugins:
      - name: cors

If you manage to make it work and submit a PR, I will be happy to merge it.

[batuhanbilginn]

I believe there's currently work in progress for vercel to move towards a generic library like postgres-js rather than being coupled with implementation details of the pooler. When that happens, cli may not need to expose websocket port anymore. So this doesn't really justify long term support.

Interesting. It means that they will support TCP connections on Edge Runtime. Paul had suggested postgres-js to me on Twitter I think this was the reason. Thanks for letting me know.

I'll find a workaround until this happens and check the solution you shared. Thanks!

[maikelagm]

Hi, I am experiencing the same issue using Drizzle and Next.js, but I have been able to establish the connection using the Postgres-js adapter with the following URL in the environment variable:

POSTGRES_URL="postgresql://postgres.pooler-dev:postgres@localhost:54329/postgres"

pooler-dev is the tenant created by Supabase CLI by default.

Port 54329 is the one that by default in config.toml exposes [db.pooler], and I changed the mode to session:

[db.pooler]
enabled = true
# Port to use for the local connection pooler.
port = 54329
# Specifies when a server connection can be reused by other clients.
# Configure one of the supported pooler modes: `transaction`, `session`.
pool_mode = "session"
# How many server connections to allow per user/database pair.
default_pool_size = 20
# Maximum number of client connections allowed.
max_client_conn = 100

This is the Drizzle configuration used:

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

import { env } from "../env";
import * as schema from "./schema";

const pool = postgres(env.POSTGRES_URL, { prepare: false });

const db = drizzle(pool, { schema });

export { db };

The problem now is that this is not compatible with the Next.js middleware that runs in the edge runtime, so it needs a serverless adapter that uses WebSocket like Vercel or Neon. Using Postgres-js produces the following error:

cloudflare:sockets
Module build failed: UnhandledSchemeError: Reading from "cloudflare:sockets" is not handled by plugins (Unhandled scheme).
Webpack supports "data:" and "file:" URIs by default.
You may need an additional plugin to handle "cloudflare:" URIs.

So, how can we expose supavisor using WebSocket? In previous messages, you mentioned modifying kong.yml, but we are using Supabase CLI. Can it be done? Or what alternative do we have? I have tried editing the file with the supabase_kong_server container running, but it has not worked with the configuration mentioned above.

This is an issue that should be experienced by all developers using Prisma or Drizzle with Next.js and looking to self-host their application with Supabase, when validating authentication in the middleware using the database session strategy. That is, they will get the error when trying to connect to the database from the middleware.

[Finton140]

@sweatybridge Is there a simpler workaround than recompiling the binary?

It's currently impossible to use NextJS middleware/edge runtime with local Supabase due to the websocket error.

Any use case which requires fetching database values in NextJS app router middleware (such as for AuthN/AuthZ) is currently not workable on local.

[Finton140]

Note that I used the following workaround as listed above:

// Connect to Vercel Postgres
if (!process.env.VERCEL_ENV) {

    neonConfig.useSecureWebSocket = false
    neonConfig.pipelineTLS = false
    neonConfig.pipelineConnect = false

    neonConfig.wsProxy = (host) => `${host}:54329/v2`;

}

export const db = drizzle(sql, { schema, logger: true })

And got the following error:

[Symbol(kType)]: 'error',
  [Symbol(kError)]: Error: Unexpected server response: 204

[sweatybridge]

@Finton140 I will make a PR using my code here https://github.com/supabase/cli/issues/2474#issuecomment-2210957044 It should be straightforward to expose supervisor websocket through Kong api port, ie. host:54321/pooler/v2

[sweatybridge]

You can try this config for connecting from local app (not hosted by edge runtime) to local pooler.

    neonConfig.useSecureWebSocket = false
    neonConfig.pipelineTLS = false
    neonConfig.pipelineConnect = false
    neonConfig.wsProxy = (host) => `${host}:54321/pooler/v2`

Use npx supabase@beta start for the new release.

[Finton140]

@sweatybridge I pulled the beta and ran it with the new config, but I'm still getting the same 204 error unfortunately.

[sweatybridge]

Can you share a full example to reproduce? Additional logs will also be helpful.

• application debug logs
• docker logs -f supabasekong

[Finton140]

@sweatybridge I've made a repo with the minimal reproducable error here

If you run the dev server and refresh the page you will see errors from the middleware, and if you run the db:migrate script you will see the 204 error.

[sweatybridge]

@Finton140 you need to read more carefully. I commented ${host}:54321/pooler/v2

You code example is using port 54329. https://github.com/Finton140/supabase-error/blob/main/database/db.ts#L13

[Finton140]

@sweatybridge 54321 returns a 404

[batuhanbilginn]

When I check the ports from docker I see this one for the supavisor:

0.0.0.0:54329->6543/tcp

There is no port such as 54321 that exposed to the localhost.

When I try to ping 54329 it returns error (204) and this is what I saw in the container logs of supavisor:

region=local [warning] ClientHandler: socket closed with reason {:shutdown, :http_request}

So I think the correct exposed port is 54329 but there is an error in the configuration.

[sweatybridge]

@batuhanbilginn 54329 is the exposed pooler port. 54321 is the exposed websocket port proxied from kong api. Websocket must run on a separate port from the supavisor postgres port (4000 vs 6543). We use kong to proxy port 54321 to 4000 of the pooler container.

@Finton140 the 404 error is due to missing / suffix. Full url should be: ${host}:54321/pooler/v2/

 % npm run dev

> supabase-error@0.1.0 dev
> next dev

  ▲ Next.js 14.2.5
  - Local:        http://localhost:3000
  - Environments: .env

 ✓ Starting...
 ✓ Ready in 2.6s
 ✓ Compiled /middleware in 365ms (174 modules)
Query: select "id", "name" from "user"
 [error: relation "user" does not exist] {
  length: 103,
  name: 'error',
  severity: 'ERROR',
  code: '42P01',
  detail: undefined,
  hint: undefined,
  position: '26',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '1392',
  routine: 'parserOpenTable'
}
undefined
 ○ Compiling / ...
 ✓ Compiled / in 1962ms (523 modules)
 GET / 200 in 2358ms

[Finton140]

@sweatybridge Thanks for clarifying. Everything seems to be working once I added the missing suffix! Thank you for all your help!

@batuhanbilginn For reference in case you need this solution yourself, I updated the test repo so everything now works. Just make sure you are using npx supabase@beta start until the changes are merged into the main branch.

[batuhanbilginn]

@sweatybridge thanks for the clarification!

and @Finton140 thank you for efforts to solve this.

All the best guys!

[sweatybridge]

Thank you guys as well for providing detailed repro and prompt feedback.

This is one of the best communities I've worked with.