In this tutorial we'll create a RESTful web service with JavaScript, Node, Express, Postgres, and pg-promise.

Our app will include the following endpoints:

URL	HTTP Verb	Action
/api/puppies	GET	Return ALL puppies
/api/puppies/:id	GET	Return a SINGLE puppy
/api/puppies	POST	Add a puppy
/api/puppies/:id	PUT	Update a puppy
/api/puppies/:id	DELETE	Delete a puppy

This tutorial uses the following tools and technologies - Node.js v4.3.1, express-generator v4.13.1, pg-promise v3.2.3, PostgreSQL v9.4, and Bluebird v3.3.4

Project setup

Install the Express Generator (if necessary):

$ npm install express-generator@4.13.1 -g

Create a new project and install the required dependencies:

$ express node-postgres-promises
$ cd node-postgres-promises
$ npm install

Test!

Navigate to http://localhost:3000 in your browser, and you should see the familiar "Welcome to Express" text. Kill the server when done. Now let's set up the Postgres bindings via pg-promise…

Install pg-promise

$ npm install pg-promise@3.2.3 --save

Why pg-promise instead of pg? Put simply, pg-promise abstracts away much of the difficult, low-level connection management, allowing you to focus on the business logic.

Finally, let's update the index.js route file:

var promise = require('bluebird');
var express = require('express');
var router = express.Router();
var options = {
  // Initialization Options
  promiseLib: promise
};
var pgp = require('pg-promise')(options);
var connectionString = 'postgres://localhost:5432/puppies';
var db = pgp(connectionString);

Here, we created an instance of pg-promise and assigned it to a variable, pgp.

Did you notice that we passed an object, options, during the initialization process? This is required, even if you do not pass any properties/initialization options to the object. In this case, we overrode pg-promise's default promise library - ES6 Promises - with Bluebird by setting the promiseLib property in the options object.

Don't forget to install Bluebird:

$ npm install bluebird@3.3.4 --save

Next, we defined a connection string, and then passed it to the pg-promise instance to create a global connection instance.

Done!

Postgres setup

Create a new file in your project root called puppies.sql and then add the following code:

DROP DATABASE IF EXISTS puppies;
CREATE DATABASE puppies;
\c puppies;
CREATE TABLE pups (
  ID SERIAL PRIMARY KEY,
  name VARCHAR,
  breed VARCHAR,
  age INTEGER,
  sex VARCHAR
);
INSERT INTO pups (name, breed, age, sex)
  VALUES ('Tyler', 'Retrieved', 3, 'M');

Run the file to create the database, apply the schema, and add one row to the newly created database:

$ psql -f puppies.sql
DROP DATABASE
CREATE DATABASE
CREATE TABLE
INSERT 0 1

Now we can start setting up the route handlers…

Routes

GET ALL

// return ALL puppies
router.get('/api/puppies', function(req, res, next) {
  db.any('select * from pups')
    .then(function(data) {
      res.status(200)
        .json({
          status: 'success',
          data: data,
          message: 'Retrieved ALL puppies'
        });
    })
    .catch(function(err) {
      return next(err);
    });
});

In the above code, we utilized the any Query Result Mask to query the database, which returns a promise object. This method is used to indicate that we are expecting any number of results back. Success and failures are then handled by .then() and .catch().

Besides, any, you can use the following Query Result Masks:

