Use Node.js and Postgres to build RESTful apis

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 [email protected] -gCopy the code

Create a new project and install the required dependencies:

$ express node-postgres-promises
$ cd node-postgres-promises
$ npm install
Copy the code

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 [email protected] --saveCopy the code

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);
Copy the code

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 [email protected] --saveCopy the code

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');
Copy the code

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
Copy the code

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);
    });
});
Copy the code

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);
    });
});
Copy the code

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"
}
Copy the code

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"
}
Copy the code

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);
    });
});
Copy the code

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/puppiesCopy the code

You should see:

{
  "status": "success",
  "message": "Inserted one puppy"
}
Copy the code

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);
    });
});
Copy the code

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/1Copy the code

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);
    });
});
Copy the code

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/1Copy the code`

Result:

{
  "status": "success",
  "message": "Removed 1 puppy"
}
Copy the code

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
  });
});
Copy the code

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); }); }Copy the code

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) {
Copy the code

PUT route:

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

DELETE route:

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

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."
}
Copy the code

`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(); }Copy the code

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) {
Copy the code

Then test it out:

$curl - data "name = = Whisky&breed annoying&age = 3", http://127.0.0.1:3000/api/puppiesCopy the code

You should see:

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

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(); }Copy the code

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

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

-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(); }Copy the code

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.

Use Node.js and Postgres to build RESTful apis

Project setup

Postgres setup

Routes

GET ALL

GET Single Puppy

POST route

PUT route

DELETE route

Error Handling

checkID()

validPostObject()

validPutObject()

Refactor

Conclusion

Related Posts

Redis source code analysis of AOF

Restore the essence of JDBC lightweight package: Apache Common DbUtils

SpringBoot+Mysql login interface, discard mapper.xml

`checkID()`

`validPostObject()`

`validPutObject()`