telemetR: A Simple RESTful API - part 2

- 16 mins

In the last post we built an API to get data from the telemetR database. In this post we will add functionality to insert and update data.

Devices

First thing first, entering new data into the devices table. This follows the data flow standards specified in the first post. Before INSERTing new data, we will check that the serial number of the device being entered is the doesn’t already exist in the database. If it does, we will return success : false and a reason why the operation was unsuccessful.

// .. other devices routes

router.post('/', (req, res) => {
  let rtn = [];
  let success = true;
  let httpCode = 500;

  // chain queries to the database
  db.task(t => {
    return db.any(
      'SELECT * FROM devices WHERE serial_num = $(serial_num)',
      req.body
    )
    .then(data => {
      // check if sql returned data
      if (data.length != 0) {
        success = false;
        rtn.push({
          message: req.body.serial_num + ' already exists in the database.'
        });
      }
      // if data not returned then enter new data
      if (success) {
        console.log(req.body);
        httpCode = 201;
        return t.one(`INSERT INTO devices (
            serial_num,
            frequency,
            vendor,
            device_type,
            mfg_date,
            model)
          VALUES (
            $(serial_num),
            $(frequency),
            $(vendor),
            $(device_type),
            $(mfg_date),
            $(model))
          RETURNING id
        `, req.body);
      } else {
        return rtn;
      }
    });
  })
  .then(data => res.status(httpCode).json({ success: success, data: data }))
  .catch(err => res.status(400).json({ success: success, data: err }));
});

First we initialize a few variables to hold information to send in the response. rtn will hold the payload of the response if the lookup finds a device with the same serial number. success is whether or not the data was appended to the database successfully. httpCode will hold the HTTP status code to send with the response.

Chaining Queries

.task is pg-promise method to chain queries to the database. Using a task will only open one connection to the database for every query within the task. This will help the API perform better under heavy loads and help with scalability.

We chained the query to lookup the serial number in devices and the INSERT query. First, we lookup the serial number. If the serial number already exists in devices we push a message to rtn, set success, and httpCode. This will be sent in the response as a failed attempt to INSERT data. In fact, the INSERT query isn’t run. If the serial number is not in the database then the INSERT query is run and the response includes devices.id.

Use Postman to enter a new device to this URL localhost:8080/devices. The body of the request is in the code snippet below. Check the screenshot from Postman below to see what a successful request looks like.

{
    "serial_num": "device1",
    "frequency": 100.001,
    "vendor": "vectronic",
    "device_type": "gps",
    "mfg_date": "2015-01-01",
    "model": "model"
}

successful device post

A successful POST request to the API. `devices.id` is returned from the database.

Below is a screenshot from Postman of an unsuccessful

unsuccessful device post

An unsuccessful POST request to the API. `devices.id` is returned from the database.

Captures

Now the POST request for captures. Recall that when data is inserted into captures that the some data is pushed into animals and deployments. This is part of the data flow in our database. Let’s make sure to check the data that will be entered against the data in the database. We will check the database for two conditions:

  1. perm_id is not in the captures table
  2. serial_num is in the devices table

If these conditions aren’t met the server will respond with success : false and a reason why the operation was unsuccessful.

router.post('/', (req, res) => {
  let rb = req.body;
  let rtn = [];
  let success = true;
  let httpCode = 500;

  db.task(t => {
    return t.batch([
      t.any('SELECT * FROM captures WHERE perm_id = ${perm_id}', rb),
      t.any('SELECT * FROM devices WHERE serial_num = ${serial_num}', rb)
    ])
    .then(data => {
      // add checks
      if (data[0].length != 0) {
        success = false;
        rtn.push({
          message: rb.perm_id + ' already exists in the database. An animal can\'t have more tan one capture.',
          data: data[0]
        })
      }
      if (data[1].length == 0) {
        success = false;
        rtn.push({
          message: 'Device ' + rb.serial_num + ' isn\'t entered in the database. Enter the device first.'
        })
      }
      if (success) {
        httpCode = 201;
        return t.one(
          'INSERT INTO captures ' +
            '(perm_id, cap_date, sex, age, species, notes, serial_num) ' +
          'VALUES ' +
            '(${perm_id}, ${cap_date}, ${sex}, ${age}, ${species}, ${notes}, ${serial_num}) ' +
          'RETURNING id', rb
        );
      } else {
        return rtn;
      }
    });
  })
  .then(data => res.status(httpCode).json({ success: success, data: data }))
  .catch(err => res.status(400).json({ success: success, data: err }));
});

This is very similar to the devices route. The big difference is the .batch command. This will batch queries sent to the database and return the results as an array. The first query checks if the animals has already been entered (it shouldn’t be), the second checks if the device is already entered (it should be). When both of these constraints are satisfied the new capture INSERT query will run. Below is the data for the request body.

{
  "perm_id": "BHS1",
  "cap_date": "2016-01-01",
  "sex": "female",
  "age": "adult",
  "species": "MULD",
  "notes": "Captured at the top of the hill",
  "serial_num": "device1"
}

successful capture post

