Supabase•3y ago

Increment Not Working

Hello there! I am having a problem doing a basic task in supabase. I am trying to increment a number in a column but it doesn't seem to be working. I am very new to backend and a total beginner to SQL, so I may be doing something silly 😅

32 Replies

tsnakejakeOP•3y ago

And here is my code in JS:

const { data, error } = await supabase
        .rpc('increment', { discord_id: '143100912687251456' })

const { data, error } = await supabase
        .rpc('increment', { discord_id: '143100912687251456' })

tsnakejakeOP•3y ago

data and error both log null

garyaustin•3y ago

Do you have RLS on the table? You will need to meet it for update.

tsnakejakeOP•3y ago

I have RLS enabled, but with 0 active policies. I am using my supabase secret key to initialize my client, so it should be able to bypass RLS restrictions

garyaustin•3y ago

Do you also have a signed in user, or just the supabase client initialization and then your call?

tsnakejakeOP•3y ago

Just the supabase client initialization and then my call This is a Node.js backend I think it may be my increment function not working, because when I update directly like this... const result = await supabase.from("rankings").update({score: 5}).eq("user", "143100912687251456") It works perfectly fine

garyaustin•3y ago

Yeah sounds like it, I just glanced at as RLS is the usual culprit.

tsnakejakeOP•3y ago

Oh you were totally right, I needed to add RLS and do the supabase service key for any update to work It's just now I can't get the rpc call to work with increment, strange... OMGGGGGGGGGGGG I figured it out Turns out user is a built in keyword, and can't be used as a column name

garyaustin•3y ago

I just warned someone about that the other day.... It is so easy to miss.... I found it in theirs because they posted their code and I loaded in my Datagrip sql editor which flags that as an issue. You can put it in double quotes but that is a bad idea.

tsnakejakeOP•3y ago

Thank you for your help @garyaustin ! By the way @garyaustin , is there a way to set the score to 1 if it doesnt exist yet in the same increment procedure?

garyaustin•3y ago

update rankings set score = coalesce(score, 0) + 1 WHERE not_user = discord_id

tsnakejakeOP•3y ago

So the way to think of this in english is: - We're updating rankings table - We get all rows where not_user = discord_id - We set those filtered scores equal to 1 higher, or 1 This is my first experience with SQL, it's weird to me how the logic is actually in the wrong order Meaning the logic is to update rankings -> filter -> set But it's written as update rankings -> set -> filter

garyaustin•3y ago

coalesce is actually looking for a value in score and if null then uses the 2nd value

tsnakejakeOP•3y ago

Oh yes, I was meaning more how update, set, and where are written I haven't dealt with backend/SQL coding before but logically I would think you do update where and then set But it's written in a different order

garyaustin•3y ago

LOL you dare question a language that has been around 50 years.

tsnakejakeOP•3y ago

Hahahahahahha But why though 🤣 I have too much Javascript in my head Also I don't think that is working, it won't insert a new row

garyaustin•3y ago

Oh sorry thought it was just the column that was null

tsnakejakeOP•3y ago

Don't be sorry! No, the whole row doesn't exist in this case

garyaustin•3y ago

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-upsert/ you asked for it...

admin

PostgreSQL Tutorial

PostgreSQL Upsert Using INSERT ON CONFLICT statement

This tutorial shows you how to use the PostgreSQL upsert feature to insert or update data if the row that is being inserted already exists in the table.

garyaustin•3y ago

Basically you do the insert and then if it sees the id exists (conflict) you tell it to do the update instead.

tsnakejakeOP•3y ago

Getting some syntax error... debugging now

garyaustin•3y ago

Drop the table name after update, it knows if from the insert part and this is all one statement.

tsnakejakeOP•3y ago

One step closer How are you debugging this by the way? Is there a SQL editor

garyaustin•3y ago

That one I just looked at. If you post your code in triple backticks then others can more easily run a check on it in a full editor. I have Datagrip I use.

tsnakejakeOP•3y ago

create function increment(_discord_id text)
returns void as
$$
  INSERT INTO rankings (discord_id, score)
  VALUES (_discord_id, 1)
  ON CONFLICT (discord_id)
  DO
    UPDATE SET score = coalesce(score, 0) + 1
    WHERE discord_id = _discord_id
$$
language sql volatile;

create function increment(_discord_id text)
returns void as
$$
  INSERT INTO rankings (discord_id, score)
  VALUES (_discord_id, 1)
  ON CONFLICT (discord_id)
  DO
    UPDATE SET score = coalesce(score, 0) + 1
    WHERE discord_id = _discord_id
$$
language sql volatile;

Ahh like this Will check out Datagrip

garyaustin•3y ago

There are other free ones out there but I've not used them as that is a free extension for my IDE.

tsnakejakeOP•3y ago

I got it to work!!!

create function increment(_discord_id text)
returns void as
$$
  INSERT INTO rankings (discord_id, score)
  VALUES (_discord_id, 1)
  ON CONFLICT (discord_id)
  DO
    UPDATE SET score = rankings.score + 1
    WHERE rankings.discord_id = _discord_id
$$
language sql volatile;

create function increment(_discord_id text)
returns void as
$$
  INSERT INTO rankings (discord_id, score)
  VALUES (_discord_id, 1)
  ON CONFLICT (discord_id)
  DO
    UPDATE SET score = rankings.score + 1
    WHERE rankings.discord_id = _discord_id
$$
language sql volatile;

I don't really understand why I have to put rankings.discord_id but not SET rankings.score on the previous line How does it know the score column exists but not discord_id

garyaustin•3y ago

I don't understand that one... I'm not seeing it flagged as an error in my editor, but I also don't have your table, so it is flagging table name and all the columns anyway.

tsnakejakeOP•3y ago

Yeah that makes sense. Anyways, thank you for your help. I will investigate further

Gaming

Programming

Increment Not Working

Did you find this page helpful?