Forum Academy Marketplace Showcase Pricing Features

How to skip the UI and save API results to the database

APIs provide an excellent source of rich and useful data. Bubble makes consuming those APIs a breeze with its API connector plugin. Some elements of dealing with APIs can be tricky, especially around pagination and adding directly to the database without any UI elements.

Bubble provides a way to do both of these easily but only once you get your head around the concepts. This document will show you how to consume an API, page through the data, and insert it directly into the database.

An API that returns a significant amount of data will use paging. For example, this API return shows 92 results for the query and a limit of 50 rows spread over two pages.

"totalDocs":92

“limit”:50
“page”:1
“totalPages”:2
“pagingCounter”:1
“hasPrevPage”:false
“hasNextPage”:true
“prevPage”:NULL
“nextPage”:2
}

The first problem you will need to solve is how to call the API, in this case, twice with a page number parameter to get all the data. The next problem is how you can insert the data into the database without having first to bring it into a repeating group, use state variables, and the like.

For this method of retrieving and storing API results to the database, your app will need to be a paid plan of personal or better. That will expose a new feature set call backend workflows.

For the rest of this guide, we will use a production API configured in the connector plugin as an action that will return a variable amount of products based on the parameters passed. Ensure that you have created a database with fields and types that match the API’s data. The best practice is to name the fields to match those returned by the API

Navigate to the backend workflow section of your application and create a new API endpoint.

new-endpoint

In the property editor, give your new endpoint a name. Make sure that it is lower case and has no spaces. You will uncheck Expose as a public endpoint, check This endpoint can be run without authentication, and check to Ignore privacy rules .

*Note this is not secure and is out of this document’s scope to discuss your application’s security. Please refer to Bubble documentation on configuring application security.

From here, we need to create parameters to pass into the third party API. Click on the Add a new parameter button. The first parameter to create is a number to “loop” through the API to get all the data. I chose to call mine iteration, and the backend workflow API will use it as a loop counter. It will make more sense later.

Here is my “get-products-from-API” workflow property editor.

The arrow shows the iteration parameter as a number.

Create the rest of the parameters to match your APIs needs. Just make sure that the parameters mapped when initializing the API in the connector match. The best practice is to use the same naming convention, and I named my API workflow get-products-from-api. With the parameters completed, you can add your first action, a plugins -> call to your API connector endpoint action. In this example, “get best products.”

add-action-1

Before we can complete the action steps needed to complete this workflow, we need to create another backend workflow API. I called mine add-product-to-db and configured the parameters mapped to the values returned by the third-party API.

Ensure that you are mapping parameters to the keys in the database by both key name and type. Once completed, go back to the first backend API workflow you created, and right after the get data action, add a scheduled workflow on a list action.

In the property editor

  • Choose the type of thing
  • List to run on which will be the result of step 1
  • API workflow to call – the one we just created I called mine add-products-to-db
  • Run at current date and time
  • I chose a 5-second interval, the lowest that Bubble did without a warning.
  • Ignore privacy *Again, this is not secure. You are responsible for the security of your application. Bubble docs have ample information on app security.

The rest of the fields are simple. Choose THIS “things” and the field name. In my example, it’s This Get Best Products doc’s field.

The next action is the most important and creates the “loop.” Create a new schedule API workflow action. This action CALLS ITSELF and increments the iteration variable by 1. That is going to be the next page of results; hang in there we are almost there.

Only call yourself again when Iteration is less than or equal to the total number of the API call pages. That will stop the loop effect.

Here is a reminder of the API results

"totalDocs":92

“limit”:50
“page”:1
“totalPages”:2
“pagingCounter”:1
“hasPrevPage”:false
“hasNextPage”:true
“prevPage”:NULL
“nextPage”:2
}

So in our case, the loop will happen only two times, equal to the totalPages field. The last step is to kick off the process.

In the designer, create a button and make a workflow. Chose the backend workflow to GET your data from the third-party API, set it to the current date and time, and then put 1 in for the Iteration, which is the same as page 1. Add in all the other parameters for the API call as required.

Picture1

Fire and forget. Go to the Data tab and refresh a few times, and see the data pouring in like magic.

Bonus: While you’re working through this, you will likely need to delete the DB entries and start again. Simple with a backend workflow, delete a list of things and a button to schedule it.

It took many tries at piecing together forum notes and videos to get this working and I hope this document helps a fellow Bubbler.

Peace and stay safe.
M

Notes and credits:

There is excellent documentation and two amazing videos if you are new to using the API connector plugin. See this link.

Inspiration on how to make this work came from a YouTube video by Evan Little @evanlitttle on this video.

15 Likes

@mike.maberry Thank you for your tutorial! I have been searching for this. I was able to follow it but then got stuck and wondered if you have any ideas. The Api’s have been initialized and are working but this is where I got stuck: it is wanting me to add “more” after "Result from step 1.’ What am I doing wrong? Also, “this” option is not available in the parameters. Any help is much appreciated! Thank you.


Is there a best practice on how much data to bring into a bubble database vs. retrieving data (maybe more than once) through an API? In the marketing automation app we are working on, there is data from transaction processing with Stripe that is useful for calculating the value of a customer and the costs to transact and acquire them. I would think the granular level of detail Stripe can provide is unnecessary, but is there a definitive practice to determine what you collect and store and what you may call more than once?

I’m having a similar issue, has anyone got a method of iterating through the API call pages without having to store the data in your database? I’m importing 3000 records per API call and It’s difficult to store these in the database without lagging the application