Hapi PG Rest API

A module to create a simple REST API in a HAPI 20 application connected to a particular Postgres DB table.

Contributing to this project

Please read the contribution guidelines before submitting a pull request.

Features:

• Records are identified by an auto-generated guid (by default)
• Data is transmitted as JSON format.
• Validation is provided by Joi.
• A client class is also available to connect to the API created

All routes return a standard response in form:

{
  "error": // Error response
  "data": // data returned from call
}

Routes that update also return a rowCount parameter showing the number of rows modified, e.g.:

{
  "error": null,
  "data": null,
  "rowCount" : 3
}

When finding many records, pagination data is returned:

{
  ...,
  "pagination": {
       "page": 1,
       "perPage": 100,
       "totalRows": 200,
       "pageCount": 2
   }
}

When querying for the schema, JSON schema and configuration data is returned:

{
  "error" : null,
  "data" : {
    "jsonSchema" : {
      ...
    },
    "config" : {
      "primaryKey" : "field",
      "primaryKeyAuto" : false,
      "primaryKeyGuid" : true
    }
  }
}

Usage

const {Pool} = require('pg');
const server = new Hapi.Server();
const RestApi = require('rest-api');

// Create a new endpoint linked to a table
const SessionsApi = RestApi({
  table : 'sessions',
  connection : pool,
  primaryKey : 'session_id',
  endpoint : '/api/1.0/sessions',
  onCreateTimestamp : 'date_created',
  onUpdateTimestamp : 'date_updated',
  validation : {
    session_id : Joi.string().guid(),
    ip : Joi.string(),
    session_data : Joi.string(),
    date_created : Joi.string(),
    date_updated : Joi.string().allow(null)
  }
});

// Import routes to HAPI
server.route([
  ...SessionsApi.getRoutes()
]);

// Or, import individual routes as required
server.route([
  SessionsApi.findManyRoute(),
  SessionsApi.findOneRoute(),
  SessionsApi.createRoute(),
  SessionsApi.updateOneRoute(),
  SessionsApi.replaceOneRoute(),
  SessionsApi.deleteOneRoute(),
  SessionsApi.updateManyRoute(),
  SessionsApi.schemaDefinitionRoute(),
]);

Configuration Options

• table : the PostGres table to connect to
• connection : the pool connection instance created with pg module
• primaryKey : the primary key field in the database table (must accept string GUID)
• endpoint : the base URL endpoint upon which the below calls are mounted
• onCreateTimestamp : a field which will be updated when the record is created
• onUpdateTimestamp : a field which will be updated when the record is updated
• validation : an object containing Joi validation for the entity (required)
• preUpdate : a function which can filter the data object being updated
• preInsert : a function which can filter the data object being inserted
• preQuery : a function which can modify the data, filter and sort after a HAPI request has been interpreted
• postSelect : a function which can modify data retrieved by select query
• upsert : an object containing arrays fields and set - adds an on conflict clause to an insert
• primaryKeyAuto : whether primary key field is auto-generated by the DB (default false)
• primaryKeyGuid : whether to use guids for primary key fields (default true)
• pagination : default pagination, specified as {page : 1, perPage : 200}
• showSql : for debugging, shows the generated SQL statements
• maxPayloadBytes : when posting large payloads, set this to override the HAPI default

Supported Endpoints

Create

Request:

POST /endpoint
Body:
{
  field : 'value',
  field2: 'value2'
}

Response:

201 Created
Body:
{
  "error" : null,
  "data" : {
    "field" : "value",
    "field2" : "value2"
  }
}

Find One

Request:

GET /endpoint/:id

Success Response:

200 OK
Body:
{
  "error" : null,
  "data" : {
    "field" : "value",
    "field2" : "value2"
  }
}

Not Found Response:

404 Not Found
Body:
{
  "error" : {
    "name" : "NotFoundError"
  },
  "data" : null
}

Find All

Request:

GET /endpoint

Success Response:

200 OK
Body:
{
  "error" : null,
  "data" : [{
    "field" : "value",
    "field2" : "value2"
  },
  {
    "field" : "value",
    "field2" : "value2"
  }],
  "pagination" : {
    "page": 1,
    "perPage": 100,
    "totalRows": 10,
    "pageCount": 2
  }
}

Filter / Sort

Request:

GET /endpoint?filter={"field":"value"}&sort={"field":+1,"field2":-1}

Success Response:

200 OK
Body:
{
  "error" : null,
  "data" : [
  ...
  ]
}

You can also use mongo-style operators such as $gt, $gte, $lt, $lte, $like, $ilike, for example:

GET /endpoint?filter={"field": {$ilike : "%value"}}&sort={"field":+1,"field2":-1}

Internally, the mongo-sql library is used to build filter queries.

Pagination

Request:

GET /endpoint?pagination={"page": 1, "perPage" : 5}

Success Response:

200 OK
Body:
{
  "error" : null,
  "data" : [
  ...
  ],
  "pagination" : {
    "page": 1,
    "perPage": 5,
    "totalRows": 10,
    "pageCount": 2
  }
}

