Below is different approach using OData, and in many cases it can make the integration very simple and minimal to no code on salesforce.
What is OData?
It is a standard way to represent data. Detail can be found at : http://www.odata.org/documentation/ however, at very high level it is web service way of representing data like relational database. Main features:
There is metadata ($metdata) to get information about all schemas, tables, columns, and procedures.
SQL like operation
We could do SQL like operation instead of creating new operation for each type (e.g. get employee by first name, by last name, etc..)
Here is naming convention (left: Classic Relational Database, right: OData 4.0 naming)
Heroku is very well known and a lot of documentation could be found at https://www.heroku.com/, so would be focus only on two items:
Single or bi directional link to Salesforce tables/fields to Heroku tables/fields. Any changes to Salesforce is migrated to Heroku Postgres database over extremely fast and efficient SQL link. And if bidirectional link is configured, any changes on Heroku is posted back to Salesforce.
Heroku App Engine
We could host custom Java/Node and other supported language application on Heroku with just click of a button. Hence, I wrote custom Java app using Apache Olingo to host on Heroku platform.
This app generates metadata and connects to heroku postgres to get data and exposes everything as OData service using Apache Olingo framework.
- Had to use Tomcat (instead of http://sparkjava.com) as Olingo requires servlet
- Need to implement two interfaces
- Metadata Interface (to render schema, entity, entity set)
- Data Processor (to fetch and return the data)
- Had to remove http header accept, as causing issue with Apache Olingo
Putting everything together
- Once it is exposed as OData Service, Salesforce can connect and all the EntitySet exposed in OData would be available as external object on Salesforce (ends with __x)
- We can do SOQL, SOSL, indirect lookup on those Salesforce Object
- This would be zero code on Salesforce and on Heroku side, we can get data from Cache, PostGres or External App using Rest or SOAP api
On Salesforce side, it would be just providing URL and it will automatically list down all objects and it will be able to create objects as selected.
- Reiterating the first diagram, Heroku Connect provides alternative to accessing and updating data via API and it is super helpful if app is living on Heroku or would like to connect directly to postgres database
- Salesforce can connect to external app via Odata on heroku, and that would reduce the code on Salesforce org and promote more click over code approach
Code for Apache Olingo implementation can be found at :