Is is possible to create a bubble object that has same fields as an external SQL query? So if I have 5 fields in my SQL connector query, can I create a data object with those same fields and then run the sql connector query and populate the data object so I can easily manipulate the data on the page?
Background - I use the SQL data connector to retrieve data from an external postgres database. My queries are pretty complex so using bubble’s database isn’t feasible as fast search is core to my product.
From what I gather with SQL connector, you can only run the query and putting data into a repeating group and adding/removing records from that resultset repeating group. It seems you can’t edit the value of a field in a given record.
I don’t want to keep rerunning the whole sql query to get data when I change the value of one field of one record.
Does it make sense to create an object that has the same fields as the query I run? I would then run the query and then populate the object with the resultset data so I can update fields. I would still send an update via SQL but I don’t want to have to rerun the get data every time a field changes. Is that even possible?
It is not necessarily a repeating group. You can do whatever you want with the data coming from the SQL connector. Of course repeating group is one way. As you said above, once it is in your Bubble datatypes, you can do anything.
You can update the data locally in Bubble and send this update to your database in certain periods (hoursly, daily etc). Or in real time (which of course going to slow things down).
I think I am missing something very basic. Can you tell me the steps of how you populate a bubble datatype from the result of an external SQL query? I typically have a repeating group where the ‘type of content’ and ‘datasource’ fields are both the sql query, which limits by ability to manipulate data.
If I understand you correctly, I can have a bubble datatype with say 5 fields that match the result of the SQL query results and populate the 5 fields of the bubble datatype from the sql output? Can you point me in the direction of how could you map this?
I think once I figure that part out, then the part of updating the external datasource wouldn’t be that difficult.
If you are going to create anything in bulk, you should do it in the backend APIs. Simply, either one time or in specific times, you will connect to the API, get the data (just as you are doing with a repeating group), then, recursively or using schedule api workflow on a list, you can create things.