Update One

Request:

PATCH /endpoint/:id
Body:
{
  field : 'value',
  field2: 'value2'
}

Success Response:

200 OK
Body:
{
  "error" : null,
  "data" : null
}

Not Found Response:

404 Not Found
Body:
{
  "error" : {
    "name" : "NotFoundError"
  },
  "data" : null
}

Update Many

Request:

PATCH /endpoint?filter={"key" : "value"}
Body:
{
  field : 'value',
  field2: 'value2'
}

Success Response:

200 OK
Body:
{
  "error" : null,
  "data" : null,
  "rowCount" : 5    // number of rows modified
}

Delete

Request:

DELETE /endpoint/:id

Success Response:

200 OK
Body:
{
  "error" : null,
  "data" : null
}

Not Found Response:

404 Not Found
Body:
{
  "error" : {
    "name" : "NotFoundError"
  },
  "data" : null
}

Get Schema

An endpoint is available that gets a basic JSON schema representation of the validation rules provided to Joi in the configuration object.

Request:

DELETE /endpoint/schema

Success Response:

200 OK
Body:
{
  "error" : null,
  "data" : {
    "jsonSchema" : {
      ...
    },
    "config" : {
      ...
    }
  }
}

Currently supported options in the schema are:

Joi validation config:

{
  id : Joi.string().guid(),
  field_1 : Joi.string(),
  field_2 : Joi.number(),
  field_3 : Joi.string().required(),
  field_4 : Joi.string().email(),
  field_5 : Joi.string().min(4).max(16)
}

Success Response:

200 OK
Body:
{
  "error" : null,
  "data" : {
    "jsonSchema" : {
      "type" : "object",
      "title" : "sessions",
      "properties" : {
        "id" : {
          "type" : "string",
          "pattern" : "/^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i"
        },
        "field_1" : {
          "type" : "string"
        },
        "field_2" : {
          "type" : "number"
        },
        "field_3" : {
          "type" : "string"
        },
        "field_4" : {
          "type" : "string",
          "format" : "email"
        },
        "field_5" : {
          "type" : "string",
          "minLength" : 4,
          "maxLength" : 16
        }
      },
      "required" : ["field_3"]
    },
    "config" : {
      "primaryKey" : "id",
      "primaryKeyAuto" : false,
      "primaryKeyGuid" : true
    }
  }
}

Validation

Data is validated with Joi validation, and on failure, the 'error' key in the response is populated with the Joi error.

For example:

{
    "data": null,
    "error": {
        "name": "ValidationError",
        "isJoi": true,
        "details": [
            {
                "message": "\"ip\" must be a string",
                "path": [
                    "ip"
                ],
                "type": "string.base",
                "context": {
                    "value": 123,
                    "key": "ip",
                    "label": "ip"
                }
            }
        ],
        "_object": {
            "ip": 123,
            "session_data": "{\"key\" : \"value\"}"
        }
    }
}

API Client

An API client is also available to connect with the server API. It depends on request-promise-native.

const APIClient = require('hapi-pg-rest-api').APIClient;
const rp = require('request-promise-native');
const client = new APIClient(rp, {
  endpoint : 'http://localhost/some/api/endpoint',
  headers : {
    Authorization : '...'
  }
});

Client methods:

const data = {field : 'value', field2 : 'value2'};
const filter = {field : 'value'};
const sort = {field2 : +1, field3 : -1};
const pagination = {page : 1, perPage : 5};

// Single record
var {data, error} = await client.create(data);
var {data, error} = await client.findOne('guid');
var {data, rowCount, error} = await client.updateOne('guid', data);
await client.delete('guid');

// Batch
var {data, error} = await client.findMany(filter, sort, pagination, columns);
var data = await client.findAll(filter, sort, pagination, columns); // Finds all pages in result set
var {data, rowCount, error} = await client.updateMany(filter, data);

// Schema
var {data, error} = await client.schema();

Error Handling

For errors returned by the API, e.g. Joi validation errors, this is returned as normal in the return from the call.

For other errors, e.g. those generates by the HTTP request (e.g. bad gateway), the error is thrown. All the client methods above throw an error if a n

This can be either an error returned by the API, e.g. a Joi validation error, or a status code error returned from request-promise-native.

Tests

To run the test suite

• Ensure the DATABASE_URL environment variable is set to a valid Postgres DSN
• Run npm run migrate to set up the test database tables
• Run npm run test to run the lab test suite

License

THIS INFORMATION IS LICENSED UNDER THE CONDITIONS OF THE OPEN GOVERNMENT LICENCE found at:

http://www.nationalarchives.gov.uk/doc/open-government-licence/version/3

The following attribution statement MUST be cited in your products and applications when using this information.

Contains public sector information licensed under the Open Government license v3

About the license

The Open Government Licence (OGL) was developed by the Controller of Her Majesty's Stationery Office (HMSO) to enable information providers in the public sector to license the use and re-use of their information under a common open licence.

It is designed to encourage use and re-use of information freely and flexibly, with only a few conditions.