šŸ§® Feature Suggestion: Query from external database and create types and entities

Had an interesting thought.

How possible would it be to query records from an external database and create records from it, live?

Example:

GET https://api.someexampleapp.com/data

Thenā€¦ Create records from GET
Alsoā€¦ Update every X minutes or on manual sync

I know, we can technically use Fibery API to push updates from our own database, but it would be awesome to be able to do from Fibery and allow the returned data to be transformed into types, entities, etc.

We use tools like ā€œRetoolā€ to make internal tools that need real-time data syncing/pullingā€¦ but honestly, prefer Fibery for this. Just the lack of ability to pull data natively makes it more cumbersome.

Is this something that Fibery or other users would find useful?

Cheers

Is what youā€™re after not already possible? I.e. using the Fibery API to make GET (and POST) calls to http servicesā€¦
https://api.fibery.io/#script

As said above:

I know, we can technically use Fibery API to push updates from our own database, but it would be awesome to be able to do from Fibery and allow the returned data to be transformed into types, entities, etc.

As you described, using ā€œGETā€ calls only work when using action buttons. It also does not incorporate seamless record deletion or creation (like, if a record from the external database is deleted, we then have to set up a deletion flow separately to remove it from Fiberyā€¦ rather than Fibery simply pulling ā€œonlyā€ what is available in the external database to begin with).

The idea would be to GET data natively in the form of a Custom Integration (external REST API) and transform the response (JSON) into types and/or entities, automatically, in real-time as changes are made in the databaes or on page Fibery page refresh, etc. (like you can with tools like Retool).

Example:

Here is how Retool creates a ā€œtableā€ from an external database.

6cf3ce2-add_data (1)

https://docs.retool.com/docs/working-with-tables

As you can see here in there docs, you can query data and automatically transform the response into ā€œcollectionsā€ (tables/entities).

Same with similar tools like ForestAdmin.

https://docs.forestadmin.com/documentation/how-tos/setup/install-forest-admin-on-a-rest-api


Again, the idea is to use a ā€œRESTā€ integration addon/plugin where data can be pulled in every x or on page table refresh and only show available returned data.

Hope this clarifies what I mean a bit more.

1 Like

To be fair, it can also be used in any automation rules, so that hopefully covers a few more use cases :slight_smile:

Automations can be triggered by many things (including once a day if needed, and hopefully more frequently soon).

Otherwise, could your use cases be covered either with Integromat or Zapier?

Might be interesting to look at some concrete examplesā€¦

1 Like

Indeed, while I like the elegance of a persistent, generic data ingest system with options in the setup flow to create new Types, assign fields, etc. (most of this functionality is already in Fiberyā€™s various integration and import functions), I tend to agree that most of the active connection stuff would be handled by Integromat. The initial Type setup, no. But you should be able to just do an Import with a CSV exported from the Database once, setup your types and fields that way, and then use Integromat to just map the SQL connection (MySQL, MS SQL both supported) to the existing fields in Fibery and put it on whatever regular scheduleā€¦ So basically it sounds like the original idea here would add a bit of convenience, but Iā€™m not sure how much real value. Unless Iā€™m missing something?

1 Like

Wellā€¦if you were inclined, you could use scripting (triggered by Integromat or whatever) to actually create the Fibery schema to match the data source, but I donā€™t know how often the work needed to produce the script would be less than the work to just set up the types as and when neededā€¦

1 Like

Yeah, I find the CSV ingest functionality that already exists to be pretty great actually. Itā€™s hard to imagine setting up a Type to map to external data being any better from some other method, assuming itā€™s basically a 1-time thing (for each unique data source).

Totally appreciate both of your inputs.

Was trying to say this a few times already. I am aware that you can use Fibery API to push/pull data. But it requires what Iā€™d call, workarounds and extra setup and maintenance. Whether you are using Fibery js, Integromat, Zapier, etc ā€“ it requires more ā€œworkaroundsā€ to push and pull data. My idea came from using Fiberyā€™s awesome API and feeling like this would be a much better and useful feature for database pulling.

Example with current Fibery scripting/rules/integromat/unofficialjs/etc:

Tigger push via Intgegromat or uojsā€¦ Check if database record entity exists in Fiberyā€¦ update if soā€¦ if notā€¦ create new recordā€¦

Additionallyā€¦ check if Fibery entities against database entities and determine if they are no longer in databaseā€¦ if they are not present in database any longer, delete entityā€¦

Example with custom rest API pull:

GET Database and return as collectionā€¦ populate as entities. Done.

Another issue is if we add new columns, we then need to incorporate updates into whichever method we are using to import data, instead of simply being able to pull the columns fields and data as entities live from the database as it is shown.

The idea is not for it to be a 1-time thing. The idea is to pull the data as it is shown in the database. So if columns are added or deleted, it would conform automatically to Fibery ā€œTypesā€. Itā€™s just like the other tools I mentioned do. Pulls the data as it is and displays it in table form. Then, you can manipulate it as is.

Also, with the current setupsā€¦ what happens if there was an error with our flow and a record wasnā€™t deleted properly from Fibery but was deleted in the database (now, itā€™s still showing in Fibery)? These are little things that become tedious.

I know there are options and workarounds, but I am basing the suggestion (not an official request) on what is available with other apps that work to create internal tools e.g. Retool (raised $75M), ForestAdmin (raised $11M), Internal (raised $5M), Airplane, etc.

I think a native Custom REST Pull would be extremely useful. Then, we can focus on using action buttons to take ā€œactionā€ on the live data.

