# TimescaleDB + Node.JS + express server for fast IOT

Date: 2017-09-07

As part of the research about the IOT world, I found [TimescaleDB](http://www.timescale.com) -  <span>open source time-series database optimized for fast ingest and complex queries.</span>

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

<!--more-->

I started with ubuntu 17.04 minimal server

## Apps instalation :

Node.JS -

```shell
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 -

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

setup TimescaleDB :

edit : postgresql.conf

```shell
sudo vim /etc/postgresql/9.6/main/postgresql.conf
#shared\_preload\_libraries = &#8221;  => shared\_preload\_libraries = &#8216;timescaledb'
```

create user and databse :

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

## Node app :

```shell
express [app name]
```

### Install modules :

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

### New files:

db/index.js:

```shell
const { Pool } = require('pg')

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

routes/api.js:

```shell
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) =&gt; {
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) =&gt; {
 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:

```shell
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:

```shell
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) =&gt; {

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

app.js :

```shell
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 :
```shell
PGUSER=[user] PGHOST=localhost PGDATABASE=iot PGPORT=5432 PGPASSWORD=[password] node bin/www
```

## Test the app :
### add some data :
```shell
curl -X POST --data 'location=test location&temperature='$((20 +RANDOM % 20))'&humidity='$((35 +RANDOM % 40)) [serverip]:3000/api/v1/condition
```

### browse to :
```shell
[serverip]:3000/api/v1/conditions
```

* * *

Resources :

[Michael Herman](http://mherman.org/blog/2015/02/12/postgresql-and-nodejs/#.WbD3axdLdhE)

[node-postgres.com](https://node-postgres.com/features/queries)