Possible to hit localhost from database functions and/or Edge functions?

[drewbietron]

I am trying to stick to a fully local development setup (locally) and am having troubles making requests to localhost from both a postgres function, or from an Edge Function. I know that both are ran via the Docker setup from the Supabase CLI, but I've tried about every combination of things I can try in order to make requests to localhost.

I've realized that localhost from the context of a Docker container, is just that container, not the machine's localhost. As an attempted work around for this, I've tried hitting the exposed Docker IP (in my case 172.25.145.178) in the database functions and/or Edge functions, but that doesn't work. I've also added a URL to my hosts config locally so that the IP above can accept requests at a URL vs an IP (eg app.local -> 172.25.145.178) which exposes my Nextjs web app and search service to their corresponding ports at app.local, but that doesn't work in the database either.

I plan on storing the url's using Supabase Vault secrets, which will allow me to have different values for different Supabase environments (eg localhost:3000, staging.app.com, production.app.com etc) and reference those variables within my functions or Edge Functions. I just can't seem to get it wired up to my locally ran services. Any help would be appreciated.

Here's some example database functions

Tried localhost, that doesnt work since these services aren't ran from within the Supabase local Docker container

create trigger index_products_to_search
  after insert or update or delete on products
  for each row execute procedure 
  public.send_to_webhook( 'localhost:3000/api/test' );

Tried adding a recoerd to hosts to listen at a URL vs localhost but no luck

create trigger index_products_to_search
  after insert or update or delete on products
  for each row execute procedure 
  public.send_to_webhook( 'http://app.local:3000/api/test' );

Would also use the same approach to hit a locally served Edge function. This one I thought might actually work since this localhost is part of the running Docker container, but no luck.

create trigger index_products_to_search
  after insert or update or delete on products
  for each row execute procedure 
  public.send_to_webhook( 'http://localhost:54321/functions/v1/function-name' );

Any help would be appreciated. Getting this nailed down would really help me get a great local dev setup going!

[sweatybridge]

Hi @drewbietron, you can connect to localhost from within the db container using ip address 172.17.0.1. For example,

create trigger index_products_to_search
  after insert or update or delete on products
  for each row execute procedure 
  public.send_to_webhook( 'http://172.17.0.1:54321/functions/v1/function-name' );

I haven't tested this trigger per se, so let me know if it works.

[samuelbohl]

Hi @sweatybridge I also here to ask about the issue @rbkayz mentioned.

The question is: How can I setup a trigger so that it calls a supabase edge function in the correct environment, without having to somehow split the migration scripts. Is there a solution that supabase provides here?

[rbkayz]

Wrote a function for this. The only thing you'll need to do is add the project_id in supabase vault

Here's the code

CREATE OR REPLACE FUNCTION webhooks_func()
	RETURNS TRIGGER
	SECURITY DEFINER
	LANGUAGE plpgsql
	AS $$
DECLARE
	url text;
	function_name text := TG_ARGV[0]::text;
	project_id text;
	project_env text DEFAULT 'LOCAL';
	payload jsonb;
	headers jsonb;
	request_id bigint;
	timeout_ms integer DEFAULT 1000;
BEGIN
	--
	--
	IF function_name IS NULL THEN
		RAISE EXCEPTION 'function_name cannot be null';
	END IF;
	--
	-- GET URL ENDPOINT
	SELECT
		decrypted_secret
	FROM
		vault.decrypted_secrets
	WHERE
		name = 'project_id'
	LIMIT 1 INTO project_id;
	--
	-- GET ENV
	SELECT
		decrypted_secret
	FROM
		vault.decrypted_secrets
	WHERE
		name = 'project_env'
	LIMIT 1 INTO project_env;
	--
	--
	IF project_id IS NULL THEN
		url := 'http://172.17.0.1:54321';
	ELSE
		url := 'https://' || project_id || '.supabase.co';
	END IF;
	--
	--
	IF project_env IS NULL THEN
		project_env := 'LOCAL';
	END IF;
	--
	--
	headers := '{"Content-type":"application/json"}'::jsonb;
	--
	--
	payload := jsonb_build_object('old_record', OLD, 'record', NEW, 'type', TG_OP, 'table', TG_TABLE_NAME, 'schema', TG_TABLE_SCHEMA, 'env', project_env);
	--
	--
	SELECT
		http_post INTO request_id
	FROM
		net.http_post(url || '/functions/v1/' || function_name, payload, '{}'::jsonb, headers, timeout_ms);
	--
	--
	INSERT INTO supabase_functions.hooks(
		hook_table_id,
		hook_name,
		request_id)
	VALUES (
		TG_RELID,
		TG_NAME,
		request_id);
	--
	--
	RETURN NEW;
END
$$;

