Combining Multiple Data Sources In One API With DreamFactory

DreamFactory 2.0 allows you to hook up any number of SQL or NoSQL databases, and then you instantly get a complete REST API for each one. This information can be combined in any way that you need on the client. But sometimes you might want to combine multiple data sources on the server and return the information in a single REST API call.

This is really easy to do with DreamFactory 2.0.

Server Side Scripting

DreamFactory 2.0 has deep support for Server Side Scripting. You can write your scripts in Node.js, V8js, or PHP, with more languages on the way. Server Side Scripting provides a simple way to modify the request or response of any API call. So one easy way to combine multiple data sources is to add some fields to a record when the data is returned.

In my example, I used the V8js engine to grab additional information from the MongoDB database:

The script first grabs an email address from the current MySQL database record. This email address is added to a custom filter that is used to load data from MongoDB. Then additional information from Mongo is added to the returned results from the call. The “from_mongo” field was created for this purpose. Here is a picture of the Server Side Scripting interface.

var_dump(event.response);
var lodash = require("lodash.min.js");

if (event.response.resource) {

   lodash._.each (event.response.resource, function( record ) {
      var myparam = {
         "filter": "email=" + record.email,
      }
      var result = platform.api.get("mongodb/_table/bigtable", myparam);
      record.from_mongo = result.content.resource[0].email;
   });
}

This script could be extended in some sophisticated ways. For example, what if each record in MongoDB stored information about a financial transaction, and there were multiple transactions for each email? In this case, you could loop through the data returned from MongoDB and add up the transaction amounts for a total that is included in the returned data.

Here is another example. Say that each record in MongoDB had a related list of personal information, like contact phone numbers for the given email address. In that case, you could loop through the data returned from MongoDB and return this as a JSON array that is included in the returned data.

Here is a picture of the results from a MySQL database call in DreamFactory’s API Docs tab. As you can see, each record has an additional field “from_mongo” with the matching email address. You could include any desired information from the MongoDB database in this field or additional fields as needed.

One thing to remember is that access to the various data sources must be added to the user’s role. The user will need access to GET the MySQL database records, and the Server Side Scripting engine will need access to GET the MongoDB records as well. This is easy to set up on the Roles Tab in the DreamFactory admin console, as pictured below.

This interface provides an extremely powerful security capability. The MongoDB database is only available for internal use by the Server Side Scripting system. This information has not been exposed through the public REST API. Meanwhile, the MySQL database is available as a client facing REST API, and it has been augmented with some additional information from the MongoDB document store.

Custom APIs

There is another way to combine multiple data sources and return them in a single API call. You could also create a Custom API and simply combine all of the data sources you need and then return them in whatever format was required. To get started, go to the Services Tab in the DreamFactory admin console, and create a Custom Scripting Service. Here is a picture of this interface:

var mongodata = platform.api.get("mongodb/_table/bigtable", event.request.parameters);
var mysqldata = platform.api.get("db/_table/moreinfo", event.request.parameters);

var result = {
   email: mongodata.content.resource[0].email,
   name: mysqldata.content.resource[0].name,
   moreinfo: mysqldata.content.resource[0].example_field
};

return result;

This custom script can be called like other DreamFactory REST API services. In this case I used a URL Parameter with a filter string. The complete call looks something like this:

https://myserver.com/api/v2/customapi?filter=email%3Dbill%yahoo%2Ecom

In this example, the filter string is “email=bill@yahoo.com” but any valid filter string would work. This information shows up in the Custom Script as the event.request.parameters variable, and this is just passed on to the database as in the previous example. The information from the various data sources can then be formatted in any manner and returned to in the REST API response.

There are a few things to note about this example. First, the Custom Service itself must be enabled in the user’s role, and the databases that are used must be granted access by the scripting engine. So in this example the databases are completely hidden and public access is only available through the Custom Script.

Lastly, you can also set up a complete Service Definition for your Custom Scripts. This provides a complete interface in the API Docs tab, so that requests and responses are fully defined and interactive. If the URL Parameters or POST-ed data do not match the definition, the Custom Script will fail. The details on how to set up a Service Definition for a Custom Script will be covered in an upcoming blog.

Here Comes DataMesh

DreamFactory has always had the ability to combine an object with related objects in a single API call. But the latest version of DreamFactory has extended this capability in a magical new direction we call DataMesh. Now, developers can specify "virtual relationships" between fields of any type. This capability works across tables, across databases, and even across SQL and NoSQL data sources. Virtual relationships can be used instead of server-side scripting in many situations. This game changing new feature will be the subject of a detailed blog post in the near future, so stay tuned.

In Conclusion

There are some very powerful capabilities in DreamFactory 2.0 for combining multiple data sources. The Server Side Scripting capability is best used to modify existing REST API services for special cases. The new Custom Scripting Service comes into play when you want to completely take over and write things a new service not available otherwise. Either way, combining data sources in REST API calls is easy in DreamFactory 2.0.