Want to Watch a Video Instead?
Configuring the MySQL and IBM DB2 APIsFor the purposes of this tutorial I’ll assume you’re already familiar with DreamFactory fundamentals, including how to generate database-backed REST APIs. If not, I suggest taking a few minutes to watch our introductory video at https://academy.dreamfactory.com.
Configuring Data MeshAfter generating your APIs, enter DreamFactory’s
Schemacomponent and select the API and table that will serve as the relationship parent. In the following screenshot I’ve chosen the
MySQLAPI and the
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 Relationshipbutton you can create new relationships where they didn’t previously exist, including relationships between two databases. Click on the
Add Virtual Relationshipbutton 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 RelationshipNow 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
employeestable looks like prior to configuring Data Mesh:
After querying Data Mesh, the results look like this: