Heroku Connect replicates and synchronises data between a Salesforce org and a Heroku Postgres database. So you can build an app in Heroku using one of seven technologies (including Node.js), hook that app up to the Postgres database, and then your app users will be able to see and modify the same data that the Salesforce users see and modify. Bear in mind that this mechanism allows you to share data: it does not allow you to share business logic.
As this solution is built in/on Heroku, it is incredibly easy to setup and get working; I had the Demo Edition setup and the first couple of SObjects working inside an hour. The mapping UI involves a lot of checkbox clicking, but once done the configuration can be exported and imported. The UI provides overview and drill-down on what is going on. One minor problem I had was quickly and efficiently sorted out by Heroku support. All good.
The clean and simple scenario is where the Postgres data is largely handled as read-only. Then (besides whatever technologies you are writing the Heroku app in) the main thing to get used to is that you are writing SQL not SOQL. Also there are two identifier values: the SFID which is the 18 character (case insensitive) Salesforce ID we are used to and a local Postgres integer ID. Foreign key fields from Salesforce reference the SFID not the ID.
Things get more awkward when you want to insert or update the Postgres data. Heroku Connect handles the synchronisation in that direction too, but some aspects of the implementation leak out:
- The SFID is not available in the transaction where an insert is done (as it is allocated in Salesforce). In my very limited tests, it was available about a second later.
- Fields such as
CreatedDateand any fields populated by Salesforce logic such as insert or update triggers will remain empty until the next synchronization is done; in the basic polling mode that can be up to 10 minutes later. So your users may experience blank fields in the UI when they insert data that change to populated fields when the UI is refreshed 10 minutes later.
Inserting objects into Postgres that have parent/child relationships is awkward. For master/detail, the required pattern is documented in Inserting records with master/detail relationships using external IDs. It requires an “External ID” field to be added to the Salesforce objects (so is a little intrusive at that side) and the Heroku code needs to populate that field and (an automatically added) foreign key with a matching unique value. This then allows Salesforce to create the same master/detail relationship using the normal Salesforce identifier. I don’t know if, but do hope that, the Salesforce foreign key is pushed back into Postgres fairly immediately like the SFID is. There is no mention of how to accomplish this for lookup relationships.