supabase db diff fails in several cases

[ff6347]

Bug report

Describe the bug

supabase db diff with simple setup
Migra fails with functions that have variable declarations.

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

When I create a new project and add a function (taken from the docs) the diff fails.

I ran the following steps:

$ supabase init
$ supabase start

Connect to the DB using TablePlus and add the following table and functions:

CREATE TABLE public.profiles (
    id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    username text
);


CREATE OR REPLACE FUNCTION public.handle_new_user ()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    SECURITY DEFINER
    AS $function$
BEGIN
    INSERT INTO public.profiles (id)
        VALUES (NEW.id);

    RETURN new;
END;
$function$;

-- trigger the function every time a user is created
CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW
    EXECUTE PROCEDURE public.handle_new_user ();

Then run the default diff

$ supabase db diff
> Error: unexpected end of JSON input

Adding --debug adds no further output.

---

If I run the diff with the --use-migra option it works.

$ supabase db diff --use-migra --file setup --schema public,auth
> Creating shadow database...
> Initialising schema...
> Diffing local database...
> Finished supabase db diff on branch main.

But when I add a function with a variable declaration the diff fails as well.

CREATE or replace FUNCTION public.do_something()
    RETURNS void
    LANGUAGE plpgsql
    SECURITY DEFINER
    AS $function$
    declare some_var text;
BEGIN
    some_var := 'hello, World!';
    RAISE NOTICE '%', some_var;
    RETURN;
END;
$function$;

$ supabase db diff --use-migra --file function --schema public
> Creating shadow database...
> Initialising schema...
> Applying migration 20220816141151_setup.sql...
> Error: ERROR: unterminated dollar-quoted string at or near "$function$
> BEGIN
> INSERT INTO public.profiles (id)
> VALUES (NEW.id)" (SQLSTATE 42601)

• Realted issue on the migra repo Supabase cli --migra plpgsql function is broken djrobstep/migra#212
• Realted gist https://gist.github.com/ff6347/57947013844be36fce122ef075460025

Expected behavior

The diff should be created without error

System information

• OS: macOS
• Supabase CLI v1.0.1

[sweatybridge]

Thank you for reporting this bug.

This turns out to be an issue with applying migrations where we used batched query locally. I've switched to simple query in v1.0.2 which sends the migration script as one string to local Postgres. #373

Let me know if the latest cli works for you.

[ff6347]

Hi @sweatybridge I installed v1.0.3 I can generate and apply migrations (tested locally) when using migra. The default diff command still gives me an error.

$ supabase db diff --debug
> Error: unexpected end of JSON input

[sweatybridge]

Thanks for confirming. I believe the default diff was broken due to a corrupted image when we transferred to ECR. I will work on a fix.

[ff6347]

FYI supabase db diff also works for me again. Thank you.

[rbkayz]

This was happening for me. All good on the JSON error. But now it's stuck here. Any help please?

PS C:\0xhashlabs\prod\hashmail-supabase> supabase db remote commit
Enter your database password:
⣾ Committing changes on remote database as a new migration...

░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░ 0%

[sweatybridge]

@rbkayz sorry I was a little caught up with fixing other issues. Is this still happening with v1.1.5? We updated initial schema to be consistent with hosted Supabase.

If doesn't help, please open a new issue and tell us more about your schema to help us investigate further.

[rbkayz]

We're all good.

1.15 works great.

Q though - is there a way to set a flag on the diff so that it only checks specific schemas? Generally I only need to diff public, auth and storage

The others like graphql, extensions add way too much random noise to the diff

[sweatybridge]

Hi @rbkayz , yes you can use supabase db diff --use-migra --schema public,auth,storage

[soedirgo]

Note that you shouldn't diff auth and storage - these are internal schemas and are managed separately.

[rbkayz]

Fair point. It's just that I have some RLS policies in storage. And some triggers in auth.users on insert

Which I need to diff in migrations so that it gets automatically pushed to my staging and prod projects

Would you recommend a better way please?

[soedirgo]

Hmm, I think if you only keep the storage RLS policies and auth.users triggers, you should be fine 👍