DreamFactory Blog

SQL and REST - Working with Related Data

Posted by Lee Hicks on Wed, Feb 12, 2014

    

As a follow up to our other blog posts on adding a REST API to your SQL database and retrieving related data from that REST API, I wanted to describe, in more detail, the power of the "related records" feature of our SQL DB Service of the REST API.

SQL databases hold huge amounts of the existing data stored in the world. Data that is vital to consumer and enterprise applications alike.

One of the powerful features of SQL databases is the ability to relate separate data records and perform queries that retrieve that related data all at once. One problem is that this requires a fairly in-depth knowledge of your database, it's schema, and SQL syntax. Another is that this ability of your database is not easily accessed from the web. For the veteran DB and server-side engineers, this may not seem like a big deal, but for a lot of front-end developers, that either don't know or don't want to know all that is involved, it is no small feat.

DreamFactory's SQL DB Service makes getting to your vital SQL data and using it from your applications easier than ever.

Getting the Whole Picture

When using data in your application, you typically want to retrieve, interact, and display related data as complete units, not individual pieces. You have a couple of choices for getting and managing this data from the database...

  1. you can pull copies of the data from each table in separate queries, keep schema-specific logic in the application to "link" them locally for use or display, and maintain separate copies for updating the database using separate posts for each table,
  2. or you can pull the related data in a single query using possibly complicated SQL join, temporary tables, etc., also keeping logic in your application as to where the data came from and how to update the individual pieces back on the database,
  3. or you, or your DB admin, can add views (if they are supported by your database) to pull the related data from various tables and return it all together, also adding separate "updating" views for updating portions of that related data back to the database.

Well, anyone that has done it the first way, will quickly tell you what a major pain that is, not to mention the bloat that happens to your application to accomplish it. Separate queries and posts increase network traffic and slow performance. Keeping separate copies of the data obviously requires more memory. All bad news for client or mobile apps.

The second approach is doable if your application uses data as read only. The minute you have to support updating that data back to the database it gets almost as bad as the first.

Views are definitely the way to go if you can get them added to your database and have access to them. DreamFactory supports views just like regular tables (currently only in read only with no schema admin support). Here again, updating the data back to the database is painful as update-able views, if supported, are restrictive and sometimes difficult to deal with.

So what's a guy (or gal) to do? Well, as you read in our earlier blog about retrieving related records, the DreamFactory REST API can return related records as part of the primary record so that all of the related data is in one JSON record.

DreamFactory's SQL DB service discovers these relationships automatically from the schema in your database. You don't have to provision anything except the connection parameters. Consider the Contact schema from the earlier blog showing related data (returned as JSON from the "db" service, reduced and edited here for clarity).

GET /rest/db/?names=Contacts,ContactInfos,ContactGroups,ContactRelationships

{
    "table": [
        {
            "name": "Contacts",
            "primary_key": "contactId",
            "field": [
                {
                    "name": "contactId",
                    "type": "id",
                    "db_type": "int(11)",
                    "allow_null": false,
                    "auto_increment": true,
                    "is_primary_key": true
                },
                ...
            ],
            "related": [
                {
                    "name": "ContactInfos_by_contactId",
                    "type": "has_many",
                    "ref_table": "ContactInfo",
                    "ref_field": "contactId",
                    "field": "contactId"
                },
                {
                    "name": "ContactRelationshipss_by_contactId",
                    "type": "has_many",
                    "ref_table": "ContactRelationships",
                    "ref_field": "contactId",
                    "field": "contactId"
                },
                {
                    "name": "ContactGroupss_by_ContactRelationships",
                    "type": "many_many",
                    "ref_table": "ContactGroups",
                    "ref_field": "contactGroupId",
                    "join": "ContactRelationships(contactId,contactGroupId)",
                    "field": "contactId"
                }
            ]
        },
        {
            "name": "ContactInfo",
            "primary_key": "infoId",
            "field": [
                {
                    "name": "infoId",
                    "type": "id",
                    "db_type": "int(11)",
                    "allow_null": false,
                    "auto_increment": true,
                    "is_primary_key": true
                },
                {
                    "name": "contactId",
                    "type": "reference",
                    "db_type": "int(11)",
                    "allow_null": true,
                    "is_foreign_key": true,
                    "ref_table": "Contacts",
                    "ref_fields": "contactId"
                },
                ...
            ],
            "related": [
                {
                    "name": "Contacts_by_contactId",
                    "type": "belongs_to",
                    "ref_table": "Contacts",
                    "ref_field": "contactId",
                    "field": "contactId"
                }
            ]
        },
        {
            "name": "ContactGroups",
            "primary_key": "contactGroupId",
            "field": [
                {
                    "name": "contactGroupId",
                    "type": "id",
                    "db_type": "int(11)",
                    "allow_null": false,
                    "auto_increment": true,
                    "is_primary_key": true
                },
                ...
            ],
            "related": [
                {
                    "name": "ContactRelationshipss_by_contactGroupId",
                    "type": "has_many",
                    "ref_table": "ContactRelationships",
                    "ref_field": "contactGroupId",
                    "field": "contactGroupId"
                },
                {
                    "name": "Contactss_by_ContactRelationships",
                    "type": "many_many",
                    "ref_table": "Contacts",
                    "ref_field": "contactId",
                    "join": "ContactRelationships(contactGroupId,contactId)",
                    "field": "contactGroupId"
                }
            ]
        },
        {
            "name": "ContactRelationships",
            "primary_key": "contactRelationshipId",
            "field": [
                {
                    "name": "contactRelationshipId",
                    "type": "id",
                    "db_type": "int(11)",
                    "allow_null": false,
                    "auto_increment": true,
                    "is_primary_key": true
                },
                {
                    "name": "contactId",
                    "type": "reference",
                    "db_type": "int(11)",
                    "allow_null": true,
                    "is_foreign_key": true,
                    "ref_table": "Contacts",
                    "ref_fields": "contactId"
                },
                {
                    "name": "contactGroupId",
                    "type": "reference",
                    "db_type": "int(11)",
                    "allow_null": true,
                    "is_foreign_key": true,
                    "ref_table": "ContactGroups",
                    "ref_fields": "contactGroupId"
                }
            ],
            "related": [
                {
                    "name": "ContactGroups_by_contactGroupId",
                    "type": "belongs_to",
                    "ref_table": "ContactGroups",
                    "ref_field": "contactGroupId",
                    "field": "contactGroupId"
                },
                {
                    "name": "Contacts_by_contactId",
                    "type": "belongs_to",
                    "ref_table": "Contacts",
                    "ref_field": "contactId",
                    "field": "contactId"
                }
            ]
        }
    ]
}

