Why are API Data Types not Exposed in the Database?

So… hitting a snag. I have a certain data type that comes back to me from an external API (one of my own). The data type that it returns is a complex data type with various fields including a name/summary, various dates, etc. and it describes an “Event” (in the database it’s referred to as an “iCalfromURL Event”). They come back to me as a list of these things and I attach them an object in the database (a Calendar in this case).

So, the Calendar data type has a list of "iCalfromURL Event"s on it.

So my API returns a list of these things and those get stored in the database. But what I can do with these is somewhat limited.

Things of “iCalfromURL Event” are not exposed in the database and only partially exposed in workflows. For example, while I can get one of these “Event” things out of a list of them they are not things that can be “Do a search for…” or Create a New Thing of this type.

But I have a need for creating new things of this type DIRECTLY IN MY APP. Is there a way to do that? Am I missing something? I fear I may have caused myself a giant pain in the butt here…

If you’ve never run across this, let me show you what happens:

Here’s what my (quite clever and well written I must say :wink: ) API returns:

As you can see, here is where “iCalFromURL Event” comes from. The complex JSON datatype is automagically created by Bubble.

However, when you do something this way, that “iCalFromURL Event” data type does not bubble up into the app’s data types. They seem to be in some kind of weird ghetto that’s isolated from other data types I’ve created.

Looking in my database, we can see that, seemingly, this data type does not exist. One would expect that it would show up somewhere around here:

And even though there are zillions of these "iCalFromURL Event"s in my database, neither do they “bubble up” to the App Data section:

The only way one can “see” or expose them is in a workflow or repeating group type of thing. Here for example. Inside of Bubble editor, we find these API data types near the bottom of the list of all data types in our app:

And then we can retrieve things of this type from things they are attached to. Like so:

That Calendar’s “Events” is a list of “iCalFromURL Events”.

So you would think that – if one needed to make a new thing of this type – one could do that. However, you can’t – the type does not show up anywhere in the Create New / Make Changes to Things workflow elements:

See? Nowhere…

So… WHY IS THIS? Is this a bug? Why do API-returned complex data types not “exist” as core data types in Bubble???

This is kinda freaking me out at the moment as don’t want to have to call out to my API just to CREATE one of these things. That would be stupid… but it might be necessary. HEAVY SIGH.

2 Likes

ANOTHER WAY to ask this question:

Why are data types like this not “SAVEABLE”, in Bubble jargon?

I guess they are like primitives? They are completely static values. (Like, you can’t “Make Changes to a Thing…” and just target an integer. Obviously that wouldn’t make any sense and isn’t something you can do.)

I’m really flummoxed at the moment about how I can work around this. The issue seems to be that objects of this type have no “unique_id”… it’s like they are broken.

Did you find any solution about it ?!

Well, the issue I describe – at least at the time being – is just “how it is.”

In terms of how I worked around roadblocks that this issue was presenting to me, it basically goes this way (and if you’re experiencing something similar, this is the type of thing you’d have to do):

  1. Since this data type (iCalfromURL Event) is not saveable, it is not searchable. So this precludes me from certain performance optimizations. For example, I can’t “Do a search…” for them with advanced parameters that are executed server-side. Instead, I can :filter them, but that’s performed client side. (This is sometimes an issue, but it’s not a huge one just yet.)

Interestingly, in an API workflow, one CAN do filtering on a list of these things. So that is helpful and has led me to some workarounds that are also, essentially, also performance enhancements.

I can always get a list of these things (since they are attached to other objects), but I cannot filter or manipulate those lists of iCalfromURL Events server-side (except in API workflows).

  1. Since I cannot make things of this data type (hey are not saveable or searchable so you cannot “Create a new thing…” of that type), I had to create an analogous type in my database. So I have the “iCal from URL Event” that comes to me via the API and a type in my database that shares field types with it (and has some more field types as well). That database type is called “Calendar Event.”

  2. These two data types both represent pretty much the same thing – a reservation in my app. If I need to perform some operation (such as display these reservations on a calendar), the workaround is to take the data types that they share and merge them. As an example:

  • My Calendar objects now have TWO lists of very similar things on them, a list of iCalfromURL Events and a list of Calendar Events. These have start (date), end (date), and summary (text) fields in common. A Calendar object can then be used to store just events of the type that come from my API, just events of the type that are created inside my app, or both.

  • Let us say that I need to present all of those reservations on a calendar. Well, I can’t merge the two lists of things (because they have different data types and one doesn’t even properly “exist”). But what I can do is combine their component fields into merged lists that share the same order as if I had merged the core data types themselves.

