As a follow up to our previous blog post about retrieving related data from that REST API, I wanted to dive deeper into the power of the "related records" feature of DreamFactory's database-backed APIs
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 within your applications easier than ever.
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.
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 the solution? As you read in our earlier post 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 relational database APIs can discover these relationships automatically from the schema in your database. You don't have to provision anything except the connection parameters. Consider the following Contact schema:
GET /api/v2/database/_table/?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:
When retrieving a record by id or using the filter string request along with the "related" URL parameter, DreamFactory will pull the existing relationships, and add them to the requested record(s) as arrays using the relationship name as the field name, (you can also 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 /api/v2/database/_table/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"
}
]
}
When you create new records using our REST API via the POST command, you can add new relationships to the records in two ways:
For this example, let's 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 /api/v2/database/_table/contacts
{
"firstName": "Joe",
"lastName": "Smith",
"twitter": "",
"skype": "acme_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.
When you update existing records using the REST API PATCH command, you can change relationships to the records in three ways:
For this example, let's 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.
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 /api/v2/database/_table/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
There are several important points to keep in mind when working with related records: