How to update price from multiple merchants via API?

[brandon8]

I’ve been searching for days and am dumbfounded that I haven’t been able to find any information on this topic:

I’m wanting to create a page with a repeating group that displays desktop PC’s from numerous merchants via API access. From my understanding you can’t display more than one API call in an RG so I’m guessing the best thing to do is have some type of workflow that is regularly pulling a list of products from the various merchant API’s and saving the results in my Bubble database.

The end result would be to display a list of thousands of available PC’s and the user can sort them by storage space, memory, GPU, etc and of course price. Since prices are updating on a daily basis for these types of products, how would I go about updating these products with the latest price and what would the recommended db structure look like?

[ankur1]

So you can set up the backend workflow to regularly fetch the data and before insertion check data is already available in the DB or not. If it is available, update only the pricing otherwise add the data.

[boston85719]

Most API providers give you parameters for sorting and filtering. These fields usually are very helpful in setting up a sort and filter feature in your Bubble app.

When I do this in my apps, I have everything setup for filtering and sorting and then the RG has the datasource set to the API provider with the parameters for sorting and filtering set as dynamic content.

I’m able to sort and filter as well as do pagination on with the API calls, never needing to store them in my DB.

[boston85719]

@brandon8

@ankur1

So you can set up the backend workflow to regularly fetch the data

It sounds like a suggestion to have a recurring event setup to do API calls. This would result in unnecessarily running API calls at regular intervals which wouldn’t be ideal.

@ankur1

before insertion check data is already available in the DB or not

This would require querying the DB unnecessarily. To optimize an application performance, a rule of thumb I follow is to touch the DB as little as possible. In the event that I am working with API data, I don’t incorporate my DB into this, and instead just pull the data from the API when I need the data from the API.

@ankur1

If it is available, update only the pricing otherwise add the data

This would be another instance of touching the database unnecessarily and combining it with running workflows.

It is always best in my opinion to work with the data from the API using the API calls, unless there is some kind of fee per API call, then I would consider storing those values in my app DB.

When saving API data in an DB I save it as the type of data returned by the API…in the screen shot below I have two text fields, but two fields that are of the type of data returned by the API.

This text element below is inside a repeating group searching my DB for this data type of Airplane Listing and is referencing one of the data fields of the API data type and I can access from it all the fields associated.

I personally have never tested using an API in which a data field on it might be updating regularly and after I have originally saved it to the DB, so I would be very interested to find out if the updated fields return with the values they were when saved to the DB or the updated values…my assumption is the field would be basically static and return the value at the time I saved it.

In any event, I always try to reduce the number of workflows and database calls in my apps. In this sort of situation I rely on performing the sorting and filtering on the API call when a user needs the data.

Here is a screen shot of a dynamic sort and filter on an API call.

Screen shot of the pagination workflow

The repeating group gets updated automatically with the data from the API in the same way it does when I do pagination on entries from my DB and I add some new values for the constraints.

Learn More

Boston85719 is an expert Bubbler with a decade experience as an educator. Real name Matthew, he has been actively building SaaS apps, marketplace apps, scheduling apps and more for clients, himself and for sale as templates.

As an official Bubble Bootcamp Instructor, he leads Bubble Bootcamps on a regular basis.

Always willing to offer advice via the Bubble Forum, Matthew also offers Private Personal and Group Training Sessions.

Through his site, NoCodeTrainer, Matthew provides a range of tutorials with editor access to help you jumpstart your Bubble development.

Always accessible you can send Matthew a private message via the forum or send an email directly with your requests.

Be sure to checkout the Stripe Integration Course when you are ready to integrate Stripe payments to start monetizing your application via product sales or subscriptions.

[brandon8]

@boston85719

Wow thanks so much for the in-depth response! I guess I have one question and two concerns:

Q. Say I scale and have 10,000 people on the site at once doing a bunch of product searches and for every search someone is calling an API, is that faster or slower than storing (most) of the products in my database with a backend workflow running once per minute/hour checking for price differences?

Concerns
The reason I have been leaning towards keeping a bulk of the info in my database is because:

1. I’m under the impression that I can only have one API source in a RG. Plus, while these different API feeds will largely give me the same parameters, they word them differently. So by adding the products to my database, I now have a more unified product catalog.

2. I absolutely need to be able to link back to these products via my own affiliate link. By saving these products from the API to my database, I can have a workflow that looks at the product URL provided by the API, sends that URL to my affiliate link services via their API, and then adds that link to the “Affiliate URL” field I’d create in that data type. I would imagine this would not be possible if I strictly used the API data, am I right?

Two sites I look at for inspiration:

• CamelCamelCamel - They only serve data from Amazon so really it’s just a massive site with a bunch of unique ways of searching and filtering through Amazon’s Product API. Although, I imagine they are still saving these products to their own database and that allows them to keep saving the price data for the historical price tracking.

