How can I insert un-exist record and skip exist record in Supabase?

Question

How can I use insert but skip the records I already have in my Supabase?

I have a very huge records that I've done scraping from it (over 3000 records, exactly is 3393 records)

I thought that INSERT will automatically skip the existed records in my Supabase but seem it isn't, I've created the same table data and let the code run twice instead of once and its records are double my existing records, so I know it's duplicate the records.

Here's the code I'm using INSERT into Supabase in ReactJS (animeData is from the JSON I've already done scraping):

/* eslint-disable react-hooks/exhaustive-deps */
import './index.css'
import { useEffect } from 'react'
import { supabase } from './supabaseClient'
import animeData from './data.json'

export default function App() {

   useEffect(async () => {
// POST Anime List!!!!
    for(let i = 0; i < animeData.length; i++){
      const { data, error } = await supabase
      .from('test')
      .insert([
        {anime_title: animeData[i].animeTitle, anime_image: animeData[i].animeImg, anime_url: animeData[i].animeUrl}
      ])
    }
    
  }, [])

  return (
    <div className="container">
    </div>
  );
}

Can I somehow SELECT existing records and skip them?

I used to create a model with the same function as this but in Back-end using MySQL, but I don't know how to write the same of it in Supabase (I think Supabase is using PostgreSQL)

The same function in the model that I've used to create, Sign Up model example:

const User = function (user) {
    this.username = user.username;
    this.password = user.password;
};
// SIGN UP
User.signup = async (newUser, result) => {
    let sql = `SELECT username FROM users WHERE username ="${newUser.username}"`;

    db.query(sql, async function (err, data) {
        if (data.length !== 0) {
            return result(err, { data: "User already exists" });
        } else {
            await db.query("INSERT INTO users SET ?", newUser, (err, data) => {
                if (err) {
                    result(err, null);
                    return;
                } else {
                    return result(null, { data: "success" });
                }
            });
        }
    });
};

I want to write something like this in Supabase but I don't know how. I've read the INSERT Supabase but I'm still clueless.

score 2 · Accepted Answer · answered Jan 11 '22 at 08:34

You want to check if you record exist, if yes you update it, if no you create it.

how to check if record exist, try to select it from your db, if the result is empty, it does not exist

 const { data:record, error } = await supabase
    .from("test")
    .select("*")
    .eq("column_name", name);

If the record already exists,update it with upsert : https://supabase.com/docs/reference/javascript/upsert

const { data, error } = await supabase
  .from('test')
  .upsert({ name: 'new name' }, { onConflict: 'id' })

Thanks, I've found a solution a few days ago, but your ways seem better than what I am doing right now, I'm using `select('*')` then `match`, it's kind of slow I guess, I'll try it later on. — Bunny, Jan 11 '22 at 08:37

score 0 · Answer 2 · answered Jun 14 '23 at 03:31

So, another solution would be to just check for a conflict:

This will work for both insert and update

    const { error } = await supabase
      .from('profiles')
      .upsert([values], { onConflict: 'user_id' })
      .eq('user_id', userId);

For me, I didn't really want to check for a record, I just want to either add to the table or overwrite the row based on the user_id

How can I insert un-exist record and skip exist record in Supabase?

2 Answers2