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 :