telemetR: A Simple RESTful API - part 1

- 28 mins

What is an API

API (Application Programming Interface) is a set of routines, protocols, and tools for building applications. On the web an API sits between a data server and web applications to communicate data between the two. I like to think of an API as language agnostic a way to interact with data in a database. We can use the same API to build a user interface with javascript, and get data in R for data analysis.

What is a RESTful API

REST is an acronym for Representational State Transfer. REST is a resource based architecture style that defines how an API should built. With REST everything (all data) is a resource and uses a noun to specify the resource instead of an verb (SOAP API). For instance, if we want to get all animal data we would use /animals instead of /get-animals. Check this post for a great primer on designing RESTful APIs

We will build a RESTful API to interact with the telemetR database.

We will use Node.js, Express.js, and pg-promise.js to build a RESTful API to interact with the telemetR database and Postman to test it. My normal database connection library is Sequelize.js. However I don’t want to rewrite the database schema as models. All we need is to write raw SQL that can be interpreted by Node and sent to postgres. We will use pg-promise to do this.

Boilerplate

Install Dependencies

First create a new folder and cd into it, then use npm init to start a new project and npm install to install the require libraries. We will use nodemon to monitor the directory and restart when files change.

mkdir telemetr-api
cd telemetr-api

npm init -y
npm install --save express pg-promise pg pg-monitor bluebird body-parser
npm install -g nodemon

open .
atom .

The --save flag means to save the files to package.json, -g means to install globally. The list two lines are shortcuts for opening the finder and Atom at the current directory.

index.js

index.js is the entry point for the API. This is where all the server logic is compiled and the server is created.

const Express = require('express');
const bodyParser = require('body-parser');
const app = Express();
const db = require('./db');

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));

// set up routes
const deployments = require('./routes/deployments');
// const animals = require('./routes/animals');
// const devices = require('./routes/devices');
// const captures = require('./routes/captures');
// const relocations = require('./routes/relocations');

app.use('/deployments', deployments);
// app.use('/animals', animals);
/  app.use('/devices', devices);
// app.use('/captures', captures);
// app.use('/relocations', relocations);

app.listen(8080, () => {
  console.log('API listening on port 8080');
});

Let’s go step by step. In the first blcok we are requiring the dependencies to run the API. In the second block we are setting body-parser to parse json input. In the next two blocks we are requiring and telling the app to use the routes we create. We will create the routes one at a time, however in order to require them the routes will need to exist. For now I’ll comment out routes we’ve yet to create, then uncomment each one as we get to it. Our first route will be for deployments.

db.js

We will put all the database connection logic in this file. We can reference this file from the routes we will create, that way we don’t have to duplicate this code in every route. When requiring pg-promise we can choose a few configuration options. I am overriding the default promise library to bluebird. I’ve used bluebird in the past, so I’ll continue using it here.

const promise = require('bluebird');
const pgm = require('pg-monitor');

const options = {
  promiseLib: promise
}
pgm.attach(options);

const pgp = require('pg-promise')(options);

const db = pgp({
  host: 'localhost',
  port: 5432,
  database: 'collars',
  user: null,
  password: null
});

module.exports = db;

package.json

Open package.json and add "dev": "nodemon index.js" script and any other information you want to add. To start your server type npm run dev into the terminal.

{
  "name": "telemetr-sqlz-api",
  "version": "1.0.0",
  "description": "simple api to connect to spatial database",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "dev": "nodemon index.js"
  },
  "author": "Me",
  "license": "ISC",
  "dependencies": {
    "bluebird": "^3.4.7",
    "body-parser": "^1.15.2",
    "dbgeo": "^1.0.0",
    "express": "^4.14.0",
    "pg": "^6.1.2",
    "pg-monitor": "^0.6.1",
    "pg-promise": "^5.5.0",
    "sequelize": "^3.28.0"
  }
}

File Structure

telemetr-api
|- db.js
|- index.js
|- package.json
|- node_modules
|- routes
|   |- animals.js
|   |- captures.js
|   |- devices.js
|   |- deployments.js
|   `- relocations.js

First Route

Routes determine how the application responds to client requests from a URI and HTTP method. These will look like URLs. Before creating any routes make a new folder called routes. All the routes we create will be stored in here.

The first route we will write will be for deployments. We will make a call to the database for data, then return the data as a JSON.

const Express = require('express');
const router = Express.Router();
const db = require('../db');

// GET all deployments
router.get('/', (req, res) => {
  db.any(\`
    SELECT
      deployments.id,
      animals.perm_id,
      devices.serial_num,
      deployments.inservice,
      deployments.outservice
    FROM deployments, animals, devices
    WHERE
      deployments.animal_id = animals.id AND
      deployments.device_id = devices.id
  \`)
  .then(data => res.status(200).json({ success: true, data: data }))
  .catch(err => res.status(400).json({ success: false, data: err }))
});

// GET specific deployment by perm_id
router.get('/:perm_id', (req, res) => {
  db.many(\`
      SELECT
        deployments.id,
        animals.perm_id,
        devices.serial_num,
        deployments.inservice,
        deployments.outservice
      FROM deployments, animals, devices
      WHERE
        deployments.animal_id = animals.id AND
        deployments.device_id = devices.id AND
        animals.perm_id = $(perm_id)
    \`, req.params
  )
  .then(data => res.status(200).json({ success: true, data: data}))
  .catch(err => res.status(400).json({ success: false, data: err }));
});

module.exports = router;

First we require the modules we need, including the db.js, then write the routes. The first route will get all the deployments in the database. You don’t have to write your SQL like that, but I like the readability. Test this route in Postman with localhost:8080/deployments.

The first route will GET all the deployments in the database, localhost:8080/deployments. The second will GET deployments by the perm_id specified in the :id portion of the route, localhost:8080\deployments\F09. Try these in Postman.

Template Literal

The ` allows multiline strings, it is an ES6 template literal. Markdown isn’t properly rendering the grave symbol so I’ve prefixed it with a \. Remove prefixed \ before running the code.

