1

My goal is to see if a row exists with the user's email. If it exists, do nothing. If it does not exist, create it. I've tried a few variations of the function below, but it fails every time when I use OAuth, printing an error that a row with that column value does not exist, which is to be expected. Instead of making the document since it doesn't exist, the whole function just stops after that error.

I understand that this is because there is no matching row to select() but there is also no way to just check if a row exists. With Firebase there was a simple function to check if a document existed, so I'm surprised to see that Supabase does not have this. Here is what I have now:

    try {
      Map<String, dynamic>? currentUserFromDB = await Supabase.db
          .from('profiles')
          .select()
          .eq('email', user!.email.toString())
          .maybeSingle();
      bool userExistsInDB = currentUserFromDB != null;
      if (!userExistsInDB) {
        await Supabase.db.from('profiles').insert({
          'uid': user.id,
          'email': user.email,
        }).catchError((error) {
          print('Error: $error');
        });
      } else {
        print('User exists in db');
      }
    } catch (e) {
      print(e);
    }

Profiles table:

  • uid (Primary key, text)
  • email (text)

Row Level Security:

  • Enable delete for users based on uid
  • Enable read access for all users
  • Enable update for users based on uid
Globe
  • 169
  • 11
  • Presuming that column is indexed, that'll be close to a constant-time lookup, so I'm not sure what the question is. Also, if you're willing to ignore the expected duplicate key error, you can just always attempt to insert the row and if it fails, you know it was already there. – Randal Schwartz Dec 23 '22 at 01:53
  • In Firebase there was a function like `FirebaseDB.document('name').exists` which returns a `bool`. My question was if something like this exists for Supabase or how to achieve the same thing and the code I put in the question was my attempt at that. I may try your suggestion to just always try to create the document but if that throws an error I think it will also stop the parent function from continuing. – Globe Dec 23 '22 at 02:35
  • As far as checking whether a row exists or not, I think you are doing it the right way, and I don't see anywhere that might throw an error, but what kind of errors are you seeing? – dshukertjr Dec 26 '22 at 07:26

1 Answers1

0

You could probably replace the whole code that you have there with a single upsert assuming uid is the primary key of your profiles table.

await Supabase.db.from('profiles').upsert({
  'uid': user.id,
  'email': user.email,
});
dshukertjr
  • 15,244
  • 11
  • 57
  • 94
  • Now, with this code, I'm not getting any errors but the row is not being added when it doesn't exist. I can only test it on web right now because the OAuth deeplink is not working on mobile. – Globe Dec 27 '22 at 06:46
  • @Globe So it's not doing anything when the row does not exist? Could you update the question and add the schema of your `profiles` table? Also what row level security do you have on the `profiles` table? – dshukertjr Dec 27 '22 at 07:19
  • I added that information. I just enabled row level security yesterday and this issue started before that so I don't think it's the cause. – Globe Dec 27 '22 at 07:28
  • @Globe Thanks for the additional info. I presume you are doing something like `auth.uid() = uid` in your security policy, but `uid` column should be type `uuid` and not `text` for it to work. Also, you need to set `insert` policy to allow new rows to be created. – dshukertjr Dec 27 '22 at 07:53
  • I am using `(((uid())::character varying(36))::text = uid)` which compares the `uuid` as `text`. This is because I need the `uid` stored as a string and I know it works. You were right that I forgot an insert policy but even with that, it does not add a row. – Globe Dec 27 '22 at 19:42
  • @Globe If a row is not being inserted, you should at least see some errors. Are you seeing any errors in your catch statement? – dshukertjr Dec 28 '22 at 00:48