Hi,
Does dream factory has any REST APIs which connects multiple datasources and then get data by joining cross database tables?
Please suggest.
Thank you
Gowtham
Hi Gowtham, can you explain your use case a little more? Are the multiple datasources different SQL databases?
DreamFactory supports the ability to write custom server-side scripts, so this type of scenario is possible (a script can invoke multiple APIs and join the returned data). You can also use the REST API to invoke stored procedures…so the stored procedures could do the joining and return the data back to the client.
1 Like
Yes Benbusse, the datasources are from multiple datasources one is from ORACLE and another is from SQL Server.
So as you described, we should write our own APIs. Does dreamfactory provides any out of box APIs for cross database data processing?
Please suggest.
Thanks
Gowtham
Not exactly. You actually don’t need to write your own APIs. One of the nice things about DreamFactory is that it already provides APIs for a bunch of SQL databases. You don’t have to write your own REST APIs for SQL databases, DreamFactory has done it for you.
SQL Server, MySQL, and Postgres are currently supported and Oracle will be in the next release. You should read this blog to learn more about SQL support https://blog.dreamfactory.com/add-a-rest-api-to-any-sql-db-in-minutes.
What you need is a little more advanced, and Dreamfactory can support it like this:
- In the Services tab, connect to your SQL Server database. Look at the REST API provided for you in the API docs.
- In the Services tab, connect to your Oracle database. Look at the REST API provided for you in the API docs. (Oracle support is in the next release in a few weeks)
- Write a custom script that calls the REST APIs you defined in steps 1) and 2) with the query params right in the API calls. Then when those two services return data (in either JSON or XML format), your custom script joins the data as necessary…inner join, outer join, whatever, and returns the post-processed JSON or XML back to the client.
There’s more info on scripts below.
-
DreamFactory 1.5 Introduces Server-Side Scripting (this is about how to call a custom server-side script from a REST API endpoint in DreamFactory, on request (pre-process) or response (post-process)
-
Build Your Own Web Services Using DreamFactory Custom Scripts (this is about how to create any arbitrary script, so in your example, your client can call a Javascript script you call ‘foo’ which calls your DreamFactory APIs for SQL Server and Oracle and then operates on the data…you can use JS promises to wait for the JSON / XML data, operate on it, then return it to the client).
-
Scripting API Access (Wiki docs on scripting API access)
This is awesome. I will go through the links.
If dreamfactory can add cross database API’s to make it more easier, that would make dreamfactory users job much simplier.
Hopefully dreafactory will do something in this line. I believe we can do the same thing for SQL databases + NoSQL databases.
Thanks a lot once again! I will do more exploration on dreamfactory.
Thanks
Gowtham