Hi all! I am just trying to use Supabase for the first time. Is someone able to help teach me some of the basics? I'm not sure if this should be in the "JavaScript" channel or here, but I am building a small app with Javascript to fetch data from an API and store it into a table. I have little experience with backend, so I am hoping to get some help as I am having some trouble wrapping my head around documentation!

Hi all! I am just trying to use Supabase for the first time. Is someone able to help teach me some of the basics? I'm not sure if this should be in the "JavaScript" channel or here, but I am building a small app with Javascript to fetch data from an API and store it into a table. I have little experience with backend, so I am hoping to get some help as I am having some trouble wrapping my head around documentation!

Thanks! Nice to meet you.

My frontend is not very good though I've previously done the svelte tutorials about 6 months ago. So I used the getting started with svelte guide and copied it as close as possible and had a positive experience.

I am just working with vanilla JavaScript at the moment.

So you will need to create a table for that.

I want to store this data for later use... Now the tricky part for me to understand is that I have this running on a CRON job, so it will be doing this every hour. I won't want to create a new row each time the CRON runs, but instead I want to check if the name exists in the DB already and if it does, then append the price and volume to the columns for that, so I think I would need to have that be a JSON object for those columns?

the backend of supabase is postgres. What you describe is a upsert Below is a snippet from the svelte guide

js
  async function updateProfile() {
    try {
      loading = true
      const user = supabase.auth.user()

      const updates = {
        id: user.id,
        username,
        website,
        avatar_url,
        lat,
        lon,
        location_interior_pnt,
        location_the_geom,
        updated_at: new Date(),
      }

      let { error } = await supabase.from('profiles').upsert(updates, {
        returning: 'minimal', // Don't return the value after inserting
      });

      if (error) throw error
    } catch (error) {
      alert(error.message);
    } finally {
      loading = false;
    }
  }

The goal is that on the front end I can eventually fetch data by Name and I will be able to create charts based on all of the stored prices and volumes.. I was thinking it should look something like this:

Name: FreakDJ 
    Price: { 50.5, 55.7, 60, 58.2 } 
    Volume: { 302, 393.2, 309.2 }

sounds good.

My code looks something like this:

data.results.forEach((x) => {
          const Name = x.name;
          const currentPrice = x.Price.toFixed(2);
          const allTimeVolume = x.txVolume.toFixed(2);

          if ( Name === "exampleName") {
            supabase.from("TestDB").insert([
              {
                Name: Name,
                Price: currentPrice,
                Volume: allTimeVolume,
              },
            ]);
          }
        });

So the table is called "TestDB"

Yeah, just testing for now

Are you getting an error?

I am not seeing one, but I wasn't sure how to output one either

I got to head back to work. though happy to help out over the weekend.

Have a nice time at work. Hopefully its a nice day for you. Thanks for taking a look! I will keep trying some things out and try to get some output working properly.

If you are testing make sure you turn off RLS on any table and get it working, then learn policies. RLS can fail silently in many cases.

RLS is not enabled on my table currently - is that what you mean by turning it off?

Yes.

Good, that should be disabled for my testing then! I will look into policies once I can get this working.

Just glancing at your code I don't see an await before the supabase call or a .then() after

Trying to add await before gives me a SyntaxError: Unexpected reserved word

So supabase calls return promises and are async. You can do .then((response)=>{console.log(response)}) if not in an async function (only place you can use await)

Ah that response allows me to see an error message

It is also required to actually run the the supabase call. Other wise you are just setting it up.

The error is message: 'column "Volume" of relation "TestDB" does not exist' but I am seeing it in my table?

just remember if you are doing base javascript, you need to have a .then() instead of await with any supabase call unless you are in an async function already.

Thank you , I will note that!

also you can look at the network and should see get/post with response data to dobule check.

But thats the point of me trying to build these projects so I can learn more about how they work!

At this point you need to learn that as everything is going async.

So my understanding is upsert will insert data if it doesn't exist, and update it if it does; however, I don't want to override what I have but rather append the new data to the price and volume columns. Is this possible with upsert?

you need a unique key for upsert

the name should be unique

yes.

I am hoping to have one row per name - and then in that row just append price and volume to a json object each hour

you could append the price and volume array on a update trigger though the db will need to read / update that row every time.

So I don't want upsert but rather read and update the row?

I understand that now. Though a custome update trigger can do what you want. You will be able to do new.price = old.price || new.price -- use || as its the the postgres array function as per https://www.postgresql.org/docs/13/functions-array.html

Hmm I am not sure I am understanding... I think I get the concept in english but not sure how to translate it to code

javascript
//Goal: if row with 'name' exists, append price to json array and volume to json array; else insert new row

//how can I check if row with name exists already before trying to update? 
if ( name exists ) {
   supabase.from('TestDB').update({ /* also not sure how to just append it to the array here */ }).eq('name', name).then((response) => { console.log(response) });
} else {
// insert data for the first time
}

So you have 3 ways to approach this. 1) just write a new row with your 3 values every hour. Then deal with putting the data together with reads. 2) read and then update an array or json column. Arrays of ints/strings are supported as a column type. 3) write an rpc function that does array appends in SQL which are supported. Knowledge of postgres/supabase go up with each, but last one is probably most performant depending on what you really are doing. Not sure what your plan is for max items in an array as far as reading and writing huge amounts of data at some point.

I am thinking I’ll have around like 100-200 unique “names”, that will fetch the price and volume hourly… and I will need to have the data setup from the database so that I can create a chart for price vs time and volume vs time… of the three ways you mention which might be the preferred way? Also I hadn’t considered data cleanup to be honest, so I might end up filling the db quickly unless I remove older data at some point, but I’d want to keep as much as possible I think….

I'm thinking your approach might be flawed on just storing data values if you are dealing with something like stock. What if API is down, database goes down, internet issues etc. Now you have gaps of missing data, but no timestamp to reconcile they are missing.

What do you propose as a solution? By the way I am more using it to learn than making a product that I will sell and/or needs to be perfect so if for some reason something went down for a brief time I think it would be ok

I encourage you to try the many rows approach you describe its the way relational databases like postgres are designed to be used. Having many lean rows would not be much larger than a few bloated rows.

I agree with @User that you should do row per, especially if just experimenting as it is the "right" way. The idea of reading and then updating an ever growing array is not going to work well long as all the data is transferred for every update 2x. There certainly are other approaches like "daily" rows with 24 entry arrays, that are comprises on #rows and reasonable size but postgres is easily able to handle millions of small rows.