I posted this as a response on another topic, but I though it might get lost at the bottom of a long thread, so I’m sharing it here.
The use case is whenever you want to take data from your Particle device and send it to a database somewhere - in the cloud, on your machine, wherever. Sure, you can use the built-in HTTP functions, but you gotta figure out security, async stuff, etc. so it sounds easy in theory but I found it hard to make everything work solidly.
What I wanted was a way to use the elegant Particle.publish() function, but I wanted to do it simply, not require setting up a dedicated server, and with the flexibility and security of a professionally managed infrastructure.
I use webtasks. These are standalone chunks of javascript code that can be executed in a secure sandbox, linked to a URI. They are hosted by Auth0, which is a pretty interesting company that focuses solely on authentication.
Here’s what you do (it may sound a little involved but some of this is just one-time setup):
========================
FIRST: Use Particle.publish to send your data out of the particle and into the cloud. This is done securely with the existing Particle infrastructure and minimal coding - easy peasy. Let’s say you had something like this in your firmware:
temperature_data = analogRead(tempPin) * temp_conversion_factor;
humidity_data = analogRead(pressPin) * humid_conversion_factor;
Particle.publish("mySQLDataEvent", String::format("%.1f,%.2f", temperature_data, humidity_data), 60, PRIVATE);
This will generate an event that has a format like this:
{"name":"mySQLDataEvent","data":"21.4, 65.32","ttl":"60","published_at":"2016-09-06T00:45:08.376Z","coreid":"0123456789ABCDEFGHIJKLMN"}
========================
SECOND: Go to Auth0 and sign up for a free account. You get free webtasks up to some limit - info in on their website. (No, I don’t work for these guys or have any connection. They just got a good thing going on, that’s all.)
========================
THIRD: Download the webtask CLI. It’s a pretty simple system. Documentation is available at webtask.io. Once you’ve installed the CLI, you do a one-time initialization - this is where they make sure you’re you by sending by email or cell an authentication code.
========================
FOURTH: Create the webtask code. Here is the code scaffold I use:
var mysql = require('mysql@2.7.0');
var uuid = require('uuid@2.0.1');
function errFunc(err) {
console.error('error: ' + JSON.stringify(err));
}
function parseData(obj) { // convert raw data into object to send to mysql
try { // put parsing code here to convert Particle.publish data into MySQL data object
var param = obj.data.split(',');
return {
id: uuid.v4(),
device_id: result.device_id,
timestamp: result.timestamp,
temperature: param[0],
humidity: param[0]
}; // the object keys need to match the MySQL field names
} catch (e) {
return {
parsingFailed: true
};
}
}
function sendData(connection, record) {
var queryString = 'INSERT INTO [TableName] SET ?'; // simple insert SQL code
connection.query(queryString, record, errFunc);
}
function storeData(context, cb, record) {
var connection = mysql.createConnection({
host: '[INSERT HOST NAME HERE - URI OR IP ADDRESS]',
user: context.secrets.USERNAME, // these secrets will be sent upon creation of the webtask
password: context.secrets.PASSWORD,
database: '[INSERT DATABASE NAME HERE]'
});
connection.connect(function(connectError) {
if (connectError) {
console.error('error connecting: ' + connectError.stack);
cb(new Error('Error code: ' + connectError.code));
} else {
console.log('Connected as id ', connection.threadId, 'Sending data: ', record);
sendData(connection, record);
connection.end(function(error) {
if (error) {
console.error('Error: ', error);
cb(new Error('Error: ' + error.code));
} else {
console.error('Disconnected - data stored');
cb(null, {
result: 'Complete'
});
}
});
}
});
}
module.exports =
function(context, cb) {
var record = parseData(context.data);
if (record.parsingFailed) {
cb(new Error('Parsing failed'));
}
storeData(context, cb, record);
};
It’s pretty simple: first you parse the string that comes from Particle.publish into an object that can be sent to MySQL, then you save that object by connecting with the MySQL server and sending an INSERT query. I save this in a file called something like webtask_put_data_to_mysql.js.
========================
FIFTH: Create the webtask. This is done in the webtask CLI. This is the command I use:
wt create webtask_put_data_to_mysql.js --secret USERNAME=username_string --secret PASSWORD=password_string
This command passes the username and password to the webtask code. It is encrypted and embedded in the webtask, so that anyone who stumbles across the code won’t get your credentials. That’s a crucial security feature that is a beautiful thing.
When you create the webtask, you get a confirmation and a URI address:
Webtask created
You can access your webtask at the following url:
https://webtask.it.auth0.com/api/run/YOUR-WEBTASK-CONTAINER/webtask_put_data_to_mysql?webtask_no_cache=1
Copy that URI to the clipboard; you’ll need it for the last step.
========================
FINALLY: Integrate your webtask with the Particle webhook. Go to console.particle.io and sign into your account. Go to the integrations section, and click on New Integration, then click on Webhook. A form will appear; under Event name put “mySQLDataEvent” and in URL paste the location you just copied in step 5. Click Create Webhook, and voila! the data streaming out of your Particle device will be sent to a MySQL server and stored in a table for you to use however you see fit.
========================
I’ve been using this system quite a bit lately, and it works really well. The webtask doesn’t require a server; it just sits around until that URI gets called, it starts up, executes in a sandbox, and then goes away. And it is secure; your credentials are nowhere to be found in the code itself.
And the architecture is flexible; I originally sent data to a MySQL server, but recently started using RethinkDB and I just had to change the webtask code; everything else works great. And there’s no reason you can’t have several webtasks reading the same data stream, doing separate things.
Hope this helps. Cheers,
L3