How to use webhooks or similar in Supabase?

[leibole]

How do I configure webhooks in Supabase?

[awalias]

hey @leibole what is the use-case you have in mind?

[leibole]

Hi, I want to trigger functions for changes in the DB, similar to firebase functions with firestore changes.

…

> On 20 Oct 2020, at 9:30, Ant Wilson ***@***.***> wrote:  hey @leibole what is the use-case you have in mind? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[kiwicopple]

Hey @leibole, we have actually built this into our Realtime server, but we haven't yet finished the Dynamic Configuration (supabase/realtime#48). We'll do this after we move out of Alpha.

So here is a workaround ( it could use some improvements):

1. Enable the HTTP extension

2. Create a function to send the payload

create or replace function send_to_webhook() returns trigger AS $trigger$
declare
  rec RECORD;
  payload TEXT;
  column_name TEXT;
  column_value TEXT;
  payload_items JSONB;
begin
  -- Set record row depending on operation
  CASE TG_OP
  WHEN 'INSERT', 'UPDATE' THEN
     rec := NEW;
  WHEN 'DELETE' THEN
     rec := OLD;
  ELSE
     RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP;
  END CASE;
 
  payload_items := coalesce(row_to_json(rec),'{}')::jsonb;
  
  -- Build the payload
  payload := json_build_object(
    'timestamp',CURRENT_TIMESTAMP,
    'operation',TG_OP,
    'schema',TG_TABLE_SCHEMA,
    'table',TG_TABLE_NAME,
    'data',payload_items
  );

  -- Notify the webhook
  perform extensions.http_post(TG_ARGV[0]::varchar, payload::varchar, 'application/json');
  
  return rec;
end;
$trigger$ language plpgsql;

Usage

Now any time you want to send to a webhook for an update/insert/delete on a particular table, you can use it like this:

-- example table
create table todos (
    todo text,
    is_completed boolean
);

-- sending to webhook 
create trigger todos_webhook 
  after insert or update or delete on todos
  for each row execute procedure 
  send_to_webhook( 'https://webhook.site/079aa292-4940-4df0-9ee3-191f2e3e15dc' );

You can change send_to_webhook( 'https://webhook.site/079aa292-4940-4df0-9ee3-191f2e3e15dc' ) to send to whichever webhook you want.

Caveats

People usually do something like this with postgresql's NOTIFY. This should be better because NOTIFY has a payload limit of 8000 bytes. However there are a few caveats:

• I haven't tested this
• You have to create triggers for every table you want to send the data to.
• We don't expose triggers in our UI (yet), so you might want to use something like DBeaver to manage your database until we create the full SQL editor

[drewbietron]

Is this essentially what the Database Webhooks do now? I am looking for a method for having these functions ran in my migrations so that I can use these with an all local development environment. Since the database webhooks aren't available in the local Supabase Studio, I think that may be the route to take? Seeing that this comment was on 10/2020, I figured this method may have been expanded upon.

Love that the CEO of Supabase is in here giving these in-depth answers!

[gushecht]

Hey there – just getting ramped on Supabase – I'm very excited, thank you!

I am wondering: is there a timeline for Database Webhooks to move out of alpha and also to be available in the local Studio?

Much appreciated.

[drewbietron]

Hey there – just getting ramped on Supabase – I'm very excited, thank you!

I am wondering: is there a timeline for Database Webhooks to move out of alpha and also to be available in the local Studio?

Much appreciated.

If some more context on this is helpful to anyone, I ended up doing a combination of PG NOTIFY and this webhook approach. I found that the webhook approach adheres to the atomic nature of Postgres transactions, so you will be introducing a HTTP call that could be slow to respond, fail, etc and that may not be worth it to your app's needs to have that transaction fail or be slow due to the webhook.

The best combination I've personally found is to use notify for nice to have events that are still stored, but not make or break if they don't succeed. For example sending an email or a push notification after an event. That's something I'm currently handling with notify and a small payload of the effected row, or an event name so that you can listen to those notify events on a separate server and handle them as you need to. I've committed to this webhook approach for things such as creating a Stripe Connected Account or Stripe Customer when a certain row or permission is added. That info is business critical on my end so it's worth the atomic transaction and/or potentially slow or failed request from Stripe since that returned data is vital.

Hope that's somewhat helpful. I've found that NOTIFY with a well structured event payload that you can listen to on another server, and create a queue of with something like SQS goes a long way for the small things (same approach for using Edge functions on events as well), and leaving the webhooks for things that definitely need to happen has worked well.

I'd also like to add that I use that same webhook logic to send events using Supabase Edge functions and the JWT info stored using Supabase Vault. That's a pretty cool option too!

[eamondunne]

Hey everyone, I'm hitting an issue when I copy the code above into supabase functions - with the following error Failed to create function: failed to create pg.functions: syntax error at or near "create"

Can anyone help with correcting the error in the code above?

Also, as someone who is new to plpgsql, do you guys have any tools or recommendations on better DX when working with plpgsql, I've found the error messages difficult to interpret so far, especially when the issue is usually a simple syntax error.

[kevinmlong]

I'm running a self-hosted Supabase and Webhooks are currently not supported (see: https:/supabase/supabase/issues/12863). I noticed that this solution causes an issue if the work done by the webhook is inserting data to another table with a foreign key constraint on the record that was inserted causing the trigger. I believe this is because http_post is blocking and the row actually isn't returned until after that. Is there a way around this?

[drewbietron]

I'm running a self-hosted Supabase and Webhooks are currently not supported (see: https:/supabase/supabase/issues/12863). I noticed that this solution causes an issue if the work done by the webhook is inserting data to another table with a foreign key constraint on the record that was inserted causing the trigger. I believe this is because http_post is blocking and the row actually isn't returned until after that. Is there a way around this?

I see that your issue was resolved a few weeks ago in the CLI. Are you using that exact same function above for webhooks? I'm trying to track down how Supabase implements webhooks in their dashboard and their usage seems to return the old and the new record, but the example above just returns one or the other depending on if its insert, update or delete. Curious if you adapted your usage more than what was posted. Thanks!