Telemetry Routes

Let’s write routes that will allow us to get data in the telemetry table. Below is the relocations.js folder (relocations is a term frequently used to refer to GPS points).

const Express = require('express');
const router = Express.Router();
const dbgeo = require('dbgeo');
const db = require('../db');

// GET request: show all gps locations for one animal
router.get('/:perm_id', (req, res) => {
  db.any(\`
    SELECT
      animals.perm_id,
      telemetry.*
    FROM animals, telemetry
    WHERE
      animals.id = telemetry.animal_id AND
      animals.perm_id = $(perm_id)
    ORDER BY telemetry.acq_time
    \`, req.params
  )
  .then(data => res.status(200).json({ success: true, data: data }))
  .catch(err => res.status(400).json({ success: false, data: err }));
});

// GET request: return geom as a geojson
router.get('/:perm_id/geojson', (req, res) => {
  db.any(\`
    SELECT
      animals.perm_id,
      ST_AsGeoJSON(telemetry.geom) AS geom
    FROM
      animals INNER JOIN telemetry ON animals.id = telemetry.animal_id
    WHERE
      geom IS NOT NULL AND
      animals.perm_id = $(perm_id) AND
      telemetry.validity_id = 2
    LIMIT 100
    \`, req.params
  )
    .then(data => {
      dbgeo.parse(data, {
        geometryType: 'geojson',
        outputFormat: 'geojson'
      }, (err, result) => {
        res.status(200).json({ success: true, data: result });
      });
    })
    .catch(err => res.status(400).json({ success: false, data: err }));
});

We’ve required the dbgeo package for these routes, this will allow us to convert the data returned from the database into a GeoJSON.

The first route returns all the telemetry data for the specified animal. Try localhost:8080/relocations/F09. The second route will return the same data, however the data returned will be a GeoJSON. In practice we wouldn’t create a route with the format in the URI. Instead we would include a format parameter in the query string. This is simpler, so let’s stick with that.

Here is a map of data portion for localhost:8080/relocations/M10/geojson.

Note: if you leave out telemetry.validity_id = 2 there is one improbable spike in the data.

More Routes

Below I’ll write the routes to for animals and devices. These will be very similar the the routes above. After each set of routes I’ll include images from testing in Postman.

devices.js

const Express = require('express');
const router = Express.Router();
const db = require('../db');

// GET all devices
router.get('/', (req, res) => {
  db.any('SELECT * FROM devices')
  .then(data => res.status(200).json({ success: true, data: data }))
  .catch(err => res.status(400).json({ success: false, data: err }));
});

router.get('/:serial_num', (req, res) => {
  db.many('SELECT * FROM devices WHERE serial_num = $(serial_num)', req.body)
  .then(data => res.status(200).json({ success: true, data: data }))
  .catch(err => res.status(400).json({ success: false, data: err }));

module.exports = router;

get all devices

Get all devices

get devices by id

Get animal by serial_num

animals.js

const Express = require('express');
const router = Express.Router();
const db = require('../db');

// GET all animals
router.get('/', (req, res) => {
  db.any('SELECT * FROM animals')
  .then(data => res.status(200).json({ success: true, data: data }))
  .catch(err => res.status(400).json({ success: false, data: err }));
});

// GET one animal by perm_id
router.get('/:perm_id', (req, res) => {
  db.any('SELECT * FROM animals WHERE perm_id = $(perm_id)', req.params)
  .then(data => res.status(200).json({ success: true, data: data }))
  .catch(err => res.status(400).json({ success: false, data: err }));
});

module.exports = router;

get animal

Get all animals

get animal by id

Get animal by perm_id

captures.js

const Express = require('express');
const router = Express.Router();
const db = require('../db');

// GET lall captures
router.get('/', (req, res) => {
  db.any('SELECT * FROM captures')
    .then(data => res.status(200).json({ success: true, data: data }))
    .catch(err => res.status(400).json({ success: false, data: err }));
});

// GET captures by perm_id
router.get('/:perm_id', (req, res) => {
  db.any('SELECT * FROM captures WHERE perm_id = $(perm_id)', req.params)
  .then(data => res.status(200).json({ success: true, data: data }))
  .catch(err => res.status(400).json({ success: false, data: err }));
});

module.exports = router;

get captures

Get all captures

get capture by id

Get captures by perm_id

Wrap Up

Now that we have the foundation of an API we can easily access the data from any client we choose. At this point all we can only use GET requests. We can’t write, update, or delete data yet. We will get to that in the next post.

telemetR Series

  1. Introduction
  2. Creating an Animal Movement Database
  3. Extending the Database with PostGIS
  4. Importing New Data
  5. QA/QC with Spatial Attributes
  6. Simple RESTful API part 1

upcoming topics

rss facebook twitter github youtube mail spotify instagram linkedin google google-plus pinterest medium vimeo stackoverflow reddit quora