For example: To display these things on a calendar, I need their start and end dates, right? So I’ve got four custom states on a thing on a page that will hold this “pre-loaded” data. These are:

  • A list of iCalfromURL Events
  • A list of Calendar Events
  • A list of start dates which is List of iCalfrom URL Events:(which may be filtered or sorted)'s starts :merged with List of Calendar Events:(filtered and or sorted the same way)'s starts
  • A list of end dates which is List of iCalfrom URL Events:(which may be filtered or sorted)'s ends :merged with List of Calendar Events:(filtered and or sorted the same way)'s ends

Yes, it’s all a bit of a pain in the butt, but it’s not entirely unsurmountable.

The thing that’s a problem mostly is the loss of a unified “Do a search for…” all of the things that represent reservations. Such operations are very performant (faster usually than doing a :filter client side).

So, for the calendar situation (for example), most of the time I’m only concerned with events that are IN THE FUTURE. However, the events that come back from my API may have many, many events that have occurred before the present time. To load all of these all the time creates a bottleneck.

So, in my API workflow used to update a Calendar by fetching events from an iCal file, I store the full list of returned values on the Calendar, but ALSO do a second filtering step that stores ONLY the events that are happening in the future (as of the API workflow’s “current date/time”) in another field on the Calendar – Future iCalfromURL Events.

Of course, it would be much easier if I could have simply extended the “iCalfromURL Event” data type by adding additional fields, etc. and could create new ones so that events being created in my system are all of the same type. But, alas, it is not possible at the moment.

It did waste quite a few hours of my time figuring these workarounds out. Hopefully the preceding notes will be helpful to others facing a similar problem.

Best regards,
Keith

1 Like

How is it “not savable”? You created an API for it, so you should be able to create workflow that “Creates a thing” in the database. If you want to save the data from the iCalFromURL API, create a Data Type, with the fields returned in the API initialization (i.e. the screenshot with the Returned Values) for it, then use workflow to call the API and save it to the Data Type you created.

Creating an API doesn’t do anything, other than allow you to access data from the API. You have to build data structures and logic in Bubble to consume/interact with the data from the API.

  1. One way is to directly call and display it in Bubble.

  2. Another way is to call it and then push it into a table (i.e. Data Type) in your Bubble database.

Option 1 means you don’t have to store anything in Bubble, which could be nice depending on what your use case entails. Option 2 gives you more control over the data to manipulate and use. It is also faster to retrieve in the future.

Reach out to @romanmg or @copilot, both should be able to provide one-on-one assistance to help you understand how to “create things” from an API.

4 Likes

Thanks for the input, @Kfawcett… I saw your other comment and it COULD BE that the plug-in creator offers features that can be used to work around this issue. I may need to explore that more but at first glance I have not seen evidence of that.

Let me try to explain this limitation of the current Bubble API Connector a little bit more. (Read on.)

Aside: The ONLY real workarounds (which are unacceptably slow) would be to have one’s API return a single value at a time. (And you still have the general problem of iterating.)… OR, have one’s API (instead of building a list of complex objects and returning those) instead poke the values into one’s database by making API calls BACK TO THE BUBBLE APP. (i.e., anywhere that you might push a new value onto the array, instead make a call to the bubble app to create a single instance of that complex thing in the database. (This is kind of absurd and over-the-top resource intensive, but it would be a way.))

The issue here is that there are not flatten/unflatten functions on the receiving end of Bubble.

If an API returns an ARRAY of complex values (that is, a LIST of JSON objects with multiple keys/fields), all Bubble can “see” is that the API returns a list of complex objects.

