Mashing up Force.com APIs with ql.io (Part 1)

Last Friday, the Salesforce Platform Developer Evangelism Team ran a (mostly) internal hackday, bringing together evangelists, support staff, product management, R&D engineers and a couple of special community guests for a day of exploratory hacking. Although the theme for the day was ‘Mobile’, this wasn’t a constraint, and I teamed up with sales support engineer Henry Liu to explore ql.io, a ‘declarative, data-retrieval and aggregation gateway for quickly consuming HTTP APIs’ created by eBay’s platform engineering group, in use (among other places) at Kinvey for integrating with third-party data sources. In this blog entry (the first in a three part series) I’ll describe how we got started, creating a simple wrapper for a Force.com REST API call.

This script from the examples page illustrates a typical ql.io use case, mashing up three different web services from two providers:

select e.ItemID as id, e.Title as title, e.ViewItemURLForNaturalSearch as url, g.geometry.location as latlng
  from details  as e, google.geocode as g
  where e.itemId in (select itemId from finditems where keywords = "iPad")
        and g.address = e.Location

The inner select uses the eBay Finding API to search for items including the keyword ‘iPad’, while the outer select retrieves geographic coordinates for the items’ locations from the Google Geocoding API and item detail from the eBay Shopping API. ‘Tables’, defined elsewhere, hold the mappings to actual web service requests – the ql.io documentation does a good job of explaining how it all fits together. The query results are in JSON format:

[
  {
    "id": "360575315333",
    "title": "Apple iPad 1st Generation 32GB, Wi-Fi, 9.7in - Black (MB293LL/A) (G)",
    "url": "http://www.ebay.com/itm/Apple-iPad-1st-Generation-32GB-Wi-Fi-9-7in-Black-MB293LL-A-G-/360575315333",
    "latlng": {
      "lat": 40.7143528,
      "lng": -74.00597309999999
    }
  },
  ...lots more...
]

Given that the table definitions add only a dozen or so more lines of script, it was clear that ql.io’s table abstraction would allow us to quickly integrate a bundle of different web services without writing a mountain of boilerplate code. We had a couple of goals in mind for the day – mashing up the Force.com REST API with Google Geocoding to get latitude and longitude for Account records, and filtering Chatter posts on keywords – but the first step was to figure out how to access the Force.com REST APIs from ql.io.

After a couple of false starts trying to configure ql.io with user credentials and have it login via OAuth Username-Password Flow, we realized that it’s best to think of ql.io as a filter, supplying it with an OAuth access token and instance url rather than configuring it to login itself. Simulating a client app, we obtained the necessary values via curl (parameters modified for this example!):

$ curl -d 'grant_type=password&client_id=MY_APP_ID&client_secret=MY_APP_SECRET&username=user@example.com&password=mypassword' 
     -H 'X-PrettyPrint: 1' https://login.salesforce.com/services/oauth2/token
{
  "id" : "https://login.salesforce.com/id/ORG_ID/USER_ID",
  "issued_at" : "1363061090025",
  "instance_url" : "https://na9.salesforce.com",
  "signature" : "SOME_BASE_64",
  "access_token" : "MY_ACCESS_TOKEN"
}

Now it was straightforward to create a script in ql.io/console that would retrieve a list of Accounts:

create table accounts
  on select get from "https://na9.salesforce.com/services/data/v27.0/query?q=SELECT+Name+FROM+Account"
  using headers 'Authorization' = 'Bearer MY_ACCESS_TOKEN'
  resultset 'QueryResult.records'

select * from accounts

With the result:

[
  {
    "type": "Account",
    "url": "/services/data/v27.0/sobjects/Account/001E0000002Jv2bIAC",
    "Name": "GenePoint"
  },
  {
    "type": "Account",
    "url": "/services/data/v27.0/sobjects/Account/001E0000002Jv2cIAC",
    "Name": "United Oil&Gas, UK"
  },
  ...lots more...
]

This is a start, but, clearly, hardcoding access tokens into a web console isn’t a viable solution! It turns out that you can configure script routes in ql.io, wrapping select statements as web services, so, after a bit more prodding, poking and reading documentation, we created one file in the ql.io server’s tables directory:

create table force.accounts
  on select get from "https://{instance_url}/services/data/v27.0/query?q=SELECT%20Name%20FROM%20Account"
  using headers 'Authorization' = '{authorization}'
  resultset 'QueryResult.records'

And another in the routes directory:

return select * from force.accounts
  via route '/accounts' using method get;

We set those instance_url and authorization parameters via HTTP headers from a client, like this:

curl -H 'instance_url: na9.salesforce.com' -H 'Authorization: Bearer MY_ACCESS_TOKEN' http://localhost:3000/accounts

Et voilà:

[{"type":"Account","url":"/services/data/v27.0/sobjects/Account/001E0000002Jv2bIAC","Name":"GenePoint"},{"type":"Account","url":"/services/data/v27.0/sobjects/Account/001E0000002Jv2cIAC","Name":"United Oil&Gas, UK"},...lots more...]

So far, we haven’t done anything you can’t do by accessing the Force.com REST API directly, but next time, in part 2, we’ll see how, with a Formula Field and just a few lines more ql.io script, we can get the latitude and longitude for each Account from the Google Geocoding API, far quicker than if we implemented an equivalent REST Web Service in Apex, and without consuming any callouts.