Virtual Relationships Between Database Tables

A few years ago, we wrote a blog post about working with related data using the DreamFactory REST API platform. The SQL DB service can return a table along with any number of related tables in a single API call. You can POST the JSON document back to the API in order to create, update, or delete the related data. DreamFactory automatically maintains all of the links and junction objects for you. This is possible because we use the schema information stored in the database to learn about the various relationships between the tables.

But what if you have tables that are related in some way, and those relationships are not detailed in the schema? What if the related tables exist on different databases, or even on different servers? Previously, the only solution was to make multiple calls from the client-side and assemble the information as needed. Or you could create a custom service or write a server-side script to combine unrelated tables of data. Here is a recent blog post about how to do this.

But now, with the introduction of DreamFactory 2.0, developers can specify virtual relationships between any two tables. The tables can be located on different databases, and the databases can be located on different servers. This is a game changing capability because now a developer can create, read, update, and delete related data across multiple servers with a single REST API call!

These virtual relationships are imposed from outside by the DreamFactory platform. There is no need to change the actual configuration of your database. What's more, once DreamFactory knows about the virtual relationships, you can work with related data of any kind without the need for server-side scripting. This feature packs even more capability into the request URL string itself, further reducing the need to build custom REST APIs.

The Magic of DataMesh

We call this revolutionary new feature DataMesh. The SQL DB service now combines the existing relationships found in the schema with any number of virtual relationships specified by the developer. These virtual foreign keys are stored locally as part of the system information maintained by the DreamFactory platform. So now, developers can provision virtual relationships between any of the database services found on the platform. The REST API normalizes database access, so for example, you could combine information from SQL Server, Oracle, and MySQL seamlessly. These new virtual relationships are then exposed in the table records API and invoked with a simple URL parameter just like the other existing relationships.

An Inter-Service Example

Say for example that you have a legacy SQL database that contains a contact table with fields like email, name, phone, etc. Over the years a customer table was also created to store purchase information, and these records include a username field which is in the form of an email address. The schema for this database doesn't reflect any relationships between these tables. This might be for historical reasons, or perhaps the developer does not have permission to modify the schema. At any rate, now you need to create an application which displays information from the contact table along with the related purchase history from the customer table.

Related reading: SQL and REST working with related data

Using DataMesh, you could create a virtual relationship between the contact table and the customer table by taking the following steps:

• In the Admin Console you need to provision a service for the legacy database, for this example let's use the name mysql
• Choose your primary table to access the data, in this case the contact table
• Select the customer table via the Schema Tab in the Admin Console

• Select the username field, and open for edit
• Click the Foreign Key and Virtual Foreign Key checkboxes, this will enable the Reference Table and Field selection menus below
• Select contact for the table and email for the field
• Then click the Update button to save the configuration

This will automatically create a belongs_to relationship from the customer to the contact, as well as a has_many relationship in the opposite direction. That's it! You have created a virtual relationship.

To see how these different relationships are represented or provisioned via the API, take a look at the following JSON snippets...

As a baseline, here is a field with a foreign key that is part of the existing database schema. In this case our contact table points to a business account table:

    {

        "name": "account_id",

        "type": "reference",

        "is_virtual_foreign_key": false,

        "is_foreign_service": false,

        "ref_service": null,

        "ref_table": "account",

        "ref_fields": "id"

    }

Here is the customer field username with a virtual foreign key pointing to the contact table on the same database:

    {

        "name": "username",

        "type": "string",

        "is_virtual_foreign_key": true,

        "is_foreign_service": false,

        "ref_service": null,

        "ref_table": "contact",

        "ref_fields": "email"

    }

The relationship from the perspective of the contact table:

    {

        "name": "customer_by_username",

        "type": "has_many",

        "field": "email",

        "is_virtual": true,

        "is_foreign_service": false,

        "ref_service": null,

        "ref_table": "customer",

        "ref_fields": "username"

    }

The relationship from the perspective of the customer table:

    {

        "name": "contact_by_username",

        "type": "belongs_to",

        "field": "username",

        "is_virtual": true,

        "is_foreign_service": false,

        "ref_service": null,

        "ref_table": "contact",

        "ref_fields": "email"

    }

A Service-to-Service Example

Let's work with the above example again, except this time assume that the customer table exists on a separate database. The only real differences in provisioning are as follows:

• In the Admin Console you need to provision an additional service for the separate database that contains the customer table, for this example let's use the name ecommerce
• After clicking the Foreign Key and Virtual Foreign Key checkboxes, you also need to click the Foreign Reference Service checkbox which allows you to select the additional database

Take note of the "dot" notation in the relationship name. This indicates that the relationship is pointing to a different service.

And here are the JSON snippets for this scenario...

Here is the customer field username with a virtual foreign key pointing to the contact table on the legacy database we named mysql:

    {

        "name": "username",

        "type": "string",

        "is_foreign_key": true,

        "is_virtual_foreign_key": true,

        "is_foreign_service": true,

        "ref_service": "legacy",

        "ref_table": "contact",

        "ref_fields": "email"

    }

The relationship from the perspective of the contact table:

    {

        "name": "ecommerce.customer_by_username",

        "type": "has_many",

        "field": "email",

        "is_virtual": true,

        "is_foreign_service": true,

        "ref_service": "ecommerce",

        "ref_table": "customer",

        "ref_fields": "username"

    }

The relationship from the perspective of the customer table:

    {

        "name": "mysql.contact_by_username",

        "type": "belongs_to",

        "field": "username",

        "is_virtual": true,

        "is_foreign_service": true,

        "ref_service": "mysql",

        "ref_table": "contact",

        "ref_fields": "email"

    }

Note that the names of the relationships are auto-generated, but can be aliased to something more desirable for use in the API if needed.

Using the Virtual Relationships

Any of the created relationships can be used in the "related" URL query parameter, just like existing relationships defined by the schema. The beauty of this is that an administrator can provision the databases and create the virtual relationships and then a client developer can simply use the related data given their role based access settings. No custom services needed. In our demo data, the contact "Jane Smith" was found in the customer table with the same email.

https://demo/api/v2/mysql/_table/contact?related=customer_by_username

{

    "resource": [

        {

            "id": 1,

            "first_name": "John",

            "last_name": "Smith",

            "display_name": "John Smith",

            "email": "johnsmith@demo.com",

            "customer_by_username": []

        },

        {

            "id": 2,

            "first_name": "Jane",

            "last_name": "Smith",

            "display_name": "Jane Smith",

            "email": "janesmith@demo.com",

            "customer_by_username": [

                {

                    "id": 2,

                    "display_name": "Jane Elise Smith",

                    "username": "janesmith@demo.com"

                }

            ]

        }

    ]

}

Video

Also check out this short video demo of the material covered in this blog.

In Conclusion

There are some very powerful capabilities in DreamFactory 2.0 for combining multiple data sources. The new DataMesh feature takes our product one step further in getting all the data your application needs in a easy, clean, and succinct manner. That plus all of the other great features of DreamFactory should get you well on your way to happy app development!