A successful post to Captures

successful capture post

An unsuccessful post to Captures. The animal is already entered in the database.

successful capture post

An unsuccessful post to Captures. The device is not entered in the database.

Relocations

This route will not enter data directly into telemetry, instead it will enter data into raw_gps then a trigger will parse the data and INSERT it into telemtry. We will use the same methods as the routes above.

router.post('/', (req, res) => {
  let rtn = [];
  let success = true;
  let httpCode = 500;

  db.task(t => {
    return t.batch([
      t.any('SELECT * FROM devices WHERE serial_num = $(serial_num)', req.body),
      t.any(`SELECT
          deployments.id,
          devices.serial_num,
          animals.perm_id,
          deployments.inservice,
          deployments.outservice
        FROM deployments, devices, animals
        WHERE
          deployments.animal_id = animals.id AND
          deployments.device_id = devices.id AND
          devices.serial_num = $(serial_num)
        `, req.body)
    ])
    .then(data => {
      // check device exists in database
      if (data[0].length == 0) {
        success = false;
        rtn.push({
          message: 'A device with serial_num ' + req.body.serial_num +
          ' is not in the database. Enter this device first.'
        });
      }
      // check deployment is in the database
      if (data[1].length == 0) {
        success = false;
        rtn.push({
          message: 'Device ' + req.body.serial_num + ' does not have ' +
          'a deployment in deployments table. An capture needs to be entered.'
        });
      }
      // if constraints are met insert data
      if (success) {
        httpCode = 201;
        console.log('INSERT DATA')
        return t.one(`INSERT INTO raw_gps (
          serial_num,
          acq_time,
          activity,
          latitude,
          longitude,
          altitude)
        VALUES (
          $(serial_num),
          $(acq_time),
          $(activity),
          $(latitude),
          $(longitude),
          $(altitude))
        RETURNING id
        `, req.body);
      } else {
        // else return failure message
        return rtn;
      }
    })
  })
  .then(data => res.status(httpCode).json({ success: success, data: data }))
  .catch(err => res.status(400).json({ success: success, data: err }));
});

This is a lot of code to digest. It is very similar to the POST request for captures. First batch the SELECT queries to check the constraints. Then make sure the device exists in the database and the device is deployed. If the constraints are met INSERT the data into the database, else return the failure message.

relocations success

Bulk Insert

The above code is useful if we only want to insert one GPS relocation at a time. Which is to say this isn’t very useful. We need to alter this code to accept bulk inserts. Let’s create a new route localhosts:8080/relocations/batch to test the functionality.

// ... other require statements
const pgp = require('../db').pgp;
const db = require('../db').db;

router.post('/batch', (req, res) => {
  // a reusable table definition, similar to a model
  const cs = pgp.helpers.ColumnSet([
    'serial_num',
    'acq_time',
    'activity',
    'latitude',
    'longitude',
    'altitude'
  ], { table: 'raw_gps' });

  // creating the query string
  const query = pgp.helpers.insert(req.body, cs);
  console.log(query); // printing the query string to the console

  // execute query
  db.none(query)
  .then(() => res.status(201).json({
    success: true,
    message: 'successfully appended data to raw_gps' }))
  .catch(err => res.status(400).json({ success: false, data: err }));
});

Note, batch insert requires a change to the db.js file. We will need to change the require statements in all the files that use it. This is a small change, change the exports to this: module.exports = { db: db, pgp: pgp };.

This chunk of code uses functionality provided by the helpers namespace to quickly and flexibly generate SQL query strings. First we create a table definition with ColumnSet. This object stores information about the columns we want to INSERT data into from req.body. We then use this object to map data to the proper columns. This is much easier than using transactions, and much more efficient.

With transactions the resulting SQL string is a list of multiple SQL string.

INSERT INTO col_1, col_2 VALUES ('val-1', 'val-1');
INSERT INTO col_1, col_2 VALUES ('val-2', 'val-2');
INSERT INTO col_1, col_2 VALUES ('val-3', 'val-3');
-- etc ...

With a ColumnSet there are no transactions, just one SQL string with a list of values.

INSERT INTO col_1, col_2 VALUES (('val-1', 'val-1'), ('val-2', 'val-2'), ('val-3', 'val-3'), etc ...);

I didn’t include the constraint checks in this chunk of code. It is similar to our first chunk, I’ll let you figure that part out for now1.

successful batch to relocations

A successful batch insert for relocations

Wrap Up

There you have it, the foundation for a full featured API to GET and POST to the database. This API can be consumed by any client. We can write several R functions to get the data, a web application, or command line tool.

The pg-promise allows us to separate the creation of the database and API. We can extend this even further by independently designing the client to consume the API. The big advantage of this separation is that each unit (database, API, client) can be developed and deployed independently. This allows extreme flexibility when developing and deploying.

In the next post we will, finally, use R to do some basic data visualization and analysis. Then Use the API in a Shiny application.

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
  7. Simple RESTful API part 2

upcoming topics

Footnotes

  1. A potential cool feature: send a csv file, parse then push to the database.

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