Subscribing to Database Changes

Listen to database changes in real-time from your website or application.

You can use Supabase to subscribe to real-time database changes. There are two options available:

Broadcast. This is the recommended method for scalability and security.
Postgres Changes. This is a simpler method. It requires less setup, but does not scale as well as Broadcast.

Using Broadcast#

To automatically send messages when a record is created, updated, or deleted, we can attach a Postgres trigger to any table. Supabase Realtime provides a realtime.broadcast_changes() function which we can use in conjunction with a trigger. This function will use a private channel and needs broadcast authorization RLS policies to be met.

Broadcast authorization#

Realtime Authorization is required for receiving Broadcast messages. This is an example of a policy that allows authenticated users to listen to messages from topics:

1
create policy "Authenticated users can receive broadcasts"
2
on "realtime"."messages"
3
for select
4
to authenticated
5
using ( true );

Create a trigger function#

Create a function to call whenever a record is created, updated, or deleted. This function will make use of some of Postgres's native trigger variables. For this example, we want to have a topic with the name topic:<record id> to which we're going to broadcast events.

1
create or replace function public.your_table_changes()
2
returns trigger
3
security definer
4
language plpgsql
5
as $$
6
begin
7
  perform realtime.broadcast_changes(
8
    'topic:' || coalesce(NEW.id, OLD.id) ::text,       -- topic - the topic to which you're broadcasting where you can use the topic id to build the topic name
9
    TG_OP,                                             -- event - the event that triggered the function
10
    TG_OP,                                             -- operation - the operation that triggered the function
11
    TG_TABLE_NAME,                                     -- table - the table that caused the trigger
12
    TG_TABLE_SCHEMA,                                   -- schema - the schema of the table that caused the trigger
13
    NEW,                                               -- new record - the record after the change
14
    OLD                                                -- old record - the record before the change
15
  );
16
  return null;
17
end;
18
$$;

Create a trigger#

Set up a trigger so the function runs after any changes to the table.

1
create trigger handle_your_table_changes
2
after insert or update or delete
3
on public.your_table
4
for each row
5
execute function your_table_changes ();

Listening on client side#

Finally, on the client side, listen to the topic topic:<record_id> to receive the events. Remember to set the channel as a private channel, since realtime.broadcast_changes uses Realtime Authorization.

1
import { createClient } from '@supabase/supabase-js'
2
3
const supabase = createClient('your_project_url', 'your_supabase_api_key')
4
5
// ---cut---
6
const gameId = 'id'
7
await supabase.realtime.setAuth() // Needed for Realtime Authorization
8
const changes = supabase
9
  .channel(`topic:${gameId}`, {
10
    config: { private: true },
11
  })
12
  .on('broadcast', { event: 'INSERT' }, (payload) => console.log(payload))
13
  .on('broadcast', { event: 'UPDATE' }, (payload) => console.log(payload))
14
  .on('broadcast', { event: 'DELETE' }, (payload) => console.log(payload))
15
  .subscribe()

Using Postgres Changes#

Postgres Changes require minimal setup, but have some limitations as your application scales. We recommend using Broadcast for most use cases.

Enable Postgres Changes#

You'll first need to create a supabase_realtime publication and add your tables (that you want to subscribe to) to the publication:

1
begin;
2
3
-- remove the supabase_realtime publication
4
drop
5
  publication if exists supabase_realtime;
6
7
-- re-create the supabase_realtime publication with no tables
8
create publication supabase_realtime;
9
10
commit;
11
12
-- add a table called 'messages' to the publication
13
-- (update this to match your tables)
14
alter
15
  publication supabase_realtime add table messages;

Streaming inserts#

You can use the INSERT event to stream all new rows.

1
// @noImplicitAny: false
2
import { createClient } from '@supabase/supabase-js'
3
4
const supabase = createClient('your_project_url', 'your_supabase_api_key')
5
6
// ---cut---
7
const channel = supabase
8
  .channel('schema-db-changes')
9
  .on(
10
    'postgres_changes',
11
    {
12
      event: 'INSERT',
13
      schema: 'public',
14
    },
15
    (payload) => console.log(payload)
16
  )
17
  .subscribe()

Streaming updates#

You can use the UPDATE event to stream all updated rows.

1
// @noImplicitAny: false
2
import { createClient } from '@supabase/supabase-js'
3
4
const supabase = createClient('your_project_url', 'your_supabase_api_key')
5
6
// ---cut---
7
const channel = supabase
8
  .channel('schema-db-changes')
9
  .on(
10
    'postgres_changes',
11
    {
12
      event: 'UPDATE',
13
      schema: 'public',
14
    },
15
    (payload) => console.log(payload)
16
  )
17
  .subscribe()