TimescaleDB + Node.JS + express server for fast IOT

As part of the research about the IOT world, I found TimescaleDB –  open source time-series database optimized for fast ingest and complex queries.

TimescaleDB is based on PostgreSQL and by their test is much faster.

So for the next project, I’ll build a REST API for TimscaleDB with Node.Js 8.4, Express and TimescaleDB

 

I started with ubuntu 17.04 minimal server

Apps instalation :

Node.JS –

curl -sL https://deb.nodesource.com/setup_8.x | sudo -E bash - 
sudo apt-get install nodejs
sudo npm i express -g
sudo npm i express-generator -g

TimescaleDB –

sudo add-apt-repository ppa:timescale/timescaledb-ppa
sudo apt-get update
sudo apt install timescaledb

setup TimescaleDB :

edit : postgresql.conf

sudo vim /etc/postgresql/9.6/main/postgresql.conf

#shared_preload_libraries = ”  => shared_preload_libraries = ‘timescaledb’

create user and databse :

sudo /etc/init.d/postgresql restart
sudo -u postgres createuser [user] 
sudo -u postgres psql
> ALTER USER "[user]" WITH PASSWORD '[password]';
> CREATE database iot;
> \c iot
> CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
> CREATE TABLE conditions (
 time TIMESTAMPTZ NOT NULL,
 location TEXT NOT NULL,
 temperature DOUBLE PRECISION NULL,
 humidity DOUBLE PRECISION NULL
);
> GRANT ALL PRIVILEGES ON TABLE conditions TO "[user]";
> SELECT create_hypertable('conditions', 'time');

 

Node app :

express [app name]

Install modules :

npm install pg --save
npm i express-promise-router --save

New files:

db/index.js:

const { Pool } = require('pg')

pool = new Pool();
module.exports = {
query: (text, params) => pool.query(text, params)
}

routes/api.js:

const Router = require('express-promise-router')
const db = require('../db')
const router = new Router()
module.exports = router

router.post('/v1/condition', async (req, res, next) => {
const params = req.body;
const q = 'INSERT INTO conditions(time, location,temperature,humidity) values($1, $2,$3,$4)'
const data = ['now()',params.location,params.temperature,params.humidity]
 const {rows} = await db.query(q,data);
 res.send('done');
});

router.get('/v1/condition', async (req, res, next) => {
 const { rows } = await db.query("SELECT time_bucket('5 minutes', time) as five_min, avg(temperature) FROM conditions GROUP BY time;");
 res.send(rows);
});

routes/root.js:

const Router = require('express-promise-router')
const db = require('../db')
const router = new Router()
module.exports = router

/* GET home page. */
router.get('/', function(req, res, next) {
 res.render('index', { title: 'Express' });
});

Edit files:

routes/index.js:

const Router = require('express-promise-router')

const db = require('../db')
const router = new Router()

// export our router to be mounted by the parent application
 module.exports = router

const root = require('./root')
const api = require('./api')

module.exports = (app) => {

 app.use('/', root) 
 app.use('/api',api) 
 }

app.js :

const express = require('express');
var path = require('path');
var favicon = require('serve-favicon');
var logger = require('morgan');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');

const mountRoutes = require('./routes');

const app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'jade');

// uncomment after placing your favicon in /public
//app.use(favicon(path.join(__dirname, 'public', 'favicon.ico')));
app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

mountRoutes(app);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
 var err = new Error('Not Found');
 err.status = 404;
 next(err);
});

// error handler
app.use(function(err, req, res, next) {
 // set locals, only providing error in development
 res.locals.message = err.message;
 res.locals.error = req.app.get('env') === 'development' ? err : {};

// render the error page
 res.status(err.status || 500);
 res.render('error');
});

module.exports = app;

 

Run the app :

PGUSER=[user] PGHOST=localhost PGDATABASE=iot PGPORT=5432 PGPASSWORD=[password] node bin/www

 

Test the app :

add some data :

curl -X POST --data 'location=test location&temperature='$((20 +RANDOM % 20))'&humidity='$((35 +RANDOM % 40)) [serverip]:3000/api/v1/condition

 

browse to :

[serverip]:3000/api/v1/conditions

 


Resources :

Michael Herman

node-postgres.com