Reid Carlberg

Connected Devices, & Other Adventures

Tag Archives: heroku

Access IoT Data in Salesforce using Heroku Connect OData


Yesterday I wrote about getting IoT Data into Heroku.  It’s pretty cool, but other than looking at a Heroku PostgreSQL Dataclip, there’s not a lot you can do with the super simple IoT host app I built.  Which made me wonder:

OK, I have the data. Now what?

Well, my usual tool of choice is, but this kind of high-frequency, low-difference data doesn’t work well when fully imported to that environment. This got me thinking about two relatively new and completely exciting things: Lightning Connect and Heroku Connect External Objects.

Lightning Connect (earlier blog, cool tutorial) is a product that includes external data in Salesforce by reference — the data is accessed via the OData protocol and is never copied into your org.  Hence it does not count against storage limits. Heroku Connect (Pat’s awesome blog) is all about easy data synchronization between Salesforce and a Heroku PostgreSQL database. But Heroku Connect also includes an External Objects (@vzmind‘s awesome blog) option that publishes any Heroku PostgreSQL table you request in OData format.


I started by adding the Heroku Connect add-on and then opening it.

heroku addons:add herokuconnect
heroku addons:open herokuconnect

Heroku Connect demo edition is free, which is great, but you’ll need to file a ticket to have External Objects activated. Once that is done it’s super simple. Note: External Objects are activated per app, not per user. If you create a new app, you’ll need to request them again.

Start by creating a connection:


Next initialize your username and password.  These will come to your email.


You’ll now see your OData URL as well as a list of tables you might want to share.  Note that the listing of available tables appears to be an asynchronous process — it takes a minute for them to show up. Grab a cup of coffee. Then check the ones you want to share.


External Data Source configuration is super easy (with many examples), the only difference here being that the Identity Type is Named Principal and the Authentication Protocol is Password Authentication.  Settings example below based on my setup.


And now we have the Heroku Readings object in Salesforce!


Which is super cool.  But we can make it cooler by connecting this IoT data with something meaningful in Salesforce.  For example, a Device record. As long as I have an External ID field on the Device, I can create an Indirect Lookup between that Device and the Heroku Readings by modifying the Reading’s “Device Name” field type.


And now we have live IoT data meaningfully connected to Salesforce data without importing it and using up storage.


Which, I gotta say, feels pretty much like a HOLY SHIT moment to me.

Node.js IoT Host on Heroku: The Simplest Thing that Might Possibly Work


TL;DR – Look ma — CODE that stores DATA which becomes usable in SALESFORCE via OData.

For the last couple of days, I’ve been talking about some hardware I’ve put together (here and here).  These efforts all come from a pretty simple problem I have:

My house is old, drafty and hard to heat. And I live in Chicago.

This problem is particularly noticeable in January and February, but July and August can be just as bad in the other direction so I need to address it somehow. Ugh.


Well, that which can be measured can be improved, right?  So that’s where I’m going to start. Measurement is actually the easy part. Simple Data Recorder handles that.  Where I’ve struggled is figuring out where to put that data which I measure, how to put it there and then what to do with it.  This post covers the first two.

Most of the time, I work on the side of the Salesforce1 platform.  I’m extremely comfortable in it, but the free Developer Edition will run out of storage very quickly if you start throwing a new record at it every minute or so. Enter the other side of Salesforce1: Heroku.

Naive IoT Host, deployed to Heroku, solves my storage problem. I’m using the free tier of everything so far, and should be able to for ~20 days at my current rate of data production. You can see a live dataclip of conditions in my house right now should you be interested. If you’ve even glanced at the code, you know it won’t scale to 100,000 devices any time soon.  But it will do a couple of dozen just fine, and that’s just the scale I need for this experiment.

The high level end to end looks something like this:

arduino-rpi-heroku-nodejs-postgresEverything starts out on the Arduinos.  The Raspberry Pi polls the Arduinos for data, transforms that data into JSON, and then posts that to a Node.js app running on Heroku. The Node.js app augments that data with current weather conditions and then writes it to a PostgreSQL table.  I have it set to do this about every 5 minutes.

The basic Node.js app is built using Express, but I haven’t spent any significant time optimizing it.

I’m using ipfilter to ensure that only someone from a whitelisted IP address can make a request on the app.  If you aren’t calling from some recognized IP, you get an “unauthorized” response.


Sequelize coordinates the database activities.  For some reason I didn’t look at ORM when I wrote that tweet a couple of months ago. Sequelize isn’t perfect, but it’s super fast as a getting started tool, and it’s way better than anything I might write.  You start by declaring a model, which it then automatically creates for you in the actual database.  It doesn’t do database modifications as far as I can tell — there’s some kind of a separate module for that which I haven’t investigated yet.

var Reading = sequelize.define('Reading', {
	deviceName: Sequelize.STRING,
	rangeStartDate: Sequelize.DATE,
	rangeEndDate: Sequelize.DATE,
	maxBrightness: Sequelize.INTEGER,
	minBrightness: Sequelize.INTEGER,
	maxTemperature: Sequelize.DECIMAL(4,2),
	minTemperature: Sequelize.DECIMAL(4,2),
	weatherSummary : Sequelize.STRING,
	weatherTemperature: Sequelize.DECIMAL(4,2),
	weatherWindSpeed: Sequelize.DECIMAL(4,2),
	weatherWindBearing: Sequelize.INTEGER

Lastly, the Forecast module connects me to to get the current outside weather conditions.  I thought this would be useful information to keep in mind as I start to look at the data. Now, I could have chosen to work with current-condition-then data in a number of ways, including querying for it analysis time (whenever that turns out to be), but the incremental cost of including it with each reading was so trivial I decided to do it.

And voila — for your enjoyment — the data.

OK, so far so good.

Next: Accessing the data in Salesforce using Heroku Connect External Objects OData

%d bloggers like this: