Following on from my recently-created Orchestrate module for Node-RED, I’ve now created a similar set of nodes for another cool new kid on the databases block – Crate.

As it has been brought to our attention that we haven’t done a great job of explaining what exactly Node-RED is on this blog, here is another attempt at doing so to add some context to this post.

Node-RED is an app built on the Node.js runtime environment (and so heavily rooted in JavaScript). It’s a free and open source offering from IBM Labs and anyone can download it and use it on their own machines or get a free IBM Bluemix account and host it there.

Node-RED allows us to quickly and easily build systems for managing IoT devices (among other things) by simply dragging and dropping modules (a.k.a nodes) onto and around a workspace and then connecting them to form flows (a bit like Microsoft Visio diagrams). At the moment, it usually runs on cloud servers and interfaces using MQTT and HTTP calls, but it’s not hard to envisage a future when IoT devices have powerful enough CPUs that they will be able to run it locally themselves. This opens up the programming and management of IoT devices to anyone who is interested, massively lowering the barrier of entry.

For more on Node-RED, I’d recommend checking out their site at www.nodered.org.

Crate, the other subject of this post, is a new stab at the idea of databases. I’ll paraphrase their site here: It uses the SQL syntax we all know and (mostly) love and combines resiliency, scalability and high availability in a distributed design. This allows people to query massive amounts of data in realtime rather than in batches.

Crate is, in other words, an elastic datastore that owes a lot of its design to SQL but is aimed at that big data problem that everyone loves to talk about so much. The marriage of (potentially millions of) Node-RED-managed IoT devices talking to Crate database technology seems like one made in heaven.

Unlike Orchestrate, Crate is not hosted for you in the cloud (although it does provide a sweet HTTP REST API endpoint for you to talk to if you wish). You’ll need to download and install Crate yourself on a Unix VM or machine or even cloud server. Luckily, the Crate guys have set up the absolute slickest install process of its kind that I’ve ever seen. Seriously. I want to put multiple hats on just to be able to take them off and bow to whoever on their team got that going.

For more on Crate, check out their website at www.crate.io.

Here is how to set things up on an Ubuntu Vagrant box, for those interested:

  1. Go to desired directory.
  2. vagrant init hashicorp/precise32
  3. Edit the Vagrantfile and paste in the following in the port forwarding section (default line 23):
    config.vm.network "forwarded_port", guest: 4200, host: 4200
  4. vagrant up
  5. When ready, vagrant ssh
  6. sudo apt-get update
  7. sudo apt-get python-software-properties
  8. sudo apt-get install curl
  9. bash -c "$(curl -L install.crate.io)" this line is witchcraft!
  10. When it’s finished doing it’s thang, you can connect to the database dashboard from your host machine on localhost:4200/admin
  11. (Optional) Use their sweet testing function to fill your database with tweets to play around with.

Once you’re up and running, the following readme from the Github and NPM pages will give you the instructions you need to start using Node-RED to interface with your Crate instance.


Crate Client for Node-RED

NPM

An independently-made Node-RED wrapper for the Crate elastic data store, installed locally or anywhere else that can be referenced by a URL. There are both input and output nodes included for interacting with the Crate HTTP endpoint API.

Please note: Crate is a trademark of Crate Technology Gmbh, registered in the E.U. and in other countries.

What’s all this about?

Node-RED

From Node-RED’s own web page:

Node-RED is a tool for wiring together hardware devices, APIs and online services in new and interesting ways.

Crate

From Crate’s own web page:

Crate is a new breed of database to serve today’s mammoth data needs. Based on the familiar SQL syntax, Crate combines high availability, resiliency, and scalability in a distributed design that allows you to query mountains of data in realtime, not batches.

We solve your data scaling problems and make administration a breeze. Easy to scale, simple to use.

Installation

Run this command in the root directory of your Node-RED installation:

Note that this will also install the nodes’ sole dependency – node-crate, on which this Node-RED package is built. All credit to megastef – you the man.

Usage

Once installed (and after restarting Node-RED and refreshing its page in your browser), you’ll see some nodes labelled “crate” in your node locker under the ‘storage’ section.

The node with both an input and an output is (confusingly) our input node. This node allows you to get information from your crate database.

The node with only an input is our output node. This allows you to send information to your crate database and save it there.

When you drag either node to your workspace, you’ll need to open its options and either add a crate database (if you haven’t already), or select one. Adding a database is as simple as pasting in the database’s _sql HTTP endpoint URL.

For the output node you can specify a table to send the information to in the node’s options.

Both nodes take their information from a msg object passed to them. Below are the specs for each node’s msg inputs. Note that these are also visible if you select a node in Node-RED and view the info tab on the right of the screen.

Ouput node msg object

  • msg.table (semi-optional): Specify which table to insert the data into (this will override the option entered in the node config window if set, allowing you to use one output node to send to multiple tables). Note that table must be set either in the node options or in this property.
  • msg.data (required): The JS data object you want to insert in the table, in the form { columnName1: 'value1', columnName2: 'value2' }.
  • msg.where (optional): Makes the insert an update action instead, where your string condition is met, e.g. 'columnName3=5'.

Input node msg object

  • msg.query (required): An sql-style string query, for example:

  • msg.args (required): Arguments for your query, in an array, e.g. ['%crate%'].

Features

Currently, the output node provides support for:

  • Inserts
  • Updates
  • One output node can provide access to multiple tables within a Crate database through use of the msg.table property passed to it that can override options set on the node.

The input node provides support for:

  • Querying

For more on these features and how they work in Crate, check out their docs.

Roadmap

  • BLOBs
  • Bulk actions
WordPress Backup