You can snag that list and and save THAT. But the sub-items, the OBJECTS IN THE LIST, are constants/primitives now. So, to review:

If an API does this, the data returned in the list is exactly like a list of integers, or a list of texts, or a list of dates. The things in the list are primitives and are immutable. Even worse, these primitives have no definition at the database level in Bubble.

You cannot “promote” these values (the things in the list) easily to full citizens. I SUPPOSE one could kick off an API Workflow on a List to operate on the list of primitives returned in this way and create individual database entries for them.

The problem with THAT solution is that it is VERY SLOW and one has no way of knowing how long that operation will take and there are no guarantees of any sort as to when the operation might finish and the data would be available to use in a meaningful way.

All of this problematic stuff would go away if there were ONE MORE option in the API Connector. The feature would work this way:

  • When an API returns a list of complex objects there would be a new option: “Flatten list to list of [data type]” where [data type] would be a data type that one has previously defined and then you could map the fields of the API-returned list to fields in THAT user-defined data type.

If the plug-in creator offers such functionality I’d love to know about that.

All of the above is pretty easy to confirm and test one’s self. For example, just find any API that returns a list of complex things. Or write a quick little API that returns such a list. (For example: an API that you post anything to and it returns something like:

myOogumBoogums where the array of things looks like so:

[{
“oogum”: “something”,
“boogum”: “something else”
}, {
“oogum”: “something 2”,
“boogum”: “something else 2”
},
{
“oogum”: “something 3”,
“boogum”: “something else 3”
}
]

What you will find is that the API connector will see that your API returns a List of OogumBoogums. You can of course save this list of OogumBoogums on something. HOWEVER, OogumBoogums INDIVIDUALLY are not a thing.

1 Like

Here’s an example of an API returning an array inside the JSON (i.e toRecipients and ReplyTo).

One way to populate these values is to flatten them into one table in the database.

Then, in your API workflow endpoint make sure you check the “is a list/array” checkbox.

image

Then in that endpoint “Create a thing” and set the list.
image

Now call that API Workflow from a page… maybe by clicking a button or some other action.


1 Like

Thanks for this detail. Interesting… what is this?:

https://global.discourse-cdn.com/bubble/original/3X/8/5/85debe34f633efaca3058481296b3857a2166bb8.png

Where is that interface? (I’ve never seen a place where this can be done.)

Thx!

Alternative way of asking: What am I looking at there? Is that fields entry for a edit/create Data Type or am I looking at something else?

That is part of the screen where you create the Data Type.

Interesting stuff. It’s just so much bullshit though, right? Having to do this turns a more or less instantaneous process (zap data back from API) into a very long process (the API workflow on a list to populate a “real” data type in the db).

Like in my case, the iCal events coming back can easily number in the hundreds or thousands. The API call completes in a couple seconds (my API actually processes a typical iCal w/in a couple hundred ms — the bulk of the time seems to be Bubble updating the database with returned values).

The data that comes back if fully ready for consumption at that time. But if I were to then send it though an API workflow to flatten and convert to searchable/savable data types, I could be looking at several minutes before a large calendar’s events are fully processed.

That’s not helpful when the whole point of the API in the first place it to just ping the external service and display a fully up to date version of the events in Bubble.

While it can be worked around, the workarounds are not performant, completely redundant (they cost much more in compute and storage than if Bubble just did the right thing in the first place), and so this is like 60% bug and 40% missing feature.

None of this is documented and so I’ve the feeling the folks at Bubble do not quite grok that things DO NOT work as expected.

(I actually think this IS a bug. That is, the individual list items —- which ARE given a data type [they are auto-named as API_call_name return_value_name AND Bubble knows the field names, right?] — but this data type is not promoted back up to the db level.

I SUPPOSE there might be cases where one does not want full search/save access to such data types, but I’m stumped as to why that’s the default behavior.)

My point is this: Those values are IN THE DATABASE. They are already taking up space. Their type is fully defined. You can access all the individual fields. But since the data type is not exposed at the database “All Data Types” level, you can’t operate on them in efficient server side ways without wasting a bunch of Bubble compute time.

Also, it’s not entirely clear that these values stuck in the API ghetto data type subclass can ever be deleted. You can zero out the List of Such Things that you attached to an object. However, it is not obvious that the individual values in that list are deleted as a result. And there’s no way to check it as those individual items are not searchable and have no unique ids.

If they really ARE primitives, then they should vanish when the list vanishes. Like, let’s say you have a list of integers [2, 4, 32, 14] that’s stored as MyFavoriteNumbers on object User. If you Make Changes to User and do “MyFavoriteNumbers set list null” the integers go bye bye right? But does this happen to the complex primitives imported as lists that come back from an API???

It’s all super effing weird and broken AF, basically.

1 Like

They are NOT IN THE DATABASE, unless you’re saving them to a data type (i.e.table) – otherwise, you’re calling to an external system every time and displaying data from that external system. The only thing that the API connector, or Plugin Editor, is doing for you is creating the call format – and saving you the time of writing all of it in a programming language.

I think you are failing to grasp the concept. The team at Bubble are far smarter than most of us here. Good luck.

They are in the database! My API operates as data. When I call that API, it is in THIS context:

  • Create New or Make changes to a thing (depending).
  • Thing to change or value to set: This Calendar’s List of iCalFromURL Events set list Get data from external API - iCalFromURL’s Events
  • Thing to change: This Calendar’s Blocked Days set list Get data from external API - iCalFromURL’s Blocked Days

See, the values come across. The values get stored. I use them to do things. I display them visually on calendars.

However, they have the weird properties I describe in great detail above.

You have seen the same thing. You just skip the step of storing and go right to iterating over them in an API workflow. (This is prolly the right thing to do at present, but if your use case has a need for speed, doing that can be a deal-breaker.)

But you don’t have to do that. You CAN in fact store them. It’s just that they are limited in these strange ways cuz they are — for lack of a better way to describe it — either primitives or primitive-like.

That’s the point of my whole original post… I’d been using the API Connector in this way, blissfully unaware of the weird issues. It was only upon moving on to some new functionality in my app where I was like, “Oh, we’ll just use THAT data type as our native descriptor for Calendar events… hey waitaminnit…” that I discovered this oddity.

My workaround (described in another reply in this thread) is to have an essentially parallel top-level data type to describe the same things. This KIND OF works. You can do stuff like merge the starts and ends of these two different things (as those are just lists of dates, and so are congruous), but you can’t merge “iCalFromURL Event”s and native “Calendar Events” because, even though they have parallel field types, they are not fundamentally of the same data type.

This has the unfortunate side effect of forcing much client-side data manipulation. Because, again, data types in the API list-return ghetto are primitives and so are not saveable and are not searchable.

Consider this question:

Can you “Do a search for… 2”? (Where 2 is really the integer 2.)

Answer: “No, of course not. Don’t be stupid.”

You can of course “Do a search for… Users… where… Constraint Cats = 2”. That makes sense.

Similarly, you can not “Create a new thing… 2”. You cannot create a new thing that is purely a primitive. You cannot “Create a new…” thing of type Integer. Again, that makes no sense.

You can, of course, create a new thing of user-created data type “Favotite Number” and set the value of its “Number” field to 2. THAT makes sense.

Here is the thing that DOES NOT make sense: Complex JSON objects in a list returned to the API Connector are primitives that might as well be the integer 2.

It might be more appropriate to say they are like primitives such as a date range (date ranges have 3 sub-properties, and yet they are primitives).

You’d never notice this until you hit the point where you might want to alter one of these things, or create an “empty” one of these things.

Anyway, it’s both fascinating and completely wrong, but worth understanding. (And I am trying to get it fixed, but I have other obtuse open issues with bubble support and this one is not the highest priority.)

I’ve made a video that will help explain: Check this out, it’s very interesting. (Also, I show the data IS IN THE DATABASE…) This is a bug (or, perhaps more correctly, Bubble thought they had finished this functionality and cursory tests made it LOOK like they had, but it is not complete). Also, you wouldn’t be doing your iteration over lists like this if this functionality were actually complete so I don’t get why you don’t think this is a problem. But I’ve laid it out pretty well here (in my own, inimitable style):

vimeo.com/283382966

In short, lists returned via the API Connector behave in what would seem to be very strange ways. But it’s not actually strange, it’s exactly like primitives would behave… It’s just that nobody would WANT such behavior, so it’s a bug, see?

I believe, and this could be completely wrong, Bubble caches the data from initial call object. So, if you have 100, or 1000, or 10,000 of these objects, how do you expect to be able to access all of them without saving to your Bubble tables? Even if Bubble is creating fields that you are able to access from the initial API call, do you think Bubble is downloading all 10,000 instances of that object from the remote system?

The button’s in your repeating group do not have access to operations like “current cell” in the workflow – “current cell” is only available on the page you’re editing. You have to use “parent group’s xxx” or, in your case, “Current Page listing”. Then you should be able to access the related items.

What bubble calls “Data Types” are synonymous to tables. No idea why they named it anything other than “Tables”.

image

When you’re “creating a thing” in workflow, you are selecting the Table in the “Type” field, not a field’s data type.

image

This is the part where we need to better understand from the Bubble team (@josh @emmanuel) why you/we have access to the API call’s keys inside of the “Field Type” menu field. I don’t think that should be allowed.

Thanks, K. The point about having access to the API call’s keys in the Field Type menu is a good one. (And I’d say that’s kind of at the root of this issue.)

At any rate, it just still seems to me that, if one is using an API in this way, it’s completely reasonable to expect that a data type (which I guess in this case is the API’s table?) would be “moved” (copied? transferred?) to the “other” table (main database “table”)…

To your point about “how do you expect to be able to access [the objects] without saving to your Bubble tables?”, this is what I would think that Bubble is doing when I “Make changes to a thing… (an object in my database like a Calendar object)” and assign its “Events” field (a list of events) to the data returned by my API.

Your explanation helps me understand (I think) what is actually going on which (if I understand you correctly) is that data is not REPLICATED and stored on that object, in that field, but instead a pointer is created to some other table.

What I want to stress is that this other table, the data from the API call, does NOT seem to be some sort of temporary cache. It is, in fact, persistent (at least if it is “saved” in the way that I do it here – assigning it to some field on an object in my app’s Bubble database). The values in a Calendar’s Events, Booked Dates, Error Message, etc. are THERE (somewhere) in Bubble and can be referenced (like some Calendar’s Events’s Starts [a list of starting dates], some Calendar’s Event’s Summarys [a list of texts], some Calendar’s Events [a list of iCalfromURL Event things]) at any time in the future they do not go away.

But it’s quite confusing (at least to me) why such objects would not be “imported” (for lack of a better word on my end) from that API table up into the app database.

The reason this seems odd to me is that, if the API is returning a simple value (as in the case of my Error message [a single string] or my Booked Dates [a list of date objects]) those constants are just there in the app database and can of course be operated on.

Like I could Make Changes to a Calendar’s list of booked dates – such as change Calendar’s Booked Dates:first item to Calendar’s Booked Dates:first item +days 5, right? Or I could change Calendar’s Error to a completely different string such as “There wasn’t an error, but I put this text here anyway…”

But the “list of Events” things are just “stuck” there as they are (apparently) not a list of objects that has been brought across, but an entire table object to which I have “read only” access.

Am I understanding that right?

Thanks,
Keith

You showed some dates in the Repeating Group, were those event dates from the initial API call’s list? Are you able to show, in the Repeating Group, any other lists of Events? My assumption is, no. Which would mean that only the first call’s values are being stored/cached.

It wouldn’t be prudent, or efficient, to have Bubble automagically return every object from the API. As an example, let’s say you had an API to get transactions made on Amazon’s eCommerce site. Would you expect Bubble to pull all transactions ever made into your Bubble database on the initial call? I wouldn’t. That would be insane. Instead, I need to create workflow to tell Bubble that I want to return X transcactions at a time and store them in X table, and only store the values from X number of fields from the API, because I don’t care about who purchased it, I only want the item purchased and the cost. So my table only has two fields, even though the API is returning 30 fields.

Hi K, what you were seeing is dates that came from my API. They were not produced at that moment by the API call. They were dates STORED IN MY DATABASE. The dates came from a PREVIOUSLY EXECUTED API call. I was fetching the values DIRECTLY from the Calendar object being visualized on that page. I was NOT showing return values that came back JUST THEN by an API call. (See what I mean?)

Those dates were dates from the “Start” field of that Calendar’s “List of iCalfromURL Events” stored on the object. That list was populated by an API data call like this:

In the image above, iCalFromURL’s Event is the array of complex objects my API returns to represent iCalendar events. iCalFromURL’s DaysBlocked is the array of date objects my API returns to represent all individual booked days that are part of the Events.

For a little more detail, here’s the setup for the API call itself in Bubble (the dialog that pops up when you configure the parameters for the API call – my API takes a URL to the iCalendar .ics file in question as well as a time zone [so that the date math is done correctly]):

When this “Make changes to Calendar” step is complete, Calendar’s iCal Events is a list of those “weird” iCalFromURL Events and Calendar’s Booked Dates is a list of dates.

So what I was showing you is that YOU CAN fetch a list of complex objects from an API and store them directly in a field IN THE APP DATABASE.

Once the values are stored in that way, you can READ them back, as in my example, show them in a list. But apparently the only linkage between one’s app database and those objects is the field in which those objects are “stored”.

It’s EXACTLY THE SAME as if you had an API that, say, creates and returns a made up book title (let’s call it MakeASillyBookTitle). If you set that API up as a “DATA” type call, you might do something like:

  • Create a New Made Up Book and, in that step, you set the value of a field called “Title” to be the result of the API call to the MakeASillyBookTitle API. The string returned by the API call gets pushed into the Title field of the newly created “Made Up Book” object, right?

This is how my iCalFromURL API is used. At either scheduled times, or as an initialization step in various places, I fire off a workflow like what you see above. Asynchronously, Bubble goes out, pings the API and brings back a fresh version of the events represented by the iCalendar at that URL.

The point being to insure that events I might be displaying in my app are as up-to-date as one can expect.

This all works swimmingly. The only downside, like I’ve been saying, is that the ways that one can USE those previously stored event things are limited because they are (apparently) in another table or something and are not actually “in” my app’s database in the same way that OTHER complex data types are.

Does that help you understand?

Is there a TLDR version? :blush:

If I’m skimming your post correctly. You’re saying you did store the data from the API in one of your tables. If so, then you need to display records from that table,not from the API’s data type thing – which should be something like Calendar;s “Future iCal Events”, not “iCalFromURL Events”

image

This is where I think you would select “Calendar” or “Future iCalEvents” as the type of thing and the data source would be the Calendar tables list of…, not “iCalFromURL Event”

Not like this… you would only select “iCalFromURL Event” if you were going to make the “Data Source” field a “Get data from API”.

Hi K,

You mostly understand me, but I’m not having any “problems” of a simple nature with it.

I don’t have a problem displaying the data. Never have. Indeed, the way one does it (and the reason one doesn’t realize that there are limitations to data stored in this way), if just via Calendar’s (or whatever the list is attached to) whatever_field_the_data_is_stored_in.

(As you pointed out. I’m totally aware of that. That’s how I displayed it in the video.)

My point about ALL of this is simply that it makes no sense that this data exists in a separate table (or the API Ghetto as I prefer to call it). Just seems that there should be automatic “flatten and promote” for data such as this.

It sucks that it’s not searchable. You cannot “Do a search for…” this other-table-data-type or whatever one wants to call it. Neither can you “Create a new thing…” of this data type. Neither can you “Make changes…” to such things. Because the things (while being stored in Bubble somewhere) are apparently not in the APP DATABASE table.

As you have very correctly pointed out, you can iterate over a list of such things that comes back from an API and thereby put them INTO an app database data type, but my problem with that is that it adds a bunch of pointless compute and TIME overhead.