Issue querying again

[SeaKrill]

Sorry to be a nuisance but I'm having another issue with the query function.

Trying to be able to check if an email already exists in the database on account creation. So far i've already set it up so it puts the email into the public profile of the user but it keeps turning up null again when i try to check it against the query.

remote func try_sign_up(email):
    print(email)
    var player_id = get_tree().get_rpc_sender_id()
    var player = get_parent().get_node("Players/" + str(player_id))
    var _email = yield(check_user(player, email), "completed")
    print(_email.data)

func check_user(player, email) -> DatabaseTask:
    var query : SupabaseQuery = SupabaseQuery.new().from("profiles").select(["email"]).Is("email", email, true)
    return player.database.query(query)

the database so far is just setup with a "profiles" table with a column called email where the users email is placed on sign up. am i setting up the query correctly?

I'm just trying to check, in profiles/email is there queried email. if yes print true, if not print false

[fenix-hub]

.Is("email", email, true) the third parameter is a negation check here, which means that if set to true it will check the opposite (select the column 'email' for all the rows where email value is DIFFERENT from the player's email)

[SeaKrill]

Tried .Is("email", email) too but it still comes out as null.

[fenix-hub]

Can you print _email.error in console to know if there's any error? As a best practice you could always check

if _email.error == null:
   # _email.data
else:
   # error

[SeaKrill]

empty >> JWSError (CompactDecodeError Invalid number of parts: Expected 3 parts; got 1): (empty)

[fenix-hub]

Did you authenticate before making database calls?

[SeaKrill]

How would I do that?

[SeaKrill]

If i understand correctly the database call is being made on a node that has its own auth and database node

[fenix-hub]

How would I do that?

Supabase.auth.sign_in()

[SeaKrill]

But wouldnt this require the user to sign in to an account first? Because I was trying to use this to check if an email was already used when a player was trying to create an account.

Or am I missing a feature on the sign_up function that would allow me to return a message to user that email already exists?

[fenix-hub]

If you sign_up with an already existing email, a coherent erorr will be returned, so you can check that way. Anyway database operation must be always authenticated with a JWT (so a logged user)

[SeaKrill]

I came up with this but when I try to create an account with an email that exists the error prints as null

remote func try_sign_up(email, password) -> SupabaseUser:
    var player_id = get_tree().get_rpc_sender_id()
    var player = get_parent().get_node("Players/" + str(player_id))
    var result: AuthTask = yield(player.auth.sign_up(email, password), "completed")
    print(result.error)
    return result

[SeaKrill]

When I print out the _error in auth task i do get null but then there is a second print that happens that pops up invalid_request >> refresh_token required if that has anything to do with it.

I am getting a 400 error on Supabases end in my api logs error:invalid_request, error_description:refresh_token required

[SeaKrill]

It seems this is an issue on Supabases end. https://github.com/supabase/auth/issues/1517

seems it returns faux info rather than an error now. Not sure how to use this on account creation or what best practices are when an account exists with email already as per that thread its a security issue.

[fenix-hub]

Honestly I wasn't aware of this change, so thank you for letting me know. I was planning to investigate on my SDK for eventual bugs, but looks like we can't do much about it. It truly is a limit in my opinion. Anyway regardless of what I think, I would suggest you to create a Database Function (not a Cloud Function since it is not necessary in my opinion) to check if an email exists.

[fenix-hub]

You can already call Database Function in this SDK through the /rpc API

[SeaKrill]

How would I go about writing this supabase function if you don't mind me asking?

so far ive gotten:

begin
  select exists (select * from profiles where profiles.email = _email);
end;

on supabase in a function that returns bool with one text argument called "_email"

while in godot I have

remote func try_sign_up(email, password, username):
    var player_id = get_tree().get_rpc_sender_id()
    var player = get_parent().get_node("Players/" + str(player_id))
    var result = yield(player.database.rpc("check_user", {"_email":email}), "completed")

But i keep getting an error empty >> JWSError (CompactDecodeError Invalid number of parts: Expected 3 parts; got 1): (empty)

not sure why, don't really know how to approach it.

[fenix-hub]

begin select exists (select * from profiles where profiles.email = _email); end;

Can you share the whole database function definition?

[SeaKrill]

create function public.check_user() 
returns bool
arguments _email text
language plpgsql 
security invoker set search_path = public
as $$
begin
  select exists (select * from profiles where profiles.email = _email);
end;
$$;

[fenix-hub]

empty >> JWSError (CompactDecodeError Invalid number of parts: Expected 3 parts; got 1): (empty) could mean that you are making the request without the JWT, since authentication is required for the function. You could try removing authentication for the database function

[SeaKrill]

So I think i've got a work around but theres still some bugs. I now sign the person in as anonymous when they try to create an account to access the database for the check but I now get this error. Which makes me feel like either my postgres isnt right or my rpc call but I think it solves my access issue.

42601 >> query has no destination for result data: Null (If you want to discard the results of a SELECT, use PERFORM instead.)

(gdscript) var check = yield(player.database.rpc("check_email", {"_email": email}), "completed")

alternatively I tried doing the anonymous sign up with the query method and get this error instead:

empty >> "failed to parse filter (is.myemail@email.com)" (line 1, column 4): unexpected "m" expecting null or trilean value (unknown, true, false) (empty)

(gdscript)

func check_user(player, email) -> DatabaseTask:
    var query : SupabaseQuery = SupabaseQuery.new().from("profiles").select().Is("email", email)
    return player.database.query(query)

which I think it means it has auth now but my code is incorrect somewhere

my database table:

[fenix-hub]

42601 >> query has no destination for result data: Null (If you want to discard the results of a SELECT, use PERFORM instead.) You are making a select exists query but don't store neither return the result in your function. Also, you don't actually need the select statement since the EXISTS operator already returns true/false. You could write your function as

create function public.check_user(email text) 
returns bool
language plpgsql 
security invoker set search_path = public
as $$
begin
  return exists (select * from profiles where profiles.email = check_user.email);
end;
$$;

[SeaKrill]

Got it working with

create function public.check_user(check_email text) 
returns text
language plpgsql 
security invoker set search_path = public
as $$
begin
  return exists (select * from profiles where profiles.email = check_email);
end;
$$;

otherwise you get an error in godot

SCRIPT ERROR: Invalid call. Nonexistent function 'empty' in base 'bool'.
          at: SupabaseDatabase._on_task_completed (res://Scripts/Database/database.gdc:63)

so changing bool to text prevents it. It still works as bool, it just pops an error in the debug.

Otherwise this works now! Thanks so much for taking so much time to help me out, I really appreciate it!

[fenix-hub]

Happy to know I could help you. Have a nice day!