• PCPartPicker - A little different from what I’m going for, but a site like this wouldn’t be possible without saving products as their own data type otherwise you’d have duplicate products all over the place. For example, a Ryzen 2700X is sold at virtually ever merchant, but there is only one listing on the site for the 2700X and in that single listing they are constantly pulling price data for the product from several merchants for the user to compare.

So yeah, the value of having a single RG that searches for a certain type of product from multiple merchants is what I am trying to achieve as that is where the real value to the user comes in.

[boston85719]

@brandon8

Say I scale and have 10,000 people on the site at once doing a bunch of product searches and for every search someone is calling an API, is that faster or slower than storing (most) of the products in my database with a backend workflow running once per minute/hour checking for price differences?

Think about it this way. What is faster, doing a search of the database or doing an API call…in most instances the API call would be slightly slower due to the 400ms that would likely require the data being request and sent from the API database to your app…BUT what is slower, doing searches of the database and as well doing a bunch of once per minute/hour checking for price differences or just doing an API call…the API call would be a lot less work for your application — this is kind of why I said the way to optimize the application is to just do the API call and filter/sort on it…

@brandon8

• I’m under the impression that I can only have one API source in a RG. Plus, while these different API feeds will largely give me the same parameters, they word them differently. So by adding the products to my database, I now have a more unified product catalog.

I was under the impression this was all from one single API, and not a group of different API calls. So if you are using a bunch of different APIs to get the products then maybe setup to store those values on your database.

@brandon8

I absolutely need to be able to link back to these products via my own affiliate link. By saving these products from the API to my database, I can have a workflow that looks at the product URL provided by the API, sends that URL to my affiliate link services via their API, and then adds that link to the “Affiliate URL” field I’d create in that data type. I would imagine this would not be possible if I strictly used the API data, am I right?

You would still be able to do this with just the API call

@brandon8

CamelCamelCamel - They only serve data from Amazon so really it’s just a massive site with a bunch of unique ways of searching and filtering through Amazon’s Product API. Although, I imagine they are still saving these products to their own database and that allows them to keep saving the price data for the historical price tracking.

I don’t know what they do, but I highly doubt that are bothering to create, maintain and pay for a large database when they are simply leveraging Amazons database…that is part of why APIs are great.

@brandon8

• PCPartPicker - A little different from what I’m going for, but a site like this wouldn’t be possible without saving products as their own data type otherwise you’d have duplicate products all over the place. For example, a Ryzen 2700X is sold at virtually ever merchant, but there is only one listing on the site for the 2700X and in that single listing they are constantly pulling price data for the product from several merchants for the user to compare.

This gets into a whole other set of concerns and other advanced features required to do this optimally in your app

@brandon8

So yeah, the value of having a single RG that searches for a certain type of product from multiple merchants is what I am trying to achieve as that is where the real value to the user comes in.

Where are you getting these values? From a single API or does each Merchant have their own unique API?

[brandon8]

Once again, super helpful info!

@boston85719

You would still be able to do this with just the API call

How would I go about doing this? I’m not expecting a step by step but can you run me through what a workflow would look like at a high level? Even with just one API, my concern would be that because an API is being called every time a user is conducting a search, any type of workflow would just continue to be duplicated. So if 500 results pop up, the API will fire off 500 links to be created by my URL shortener service even though those links may have already been shortened by the service previously.

@boston85719

I don’t know what they do, but I highly doubt that are bothering to create, maintain and pay for a large database when they are simply leveraging Amazons database…that is part of why APIs are great.

Referring to CamelCamelCamel, I’m pretty sure they are storing that data and just fetching new prices because a big part of their model is historical price tracking for a single item. You wouldn’t be able to price track something (like a product or a stock) without timestamping that price to the database every time it is fetched, right?

@boston85719

Where are you getting these values? From a single API or does each Merchant have their own unique API?

Right now I’m trying to start simple and just have a page with a single RG that shows desktop PC’s from multiple merchants (Amazon, Best Buy, New Egg, etc). Each of these merchants have already given me access to their API and while they all sell a similar product and offer the core parameters I need for a good filter and sort (the various specs of the PC’s) they all use different parameters to talk about the same thing.

So in that situation it seems like I would need to have a datatype called something like “Desktop PC” and within that data type I have fields with the key information users will want to sort through (Price, availability, CPU, memory size, power supply, etc)

I just can’t seem to find documentation or even a YouTube video that outlines how to automatically run an API workflow in the background (on its own at a regular interval), store that data in the correct datatype(s)/fields and then to CONTINUE to keep those data types updated.

Everything I’ve seen is more of a “Here’s how to trigger an API workflow to save data to your db” but nothing about “here’s how to KEEP those data types up-to-date via API workflows and how to add new datatypes when the API can’t find an existing data type to update”

[boston85719]

@brandon8