3 Likes

Mmm, you have some good points, certainly. This is not an area of need for me at the moment, so I canā€™t fully empathize with your position and goals, but it seems like a reasonable feature request at any rate.

1 Like

Thanks for the elaboration @Illusory, I can definitely hear the pain points more clearly now.
Just so I understand, it sounds like your need is focussed on ā€˜pullingā€™ into Fibery (like a one-way sync/mirror) including Fibery schema updates to reflect source schema changes.
Did you imagine needing to push data (and schema changes) from Fibery as well? Or is it mostly about having ā€˜externalā€™ data viewable and connectable from within Fibery?

Did you at all follow the discussion here:

Is your need conceptually related to this sort of thing?
As Michael indicated in that discussion, opening up an ā€˜integration SDKā€™ is not impossible, but might not be widely used.

BTW, do you have specific external data sources that you consider v important? Perhaps an integration you desperately need is on the horizonā€¦ :slight_smile:

1 Like

Hey @Chr1sG !

I will read through that and update you on my thoughts.

I think in general, we were looking for an easy way to integrate an external database via REST or even a 1-to-1 database sync integration feature (for example: PostgresSQL). Similar to tools like https://www.getcensus.com. The idea is to take data from these external databases and sync Fibery so they are aligned perfectly. We can then leverage Fibery relational features, charting features and so on to gain further insights and take action.

2 Likes

Just an update here.

I came across Basdash.

Basedash makes your database editable, collaborative, and protected.

Tagline: Every product team needs to view & edit data from their database, yet they have to build custom internal tools to do it. Basedash is the internal tool you donā€™t have to build.

Main premise:

  • Fix typos, update user info, toggle feature flags, tweak permissions without building a new API or a custom dashboard.

They have ā€œActionsā€ coming soon as well: Actions | Voters | Basedash

Just another example of how a direct connection to a database could look for Fibery.

We were attempting to use Fibery to track, edit and control our hardware inventory. However, all data in Fibery must be manually entered or sent via API. This means all hardware specs, statuses, etc must be added to our database, then added to Fibery through tedious API mapping. The ability to sync with a Postgres database and directly view and/or edit said database, as well as leverage other Fibery features like Relations or Actions is extremely attractive.

Example, if we have hardware that has been repeatedly flagged with a bug by users (submitted to our database via our web application)ā€¦ we can have that synced to Fibery via Postgres sync. Then, automatically relate Hardware Bugs type with Hardware Inventory type. Then, trigger actions automatically or manually that directly edit the hardware in our database (like remove the hardware from available rotation and create a work order entity in Project Management type to inspect or fix)

At this point, we are only using Fibery for project management ā€“ even though a big part of our project management directly incorporates maintenance, updates and more on our hardware.

Anyways, hope this is valuable information on how a company like us would like to use Fibery, ideally.

Best,
Josiah

3 Likes

I know you donā€™t want to deal with integromat, nodered or something like that, but this does seem like something that could be encapsulated through one of those tools into a reusable component that would avoid having to deal with them for each table you want to sync.

You essentially have a component that has the following parameters:

  • Database connection config
    • host, port, user, password, schema, table, etc
    • fields to use
    • optional column whitelist/blacklist
  • Fibery Config
    • Space, Database
    • Default mapping between database types and fibery columns to use with some way to override
  • Preferences
    • Maybe a toggle to remove columns that no longer are in the database
    • A limit on the number of rows or rows to add in a day to throttle it as needed
    • Settings around how and when to check

I also think that syncing a database table would be a little easier than a rest API, since a rest API can often return nested json data, and a rest API typically isnā€™t going to expose a consistent type that can be depended on. One no-code tool Iā€™ve played around with allows you to do something similar with a database and rest interface, but they include the ability to transform the data or write custom queries to make sure you are getting only what you need.

In other words, I think it would be worth thinking of the input options and features that would be required. This would make developing a reuseable component easier and would better define what exactly you are expecting.

2 Likes

Iā€™m a bit amazed by the dialog here in this feature suggestion.
Of course there are ways to use Zapier and Fibery to do something like this. Iā€™m currently doing something similar and: itā€™s tedious. I use the Fibery API more than the integromat modules because they handle batches better. The Fibery modules could use a v2 to make this kind of work go quicker.
That doesnā€™t change the fact that this is a feature suggestion/request. The OP has clarified several times that this is an improvement upon the status quo, not a statement that this is impossible.

@Illusory this is a fantastic feature request and I think it could be an incredible value for Fibery customers and a real differentiator. Thanks for thinking of suggestions and use cases that expand our horizons here.

3 Likes

Make sure to vote on it.
@mdubakov provided a potential solution, though we havenā€™t had time to check.
https://community.fibery.io/t/create-custom-fibery-integrations-apps-fast/2412/2

We are waiting for BaseDash to offer ā€œActionsā€ which weā€™re set to receive beta access soon. This may reduce the need for us to use Fibery in the way we were.

Love fibery still! Best tool out there for what it is!

1 Like

It appears Fibery has added support to connect Postgres to generate Fibery Reports! This is a GREAT step.

Is there any plans to have the ability to sync Postgres tables as entities? :pray: @mdubakov

I think this isnā€™t something that new - it has been possible to create reports based on a variety of non-Fibery sources for quite a while now.

2 Likes

Honestly, not sure why I missed it. Very awesome! Just hoping the ability to create entities from these sources becomes implemented at some point.

2 Likes