one - a single row is expected
many - one or more rows are expected
none - no rows are expected
result - passes the original object when resolved (we'll look at an example of this shortly)

GET Single Puppy

// return SINGLE puppy
router.get('/api/puppies/:id', function(req, res, next) {
  var pupID = req.params.id;
  db.one('select * from pups where id = $1', pupID)
    .then(function(data) {
      res.status(200)
        .json({
          status: 'success',
          data: data,
          message: 'Retrieved ONE puppy'
        });
    })
    .catch(function(err) {
      return next(err);
    });
});

Test the GET requests out in the browser…

GET ALL puppies: http://localhost:3000/api/puppies

{
  status: "success",
  data: [
    {
      id: 1,
      name: "Tyler",
      breed: "Shih-tzu",
      age: 3,
      sex: "M"
    }
  ],
  message: "Retrieved ALL puppies"
}

GET SINGLE puppy: http://localhost:3000/api/puppies/1

{
  status: "success",
  data: {
    id: 1,
    name: "Tyler",
    breed: "Shih-tzu",
    age: 3,
    sex: "M"
  },
  message: "Retrieved ONE puppy"
}

POST route

// insert puppy
router.post('/api/puppies', function(req, res, next) {
  db.none('insert into pups(name, breed, age, sex)'+
          'values(${name}, ${breed}, ${age}, ${sex})',
          req.body)
    .then(function() {
      res.status(200)
        .json({
          status: 'success',
          message: 'Inserted one puppy'
        });
    })
    .catch(function(err) {
      return next(err);
    });
});

Test with curl in a new terminal tab:

$ curl --data "name=Whisky&breed=annoying&age=3&sex=f" \
http://127.0.0.1:3000/api/puppies

You should see:

{
  "status": "success",
  "message": "Inserted one puppy"
}

Double check the GET ALL route in your browser to ensure that the new puppy is now part of the collection.

PUT route

// update puppy
router.put('/api/puppies/:id', function(req, res, next) {
  db.none('update pups set $1~=$2 where id=$3',
          [req.body.column, req.body.value, req.params.id])
    .then(function() {
      res.status(200)
        .json({
          status: 'success',
          message: 'Updated puppy'
        });
    })
    .catch(function(err) {
      return next(err);
    });
});

Here, we expect that a column name is passed in with the PUT request along with a new value. Try it out:

$ curl -X PUT --data "column=name&value=Hunter" \
http://127.0.0.1:3000/api/puppies/1

This should change the name of the first puppy from 'Tyler' to 'Hunter'. Again, check the GET ALL route in your browser to ensure that puppy's updated name is now Hunter.

Did you notice the ~ in the SQL query? This is used to prevent SQL injection by properly escaping the variable.

DELETE route

// remove puppy
router.delete('/api/puppies/:id', function(req, res, next) {
  var pupID = req.params.id;
  db.result('delete from pups where id = $1', pupID)
    .then(function(result) {
      /* jshint ignore:start */
      res.status(200)
        .json({
          status: 'success',
          message: `Removed ${result.rowCount} puppy`
        });
      /* jshint ignore:end */
    })
    .catch(function(err) {
      return next(err);
    });
});

So, we used the result Query Result Mask, in order to output the number of rows removed from the database.

1	`$ curl -X DELETE http://127.0.0.1:3000/api/puppies/1`

Result:

{
  "status": "success",
  "message": "Removed 1 puppy"
}

Error Handling

First, update the error handlers in app.js to serve up JSON:

// development error handler
// will print stacktrace
if (app.get('env') === 'development') {
  app.use(function(err, req, res, next) {
    res.status( err.code || 500 )
    .json({
      status: 'error',
      message: err
    });
  });
}
// production error handler
// no stacktraces leaked to user
app.use(function(err, req, res, next) {
  res.status(err.status || 500)
  .json({
    status: 'error',
    message: err.message
  });
});

Next, let's add three helper functions:
- checkID() - ensures that the ID parameter from the query string exists
- validPostObject() - ensures that the JSON object is valid for the POST request
- validPutObject() - ensures that the JSON object is valid for the PUT request

We could put these functions into a helpers file, but let's keep it simple and just add it to the index.js file with our route handlers.

`checkID()`

function checkID(req, res, next) {
  var pupID = parseInt(req.params.id);
  db.oneOrNone('select id from pups where id = $1', pupID)
    .then(function(data) {
      if(!data) {
        /* jshint ignore:start */
        res.status(400)
          .json({
            status: 'error',
            message: `ID '${pupID}' does not exist.`
          });
        /* jshint ignore:end */
      } else {
        return next();
      }
    })
    .catch(function(err) {
      return next(err);
    });
}

What's happening here? Basically, if the ID exists, we pass the request to the next middleware function. If the ID does not exist, we send an error. Add the function to the appropriate route handlers:

GET Single Puppy:

router.get('/api/puppies/:id', checkID,
  function(req, res, next) {

PUT route:

router.put('/api/puppies/:id', checkID,
  function(req, res, next) {

DELETE route:

router.delete('/api/puppies/:id', checkID,
  function(req, res, next) {

Test this out for each updated route handler. If an ID exists then the route handler should function as it normally should. But, if an ID does not exist, you should see an error:

{
  "status": "error",
  "message": "ID '7' does not exist."
}

`validPostObject()`

function validPostObject(req, res, next) {
  var payload = req.body;
  var keys = ['name', 'breed', 'age', 'sex'];
  keys.forEach(function(key){
    if(!(key in payload)) {
      /* jshint ignore:start */
      return res.status(400)
        .json({
          status: 'error',
          message: `Invalid JSON. '${key}' does not exist.`
        });
      /* jshint ignore:end */
    }
  });
  return next();
}

This function simply takes the payload from the POST request and verifies that all keys are present.

Update the route handler, like so:

router.post('/api/puppies', validPostObject,
  function(req, res, next) {

Then test it out:

$ curl --data "name=Whisky&breed=annoying&age=3" \
http://127.0.0.1:3000/api/puppies

You should see:

{
  "status": "error",
  "message": "Invalid JSON. 'sex' does not exist."
}

Keep in mind that this function does not check if a key also contains a value - so, "name=Whisky&breed=annoying&age=3&sex=" will still pass - and the function breaks at the first key not found. What if the end user forgets multiple keys? Shouldn't we give them this info all at once? Yes. Refactor on your own using filter!

Don't forget to test with valid JSON to ensure that all is well.

`validPutObject()`

function validPutObject(req, res, next) {
  var payload = req.body;
  var keys = ['column', 'value'];
  keys.forEach(function(key){
    if(!(key in payload)) {
      /* jshint ignore:start */
      return res.status(400)
        .json({
          status: 'error',
          message: `Invalid JSON. '${key}' does not exist.`
        });
      /* jshint ignore:end */
    }
  });
  return next();
}

This should be straightforward. Again, update the route handler-

router.put('/api/puppies/:id', checkID, validPutObject,
  function(req, res, next) {

-and then test it out.

Refactor

Finally, did you notice the duplicate code in the last two functions - validPostObject() and validPutObject()? Let's clean that up by combining the logic into a single function:

function validPayload(req, res, next) {
  var keys;
  if (req.method === 'POST') {
    keys = ['name', 'breed', 'age', 'sex'];
  } else if (req.method === 'PUT') {
    keys = ['column', 'value'];
  }
  keys.forEach(function(key){
    if(!(key in req.body)) {
      /* jshint ignore:start */
      return res.status(400)
        .json({
          status: 'error',
          message: `Invalid JSON. '${key}' does not exist.`
        });
      /* jshint ignore:end */
    }
  });
  return next();
}

Update the route handlers, then test one last time!

Conclusion

We now have a basic RESTful API built with Node, Express, and pg-promise. Be sure to comment below if you have any questions.

Grab the code from the repo.