I just can’t seem to find documentation or even a YouTube video that outlines how to automatically run an API workflow in the background (on its own at a regular interval), store that data in the correct datatype(s)/fields and then to CONTINUE to keep those data types updated.

Everything I’ve seen is more of a “Here’s how to trigger an API workflow to save data to your db” but nothing about “here’s how to KEEP those data types up-to-date via API workflows and how to add new datatypes when the API can’t find an existing data type to update”

You likely would not find that. When I personally make video tutorials I do it for a wide audience, so it is not so specific to a single use case…I think most other content creators would be similar in their approach.

Most of what I’ve had to do in my learnings of Bubble is to take the tid bits of information I can find, digest and internalize it and then think through my own use case for how to apply it properly.

@brandon8

Referring to CamelCamelCamel, I’m pretty sure they are storing that data and just fetching new prices because a big part of their model is historical price tracking for a single item. You wouldn’t be able to price track something (like a product or a stock) without timestamping that price to the database every time it is fetched, right?

I suppose so, if that is the value proposition they offer.

@brandon8

How would I go about doing this?

I would expect to only need the affiliate link when the user clicks onto a search result. I would setup my elements that trigger a workflow to navigate the user to use the affiliate link when they click on the product.

@brandon8

So if 500 results pop up, the API will fire off 500 links to be created by my URL shortener service even though those links may have already been shortened by the service previously.

I don’t see much reason to do this…you will be saving things that may never be selected by a user…should just do it on the fly when they click onto the product. Simple client side action that doesn’t touch the database at all.

[brandon8]

@boston85719

You likely would not find that. When I personally make video tutorials I do it for a wide audience, so it is not so specific to a single use case…I think most other content creators would be similar in their approach.

Most of what I’ve had to do in my learnings of Bubble is to take the tid bits of information I can find, digest and internalize it and then think through my own use case for how to apply it properly.

I imagine the practice of storing data from an API and then keeping that database updated is not the single use case you’re referring to as this is what you’d do with a stock trading app, hotel directory or even just very basic price tracker websites. I’m guessing you are more so referring to the practice of running that API in the background on a regular interval rather than being triggered by a user?

I’ve spent days watching endless API and database videos and reading through forums and I am struggling to even piece it together. What I’m wanting to do seems like it would be such a simple task. Here’s how my mind is breaking it down:

1. Every day I run an API workflow in the background that retrieves a list of products from a merchants API. If I have multiple merchants, I would do the same process for each API.

2. Bubble then saves the results from each API workflow to my Bubble database with each product being a new “thing” in my data type.

3. I then have a page with a single RG that is simply pulling from that data type.

4. The next day, Bubble repeats the same thing, running the individual API workflows to update the fields of that data type, if anything has changed.

Finding a guide on that entire process I get that it’s a little more niche. But even finding stuff that explains the pieces I am struggling to find.

I know I can figure out how to run an API workflow to then save that data to my database. However I don’t know how how Bubble would handle me running that same API workflow again (like the next day). By default would it replace the existing data in the database? Would it just duplicate the data? Is Bubble smart enough to only store “new” data it finds from the latest API grab?

Even documentation or a video that just covers just THAT (regarding the API) I think would help me tremendously. So if you or anyone has any suggested links, I would be grateful!

[boston85719]

@brandon8

I imagine the practice of storing data from an API and then keeping that database updated is not the single use case you’re referring to as this is what you’d do with a stock trading app, hotel directory or even just very basic price tracker websites. I’m guessing you are more so referring to the practice of running that API in the background on a regular interval rather than being triggered by a user?

If your purpose is to save the historical data then go the route you are thinking and save the data to your database.

If you are focused on creating your affiliate links then do what I’ve suggested and do it on the fly.

@brandon8

However I don’t know how how Bubble would handle me running that same API workflow again (like the next day). By default would it replace the existing data in the database? Would it just duplicate the data? Is Bubble smart enough to only store “new” data it finds from the latest API grab?

It is up to you to configure it to work the way you want it to. If you want duplicates (which they shouldn’t be because they are having different prices which means not all fields are the same indicating it is not duplicated) then make the workflow run to create a new thing.

If you want to replace the data (which doesn’t sound like you do because you keep mentioning storing the historical price trends) then set it up so the workflow makes changes to a thing. And if you want historical pricing saved setup your database to accommodate for that by having the relative data types in your DB for saving the price and date of the price.

Bubble is smart enough to do whatever the developer programs it to do…it is up to you the developer to create the systems you want.

If you like I’d be happy to go over such concepts in a private training session.

Private Personal and Group Training Sessions

[net-tt]

Hi @boston85719
I also don’t like to store the API data to DB unless it is necessary. I retrieve a list of tasks on page load and then I need to constantly get the status update of the tasks that are in progress. I don’t want to retrieve the whole list. I want to retrieve the data just for the tasks that are not completed. But how can I update a status (a field) of a particular task in a repeating group without saving to DB first?