How to Enable Realtime via CLI / Bash

[ghost]

Hello! We're building our MVP using Supabase. Our structure is that each one of the companies that pay to use our platform will be containerized, running in their own virtual instance with their own version of Supabase locally installed in that VM. Our biggest concern is how we're going to manage updates and database changes.

I've been able to use pgAdmin and its Schema Diff tool to handle migrations manually between databases, by pasting the changes into the browser's SQL Editor. I'd assume I can automate this, and I've seen some forum posts showing it.

However, it seems like realtime gets disabled (or never gets enabled) when a new table is imported (even if it is enabled in the database that the schema changes are coming from). I will say that I am using the public schema; if there is another schema that handles realtime, I could just be missing that one.

TLDR; Is there a way to enable realtime for a table via the Supabase CLI? I'm looking to automate the process using a bash script.

Thanks for your help!!

[gregnr]

Hey @nbarrow-inspire-labs! Sorry to hear you're hitting this. Just to confirm, does the PG publication persist after performing your migrations?

https://supabase.com/docs/guides/realtime/extensions/postgres-changes#replication-setup

Each table will also need to be added to that publication:

-- add a table to the publication
alter
  publication supabase_realtime add table messages;

[silentworks]

You can create a migration file with the above code from @gregnr that you run on each instance through your setup process with the supabase cli.

[ghost]

@gregnr @silentworks thank you both for the tips, I'll give it a shot!

[ghost]

I took a look, and I think I could get something like that working. Forgive me, I'm new to SQL, always worked with something like MongoDB in the past and never had to dive under the hood. Is there a way to automatically add the entire public schema or all the tables in the public schema? Would something like this work (I have no idea if this syntax would work):