The schema states that...

  • Contacts have directly related records called ContactInfos.
  • Contacts also have indirect relationship with ContactGroups, via ContactRelationships junction table.
  • Contacts can have many ContactInfos and belong to multiple ContactGroups.
  • Each ContactInfo can only apply to one Contact, linked by contactId field.
  • Each ContactGroup "contains" multiple Contacts, linked by rows in ContactRelationships table.

A retrieve by id or a query with a filter string request using the "related" URL parameter pulls the existing relationships, and adds them to the requested record(s) as arrays using the relationship name as the field name, (coming end of February - pass '*' as the value of "related" parameter to get all relationships). For example, retrieving the record for a specific contact with primary key '12'...

GET /rest/db/Contacts/12?related=ContactInfos_by_contactId,ContactRelationships_by_contactId,ContactGroups_by_ContactRelationships

{
  "contactId": 12,
  "firstName": "Curtis",
  "lastName": "Lu",
  "imageUrl": "",
  "twitter": "@curtis9",
  "skype": "curtis9",
  "ContactInfos_by_contactId": [
    {
      "infoId": 34,
      "contactId": 12,
      "infoType": "Home",
      "phone": "500 555-0137",
      "email": "curtis9@Home.com",
      "address": "5927 Rainbow Dr",
      "city": "UPPER TRACT",
      "state": "WV",
      "zip": "26866",
      "country": "USA"
    },
    {
      "infoId": 35,
      "contactId": 12,
      "infoType": "Work",
      "phone": "500 555-0136",
      "email": "curtis9@Work.com",
      "address": "5167 Condor Place",
      "city": "UPPER TRACT",
      "state": "WV",
      "zip": "26866",
      "country": "USA"
    },
    {
      "infoId": 36,
      "contactId": 12,
      "infoType": "Mobile",
      "phone": "500 555-0177",
      "email": "curtis9@Mobile.com",
      "address": "1873 Mt. Whitney Dr",
      "city": "UPPER TRACT",
      "state": "WV",
      "zip": "26866",
      "country": "USA"
    }
  ],
  "ContactRelationshipss_by_contactId": [
    {
      "contactRelationshipId": 12,
      "contactId": 12,
      "contactGroupId": 3
    }
  ],
  "ContactGroupss_by_ContactRelationships": [
    {
      "contactGroupId": 3,
      "groupName": "South East"
    }
  ]
}

Creating the Whole Picture

When you create new records using our REST API via the POST command, you can add new relationships to the records in two ways...

  • create new related records and automatically create the relationship between them,
  • or create a relationship to other already existing records (updating them at the same time).

For this example, lets add the always popular Joe Smith who works for ACME Inc. to our Contacts. At the same time we will add some work information and create a new group "ACME Inc." for his company. We also want to add Joe to our existing "Sales" contact group.

POST /rest/db/Contacts

{
  "firstName": "Joe",
  "lastName": "Smith",
  "twitter": "",
  "skype": "amce_joe",
  "ContactInfos_by_contactId": [
    {
      "infoType": "Work",
      "phone": "555-555-1234",
      "email": "joesmith@acme.com",
      "address": "1234 Demo Way",
      "city": "ATLANTA",
      "state": "GA",
      "zip": "30303",
      "country": "USA"
    }
  ],
  "ContactGroups_by_ContactRelationships": [
    {
      "groupName": "ACME Inc."
    },
    {
      "contactGroupId": 1,
      "groupName": "Sales"
    }
  ]
}

