How to write data to BubbleDB using Data API Bulk Data Loader

Trying out the new Data API. Here’s the POST from R / httr.

http://{domain}/version-test/api/1.1/obj/tablename/bulk

req ← POST(“https://meetaway.com/version-test/api/1.1/obj/junktable/bulk”,
add_headers(‘Content-Type’=‘text/plain’,‘Authorization’ = ‘Bearer cef0e…47a37’),
body = '{“field1”: “ABC”,“field2”: “DEF”}
{“field1”: “GHI”,“field2”: “JKL”}
',
encode = ‘json’,
verbose()
)
stop_for_status(req)
content(req)

Update Table Persmissions

A whole new world!

2 Likes

Any chance someone could explain to me how to use this bulk api with a varying amount of items I want to create

Basically each time I create items there will be a different amount that I want to create, can I create a number of items where the number of items is determined by a dynamic field or an input.
if so How would this look?

If all that’s changing is that the number of rows is dynamic and you’re POSTing data to a table in Bubble, it would look like this:

req ← POST(“https://meetaway.com/version-test/api/1.1/obj/table_name_where_youre_sticking_data/bulk”,
add_headers(‘Content-Type’=‘text/plain’,‘Authorization’ = ‘Bearer <your api key from bubble’),
body =
‘{“field1”: “ABC”,“field2”: “DEF”} # this is row 1
{“field1”: “GHI”,“field2”: “JKL”} # this is row 2, and so forth
’,
encode = ‘json’,
verbose()
)
stop_for_status(req)
content(req)

You’ll then need to setup the permissions within bubble.

Select the table name. This is also where you’ll find the api key needed for the POST call.

The go into the data tab and enable and enable create Data API.

Hope this helps.

1 Like

awesome thankyou,

how will the above code know how many item I’m sending to it,

You don’t need to specify the row count. Bubble will simply create a new row for each row in your cURL command.

Im really struggling with adding creating multiple things, I work around it then i get to another part of my app and bang i’m back there again.
I have literally spent days, probably atleast 40-50 hours coming back to this

basically i want to create a list of things from a search.

I am desperately asking for help

I’m struggling with this command as well for bulk. When you create the text/plain data content, do you need to separate by a newline character?

I have the following:
curl -X POST https://<app>/version-test/api/1.1/obj/Sensor -H 'Content-type: text/plain' -d '{"AccountId": "123", "Status": "INACTIVE"}{"AccountId": "1234", "Status": "INACTIVE"}'

What is incorrect about the data that I am getting {"status":"error","message":"Could not parse as JSON: {\"AccountId\": \"123\", \"Status\": \"INACTIVE\"}{\"AccountId\": \"1234\", \"Status\": \"INACTIVE\"}"}

Yes, newline between each JSON object. i.e. you construct an invalid JSON, haha!

You’re also missing the “/bulk” url path.

Oh thanks @mishav! Forgot to paste the bulk haha

curl -X POST https://<app>/version-test/api/1.1/obj/Sensor/bulk -H 'Content-type: text/plain' -d '{"AccountId": "123", "Status": "INACTIVE"}\n{"AccountId": "1234", "Status": "INACTIVE"}'

Gets me the following: {“status”:“error”,“message”:“Could not parse as JSON: {“AccountId”: “123”, “Status”: “INACTIVE”}\n{“AccountId”: “1234”, “Status”: “INACTIVE”}”}

So I tried with the newline before and I get the above so not quite too sure if this is a bug then :confused:

The newline is getting escaped, or rather, staying escaped …

My linux box is shutdown at the moment, but can you try a $ before the first '

curl ... -d $'{"AccountId": ...}\n{...}'

Another idea, try it from Postman?

Ahh you are correct with it being escaped! Thank you!

I would like to create a quick bash script to create many entries so I preferred not using Postman. Thank you!

For future people:
curl -X POST https://<app>/version-test/api/1.1/obj/Sensor/bulk -H 'Content-type: text/plain' -d $'{"AccountId": "123", "Status": "INACTIVE"}\n{"AccountId": "1234", "Status": "INACTIVE"}'

3 Likes

One thing to note that I would love feedback on:

Bubble states in their reference, “The maximum number of items that can be created via a single bulk request is currently 1000. There is also a limit of 4 minutes for the request to complete; if it takes longer than 4 minutes, items that have not yet been created will be marked as errors in the response. Creation speed is determined by the amount of available capacity your app has, as well as the size of the items that you are creating.”

I ran the following:

  • curl -X POST https://<app>/version-test/api/1.1/obj/MyObject/bulk -H ‘Content-type: text/plain’ -H “Authorization: Bearer <token>” -w “%{time_total}\n” -d $’{200-separate-entries},’
  • 200 entries were trying to be sent
  • Total characters being sent over: 19006

I ran this 15 times and 13 times it failed with the following:

120.236962
curl: (52) Empty reply from server