Then create the trigger as follows with the name of your function

CREATE OR REPLACE TRIGGER webhook_after_insert_tbl_org_members
	AFTER INSERT ON tbl_org_members
	FOR EACH ROW
	EXECUTE FUNCTION webhooks_func('invite-members');

Good part is that this goes in the migration when you diff the public schema (as opposed to setting the trigger from the dashboard UI)

[rbkayz]

@samuelbohl

[samuelbohl]

Thanks! @rbkayz The authorization header is missing, but I guess I can also just store it in the vault?

[rbkayz]

You can. Or you can pass the --no-verify-jwt flag when deploying these trigger functions

[ChuckJonas]

This should really be mentioned somewhere in the docs. It took me way too long to track this down

[fabrizioanichini]

Is there any easy way right now?

[floross]

Hi, I am in macos and I am still not able to call my local nestjs api serveur (running on 3000) from a database trigger. In the net._http_response I still got Couldn't connect to server error. I tried to use http://host.docker.internal:3000/api and http://172.17.0.1:3000/api without any success. I used ngrok now but I'd prefered to stay on my local computer (I don't have internet all the time).

Is there another way to hit my local api from the supabase cli database ?

[jrpersico]

Did you find a solution?

[chang-ryan]

curious here too, i'm getting the same problems when trying to use host.docker.internal

[chang-ryan]

[chang-ryan]

not working for me

[devjume]

Created following snippet that creates trigger to call edge function endpoint based on environment.

DO $$
DECLARE
    function_name TEXT := 'hello_world'; 
    trigger_name TEXT := 'new_test_run_hello_world';
    table_name TEXT := 'public.test';
    production_url TEXT := 'https://XXXXX.supabase.co'; 
    development_url TEXT := 'http://host.docker.internal:54321'; 
    function_url TEXT := ''; -- Dont edit. This will be set dynamically
    server_ip INET;
BEGIN
    --- DONT EDIT BELOW
    SELECT inet_server_addr() INTO server_ip;

    function_url := 
    CASE 
        WHEN server_ip >= '172.0.0.0'::inet AND server_ip <= '172.255.255.255'::inet
        THEN development_url || '/functions/v1/' || function_name
        ELSE production_url || '/functions/v1/' || function_name
    END;
    --- DONT EDIT UP
	
    -- FOR DEBUGGING
    -- RAISE EXCEPTION 'Test %, %, %', production_url, development_url, function_url;

     -- EDIT FOLLOWING TO SETUP YOUR TRIGGER

    EXECUTE format('
        CREATE OR REPLACE TRIGGER %s
            AFTER INSERT
            ON %s
            FOR EACH ROW
            EXECUTE FUNCTION supabase_functions.http_request(''%s'', ''POST'', ''{"Content-type":"application/json"}'', ''{"name":"Person1"}'', ''1000'');
    ', trigger_name, table_name, function_url);

END $$;

Pros:

• It checks if postgres database IP is in 172.0.0.0-172.255.255.255 range (meaning local docker), and sets the webhook url based on that to localhost or production url.
• Webhooks will show in the UI. Database > Webhooks.
• Can be used with migrations and version controlled.

Cons:

• Each webhook must be added manually to the migration file by using the snippet.
• Supabase project url is exposed to git repository via the migration files. But I treat that as public information anyway.

I use the snippet in following development workflow when I want to trigger edge function on database event. In bot local development and production.

1. Create new edge function: supabase functions new HELLO_WORLD
2. Code the edge function
3. Server edge function locally supabase functions serve
4. Add it to local Supabase instance using in SQL editor by running previous snippet
5. Debug and verify that everything works
6. Create new db migration file supabase migration new DB_TRIGGER_WEBHOOK_XXX
7. Test migrations locally and verify again that everything works supabase db reset
8. Deploy edge function to production supabase functions deploy hello-world
9. Deploy migration to production supabase db push
10. Profit

I didn’t fully test this with supabase migration as I use drizzle. But I am pretty sure this works as stated.

Edit: Better IP check and instread localhost it uses host.docker.internal

[RyanClementsHax]

For those that don't want to use a hardcoded ip like 172.17.0.1, you can take advantage of docker's networking and use the db alias.

Ex:

postgresql://authenticator:postgres@db:5432/postgres

[magick93]

Did anyone find a way to achieve this using Linux?

[kkukshtel]

It took me so long to find this answer and thank god for the thread there's no way I would have thought to check Docker. It should be emitted as part of supabase start imo.

http://172.17.0.1:54321 worked for me.

[cwooldridge1]

for local host from edge functions you can use host.docker.internal

e.g.

    const response = await fetch(`http://host.docker.internal:3000/api`, {
      method: "GET",
      headers: {
        "Content-Type": "application/json",
      },
    });