Today’s IT teams are struggling to make sense of organizational data that has been compiled piecemeal and often stored within disparate storage solutions. Often this information needs to be aggregated and presented in a unified format, yet pulling data from multiple data sources and displaying it in a coherent way can be onerous and error-prone. The challenge is compounded when the data resides in different databases, and possibly within different clouds.
To remedy this, companies often embark upon costly and time consuming data lake, data mart, and data warehouse projects. In many cases though, the IT team is simply looking for an effective solution to combine data within a single unified interface!
In this tutorial I’ll introduce you to a powerful and very popular feature of the DreamFactory platform called Data Mesh. Using Data Mesh you can create virtual relationships between two databases much in the same way you can create foreign key relationships between two database tables. We’ll walk through an example in which a MySQL database running on Amazon RDS is meshed with an IBM DB2 database running on IBM Cloud, merging the data together so it can be retrieved via a single API endpoint.
Want to Watch a Video on this Topic Instead?
A video-based version of this tutorial is available at https://academy.dreamfactory.com. Or just click on the video below!
Configuring the MySQL and IBM DB2 APIs
For the purposes of this tutorial I’ll assume you’re already familiar with DreamFactory fundamentals, including how to generate database-backed REST APIs. If you’d like to follow along in your own environment, you’ll need to configure two database APIs within your DreamFactory instance.
Configuring Data Mesh
After generating your APIs, enter DreamFactory’s
Schema component and select the API and table that will serve as the relationship parent. In the following screenshot I’ve chosen the
MySQL API and the
employees table: Once selected, you can scroll down to the table’s “Relationships” section. This section warrants a bit of explanation. When DreamFactory generates a database API, it analyzes all tables, stored procedures, views, table columns and datatypes, and foreign key relationships. This section contains a list of join aliases that you can use to easily join tables via the API: However you’re not limited to these aliases; by clicking the
Add Virtual Relationship button you can create new relationships where they didn’t previously exist, including relationships between two databases. Click on the
Add Virtual Relationship button and you’ll be presented with an interface for defining the relationship between two databases. See the following screenshot: In this screenshot, I’ve defined the fields as follows:
Always Fetch: This field enables the virtual relationship. You can also optionally enable the relationship on demand via the API.
Type: This field determines the relationship type. You can choose from
Has Many, and
Many to Many.
Reference Service: This field identifies the related service. It’s set to
DB2because the relationship pertains to the previously configured IBM DB2 API.
Reference Table: This field identifies the related table. Recall we selected the
employeestable, so we’re going to relate the
employeestable to the
Reference Field: This field identifies the foreign key field found in the related
After defining these fields, save the changes and you’re ready to begin using the new relationship!
Querying the Relationship
Now that the relationship has been defined, let’s execute a query and view the combined results. We’ll begin by showing what a query to the
employees table looks like prior to configuring Data Mesh: After querying Data Mesh, the results look like this:
DreamFactory’s Data Mesh feature offers an incredibly straightforward, point-and-click solution for creating sophisticated and transparent unified queries. You’re certainly not limited to meshing two databases together; try meshing two, three, or more databases together and marvel over the time and aggravation savings!