Exploiting the database without slowing it down

==========
What’s That?
==========
This post is a tutorial on how to save data using JSON format. The method allows to compress multiple database records into a single one, thus, speeding up workflows that involve manipulating lists of records.

========
But Why?
========
Few weeks ago, while developing an app for a client, I hit a wall. The dynamic form I was trying to make required the database to copy/delete a list of 48 records. With a paid, professional plan, this process took 2-3 minutes! I still can’t conceive how a such small task can take that long. There are web apps today that deal with thousands of records in less time.
Here’s a link that points out to an app I made to test off the speed of Bubble’s DB:
Speed test app
Speed test app Editor

After a few days of research I ended up with a workaround that solved the problem: shrinking the 48 records into a single one using JSON. Now my workflows are executing in less than a second.

I got the advice to make use of API workflows; they run in the background so the UX doesn’t suffer from it. But what happens when the user fills out a form and after hitting “Save” they can’t see or retrieve the results before 2-3 minutes? This leads to confusion and frustration.

==========
Dummy App
==========
Here’s a link to an app I made to show off a concrete example. I commented the elements and the workflows to explain what serves to what. Anyway the app is pretty simple.

If it was for real, the app would be meant for automobile mechanics to use when they inspect cars. The app offers a list of components to evaluate and save as an “Inspection”. For every inspection, there are basic informations (Inspector’s name, car brand…) and there are 20 questions (or components) to evaluate. For each of them, the user can specify the level of degradation, the urgency to fix it and leave a comment.

So what’s the best way to save an Inspection? Creating a “thing” in Bubble with a field for every piece of information? That would not be sustainable as there are 3 informations for every questions. So 3x20 means 60 fields. What if in the future we want to add 20 questions and add the possibility to specify a part number? This would mean 4x40 fields: 160. Bubble’s database is currently limited to 140 fields so we’d be doomed.
The other way, the good one, would be to create a record for each question. This way we could add as many components as we’d like + a possibility of over 100 more fields for each of them. Unfortunately, this is where Bubble’s performances come short. To create 20 records at a time would take easily 1-2 minutes. The user experience is destroyed.

The workaround (it really is just that) is to have our 20 questions saved in a JSON string that is saved in a single field of a single record. This is how this dummy app works.
Test the app
See the editor

===================
The Process in Summary
===================
-Write by hand the JSON template (I used a text editor that offers indentation + colouring)
-Design the app: putting the elements on the canvas and styling it, create the “things” in the data tab
-Build the parsing process: the part where Bubble reads the JSON and generate the list of questions from it
-Build the update process: the part where Bubble reads the inputs as the user change their content and put their data back in the JSON
-Build the saving process: the part where Bubble takes the up-to-date JSON and saves it in a record along with the other data

==========
The Process
==========
ESTABLISH THE STRUCTURE OF THE JSON
This includes planning what are the types of information I’ll want to save. Because the questions are dynamically generated in a repeating group, the JSON must be a list.
This is the first 2 questions of the app:

[
{
“title”: “Front Tires”,
“state”: 0,
“urgency”: “”,
“comment”:“”,
},
{
“title”: “Rear Tires”,
“state”: 0,
“urgency”: “”,
“comment”:“”,
}
]

The JSON is saved in Bubble as the default value of the JSON custom state. So whenever the user wants to fill a new inspection form, the template is taken as default.

It is important to note that once you’ve written your JSON template, you NEED to remove all the new lines. The javascript won’t accept a multiline JSON. One tool I discovered is JSON Validator from Curious Concept. It allows to format a JSON as desired + it serves as a validator.

READ THE JSON AND GENERATE THE FORM WITH IT
This part requires the ever useful “Toolbox” plugin (big thanks to @mishav ). The custom workflow “JSON Parser” has a “Run Javascript” action which expect a JSON string to parse. It creates an array for each information of the questions (title/state/urgency/comment). These lists are then passed to “Javascript to Bubble” functions (bubble_fn_whatever). The number of questions (in this case 20) is passed to the function bubble_fn_listLength.

On the Bubble side now. A “List of Numbers” element generates a list of numbers that starts from 1 to the value of the bubble_fn_listLength (Javascript to Bubble element). This list of numbers is used as the source of the repeating group so it has the right amount of cells.

Every element of the cell take its data from the appropriate bubble_fn_whatever function and by selecting the item # corresponding to its “Current cell’s index”

UPDATE THE JSON FROM THE INPUTS
The only built-in way that I found to extract data from a repeating group without interacting with the database is with “An input’s value has changed” workflow trigger. So in the dummy app, there are 3 workflows: one for the slider (state value), 1 for the dropdown (urgency) and one for the text input (comment).

So when an input’s value gets updated, it triggers a custom workflow (JSON Updater) that passes the value of the input, the field it belongs to (state/urgency/comment) and the “Current cell’s index”. The custom workflow has a “Run Javascript” action that replaces the original value in the JSON with the new information. This means that the custom state that originally holds the template is updated in real time as the user changes the inputs values.