The first entry is the time it took for that curl request. 2 of the 15 succeeded with a couple seconds before 120 seconds was reached.

Now, based off of that information, I was able to connect, but the server did not respond back with anything. A few questions come up:

  • I thought the limit for the request to complete was 4 minutes, but here we see two minutes. Why is there this discrepancy?
  • Does Bubble not respond back with anything but timeout the connection?
  • As a customer, are there logs that I can see for this issue? In the server logs I can’t seem to find any Data API logs. Or if I am searching incorrectly, what is the correct way to search for these logs on the server logs?
  • Does Bubble (or anyone) know benchmarks or limits on Bubble’s Data API?
2 Likes

Good questions, I hope the Bubble team respond.

Did the failing attempts return HTTP error codes, or individual error responses for each bulk row?

Did you check the capacity logs to see if the app was rate limited due to capacity?

There is also rate limiting of requests, the docs say 1000 requests/minute. Presumably that is all requests including from APIs, pages and searches.

The app rate did not exceed 50% so I assume that the issue was not due to the rate limit of requests.

I’m also curious to hear from bubble team on your questions.

FWIW, I did a test with postman sending bulk data into our app . https://domain/version-test/api/1.1/obj/table/bulk. I do get a 200 response and the unique_id for the row that is created.

This was great @kramwe,

i tried replicating what you did in R and for those not familiar with R this might add to it:

Here is my script:

install.packages("httr")

library(httr)

req <- POST(url = "https://appname.bubbleapps.io/api/1.1/obj/menu_type/bulk", 
     add_headers('Content-Type'='text/plain', 'Authorization' = 'Bearer apikey'),
     body = 
     '{"key1": "test", "key2": "60"}
     {"key1": "test", "key2": "50"}',
     encode = "json", 
     verbose()
)

stop_for_status(req)
content(req)

The difference here is i show how to install thepackages and which. Also, remember to send the data as a number or a string depending on what bubble expects. Worth noting is that the body of the document is not in the same format as bubble prescribes in the documentation. I could only get it to work this way.

here is the documentation in R for httr: https://www.rdocumentation.org/packages/httr/versions/1.4.1/topics/POST

3 Likes

I actually didnt find it convenient to write the body manually and wanted a way to quickly format the data from csv which is easy to type in google sheets. So i explored another script in Rstudio that i think can greatly speed up how you manually enter data into bubble.

This makes it very easy for anyone to send you data. They could simply enter it into a google sheet and you can reformat it for bulk upload, avoiding many clicks if you are on a hobby plan and that bubbles csv upload is very slow for many items.

Here is the data test file i made, the script should run with this test file if you specify the correct directory in rstudio: test.csv - Google Drive

Here is my script in R:

#clean the working environment
rm(list=ls())

#set working directory, keep files organized
setwd("C:/Users/")

#
#install packages('readr'}
#install.packages('jsonlite')
#install.packages("stringr")
#install.packages("stringi")

#libraries
library(readr)
library(jsonlite)
library(stringr)
library(stringi)

#import data in csv format
data <- read_csv("test.csv")
#View(data)

#Convert the CSV to Json format and verify it worked by printing
inJSON <- toJSON(data)
print(inJSON)

#Finding the locations of the separators of each data entry. 
#This will allow us to determine to know where to begin and end the substring
# generating two tables indicating each location of patterns along the whole string
start_table <- data.frame(str_locate_all(inJSON, "key1"))
#start_table

stop_table <- data.frame(str_locate_all(inJSON, ","))
#selecting row 1, col 2.
#stop_table[1,2]

#defining the function that will restructure the data for bubble bulk upload format
restrucJSON <- function(jsonformat){
  
  #prepping the loop 
  count <- str_count(jsonformat, "key1") #count the number of times the loop is going to run, or the number of database entries 
  a <- 1 #there is only one row per entry 
  b <- 6 #there are 6 columns in this data, so we take every 6th position of the commas
  
  #loop to print
  for (i in c(1:count)) { #using the count to tell the loop how many times to run
    x <- start_table[a,1]
    y <- stop_table[b,1]
    print(substr(jsonformat, x-2, y-1)) 
    a <- a+1 #
    b <- b+6 #start counting from the next 6th comma
  }
  
}

#lets try out the function with the inJSON object
export <- restrucJSON(inJSON)

I tried it with bulk upload and it worked:

This is the message that i received from bubble in Rstudio:


as you can tell, it gives you a status on each data entry at the bottom of the console.

Here i am uploading as a .txt file in the same format as is used in the POST function above. However, i upload a file instead of typing the body:

body = upload_file(“/test.txt”),

There may be better ways to do this, but this way worked for me :slight_smile:

3 Likes

Hi,

I realised there are some pain points in setting up the bulk data api, so I made a video where I run through the setup from start to finish for a simple use case.

Let me know if this is helpful!