Notice that no primary key is included in the related ContactInfo or the new ContactGroup records. This indicates that they are new records that need to be created. After the records are created, the relationships are automatically created. For the ContactInfo, the contactId field is simply set to the newly created Contact records primary key. For the ContactGroups, an entry is added to the ContactRelationships junction table linking the two new records.

To relate the new Contact record with existing records, just include the identifying fields or the whole existing record. Joe is added to the "Sales" group by automatically adding an entry in the ContactRelationships junction table. The "groupName" is not necessary in this case, only the primary key of the group record. In fact, if it is included, it is considered for update on that record (see updates section below). Note that for direct relationships, if the relating field (for ContactInfo it is 'contactId') is in the record, the value will be overwritten with the current Contact record's primary key.

Updating the Whole Picture

When you update existing records using the REST API PATCH command, you can change relationships to the records in three ways...

  • create new related records and automatically create the relationship between them,
  • or create a relationship to other already existing records (updating them at the same time),
  • or remove a relationship to existing related records.

For this example, lets say you and Joe (given the id "33" from earlier creation) become good golfing buddies and he gives you his twitter handle and home phone and address. Joe also got promoted to a new office across the street, but still with ACME. So you want to add him to your Golf group, update his work info, add his home info, and update his record with the twitter handle.

PATCH /rest/db/Contacts/33

{
  "twitter": "@popular_joe",
  "ContactInfos_by_contactId": [
    {
      "infoType": "Home",
      "phone": "555-555-5678",
      "email": "golfin_joe@gmail.com",
      "address": "999 Back Nine Drive",
      "city": "ATLANTA",
      "state": "GA",
      "zip": "30301",
      "country": "USA"
    },
    {
      "infoId": 44,
      "contactId": 33,
      "infoType": "Work",
      "address": "1111 Demo Way"
    }
  ],
  "ContactGroups_by_ContactRelationships": [
    {
      "contactGroupId": 9,
      "groupName": "Golf"
    }
  ]
}

As with any of the above scenarios, when updating related records, you can pass only what changed or the whole record, whatever is most convenient for your application. You can also pass Joe's whole record including the primary key to the /rest/db/Contacts. This also works for updating multiple Contact records at once.

As you can imagine, old Joe eventually pulled the ripcord on his golden parachute and left ACME. So now you want to remove him from the Sales and ACME groups, and remove his work contact info, and update his record to remove his skype work id.

To remove relationships from a record, we include the relating fields to indicate to the service to remove the relationship. With directly related records, just set the relating field to null. In our example, this is "contactId" for ContactInfo records. We include the following URL parameter to help with cleanup of disassociated or abandoned records.

  • allow_related_delete - Defaults to false. When the relating field's database schema is set so that it will not allow null values (allow_null is false), i.e. requires a parent value, this record can not stand alone unrelated and thus must be deleted. As a safety catch, this scenario will return an error unless this URL parameter is set to true, at which time the service will attempt to delete the related child record.

For records related via a junction table, add the serviced table's primary key field and set it to null (i.e. the table being serviced here is Contacts, note the "table-dot-field" notation separating it from the rest of the fields in the record). In our example, this is "Contacts.contactId" in the ContactGroups records. The relationship is automatically removed by deleting the linking entry from junction table, in this case ContactRelationships. For records related via junction tables, like ContactGroups, this scenario can be managed by schema setup (on_update/on_delete properties with settings of SET_NULL or CASCADE).

PATCH /rest/db/Contacts/33

{
  "skype": "",
  "ContactInfos_by_contactId": [
    {
      "infoId": 44,
      "contactId": null,
      "infoType": "Work"
    }
  ],
  "ContactGroups_by_ContactRelationships": [
    {
      "contactGroupId": 3,
      "Contacts.contactId": null
      "groupName": "ACME"
    },
    {
      "contactGroupId": 1,
      "Contacts.contactId": null
      "groupName": "Sales"
    }
  ]
}

As before, the "infoType" and "groupName" fields are not required, and are just there for clarification.

To Recap

Native record fields are updated as usual.

If related records are contained in the request, the fields contained in the request trigger different actions.

If the related table's primary key field is not included in the related record, it is assumed that this record doesn't exist yet and, if allowed, the related record is created along with the new relationship.

For Parent-Child Relationships: Existing related records are updated, if allowed. If parent lookup field is in the record and not set to null, the value will be overwritten with the current parent record's primary key (useful when "adopting" other records). If parent lookup field is in the record and it is set to null, indicating that this child needs to be removed from the relationship, the lookup field is set to null, or if allowed (see above) is deleted.

For Many-to-Many Relationships: If primary key of related table (using dot notation) is set and has a null value, the junction table record is deleted thus removing the relationship.

All of this logic works for creating or updating single records or multiple records at a time. The "related" parameter can also be used (along with "fields" parameter) when making requests (POST, PATCH, DELETE) and retrieving changed data in one shot (see the API/SDK in the admin panel).

Check out our video tutorials and documentation for more information on SQL and other topics. As always, we welcome your comments.