SAVING THE JSON
The saving workflow is pretty straight forward as it takes the JSON from the custom state that originally held the template and puts it in the “JSON” field of the record that goes in the database.

EDITING A SAVED INSPECTION
The process is exactly the same then creating a new one except for one thing. Instead of using the default value (template) of the JSON custom state, it takes the content of the JSON field of the record that needs to be edited and puts it in the custom state.
Since the repeating group is populated from the parsing of the JSON, the inputs will reflect the values that were saved.

40 Likes

Clear, concise and detailed! A couple of weeks ago we ran into a similar problem and had to pay quite a lot of money to use a non-bubble programmer to find a solution. If we can successfully implement this across our various other applications, you’ve saved us a ton money.

1 Like

Just taking that concept further,

https://bubble.io/plugin/data-layer-1552810734309x983019165656809500

3 Likes

The editor I want to see is for your first link.

Make it visible to all?

That is cool!

There are various ways to implement this solution though, so you’ll might have to customize it to fit your app.

Sorry I don’t understand what you mean. In the settings I’ve set the “Application Rights” to Anyone can view.

The editor for this link, with the mass deletion option

Here’s the link to the Speed test app:
https://bubble.io/page?type=page&name=speedtest&id=joellol&tab=tabs-1

@julienallard1 I´ve just been trying your way with json and it´s super fast.

Amazing!

1 Like

Excellent way to speed things up !

Any pointers to this setup ?
On first run “registration” I am using the JSON stored in thisJSON custom-state.
But any registrations after the first I pull the previously saved JSON into the parser and modify some values with javascript in a for loop, before pushing the values to the Javascript to bubble elements on the page.
Everything displays fine on the page, but disappears when saving. Values are changed before pushed to j2b elements and does not trigger the JSON updater.

What’s the best way to approach this ?
Any idea on how build a save function that combines all the J2B list items and saves them as a JSON blob in the “thing”

JSON is formatted like the one in the example app :
[{
“key1” : “value1”, (list 1 [0])
“key2” : “value2” (list 2 [0])
},
{
“key1” : “value1”, (list 1 [1])
“key2” : “value2” (list 2 [1])
}]

I probably need to stringify and set the state back to thisJSON customstat right after all values are pushed to J2B(lists) and the page is shown

This is a good question and I actually have it my self. Perhaps @mishav has an answer to this. There seems to be a limitation with the “Javascript to Bubble” element where you can’t update a J2B element and save its published value in the same workflow.

Take the demo app of this post: the code that updates “thisJSON” is called every time an input’s value has changed rather than when the users press “Save”. I tried to only update de JSON when saving but I ended up with an empty database field.

So if I understand your question right, I suggest you find a way to run your JS code in another workflow than the one that writes the final JSON into de database. And yes you have to stringify the JSON, because the database doesn’t take Javascript Objects.

Perhaps, just for the sake of proving your point, you could make a temporary button called “Update JSON” right beside your “Save” button. That you make two separate workflows.

1 Like

My thoughts exactly :wink:

Beeing new to a lot of this, I am trying to find the best way to create a for loop to “reconstruct” the various lists into an object i can stringify :slight_smile: Any good links to other examples ?

Should I just create a for loop where I push the values to a saveJSON object ?
Accessing lists in bubble using current cells number/index is no problem, but how does one access it in JS… bubble_fn_whatever[0]

Hey @julienallard1, thanks for an amazing tutorial. I can’t wait to try it out!

I’m curious to know if you tried to solve your user interface problem by making the input values within the repeating group be saved by auto binding?

Hi @antony, yeah I did try auto-binding. It helped for the saving process but I still had to create the entries in the database first so I could have a source for the repeating group.

The auto binding function is also a bit slow; if the user changes values to quickly, the process can get “lost” with too many input change and skip them.

I don’t have other exemples. I came up with the solution myself.

I’m not too sure what your second question is. Do you mean that you want to access an object in javascript through different “Run javascript” actions? If so, your javascript variable/objects has to be global. So you have to add the prefix “window.” in front of your variable/object name:
window.thisJSON = [];
window.thisJSON.push("hey");

This way you can refer to your object from “Run javascript” actions and from HTML elements in the editor.

1 Like

I was probably a bit hasty and confused myself writing the question :slight_smile:

What I really need to do is to take all the J2B list I have populated from parsing and “link” them all back to an array of JSON Object that I can then stringify and save.

Hi @julienallard1, thanks for letting me know your experience of the autobinding. I used it extensively a year ago and found exactly the same problems. It seems like a utopia, but isn’t in practice when it gets “lost”, as you very accurately describe it. I’ll have a play with your method and see how I get on!

So what you want to achieve is some sort of “Bubble to Javascript”? You can use dynamic expression in your JS like so:

The lists published by J2Bs are array, so when you affect them to a javascript variable, it becomes an array. Then you can navigate through them like the last line of the screenshot.

1 Like

I managed to tweak things a bit for it to work in bubble:

The variables containing strings seem to require (join with “”,“” ")
Above screenshot is a work in progress, but it seems to work

stringify and save to database

You’re right, in the screenshot I posted, I forgot to put the dynamic content between quotes.