Edit (based on this, https://www.postgresql.org/docs/current/sql-alterpublication.html, I tried what's below, but I get the error: syntax error at or near "TABLES"):

-- add a table to the publication
alter
  publication supabase_realtime add tables in schema public;

[gregnr]

@nbarrow-inspire-labs I just went down the rabbit hole trying to make sense of this one. I was able to reproduce the same error syntax error at or near "TABLES" on PG v15.1 when executing from the studio SQL editor.

Turns out there is some minor sanitization happening in the studio sql editor which I believe is preventing this (this should get fixed at some point - see constructive-io/pgsql-parser#93). However even if you connect to your DB directly (eg. via psql CLI), you will get: ERROR: must be superuser to add or set schemas. Seems like superuser is required to perform this, which isn't available at this time for security reasons (see https://github.com/orgs/supabase/discussions/9314).

For now, looks like we'll have to add the tables 1-by-1. We can create a function to help with this (stolen from @mansueli & modified slightly):

CREATE OR REPLACE FUNCTION execute_schema_tables(_schema text, _query text)
RETURNS text AS
$$
DECLARE
row record;
BEGIN
FOR row IN SELECT tablename FROM pg_tables AS t
WHERE t.schemaname = _schema
LOOP
-- run query
EXECUTE format(_query, row.tablename);
END LOOP;
RETURN 'success';
END;
$$ LANGUAGE 'plpgsql';

And then:

SELECT execute_schema_tables('public', 'ALTER PUBLICATION supabase_realtime ADD TABLE %I;');

Note that you will need to re-run this when new tables are added.

Let me know if this helps!

[ghost]

@gregnr you're a legend! Really appreciate the help, that worked like a charm. I managed to get this working using GitHub Actions, modifying the ones used in the official Supabase tutorial and this third-party tutorial. I'll drop my working model below for anyone else who stumbles across this post:

1. Create a new migration (supabase migration new realtime_function) and add @gregnr 's function to the table (slightly modified for diff-ing, this was the output from supabase db diff -f realtime_function in my case as I added the function manually in the studio):

{timstamp}_realtime_function.sql

set check_function_bodies = off;

CREATE OR REPLACE FUNCTION public.executeschematables(_schema text, _query text)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
DECLARE
row record;
BEGIN
FOR row IN SELECT tablename FROM pg_tables AS t
WHERE t.schemaname = _schema
LOOP
-- run query
EXECUTE format(_query, row.tablename);
END LOOP;
RETURN 'success';
END;
$function$
;

2. create an additional job based on the Supabase tutorial linked above, waiting for that first job (to merge in any diffs) to complete (to ensure the executeSchemaTables function exists).

# https://remarkablemark.org/blog/2021/03/14/setup-postgresql-in-github-actions/
  realtime:
    needs: migration # wait for migration job to finish (i.e., to install the executeSchemaTables function)
    runs-on: ubuntu-latest

    container: node:latest

    services:
      postgres:
        image: postgres:latest
        env:
          POSTGRES_DB: postgres
          POSTGRES_PASSWORD: ${{ secrets.DEVELOPMENT_DB_PASSWORD }}
          POSTGRES_PORT: 5432
          POSTGRES_USER: postgres
        ports:
          # maps tcp port 5432 on service container to the host
          - 5432:5432
        # set health checks to wait until postgres has started
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - name: Install PostgreSQL client
        run: |
          apt-get update
          apt-get install --yes postgresql-client

      # queries database with postgres client
      - name: Enable Realtime on all Tables in Schema 'public'
        run: psql -h db.$PROJECT_ID.supabase.co -p $POSTGRES_PORT -d $POSTGRES_DB -U $POSTGRES_USER -c "begin; drop publication if exists supabase_realtime; create publication supabase_realtime; commit; SELECT executeSchemaTables('public', 'ALTER PUBLICATION supabase_realtime ADD TABLE %I;');"
        env:
          PGPASSWORD: ${{ secrets.DEVELOPMENT_DB_PASSWORD }}
          PROJECT_ID: ${{ secrets.DEVELOPMENT_PROJECT_ID }}
          POSTGRES_DB: postgres
          POSTGRES_PASSWORD: ${{ secrets.DEVELOPMENT_DB_PASSWORD }}
          POSTGRES_PORT: 5432
          POSTGRES_USER: postgres

For reference, here is my complete development.yaml:

name: Update Supabase Development Schema

on:
  push:
    branches:
      - development
  workflow_dispatch:

jobs:
  migration:
    runs-on: ubuntu-latest

    env:
      SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
      SUPABASE_DB_PASSWORD: ${{ secrets.DEVELOPMENT_DB_PASSWORD }}
      PROJECT_ID: ${{ secrets.DEVELOPMENT_PROJECT_ID }}

    steps:
      - uses: actions/checkout@v3

      - uses: supabase/setup-cli@v1
        with:
          version: latest

      - name: supabase link
        working-directory: ./inspire-tms/frontend
        run: supabase link --project-ref $PROJECT_ID

      - name: supabase push
        working-directory: ./inspire-tms/frontend
        run: supabase db push

  # https://remarkablemark.org/blog/2021/03/14/setup-postgresql-in-github-actions/
  realtime:
    needs: migration # wait for migration job to finish (i.e., to install the helper function)
    runs-on: ubuntu-latest

    container: node:latest

    services:
      postgres:
        image: postgres:latest
        env:
          POSTGRES_DB: postgres
          POSTGRES_PASSWORD: ${{ secrets.DEVELOPMENT_DB_PASSWORD }}
          POSTGRES_PORT: 5432
          POSTGRES_USER: postgres
        ports:
          # maps tcp port 5432 on service container to the host
          - 5432:5432
        # set health checks to wait until postgres has started
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - name: Install PostgreSQL client
        run: |
          apt-get update
          apt-get install --yes postgresql-client

      # queries database with postgres client
      - name: Enable Realtime on all Tables in Schema 'public'
        run: psql -h db.$PROJECT_ID.supabase.co -p $POSTGRES_PORT -d $POSTGRES_DB -U $POSTGRES_USER -c "begin; drop publication if exists supabase_realtime; create publication supabase_realtime; commit; SELECT executeSchemaTables('public', 'ALTER PUBLICATION supabase_realtime ADD TABLE %I;');"
        env:
          PGPASSWORD: ${{ secrets.DEVELOPMENT_DB_PASSWORD }}
          PROJECT_ID: ${{ secrets.DEVELOPMENT_PROJECT_ID }}
          POSTGRES_DB: postgres
          POSTGRES_PASSWORD: ${{ secrets.DEVELOPMENT_DB_PASSWORD }}
          POSTGRES_PORT: 5432
          POSTGRES_USER: postgres

Edit:
Additionally, to get this working in my local environment, I just added @gregnr 's function to my seed.sql:

-- enable realtime
set check_function_bodies = off;

CREATE OR REPLACE FUNCTION public.executeschematables(_schema text, _query text)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
DECLARE
row record;
BEGIN
FOR row IN SELECT tablename FROM pg_tables AS t
WHERE t.schemaname = _schema
LOOP
-- run query
EXECUTE format(_query, row.tablename);
END LOOP;
RETURN 'success';
END;
$function$
;

SELECT executeSchemaTables('public', 'ALTER PUBLICATION supabase_realtime ADD TABLE %I;');


-- seed the users
insert into auth.users (instance_id, id, aud, role, email, encrypted_password, raw_app_meta_data, raw_user_meta_data, email_confirmed_at, created_at)
  values ('00000000-0000-0000-0000-000000000000', '185f2f83-d63a-4c9b-b4a0-7e4a885799e2', 'authenticated', 'authenticated', 'nbarrow@inspiretmstech.com', '$2a$10$LrLmeT5Nv5XDBFtm9SezqOuq4impeQtkZAWuXm27OL.yh64aMf9CO', '{"provider":"email","providers":["email"]}', '{}', timezone('utc'::text, now()), timezone('utc'::text, now()));

insert into auth.identities (id, user_id, identity_data, provider, created_at)
  values ('185f2f83-d63a-4c9b-b4a0-7e4a885799e2', '185f2f83-d63a-4c9b-b4a0-7e4a885799e2',	'{"sub": "185f2f83-d63a-4c9b-b4a0-7e4a885799e2"}', 'email', timezone('utc'::text, now()));

insert into public.profiles (user_id, first_name, last_name, full_name)
  values
    ('185f2f83-d63a-4c9b-b4a0-7e4a885799e2', 'Nicholas', 'Barrow', 'Nicholas Barrow');

[gregnr]

@nbarrow-inspire-labs glad it helped! Thanks for the follow up.

I've renamed the above function for better DB casing (executeSchemaTables -> execute_schema_tables)

[igoramidzic]

This solution may be out of date.
For anyone who is following this example and is getting errors:

failed to send batch: ERROR: function public.execute_schema_tables(unknown, unknown) does not exist (SQLSTATE 42883)
Solution: Move the CREATE OR REPLACE FUNCTION script to a migration file.

1. Create a new migration file with: supabase migration new enable-realtime
2. Add the CREATE OR REPLACE FUNCTION script to that new migration file.
3. See if that worked with supabase db reset
4. If error persists, in your seed.sql file, alter the SELECT statement by prepending public. to the function name:

SELECT public.execute_schema_tables('public', 'ALTER PUBLICATION supabase_realtime ADD TABLE %I;');

Note: someone with decent plpgsql knowledge can likely figure out why I needed to make this change. I have 0 (ZERO) postgres experience, I'm just trying out Supabase for the first time, and Postgres as well. But, I think there is a delay in when the function is created and when we call that Select statement. So, moving the create function statement into the migration gives it enough time to "settle"? Idk... seems like it worked at some point.

---

failed to send batch: ERROR: relation "table_name" does not exist (SQLSTATE 42P01)
Solution: Alter the SELECT statement in your seed.sql file by prepending public. to %I

SELECT public.execute_schema_tables('public', 'ALTER PUBLICATION supabase_realtime ADD TABLE public.%I;');