Restful API with NodeJS, Express, PostgreSQL, Sequelize, Travis, Mocha, Coveralls and Code Climate

Restful API with NodeJS, Express, PostgreSQL, Sequelize, Travis, Mocha, Coveralls and Code Climate

Restful API with NodeJS, Express, PostgreSQL, Sequelize, Travis, Mocha, Coveralls and Code Climate

NodeJS is becoming a backend language of choice for many developers.

In this article, I am going to work you through building a Simple Restful API with:

  • NodeJS — For writing Javascript server-side applications
  • Express — A NodeJS framework
  • PostgreSQL — An open source object-relational database
  • Sequelize — An ORM(Object Relational Mapping) of PostreSQL
  • Travis —A continuous integration service for Testing Applications
  • Coveralls — **A **web service to help you track your code coverage over time.
  • Code Climate — Provides automated code review for maintainability and test coverage.
  • Babel — To convert ES6 javascript code to ES5

Though overwhelming, but you learn how to use these technologies simultaneously.

Let’s Build This!

This project is going to be built from scratch, without webpack, browserify or Gulp.

Step 1:

Setting Environment and Installing Dependencies:

a. Create a new directory called **book-app **and switch to that directory

mkdir book-app
cd book-app

b. Initialise npm and follow the instructions

npm init -y

c. Install Express and Body-parser:

npm install --save express body-parser

d. Install babel: Because we need to convert our ES6 code to ES5. Install babel dependencies by copying the code below and paste in the terminal of the working directory:

npm install --save-dev @babel/core @babel/cli @babel/node @babel/plugin-transform-runtime @babel/preset-env @babel/register @babel/runtime babel-loader

Create the .babelrc file in the path: /book-app/ and populate it with*:*

{
   "presets": ["@babel/preset-env"],
   "plugins": [["@babel/transform-runtime"]]
}

e. Install eslint and airbnb style guide. This is Optional!

This will help you format your code in an easy to read state.

npm install --save-dev eslint eslint-config-airbnb-base eslint-plugin-import

Create a file .eslintrc.js in the path: /book-app/

This the way i configured mine:

module.exports = {
  "extends": "airbnb-base",
"rules": {
"no-console": 0,
"no-param-reassign": [2, {"props": false}],
"prefer-destructuring": 0,
"treatUndefinedAsUnspecified": true,
"arrow-body-style": 0,
"comma-dangle": 0,
},
"env": {
"commonjs": true,
"node": true,
"mocha": true
},
};

Of course, you can setup yours however you wish.

f. Install nodemon:

In the cause of this tutorial, we will need to run node server over and over again. Nodemon is like a watcher that automatically restarts the server each time changes are made. You can install it globally using:

npm install -g nodemon

Note that this installation will not be in this project package.json file, because it was installed globally.

g. Including nodemon in the package.json file:

Open the package.json file, edit the scripts object as follows:

"scripts": {
  "dev": "nodemon --exec babel-node ./api/index.js",
  "test": "echo \"Error: no test specified\" && exit 1"
}

Observe the** ./api/index.js** ? Don’t worry, we will create the file in step 2.

At this point, your package.json file should look like this:

{
"name": "book-app",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"dev": "nodemon --exec babel-node ./api/index.js",
"test": "echo \"Error: no test specified\" && exit 1"
},
"keywords": [],
"author": "",
"license": "ISC",
"dependencies": {
  "body-parser": "^1.18.3",
  "express": "^4.16.4"
},
"devDependencies": {
  "@babel/cli": "^7.4.3",
  "@babel/core": "^7.4.3",
  "@babel/node": "^7.2.2",
  "@babel/plugin-transform-runtime": "^7.4.3",
  "@babel/preset-env": "^7.4.3",
  "@babel/register": "^7.4.0",
  "@babel/runtime": "^7.4.3",
  "babel-loader": "^8.0.5"
  }
}

h. Structuring the application:

Create a directory called api . Then create the index.js file, which is the root file for this application. You should be in the path: book-app/api/

touch index.js

Change to the api directory and create another directory called server. Your directory tree should now look like this:

book-app
├── api
│   ├── server
├── ├── index.js
├── node_modules
├── package.json
└── package-lock.json

Step 2:

Edit the index.js file and start the server

a. Edit the index.js file:

import express from 'express';
import bodyParser from 'body-parser';
const app = express();
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));
const port = process.env.PORT || 8000;
// when a random route is inputed
app.get('*', (req, res) => res.status(200).send({
   message: 'Welcome to this API.'
}));
app.listen(port, () => {
   console.log(`Server is running on PORT ${port}`);
});
export default app;

b. Start the server

Since we have defined the script to start the server, we simply run in the terminal from the path: /book-app/api/:

npm run dev

Hopefully you will see nodemon running in the terminal, with the port number the server is running on:

Step 3

Setup PostreSQL Database

a. Install:

If you don’t already have postreSQL running in your machine, you can download it and setup it.

If you are on a mac OS, you can follow this instruction: https://medium.com/@Umesh_Kafle/postgresql-and-postgis-installation-in-mac-os-87fa98a6814d

For Windows users: http://www.postgresqltutorial.com/install-postgresql/

For Ubuntu users: https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04

**b. Setup: **You can use the default user called: postgres or you can create a new user. There is probably hundreds of materials out there that can help you achieve this.

If you have any issues installing and setting up postgreSQL on your local machine, you can use an online database such as ElephantSQL

Step 4

Setup Sequelize

Sequelize is an ORM(Object Relational Mapping) for PostgreSQL. What this means is that, instead of writing raw SQL queries, ORM is used instead, which makes work easier.

a. Install sequelize cli globally:

npm install -g sequelize-cli

Note, if you wish not to install this globally, you’ll need to prefix every call to the sequelize command with ./node_modules/.bin

b. Create the file: **.sequelizerc **in the path: /boook-app/. That is, outside of the api folder.

touch .sequelizerc

Write in the file, the code below

const path  = require('path')
module.exports = {
    "config": path.resolve('./api/server/src/config', 'config.js'),
    "models-path": path.resolve('./api/server/src/models'),
    "seeders-path": path.resolve('./api/server/src/seeders'),
    "migrations-path": path.resolve('./api/server/src/migrations')
};

Observe that we required path. Let’s quickly install it:

npm install --save path

c. Install postgreSQL and sequelize dependencies

npm install --save sequelize pg pg-hstore

Where: pg = postgreSQL and pg-hstore = converts data into the Postgres hstore format.

d. Initialize Sequelize:

This will generate the Sequelize boilerplate which includes, models, migrations and seeders. Run:

sequelize init

The directory tree should now resemble this:

book-app
├── api
│   └── index.js
│   ├── server
│   │   └── src
│   │       └── config
│   │       ├── └── config.js
│   │       └── migrations
│   │       └── models
│   │       ├── └── index.js
│   │       └── seeders│  
├── .babelrc
└── .eslintrc.js
└── .sequelizerc
└── package.json
└── package-lock.json

e. Edit the book-app/api/server/models/index.js file.

This is the current state:

'use strict';

var fs        = require('fs');
var path      = require('path');
var Sequelize = require('sequelize');
var basename  = path.basename(module.filename);
var env       = process.env.NODE_ENV || 'development';
var config    = require(__dirname + '/../config/config.json')[env];
var db        = {};

if (config.use_env_variable) {
  var sequelize = new Sequelize(process.env[config.use_env_variable]);
} else {
  var sequelize = new Sequelize(config.database, config.username, config.password, config);
}

fs
  .readdirSync(__dirname)
  .filter(function(file) {
    return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js');
  })
  .forEach(function(file) {
    var model = sequelize['import'](path.join(__dirname, file));
    db[model.name] = model;
  });

Object.keys(db).forEach(function(modelName) {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

We will use ES6 syntax.

So, book-app/api/server/models/index.js becomes:

f. Editing the config.js file:

Let’s quickly install dotenv package. This will make it easier when using .env variables

npm install --save dotenv

Now, the config.js file:

You can replace the username with the one you used while setting up your postgreSQL. If you don’t remember creating a username, It means you are using the default user, which is postgres . For the password if you created a user, use the password, else, leave it the way it is above.

Also, incase you are using an online database, such as ElephantSQL, uncomment this lines:

development: {
   use_env_variable: 'DATABASE_URL'
},

Then, you will export the database URL into your environment

export DATABASE_URL=our-database-url

where our-database-url is the URL we copied from ElephantSQL. Every time you need to run this application, you will need to export the DATABASE_URLfirst.

Remember, if you decide to use the online database, and have uncommented the line above, comment the development setup for the local PostgreSQL database. So as to avoid name duplication.

g. Create a .env file in the path: /book-app/.

DB_NAME=books
DB_USER=steven
DB_PASS=
DB_PORT=5432
DB_HOST=127.0.0.1
SECRET_KEY=any_secret

h. Use Database GUI(Graphical User Interface): Optional.

Instead of doing all database operations in the terminal, which is very painful for some, you can use a GUI such as pgadmin or tableplus

Step 5

Create Databases, Model and Migration

a. Create the Databases:

In the config.js file above, we referenced a database called books for development and book_test for testing. You can either use the GUI or Terminal to create these databases.

Before you run any command, ensure that the PostgreSQL server is running, on mac OS, run:

pg_ctl -D /usr/local/var/postgres start  //For mac OS
or:
brew services start postgresql    //To always run it on mac OS

pg_ctl -D "C:\Program Files\PostgreSQL\9.6\data" start 
//For Windows. Observe the 9.6, yours may be different

sudo service postgresql start //For Linux

Get the list of commands here.

Now, Creating the databases from the terminal:

createdb books
createdb book_test

b. Create the Model

We are creating just one model: book.js

sequelize model:create --name Book --attributes title:string, price:string, description:string

Locate** book.js** in the path: /book-app/api/server/src/models/

Using ES6 syntax and adding to the file, we have:

c. Refactor migration file

A migration file was also created when we ran that command to create the model, located in the path: ..server/migrations/<date>-create-book.js

Edit the migration file to have:

Save all files and run migration:

sequelize db:migrate

You can check in the database to confirm if migration ran successfully.

Step 6

Create Services, Controllers, Routes and Utilities

Yes, we can put all the code in one file. But that is not best practice. So, is good we separate code into different files. We will create four folders in the path: /book-app/api/server:

book-app
├── api
    ├── server
        ├── controllers
        ├── routes
        ├── services
        ├── utils

a. Services:

You might ask, what is a Service? See it as a medium that helps us interface with our book model.

In the Services folder, create a file: BookService.js

b. Controllers

The Service file created above is used in our controller. Create a file called BookController.js in the controllers folder

This is the content:

c. Utilities

As seen, in the BookController.js file, we imported a file called Utils.js. This file contain all the responses we will assert for while running and testing the api endpoints.

Create the Utils.js file in the Utils folder.

Its content is:

d. Routes

Create the route file called BookRoutes.js in the routes folder.

Its content:

e. Editing the /book-app/api/index.js file to add the route file defined above:

Step 7

Running Endpoints

Time to see results!

We can test the endpoint using postman app or any API testing tool of your choice.

Start up the server, if you terminated it before:

npm run dev

If you dont know how to use postman, you might need to learn the basics, before continuing.

a. POST a book:

In the postman address bar enter this:

http://localhost:8000/api/v1/books    //POST

Change the http method to POST, then define the json data to post in the body

As an example:

b. GET all books

http://localhost:8000/api/v1/books    //GET

c. GET a particular book

http://localhost:8000/api/v1/books/:id    //GET

d. UPDATE a particular book

http://localhost:8000/api/v1/books/:id    //PUT

e. DELETE a particular book

http://localhost:8000/api/v1/books/:id    //DELETE

Step 8

Write Test Cases for Endpoints

I hope, all your endpoint worked as expected.

Now, let’s write test cases for each endpoint.

a. We will need to install testing framework like mocha and assertion library like chai and nyc for test coverage. From the terminal run:

npm install --save-dev mocha chai chai-http nyc

b. We will create a test folder in the path /*book-app/api/. *Then create the test file, call it test.js . Hence, the file is located: /book-app/api/test/test.js

import chai from 'chai';
import chatHttp from 'chai-http';
import 'chai/register-should';
import app from '../index';

chai.use(chatHttp);
const { expect } = chai;

describe('Testing the book endpoints:', () => {
  it('It should create a book', (done) => {
    const book = {
      title: 'First Awesome book',
      price: '$9.99',
      description: 'This is the awesome book'
    };
    chai.request(app)
      .post('/api/v1/books')
      .set('Accept', 'application/json')
      .send(book)
      .end((err, res) => {
        expect(res.status).to.equal(201);
        expect(res.body.data).to.include({
          id: 1,
          title: book.title,
          price: book.price,
          description: book.description
        });
        done();
      });
  });

  it('It should not create a book with incomplete parameters', (done) => {
    const book = {
      price: '$9.99',
      description: 'This is the awesome book'
    };
    chai.request(app)
      .post('/api/v1/books')
      .set('Accept', 'application/json')
      .send(book)
      .end((err, res) => {
        expect(res.status).to.equal(400);
        done();
      });
  });

  it('It should get all books', (done) => {
    chai.request(app)
      .get('/api/v1/books')
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(200);
        res.body.data[0].should.have.property('id');
        res.body.data[0].should.have.property('title');
        res.body.data[0].should.have.property('price');
        res.body.data[0].should.have.property('description');
        done();
      });
  });

  it('It should get a particular book', (done) => {
    const bookId = 1;
    chai.request(app)
      .get(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(200);
        res.body.data.should.have.property('id');
        res.body.data.should.have.property('title');
        res.body.data.should.have.property('price');
        res.body.data.should.have.property('description');
        done();
      });
  });

  it('It should not get a particular book with invalid id', (done) => {
    const bookId = 8888;
    chai.request(app)
      .get(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(404);
        res.body.should.have.property('message')
                            .eql(`Cannot find book with the id ${bookId}`);
        done();
      });
  });

  it('It should not get a particular book with non-numeric id', (done) => {
    const bookId = 'aaa';
    chai.request(app)
      .get(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(400);
        res.body.should.have.property('message')
                            .eql('Please input a valid numeric value');
        done();
      });
  });

  it('It should update a book', (done) => {
    const bookId = 1;
    const updatedBook = {
      id: bookId,
      title: 'Updated Awesome book',
      price: 'import chai from 'chai';
import chatHttp from 'chai-http';
import 'chai/register-should';
import app from '../index';

chai.use(chatHttp);
const { expect } = chai;

describe('Testing the book endpoints:', () => {
  it('It should create a book', (done) => {
    const book = {
      title: 'First Awesome book',
      price: '$9.99',
      description: 'This is the awesome book'
    };
    chai.request(app)
      .post('/api/v1/books')
      .set('Accept', 'application/json')
      .send(book)
      .end((err, res) => {
        expect(res.status).to.equal(201);
        expect(res.body.data).to.include({
          id: 1,
          title: book.title,
          price: book.price,
          description: book.description
        });
        done();
      });
  });

  it('It should not create a book with incomplete parameters', (done) => {
    const book = {
      price: '$9.99',
      description: 'This is the awesome book'
    };
    chai.request(app)
      .post('/api/v1/books')
      .set('Accept', 'application/json')
      .send(book)
      .end((err, res) => {
        expect(res.status).to.equal(400);
        done();
      });
  });

  it('It should get all books', (done) => {
    chai.request(app)
      .get('/api/v1/books')
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(200);
        res.body.data[0].should.have.property('id');
        res.body.data[0].should.have.property('title');
        res.body.data[0].should.have.property('price');
        res.body.data[0].should.have.property('description');
        done();
      });
  });

  it('It should get a particular book', (done) => {
    const bookId = 1;
    chai.request(app)
      .get(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(200);
        res.body.data.should.have.property('id');
        res.body.data.should.have.property('title');
        res.body.data.should.have.property('price');
        res.body.data.should.have.property('description');
        done();
      });
  });

  it('It should not get a particular book with invalid id', (done) => {
    const bookId = 8888;
    chai.request(app)
      .get(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(404);
        res.body.should.have.property('message')
                            .eql(`Cannot find book with the id ${bookId}`);
        done();
      });
  });

  it('It should not get a particular book with non-numeric id', (done) => {
    const bookId = 'aaa';
    chai.request(app)
      .get(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(400);
        res.body.should.have.property('message')
                            .eql('Please input a valid numeric value');
        done();
      });
  });

  it('It should update a book', (done) => {
    const bookId = 1;
    const updatedBook = {
      id: bookId,
      title: 'Updated Awesome book',
      price: '$10.99',
      description: 'We have updated the price'
    };
    chai.request(app)
      .put(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .send(updatedBook)
      .end((err, res) => {
        expect(res.status).to.equal(200);
        expect(res.body.data.id).equal(updatedBook.id);
        expect(res.body.data.title).equal(updatedBook.title);
        expect(res.body.data.price).equal(updatedBook.price);
        expect(res.body.data.description).equal(updatedBook.description);
        done();
      });
  });

  it('It should not update a book with invalid id', (done) => {
    const bookId = '9999';
    const updatedBook = {
      id: bookId,
      title: 'Updated Awesome book again',
      price: '$11.99',
      description: 'We have updated the price'
    };
    chai.request(app)
      .put(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .send(updatedBook)
      .end((err, res) => {
        expect(res.status).to.equal(404);
        res.body.should.have.property('message')
                            .eql(`Cannot find book with the id: ${bookId}`);
        done();
      });
  });

  it('It should not update a book with non-numeric id value', (done) => {
    const bookId = 'ggg';
    const updatedBook = {
      id: bookId,
      title: 'Updated Awesome book again',
      price: '$11.99',
      description: 'We have updated the price'
    };
    chai.request(app)
      .put(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .send(updatedBook)
      .end((err, res) => {
        expect(res.status).to.equal(400);
        res.body.should.have.property('message')
                            .eql('Please input a valid numeric value');
        done();
      });
  });


  it('It should delete a book', (done) => {
    const bookId = 1;
    chai.request(app)
      .delete(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(200);
        expect(res.body.data).to.include({});
        done();
      });
  });

  it('It should not delete a book with invalid id', (done) => {
    const bookId = 777;
    chai.request(app)
      .delete(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(404);
        res.body.should.have.property('message')
                            .eql(`Book with the id ${bookId} cannot be found`);
        done();
      });
  });

  it('It should not delete a book with non-numeric id', (done) => {
    const bookId = 'bbb';
    chai.request(app)
      .delete(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(400);
        res.body.should.have.property('message').eql('Please provide a numeric value');
        done();
      });
  });
});
0.99',
      description: 'We have updated the price'
    };
    chai.request(app)
      .put(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .send(updatedBook)
      .end((err, res) => {
        expect(res.status).to.equal(200);
        expect(res.body.data.id).equal(updatedBook.id);
        expect(res.body.data.title).equal(updatedBook.title);
        expect(res.body.data.price).equal(updatedBook.price);
        expect(res.body.data.description).equal(updatedBook.description);
        done();
      });
  });

  it('It should not update a book with invalid id', (done) => {
    const bookId = '9999';
    const updatedBook = {
      id: bookId,
      title: 'Updated Awesome book again',
      price: 'import chai from 'chai';
import chatHttp from 'chai-http';
import 'chai/register-should';
import app from '../index';

chai.use(chatHttp);
const { expect } = chai;

describe('Testing the book endpoints:', () => {
  it('It should create a book', (done) => {
    const book = {
      title: 'First Awesome book',
      price: '$9.99',
      description: 'This is the awesome book'
    };
    chai.request(app)
      .post('/api/v1/books')
      .set('Accept', 'application/json')
      .send(book)
      .end((err, res) => {
        expect(res.status).to.equal(201);
        expect(res.body.data).to.include({
          id: 1,
          title: book.title,
          price: book.price,
          description: book.description
        });
        done();
      });
  });

  it('It should not create a book with incomplete parameters', (done) => {
    const book = {
      price: '$9.99',
      description: 'This is the awesome book'
    };
    chai.request(app)
      .post('/api/v1/books')
      .set('Accept', 'application/json')
      .send(book)
      .end((err, res) => {
        expect(res.status).to.equal(400);
        done();
      });
  });

  it('It should get all books', (done) => {
    chai.request(app)
      .get('/api/v1/books')
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(200);
        res.body.data[0].should.have.property('id');
        res.body.data[0].should.have.property('title');
        res.body.data[0].should.have.property('price');
        res.body.data[0].should.have.property('description');
        done();
      });
  });

  it('It should get a particular book', (done) => {
    const bookId = 1;
    chai.request(app)
      .get(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(200);
        res.body.data.should.have.property('id');
        res.body.data.should.have.property('title');
        res.body.data.should.have.property('price');
        res.body.data.should.have.property('description');
        done();
      });
  });

  it('It should not get a particular book with invalid id', (done) => {
    const bookId = 8888;
    chai.request(app)
      .get(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(404);
        res.body.should.have.property('message')
                            .eql(`Cannot find book with the id ${bookId}`);
        done();
      });
  });

  it('It should not get a particular book with non-numeric id', (done) => {
    const bookId = 'aaa';
    chai.request(app)
      .get(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(400);
        res.body.should.have.property('message')
                            .eql('Please input a valid numeric value');
        done();
      });
  });

  it('It should update a book', (done) => {
    const bookId = 1;
    const updatedBook = {
      id: bookId,
      title: 'Updated Awesome book',
      price: '$10.99',
      description: 'We have updated the price'
    };
    chai.request(app)
      .put(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .send(updatedBook)
      .end((err, res) => {
        expect(res.status).to.equal(200);
        expect(res.body.data.id).equal(updatedBook.id);
        expect(res.body.data.title).equal(updatedBook.title);
        expect(res.body.data.price).equal(updatedBook.price);
        expect(res.body.data.description).equal(updatedBook.description);
        done();
      });
  });

  it('It should not update a book with invalid id', (done) => {
    const bookId = '9999';
    const updatedBook = {
      id: bookId,
      title: 'Updated Awesome book again',
      price: '$11.99',
      description: 'We have updated the price'
    };
    chai.request(app)
      .put(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .send(updatedBook)
      .end((err, res) => {
        expect(res.status).to.equal(404);
        res.body.should.have.property('message')
                            .eql(`Cannot find book with the id: ${bookId}`);
        done();
      });
  });

  it('It should not update a book with non-numeric id value', (done) => {
    const bookId = 'ggg';
    const updatedBook = {
      id: bookId,
      title: 'Updated Awesome book again',
      price: '$11.99',
      description: 'We have updated the price'
    };
    chai.request(app)
      .put(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .send(updatedBook)
      .end((err, res) => {
        expect(res.status).to.equal(400);
        res.body.should.have.property('message')
                            .eql('Please input a valid numeric value');
        done();
      });
  });


  it('It should delete a book', (done) => {
    const bookId = 1;
    chai.request(app)
      .delete(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(200);
        expect(res.body.data).to.include({});
        done();
      });
  });

  it('It should not delete a book with invalid id', (done) => {
    const bookId = 777;
    chai.request(app)
      .delete(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(404);
        res.body.should.have.property('message')
                            .eql(`Book with the id ${bookId} cannot be found`);
        done();
      });
  });

  it('It should not delete a book with non-numeric id', (done) => {
    const bookId = 'bbb';
    chai.request(app)
      .delete(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(400);
        res.body.should.have.property('message').eql('Please provide a numeric value');
        done();
      });
  });
});
1.99',
      description: 'We have updated the price'
    };
    chai.request(app)
      .put(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .send(updatedBook)
      .end((err, res) => {
        expect(res.status).to.equal(404);
        res.body.should.have.property('message')
                            .eql(`Cannot find book with the id: ${bookId}`);
        done();
      });
  });

  it('It should not update a book with non-numeric id value', (done) => {
    const bookId = 'ggg';
    const updatedBook = {
      id: bookId,
      title: 'Updated Awesome book again',
      price: 'import chai from 'chai';
import chatHttp from 'chai-http';
import 'chai/register-should';
import app from '../index';

chai.use(chatHttp);
const { expect } = chai;

describe('Testing the book endpoints:', () => {
  it('It should create a book', (done) => {
    const book = {
      title: 'First Awesome book',
      price: '$9.99',
      description: 'This is the awesome book'
    };
    chai.request(app)
      .post('/api/v1/books')
      .set('Accept', 'application/json')
      .send(book)
      .end((err, res) => {
        expect(res.status).to.equal(201);
        expect(res.body.data).to.include({
          id: 1,
          title: book.title,
          price: book.price,
          description: book.description
        });
        done();
      });
  });

  it('It should not create a book with incomplete parameters', (done) => {
    const book = {
      price: '$9.99',
      description: 'This is the awesome book'
    };
    chai.request(app)
      .post('/api/v1/books')
      .set('Accept', 'application/json')
      .send(book)
      .end((err, res) => {
        expect(res.status).to.equal(400);
        done();
      });
  });

  it('It should get all books', (done) => {
    chai.request(app)
      .get('/api/v1/books')
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(200);
        res.body.data[0].should.have.property('id');
        res.body.data[0].should.have.property('title');
        res.body.data[0].should.have.property('price');
        res.body.data[0].should.have.property('description');
        done();
      });
  });

  it('It should get a particular book', (done) => {
    const bookId = 1;
    chai.request(app)
      .get(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(200);
        res.body.data.should.have.property('id');
        res.body.data.should.have.property('title');
        res.body.data.should.have.property('price');
        res.body.data.should.have.property('description');
        done();
      });
  });

  it('It should not get a particular book with invalid id', (done) => {
    const bookId = 8888;
    chai.request(app)
      .get(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(404);
        res.body.should.have.property('message')
                            .eql(`Cannot find book with the id ${bookId}`);
        done();
      });
  });

  it('It should not get a particular book with non-numeric id', (done) => {
    const bookId = 'aaa';
    chai.request(app)
      .get(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(400);
        res.body.should.have.property('message')
                            .eql('Please input a valid numeric value');
        done();
      });
  });

  it('It should update a book', (done) => {
    const bookId = 1;
    const updatedBook = {
      id: bookId,
      title: 'Updated Awesome book',
      price: '$10.99',
      description: 'We have updated the price'
    };
    chai.request(app)
      .put(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .send(updatedBook)
      .end((err, res) => {
        expect(res.status).to.equal(200);
        expect(res.body.data.id).equal(updatedBook.id);
        expect(res.body.data.title).equal(updatedBook.title);
        expect(res.body.data.price).equal(updatedBook.price);
        expect(res.body.data.description).equal(updatedBook.description);
        done();
      });
  });

  it('It should not update a book with invalid id', (done) => {
    const bookId = '9999';
    const updatedBook = {
      id: bookId,
      title: 'Updated Awesome book again',
      price: '$11.99',
      description: 'We have updated the price'
    };
    chai.request(app)
      .put(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .send(updatedBook)
      .end((err, res) => {
        expect(res.status).to.equal(404);
        res.body.should.have.property('message')
                            .eql(`Cannot find book with the id: ${bookId}`);
        done();
      });
  });

  it('It should not update a book with non-numeric id value', (done) => {
    const bookId = 'ggg';
    const updatedBook = {
      id: bookId,
      title: 'Updated Awesome book again',
      price: '$11.99',
      description: 'We have updated the price'
    };
    chai.request(app)
      .put(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .send(updatedBook)
      .end((err, res) => {
        expect(res.status).to.equal(400);
        res.body.should.have.property('message')
                            .eql('Please input a valid numeric value');
        done();
      });
  });


  it('It should delete a book', (done) => {
    const bookId = 1;
    chai.request(app)
      .delete(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(200);
        expect(res.body.data).to.include({});
        done();
      });
  });

  it('It should not delete a book with invalid id', (done) => {
    const bookId = 777;
    chai.request(app)
      .delete(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(404);
        res.body.should.have.property('message')
                            .eql(`Book with the id ${bookId} cannot be found`);
        done();
      });
  });

  it('It should not delete a book with non-numeric id', (done) => {
    const bookId = 'bbb';
    chai.request(app)
      .delete(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(400);
        res.body.should.have.property('message').eql('Please provide a numeric value');
        done();
      });
  });
});
1.99',
      description: 'We have updated the price'
    };
    chai.request(app)
      .put(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .send(updatedBook)
      .end((err, res) => {
        expect(res.status).to.equal(400);
        res.body.should.have.property('message')
                            .eql('Please input a valid numeric value');
        done();
      });
  });


  it('It should delete a book', (done) => {
    const bookId = 1;
    chai.request(app)
      .delete(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(200);
        expect(res.body.data).to.include({});
        done();
      });
  });

  it('It should not delete a book with invalid id', (done) => {
    const bookId = 777;
    chai.request(app)
      .delete(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(404);
        res.body.should.have.property('message')
                            .eql(`Book with the id ${bookId} cannot be found`);
        done();
      });
  });

  it('It should not delete a book with non-numeric id', (done) => {
    const bookId = 'bbb';
    chai.request(app)
      .delete(`/api/v1/books/${bookId}`)
      .set('Accept', 'application/json')
      .end((err, res) => {
        expect(res.status).to.equal(400);
        res.body.should.have.property('message').eql('Please provide a numeric value');
        done();
      });
  });
});

c. Include the test script in package.json file

Replace:

"test": "echo \"Error: no test specified\" && exit 1"

With:

"test": "export NODE_ENV=test &&  sequelize db:migrate:undo:all  && sequelize db:migrate  && nyc --require @babel/register  mocha ./api/test/test.js --timeout 20000 --exit"

Observe this:

export NODE_ENV=test   //Works for mac OS

Using Windows, do:

SET NODE_ENV=test  //works for windows

Remember, we have a test database we created earlier: book_test So, from the test script, we run migrate afresh each time.

Before you run the test, ensure that PostgreSQL server is running.

Save all files and run the test:

npm run test

A Sample output:

All test cases passing!

Step 9

Integrate Travis CI, Coveralls and Code Climate

This i presume, you have been waiting for.

Ensure that all test cases are passing, because Travis will also run those tests. So if any is breaking in your local, it will also break in Travis.

Before we proceed, let’s compile all we have and put them in one folder called build in this folder all ES6 is converted to ES5 by babel .

Include the script immediately after the test script in package.json

"build": "rm -rf ./build && babel -d ./build ./api -s",

We should also include scripts for coveralls and code-climate

"generate-lcov": "nyc report --reporter=text-lcov > lcov.info",
"coveralls-coverage": "coveralls < lcov.info",
"codeclimate-coverage": "codeclimate-test-reporter < lcov.info",
"coverage": "nyc npm test && npm run generate-lcov && npm run coveralls-coverage && npm run codeclimate-coverage"

The script should look like:

"scripts": {
"dev": "nodemon --exec babel-node ./api/index.js",
"test": "export NODE_ENV=test &&  sequelize db:migrate:undo:all  && sequelize db:migrate  && nyc --require @babel/register  mocha ./api/test/test.js --timeout 20000 --exit",
"build": "rm -rf ./build && babel -d ./build ./api -s",
"generate-lcov": "nyc report --reporter=text-lcov > lcov.info",
"coveralls-coverage": "coveralls < lcov.info",
"codeclimate-coverage": "codeclimate-test-reporter < lcov.info",
"coverage": "nyc npm test && npm run generate-lcov && npm run coveralls-coverage && npm run codeclimate-coverage"
},

For this project, the final look of your package.json file should be:

{
  "name": "book-app",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "dev": "nodemon --exec babel-node ./api/index.js",
    "test": "export NODE_ENV=test &&  sequelize db:migrate:undo:all  && sequelize db:migrate  && nyc --require @babel/register  mocha ./api/test/test.js --timeout 20000 --exit",
    "build": "rm -rf ./build && babel -d ./build ./api -s",
    "generate-lcov": "nyc report --reporter=text-lcov > lcov.info",
    "coveralls-coverage": "coveralls < lcov.info",
    "codeclimate-coverage": "codeclimate-test-reporter < lcov.info",
    "coverage": "nyc npm test && npm run generate-lcov && npm run coveralls-coverage && npm run codeclimate-coverage"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.18.3",
    "chai": "^4.2.0",
    "chai-http": "^4.2.1",
    "dotenv": "^7.0.0",
    "express": "^4.16.4",
    "nodemon": "^1.18.11",
    "path": "^0.12.7",
    "pg": "^7.10.0",
    "pg-hstore": "^2.3.2",
    "sequelize": "^5.7.0"
  },
  "devDependencies": {
    "@babel/cli": "^7.4.3",
    "@babel/core": "^7.4.3",
    "@babel/node": "^7.2.2",
    "@babel/plugin-transform-runtime": "^7.4.3",
    "@babel/preset-env": "^7.4.3",
    "@babel/register": "^7.4.0",
    "@babel/runtime": "^7.4.3",
    "babel-loader": "^8.0.5",
    "codeclimate-test-reporter": "^0.5.1",
    "coveralls": "^3.0.2",
    "eslint": "^5.16.0",
    "eslint-config-airbnb-base": "^13.1.0",
    "eslint-plugin-import": "^2.17.2",
    "mocha": "^6.1.4",
    "nyc": "^13.3.0"
  }
}

Now let’s create the build folder by running:

npm run build

You will see a folder in the path: /book-app/

book-app
├── api
├── build

a. Sign-Up/Sign-In with Travis

Create an account with Travis if you don’t have one already;

**b. **Create a .travis.yml file. This is the file Travis will look for and execute commands. This file should be created in the same path as the package.jsonThat is: /book-app/

Populate the file with:

language: node_js
node_js:
  - "stable"
cache:
  directories:
    - "node_modules"
install:
  - npm install
services:
  - postgresql

env:
  global:
  - NODE_ENV=test

before_script:
  - psql -c 'create database book_test;' -U postgres
  - psql -c "CREATE USER steven WITH PASSWORD null;" -U postgres
  - npm run build
  - npm install -g sequelize-cli
  - sequelize db:migrate
script:
  - npm test
after_success:
  - npm run coverage


Observe that there is a script to create a database, a user , run migrationrun build npm run build. Also observe the test command npm test

c. Push your code to github:

To see the workings of Travis, Coveralls and Code Climate, push the code you have been working on to a new git repository or an existing one created for this project.

After pushing to github, go to your Travis account and turn on that repository:

An example:

Click on the repository, It will take to a page like this:

You may likely see this badge first:

travis_unknown_build

If your build does not start automatically, look at the top right of the screenshot above, you will see More options choose the Trigger build , then a modal pops up, click the Trigger custom build . This will start the build manually.

A mail will be sent to you when the build is completed. You can check the build status again, you should see the green passing badge.

d. Use the Travis badge in your github repository

Click on the Travis badge, a modal pops up, Choose markdown and copy the link and paste in the README.md file of your repository

e. Create an account with or sign-in to your Coveralls

Connect the same repository we used for Travis by turning it on. Then click on DETAILS. Scroll down, copy the badge code when you click EMBED and add it to your README.md file

f. Create an account with or sign-in to your CodeClimate

Once you are in, Add the same repository you have used in the two cases above.

Once you click on the repository, it will take you to a page similar to this:

Code maintainability can have status A to F

The A status shows that the code can be maintained easily, while a status of Ftells the opposite.

To get the badge code, click on the Repo Settings tab. Click on the Badges. click the Markdown, copy the code and add it to your README.md file

Note:

Let me bring to your notice that Code Climate also have Code Coverage. Since Coveralls is already covering our code, i didn’t see it necessary to add Code Coverage. If you wish to add it, Click on Test Coverage and follow the instructions.

So, Your README.md should look similar to:

[![Build Status](https://travis-ci.org/victorsteven/Book-app-NodeJS-PostgreSQL-Travis-Coveralls-Code-Climate.svg?branch=master)](https://travis-ci.org/victorsteven/Book-app-NodeJS-PostgreSQL-Travis-Coveralls-Code-Climate)    
[![Coverage Status (https://coveralls.io/repos/github/victorsteven/Book-app-NodeJS-PostgreSQL-Travis-Coveralls-Code-Climate/badge.svg?branch=master)](https://coveralls.io/github/victorsteven/Book-app-NodeJS-PostgreSQL-Travis-Coveralls-Code-Climate?branch=master)    
[![Maintainability](https://api.codeclimate.com/v1/badges/750e4ce5c8a8112eec3a/maintainability)](https://codeclimate.com/github/victorsteven/Book-app-NodeJS-PostgreSQL-Travis-Coveralls-Code-Climate/maintainability)

With the output:

Bravo!

Step 10:

Final testing and scaling the api

Wow! This article is about 12 minutes read. I admire your patience to see the very end. However, I can’t call this the end because, there are still some stuffs to do, which, you wont have issues executing i guess.

a. You can host the application on <a href="https://heroku.com/" target="_blank">Heroku</a>

b. You can create more endpoints

c. You can write more test cases

d. And lots more.

Conclusion

I hope you didn’t have too much trouble following the instructions in this article. This article is very basic, meaning that you can apply the knowledge gained here to any similar project you are currently working on.

Get the source code here: github

Thanks for reading ❤

Secure Node.js, Express.js and PostgreSQL API using Passport.js

Secure Node.js, Express.js and PostgreSQL API using Passport.js

The comprehensive step by step tutorial on building secure Node.js, Express.js, Passport.js, and PostgreSQL Restful Web Service

The comprehensive step by step tutorial on building secure Node.js, Express.js, Passport.js, and PostgreSQL Restful Web Service. Previously, we have shown you a combination of Node.js, Express.js, and PostgreSQL tutorial. Now, we just add a security for that RESTful Web Service endpoints. Of course, we will start this tutorial from scratch or from zero application. We will use JWT for this Node.js, Express.js, Passport.js, and PostgreSQL tutorial.

Table of Contents:

The following tools, frameworks, and modules are required for this tutorial:

We assume that you have installed PostgreSQL server in your machine or can use your own remote server (we are using PostgreSQL 9.5.13). Also, you have installed Node.js in your machine and can run node, npm or yarn command in your terminal or command line. Next, check their version by type this commands in your terminal or command line.

node -v
v8.12.0
npm -v
6.4.1
yarn -v
1.10.1

That the versions that we are uses. Let’s continue with the main steps.

1. Create Express.js Project and Install Required Modules

Open your terminal or node command line the go to your projects folder. First, install express generator using this command.

sudo npm install express-generator -g

Next, create an Express.js app using this command.

express secure-node --view=ejs

This will create Express.js project with the EJS view instead of Jade view template because using ‘–view=ejs’ parameter. Next, go to the newly created project folder then install node modules.

cd secure-node && npm install

You should see the folder structure like this.

There’s no view yet using the latest Express generator. We don’t need it because we will create a RESTful API.

2. Add and Configure Sequelize.js Module and Dependencies

Before installing the modules for this project, first, install Sequelize-CLI by type this command.

sudo npm install -g sequelize-cli

To install Sequelize.js module, type this command.

npm install --save sequelize

Then install the module for PostgreSQL.

npm install --save pg pg-hstore

Next, create a new file at the root of the project folder.

touch .sequelizerc

Open and edit that file then add this lines of codes.

const path = require('path');

module.exports = {
&nbsp; "config": path.resolve('./config', 'config.json'),
&nbsp; "models-path": path.resolve('./models'),
&nbsp; "seeders-path": path.resolve('./seeders'),
&nbsp; "migrations-path": path.resolve('./migrations')
};

That files will tell Sequelize initialization to generate config, models, seeders and migrations files to specific directories. Next, type this command to initialize the Sequelize.

sequelize init

That command will create config/config.json, models/index.js, migrations and seeders directories and files. Next, open and edit config/config.json then make it like this.

{
&nbsp; "development": {
&nbsp; &nbsp; "username": "djamware",
&nbsp; &nbsp; "password": "[email&nbsp;protected]@r3",
&nbsp; &nbsp; "database": "secure_node",
&nbsp; &nbsp; "host": "127.0.0.1",
&nbsp; &nbsp; "dialect": "postgres"
&nbsp; },
&nbsp; "test": {
&nbsp; &nbsp; "username": "root",
&nbsp; &nbsp; "password": "[email&nbsp;protected]@r3",
&nbsp; &nbsp; "database": "secure_node",
&nbsp; &nbsp; "host": "127.0.0.1",
&nbsp; &nbsp; "dialect": "postgres"
&nbsp; },
&nbsp; "production": {
&nbsp; &nbsp; "username": "root",
&nbsp; &nbsp; "password": "[email&nbsp;protected]@r3",
&nbsp; &nbsp; "database": "secure_node",
&nbsp; &nbsp; "host": "127.0.0.1",
&nbsp; &nbsp; "dialect": "postgres"
&nbsp; }
}

We use the same configuration for all the environment because we are using the same machine, server, and database for this tutorial.

Before run and test connection, make sure you have created a database as described in the above configuration. You can use the psql command to create a user and database.

psql postgres --u postgres

Next, type this command for creating a new user with password then give access for creating the database.

postgres-# CREATE ROLE djamware WITH LOGIN PASSWORD '[email&nbsp;protected]@r3';
postgres-# ALTER ROLE djamware CREATEDB;

Quit psql then log in again using the new user that previously created.

postgres-# \q
psql postgres -U djamware

Enter the password, then you will enter this psql console.

psql (9.5.13)
Type "help" for help.

postgres=>

Type this command to creating a new database.

postgres=> CREATE DATABASE secure_node;

Then give that new user privileges to the new database then quit the psql.

postgres=> GRANT ALL PRIVILEGES ON DATABASE secure_node TO djamware;
postgres=> \q

3. Create or Generate Models and Migrations

We will use Sequelize-CLI to generating a new model. Type this command to create a model for Products and User model for authentication.

sequelize model:create --name Product --attributes prod_name:string,prod_desc:string,prod_price:float
sequelize model:create --name User --attributes username:string,password:string

That command creates a model file to the model’s folder and a migration file to folder migrations. Next, modify models/user.js and then import this module.

var bcrypt = require('bcrypt-nodejs');

Add the new methods to the User model, so the user.js class will be like this.

module.exports = (sequelize, DataTypes) => {
&nbsp; const User = sequelize.define('User', {
&nbsp; &nbsp; username: DataTypes.STRING,
&nbsp; &nbsp; password: DataTypes.STRING
&nbsp; }, {});
&nbsp; User.beforeSave((user, options) => {
&nbsp; &nbsp; if (user.changed('password')) {
&nbsp; &nbsp; &nbsp; user.password = bcrypt.hashSync(user.password, bcrypt.genSaltSync(10), null);
&nbsp; &nbsp; }
&nbsp; });
&nbsp; User.prototype.comparePassword = function (passw, cb) {
&nbsp; &nbsp; bcrypt.compare(passw, this.password, function (err, isMatch) {
&nbsp; &nbsp; &nbsp; &nbsp; if (err) {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return cb(err);
&nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; &nbsp; cb(null, isMatch);
&nbsp; &nbsp; });
&nbsp; };
&nbsp; User.associate = function(models) {
&nbsp; &nbsp; // associations can be defined here
&nbsp; };
&nbsp; return User;
};

For the models/product.js there’s no action needed, leave it as default generated the model class.

4. Create Routers for RESTful Web Service and Authentication

To authenticating users and secure the resources or endpoint create this file as a router.

touch routes/api.js

Open and edit routes/api.js then declares all require variables.

const express = require('express');
const jwt = require('jsonwebtoken');
const passport = require('passport');
const router = express.Router();
require('../config/passport')(passport);
const Product = require('../models').Product;
const User = require('../models').User;

Create a router for signup or register the new user.

router.post('/signup', function(req, res) {
&nbsp; console.log(req.body);
&nbsp; if (!req.body.username || !req.body.password) {
&nbsp; &nbsp; res.status(400).send({msg: 'Please pass username and password.'})
&nbsp; } else {
&nbsp; &nbsp; User
&nbsp; &nbsp; &nbsp; .create({
&nbsp; &nbsp; &nbsp; &nbsp; username: req.body.username,
&nbsp; &nbsp; &nbsp; &nbsp; password: req.body.password
&nbsp; &nbsp; &nbsp; })
&nbsp; &nbsp; &nbsp; .then((user) => res.status(201).send(user))
&nbsp; &nbsp; &nbsp; .catch((error) => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log(error);
&nbsp; &nbsp; &nbsp; &nbsp; res.status(400).send(error);
&nbsp; &nbsp; &nbsp; });
&nbsp; }
});

Create a router for sign in or login with username and password.

router.post('/signin', function(req, res) {
&nbsp; User
&nbsp; &nbsp; &nbsp; .find({
&nbsp; &nbsp; &nbsp; &nbsp; where: {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; username: req.body.username
&nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; })
&nbsp; &nbsp; &nbsp; .then((user) => {
&nbsp; &nbsp; &nbsp; &nbsp; if (!user) {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return res.status(401).send({
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; message: 'Authentication failed. User not found.',
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; &nbsp; user.comparePassword(req.body.password, (err, isMatch) => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if(isMatch && !err) {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var token = jwt.sign(JSON.parse(JSON.stringify(user)), 'nodeauthsecret', {expiresIn: 86400 * 30});
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; jwt.verify(token, 'nodeauthsecret', function(err, data){
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log(err, data);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; })
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; res.json({success: true, token: 'JWT ' + token});
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; } else {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; res.status(401).send({success: false, msg: 'Authentication failed. Wrong password.'});
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; &nbsp; })
&nbsp; &nbsp; &nbsp; })
&nbsp; &nbsp; &nbsp; .catch((error) => res.status(400).send(error));
});

Create a secure router to get and post product data.

router.get('/product', passport.authenticate('jwt', { session: false}), function(req, res) {
&nbsp; var token = getToken(req.headers);
&nbsp; if (token) {
&nbsp; &nbsp; Product
&nbsp; &nbsp; &nbsp; .findAll()
&nbsp; &nbsp; &nbsp; .then((products) => res.status(200).send(products))
&nbsp; &nbsp; &nbsp; .catch((error) => { res.status(400).send(error); });
&nbsp; } else {
&nbsp; &nbsp; return res.status(403).send({success: false, msg: 'Unauthorized.'});
&nbsp; }
});

router.post('/product', passport.authenticate('jwt', { session: false}), function(req, res) {
&nbsp; var token = getToken(req.headers);
&nbsp; if (token) {
&nbsp; &nbsp; Product
&nbsp; &nbsp; &nbsp; .create({
&nbsp; &nbsp; &nbsp; &nbsp; prod_name: req.body.prod_name,
&nbsp; &nbsp; &nbsp; &nbsp; prod_desc: req.body.prod_desc,
&nbsp; &nbsp; &nbsp; &nbsp; prod_price: req.body.prod_price
&nbsp; &nbsp; &nbsp; })
&nbsp; &nbsp; &nbsp; .then((product) => res.status(201).send(product))
&nbsp; &nbsp; &nbsp; .catch((error) => res.status(400).send(error));
&nbsp; } else {
&nbsp; &nbsp; return res.status(403).send({success: false, msg: 'Unauthorized.'});
&nbsp; }
});

Create a function for extract the token.

getToken = function (headers) {
&nbsp; if (headers && headers.authorization) {
&nbsp; &nbsp; var parted = headers.authorization.split(' ');
&nbsp; &nbsp; if (parted.length === 2) {
&nbsp; &nbsp; &nbsp; return parted[1];
&nbsp; &nbsp; } else {
&nbsp; &nbsp; &nbsp; return null;
&nbsp; &nbsp; }
&nbsp; } else {
&nbsp; &nbsp; return null;
&nbsp; }
};

Finally, export the router as a module.

module.exports = router;

5. Run and Test Secure Node.js, Express.js, Passport.js, and PostgreSQL Web Service

To run and test this secure Node.js, Express.js, Passport.js, and PostgreSQL Web Service, run the PostgreSQL instance first then run this command from the Terminal.

nodemon

or

npm start

To test the secure Product endpoint, open the Postman then type fill all required fields like this image.

You should get the response message Unauthorized and status code 401. Next, test signup using the Postman by changing the method to POST, add the address localhost:3000/api/signup, add the header Content-type with value application/json and the body of request raw text like this.

{ "username":"[email&nbsp;protected]", "password":"qqqq1111" }

You should get this response when executing successfully.

Next, test to log in with the above signed/registered username and password by changing the URL to localhost:3000/api/signin. You should get this response when executes successfully.

Now, you can back using the previous GET method with additional header using the token get from the sign in/log in response. You should see the Product data like below.

That it’s, the secure Node.js, Express.js, Passport.js, and PostgreSQL Web Service. You can get the working source code from our GitHub.

Learn More

The Complete Node.js Developer Course (2nd Edition)

Learn and Understand NodeJS

Node JS: Advanced Concepts

GraphQL: Learning GraphQL with Node.Js

Angular (Angular 2+) & NodeJS - The MEAN Stack Guide

The Complete Python & PostgreSQL Developer Course

SQL & Database Design A-Z™: Learn MS SQL Server + PostgreSQL

The Complete SQL Bootcamp

The Complete Oracle SQL Certification Course

Node.js - Express Persistent Session Store with PostgreSQL + Sequelize

Node.js - Express Persistent Session Store with PostgreSQL + Sequelize

Node.js - Express Persistent Session Store with PostgreSQL + Sequelize

Your application may use session to authenticate user. If you're using Node.js, the most popular option to implement session is using a library called Passport.js. The problem is by default sessions are not stored persistently. If the application is restarted, all sessions will be lost. The easiest way to store sessions persistently is by using database to store them. In this tutorial, I'm going to show you how to store session persistently to PostgreSQL database with Sequelize as the ORM.

Add Dependencies to Your Project

Below are the dependencies you'll need to follow this tutorial. Add them to your package.json and run npm install.

package.json

{
"dependencies": {
"bcrypt": "~3.0.0",
"bluebird": "~3.5.1",
"connect-session-sequelize": "~6.0.0",
"dotenv": "~6.1.0",
"express-session": "~1.15.6",
"lodash": "~4.17.11",
"passport": "~0.4.0",
"passport-local": "~1.0.0",
"sequelize": "~4.38.0"
}
}

Edit your .env

Put these variables on your .env file and adjust the values according to your database setup. In addition to database config, you also need to set a session secret.

DATABASE_NAME=your_db_name
DATABASE_USERNAME=your_db_username
DATABASE_PASSWORD=your_db_password
DATABASE_HOST=your_db_ip
DATABASE_PORT=your_db_port
SESSION_SECRET=randomstringabcde123

Create Singleton Sequelize Object

You only need to create a singleton connection object to the same database and use the same object anywhere you need it.

utils/sequelize-singleton.js

require('dotenv').config();

const Sequelize = require('sequelize');

const self = module.exports;
let sequelize;

/**
 * Construct a singleton sequelize object to query the database
 * 
 * @returns {object} - Sequelize object
 */
exports.initialize = () => {
if (!sequelize) {
 const dbName = process.env.DATABASE_NAME;
 const dbUsername = process.env.DATABASE_USERNAME;
 const dbPassword = process.env.DATABASE_PASSWORD;
 const dbHost = process.env.DATABASE_HOST;
 const dbPort = process.env.DATABASE_PORT;
return new Sequelize(dbName, dbUsername, dbPassword, {
host: dbHost,
port: dbPort,
dialect: 'postgres',
});
}

return sequelize;
};

module.exports = self.initialize();

Create User Model

The user data is stored in database, so you need to define the model for it. We use bcrypt to hash the password which is done inside beforeSave hook. There is also compareMethod prototype function used to check whether a given password matches the hashed value stored in database.

models/User.js

const _ = require('lodash');
const bcrypt = require('bcrypt');
const Bluebird = require('bluebird');
const Sequelize = require('sequelize');

const sequelize = require('../singleton/sequelize-singleton');

const mappings = {
userId: {
type: Sequelize.UUID,
primaryKey: true,
defaultValue: Sequelize.DataTypes.UUIDV4,
},
name: {
type: Sequelize.TEXT,
allowNull: false,
},
email: {
type: Sequelize.TEXT,
allowNull: false,
unique: true,
},
password: {
type: Sequelize.TEXT,
allowNull: false,
},
};

const User = sequelize.define('User', mappings, {
indexes: [
{
name: 'user_userId_index',
method: 'BTREE',
fields: ['userId'],
},
{
name: 'user_email_index',
method: 'BTREE',
fields: ['email'],
},
{
name: 'user_role_index',
method: 'BTREE',
fields: ['role'],
},
{
name: 'user_status_index',
method: 'BTREE',
fields: ['status'],
},
],
});

User.prototype.comparePassword = function (password) { // eslint-disable-line func-names
return Bluebird.resolve()
.then(() => bcrypt.compareSync(password, this.password))
.catch((err) => {
console.log(err);

return false;
});
};

User.hook('beforeSave', (user) => {
user.name = _.trim(user.name);

if ((user.previous('password') !== user.password) && (!_.isEmpty(user.password))) {
const salt = bcrypt.genSaltSync(10);
const hash = bcrypt.hashSync(user.password, salt);
user.password = hash;
}

return user;
});

exports.getMapping = () => mappings;

exports.getModel = () => User;

Create Query Helpers

In order to get user data from database, we need to use some queries. First for getting user by ID (used to deserialize user) and the other is for getting user by email (used on login).

queries/user.js

const User = require('../models/User').getModel();

exports.getUserById = userId => User.findOne({
where: { userId },
});

exports.getUserByEmail = email => User.findOne({
where: { email },
});

Create Passport.js Configuration.

To use Passport.js, we need to create the configuration by implementing required methods (serializeUser and deserializeUser) and defining the authentication strategy we want to use. In this tutorial, we use local authentication using passport-local.

config/passport.js

const Bluebird = require('bluebird');
const LocalStrategy = require('passport-local').Strategy;

const userQueries = require('../queries/user');

module.exports = (passport) => {
passport.serializeUser((user, done) => {
done(null, user.userId);
});

passport.deserializeUser((id, done) => Bluebird.resolve()
.then(async () => {
const user = await userQueries.getUserById(id);

done(null, user);
})
.catch(done));

passport.use('local', new LocalStrategy(
{
usernameField: 'email',
passwordField: 'password',
passReqToCallback: true,
},
(req, email, password, done) => Bluebird.resolve()
.then(async () => {
const user = await userQueries.getUserByEmail(email);

if (!user || !await user.comparePassword(password)) {
return done(null, null);
}

return done(null, user);
})
.catch(done),
));
};

Create Session Model

As you need to store session in database and use Sequelize as the ORM, you need to create a model for the session. The table has 3 columns:

  • sid (STRING) : The session ID
  • expires (DATE): Time when the token becomes expired
  • data (STRING): Contains cookie data and user ID in JSON format. Example: {"cookie":{"originalMaxAge":2592000000,"expires":"2018-12-22T07:29:53.051Z","httpOnly":true,"path":"/"},"passport":{"user":"4b946762-b931-4bc2-b285-0a7464ad3c3a"}}
const Sequelize = require('sequelize');

const sequelize = require('../singleton/sequelizeSingleton');

/**
 * Sessions table is used to store user session persistently.
 * 
 *
   * Read more on https://www.npmjs.com/package/connect-session-sequelize
 */
const mappings = {
sid: {
type: Sequelize.STRING,
primaryKey: true,
},
expires: Sequelize.DATE,
data: Sequelize.STRING(50000),
};

const Session = sequelize.define('Session', mappings, {
indexes: [
{
name: 'session_sid_index',
method: 'BTREE',
fields: ['sid'],
},
],
});

exports.getMapping = () => mappings;

exports.getModel = () => Session;

Add Controllers to Handle Sign In and Sign Out

Of course we need to create a controller to handle sign in and another one to handle sign out. Below is the controller for sign in. It authenticates user by using local strategy (the name of the strategy must match what we've already defined on passport configuration). Every time a user successfully logged in, a new session is regenerated for that user.

routes/sign-in.js

const Bluebird = require('bluebird');
const passport = require('passport');

/**
 * Authenticate with passport.
 * @param {Object} req
 * @param {Object} res
 * @param {Function} next
 */
const authenticate = (req, res, next) => new Bluebird((resolve, reject) => {
passport.authenticate('local', (err, user) => {
if (err) {
return reject(err);
}

return resolve(user);
})(req, res, next);
});

/**
 * Login
 * @param {Object} req
 * @param {Object} user
 */
const login = (req, user) => new Bluebird((resolve, reject) => {
req.login(user, (err) => {
if (err) {
return reject(err);
}

return resolve();
});
});

/**
 * Regenerate user session.
 * @param {Object} req
 */
const regenerateSession = req => new Bluebird((resolve, reject) => {
req.session.regenerate((err) => {
if (err) {
return reject(err);
}

return resolve();
});
});

/**
 * Save user session.
 * @param {Object} req
 */
const saveSession = req => new Bluebird((resolve, reject) => {
req.session.save((err) => {
if (err) {
return reject(err);
}

return resolve();
});
});

/**
 * HTTP handler for sign in.
 *
 * @param {Object} req
 * @param {Object} res
 * @param {Function} next
 */
module.exports = (req, res, next) => Bluebird.resolve()
.then(async () => {
const user = await authenticate(req, res, next);

if (!user) {
return res.status(401).send('Invalid email or password');
}

await login(req, user);
const temp = req.session.passport;

await regenerateSession(req);
req.session.passport = temp;

await saveSession(req);

return res.send();
})
.catch(next);

The logout controller is very simple, just make the session expired by calling req.logout().

routes/sign-out.js

/**
 * HTTP handler for sign out.
 *
 * @param {Object} req
 * @param {Object} res
 */
module.exports = (req, res) => {
req.logout();

res.send();
};

Use Passport.js and Session Store on Your Application

Finally, load the Passport.js configurtaion in your application. Then use express-session along with the configuration. To save sessions persistently, you need to add store configuration with a new instance of connect-session-sequelize. As for db, pass the sequelize singletion object, while the table should be filled with the name of the table that stores the sessions - in this tutorial the table name is Session. Don't forget to load the models of Staff and Session beforehand.

app.js

const app = require('express')();
const session = require('express-session');

const passport = require('passport');
const SequelizeStore = require('connect-session-sequelize')(session.Store);

const passportConfig = require('./config/passport');
const sequelize = require('./utils/sequelize-singleton');

require('./models/Staff');
require('./models/Session');

passportConfig(passport);
app.use(session({
secret: process.env.SESSION_SECRET,
resave: false,
saveUninitialized: false,
cookie: {
maxAge: 30 * 24 * 60 * 60 * 1000, // 1 month
},
store: new SequelizeStore({
db: sequelize,
table: 'Session',
}),
}));
app.use(passport.initialize());
app.use(passport.session());

app.post('/sign-in', require('./routes/sign-in'));
app.post('/sign-out', require('./routes/sign-out'));

Now you can try the code by sending HTTP request to the sign in and sign out endpoints. If it works, you should see user session stored in Sessions table. The session of a user will become expired if the user calls the sign out endpoint.

That's all about how to store session persistently in PostgreSQL database using Passport authentication and Sequelize ORM.

Node, Express, PostgreSQL, Vue 2 and GraphQL CRUD Web App

Node, Express, PostgreSQL, Vue 2 and GraphQL CRUD Web App

A comprehensive step by step tutorial on building CRUD Web App using Node, Express, PostgreSQL, Vue 2 and Graphql CRUD Web App

A comprehensive step by step tutorial on building CRUD Web App using Node, Express, PostgreSQL, Vue 2 and Graphql CRUD Web App

For the client side (Vue 2) we will use Vue-Apollo module. For the backend side, we will use Node, Express, Sequelize, and PostgreSQL with Express-Graphql module and their dependencies. The scenario for this tutorial is simple as usual, just the CRUD operation which data accessible through GraphQL.

Table of Contents:
  • Create Express.js Application and Install Required Modules
  • Add and Configure Sequelize.js Module and Dependencies
  • Create or Generate Models and Migrations
  • Install GraphQL Modules and Dependencies
  • Create GraphQL Schemas for the Book
  • Add Mutation for CRUD Operation to the Schema
  • Test GraphQL using GraphiQL
  • Create Vue 2 Application
  • Install Required Modules, Dependencies, and Router
  • Create a Component to Display List of Books
  • Create a Component to Show and Delete Books
  • Create a Component to Add a New Book
  • Create a Component to Edit a Book
  • Run and Test GraphQL CRUD from the Vue 2 Application

The following tools, frameworks, and modules are required for this tutorial:

  • Node.js (choose recommended version)
  • Vue 2
  • Express.js
  • GraphQL
  • Express-GraphQL
  • Vue-Apollo
  • Bootstrap-Vue
  • Terminal (Mac/Linux) or Node Command Line (Windows)
  • IDE or Text Editor (We are using Visual Studio Code)

We assume that you have already Installed Node.js. Make sure Node.js command line is working (on Windows) or runnable in Linux/OS X terminal.

node -v
v10.15.1
npm -v
6.8.0
yarn -v
1.10.1

That the versions that we are uses. Let's continue with the main steps.

1. Create Express.js Application and Install Required Modules

Open your terminal or node command line the go to your projects folder. First, install express generator using this command.

sudo npm install express-generator -g

Next, create an Express.js app using this command.

express vue-graphql

This will create Express.js project with files and directories.

create : vue-graphql/
create : vue-graphql/public/
create : vue-graphql/public/javascripts/
create : vue-graphql/public/images/
create : vue-graphql/public/stylesheets/
create : vue-graphql/public/stylesheets/style.css
create : vue-graphql/routes/
create : vue-graphql/routes/index.js
create : vue-graphql/routes/users.js
create : vue-graphql/views/
create : vue-graphql/views/error.jade
create : vue-graphql/views/index.jade
create : vue-graphql/views/layout.jade
create : vue-graphql/app.js
create : vue-graphql/package.json
create : vue-graphql/bin/
create : vue-graphql/bin/www

Next, go to the newly created project folder then install node modules.

cd vue-graphql && npm install

There's no view yet using the latest Express generator. We don't need it because we will create a GraphQL server.

2. Add and Configure Sequelize.js Module and Dependencies

Before installing the modules for this project, first, install Sequelize-CLI by type this command.

sudo npm install -g sequelize-cli

To install Sequelize.js module, type this command.

npm install --save sequelize

Then install the module for PostgreSQL.

npm install --save pg pg-hstore

Next, create a new file at the root of the project folder.

touch .sequelizerc

Open and edit that file then add these lines of codes.

const path = require('path');

module.exports = {
  "config": path.resolve('./config', 'config.json'),
  "models-path": path.resolve('./models'),
  "seeders-path": path.resolve('./seeders'),
  "migrations-path": path.resolve('./migrations')
};

That files will tell Sequelize initialization to generate config, models, seeders and migrations files to specific directories. Next, type this command to initialize the Sequelize.

sequelize init

That command will create config/config.json, models/index.js, migrations and seeders directories and files. Next, open and edit config/config.json then make it like this.

{
  "development": {
    "username": "djamware",
    "password": "[email protected]@r3",
    "database": "node_sequelize",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "test": {
    "username": "root",
    "password": "[email protected]@r3",
    "database": "node_sequelize",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "production": {
    "username": "root",
    "password": "[email protected]@r3",
    "database": "node_sequelize",
    "host": "127.0.0.1",
    "dialect": "postgres"
  }
}

We use the same configuration for all the environment because we are using the same machine, server, and database for this tutorial.

Before run and test connection, make sure you have created a database as described in the above configuration. You can use the psql command to create a user and database.

psql postgres --u postgres

Next, type this command for creating a new user with password then give access for creating the database.

postgres-# CREATE ROLE djamware WITH LOGIN PASSWORD '[email protected]@r3';
postgres-# ALTER ROLE djamware CREATEDB;

Quit psql then log in again using the new user that previously created.

postgres-# \q
psql postgres -U djamware

Enter the password, then you will enter this psql console.

psql (9.5.13)
Type "help" for help.

postgres=>

Type this command to creating a new database.

postgres=> CREATE DATABASE book_store;

Then give that new user privileges to the new database then quit the psql.

postgres=> GRANT ALL PRIVILEGES ON DATABASE book_store TO djamware;
postgres=> \q

3. Create or Generate Models and Migrations

We will use Sequelize-CLI to generate a new model. Type this command to create a model for 'Book'.

sequelize model:generate --name Book --attributes isbn:string,title:string,author:string,description:string,publishedYear:integer,publisher:string

That commands will generate models and migration files. The content of the model file looks like this.

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Book = sequelize.define('Book', {
    isbn: DataTypes.STRING,
    title: DataTypes.STRING,
    author: DataTypes.STRING,
    description: DataTypes.STRING,
    publishedYear: DataTypes.INTEGER,
    publisher: DataTypes.STRING
  }, {});
  Book.associate = function(models) {
    // associations can be defined here
  };
  return Book;
};

And the migration file looks like this.

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Books', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      isbn: {
        type: Sequelize.STRING
      },
      title: {
        type: Sequelize.STRING
      },
      author: {
        type: Sequelize.STRING
      },
      description: {
        type: Sequelize.STRING
      },
      publishedYear: {
        type: Sequelize.INTEGER
      },
      publisher: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Books');
  }
};

Finally, for migrations, there's nothing to change and they all ready to generate the table to the PostgreSQL Database. Type this command to generate the table to the database.

sequelize db:migrate

4. Install GraphQL Modules and Dependencies

Now, the GraphQL time. Type this command to install GraphQL modules and it's dependencies.

npm install express express-graphql graphql graphql-date cors --save

Next, open and edit app.js then declare all of those modules and dependencies.

var graphqlHTTP = require('express-graphql');
var schema = require('./graphql/bookSchemas');
var cors = require("cors");

The schema is not created yet, we will create it in the next steps. Next, add these lines of codes for configuring GraphQL that can use over HTTP.

app.use('*', cors());
app.use('/graphql', cors(), graphqlHTTP({
  schema: schema,
  rootValue: global,
  graphiql: true,
}));

That's configuration are enabled CORS and the GraphiQL. GraphiQL is the user interface for testing GraphQL query.

5. Create GraphQL Schemas for the Book

Create a folder at the server folder for hold GraphQL Schema files then create a Javascript file for the schema.

mkdir graphql
touch graphql/bookSchemas.js

Next, open and edit server/graphql/bookSchemas.js then declares all required modules and models.

var GraphQLSchema = require('graphql').GraphQLSchema;
var GraphQLObjectType = require('graphql').GraphQLObjectType;
var GraphQLList = require('graphql').GraphQLList;
var GraphQLObjectType = require('graphql').GraphQLObjectType;
var GraphQLNonNull = require('graphql').GraphQLNonNull;
var GraphQLID = require('graphql').GraphQLID;
var GraphQLString = require('graphql').GraphQLString;
var GraphQLInt = require('graphql').GraphQLInt;
var GraphQLDate = require('graphql-date');
var BookModel = require('../models').Book;

Create a GraphQL Object Type for Book models.

var bookType = new GraphQLObjectType({
  name: "book",
  fields: function() {
    return {
      id: {
        type: GraphQLInt
      },
      isbn: {
        type: GraphQLString
      },
      title: {
        type: GraphQLString
      },
      author: {
        type: GraphQLString
      },
      description: {
        type: GraphQLString
      },
      publishedYear: {
        type: GraphQLInt
      },
      publisher: {
        type: GraphQLString
      },
      createdAt: {
        type: GraphQLDate
      },
      updatedAt: {
        type: GraphQLDate
      }
    };
  }
});

Next, create a GraphQL query type that calls a list of book and single book by ID.

var queryType = new GraphQLObjectType({
  name: 'Query',
  fields: function () {
    return {
      books: {
        type: new GraphQLList(bookType),
        resolve: function () {
          const books = BookModel.findAll({
            order: [
              ['createdAt', 'DESC']
            ],
          })
          if (!books) {
            throw new Error('Error')
          }
          return books
        }
      },
      book: {
        type: bookType,
        args: {
          id: {
            name: 'id',
            type: GraphQLString
          }
        },
        resolve: function (root, params) {
          const bookDetails = BookModel.findByPk(params.id).exec()
          if (!bookDetails) {
            throw new Error('Error')
          }
          return bookDetails
        }
      }
    }
  }
});

Finally, exports this file as GraphQL schema by adding this line at the end of the file.

module.exports = new GraphQLSchema({query: queryType});

6. Add Mutation for CRUD Operation to the Schema

For completing CRUD (Create, Read, Update, Delete) operation of the GraphQL, we need to add a mutation that contains create, update and delete operations. Open and edit graphql/bookSchemas.js then add this mutation as GraphQL Object Type.

var mutation = new GraphQLObjectType({
  name: 'Mutation',
  fields: function () {
    return {
      addBook: {
        type: bookType,
        args: {
          isbn: {
            type: new GraphQLNonNull(GraphQLString)
          },
          title: {
            type: new GraphQLNonNull(GraphQLString)
          },
          author: {
            type: new GraphQLNonNull(GraphQLString)
          },
          description: {
            type: new GraphQLNonNull(GraphQLString)
          },
          publishedYear: {
            type: new GraphQLNonNull(GraphQLInt)
          },
          publisher: {
            type: new GraphQLNonNull(GraphQLString)
          }
        },
        resolve: function (root, params) {
          const bookModel = new BookModel(params);
          const newBook = bookModel.save();
          if (!newBook) {
            throw new Error('Error');
          }
          return newBook
        }
      },
      updateBook: {
        type: bookType,
        args: {
          id: {
            name: 'id',
            type: new GraphQLNonNull(GraphQLInt)
          },
          isbn: {
            type: new GraphQLNonNull(GraphQLString)
          },
          title: {
            type: new GraphQLNonNull(GraphQLString)
          },
          author: {
            type: new GraphQLNonNull(GraphQLString)
          },
          description: {
            type: new GraphQLNonNull(GraphQLString)
          },
          publishedYear: {
            type: new GraphQLNonNull(GraphQLInt)
          },
          publisher: {
            type: new GraphQLNonNull(GraphQLString)
          }
        },
        resolve(root, params) {
          return BookModel
          .findByPk(params.id)
          .then(book => {
            if (!book) {
              throw new Error('Not found');
            }
            return book
              .update({
                isbn: params.isbn || book.isbn,
                title: params.title || book.title,
                author: params.author || book.author,
                description: params.description || book.description,
                publishedYear: params.publishedYear || book.publishedYear,
                publisher: params.publisher || book.publisher,
              })
              .then(() => { return book; })
              .catch((error) => { throw new Error(error); });
          })
          .catch((error) => { throw new Error(error); });
        }
      },
      removeBook: {
        type: bookType,
        args: {
          id: {
            type: new GraphQLNonNull(GraphQLInt)
          }
        },
        resolve(root, params) {
          return BookModel
          .findByPk(params.id)
          .then(book => {
            if (!book) {
              throw new Error('Not found');
            }
            return book
              .destroy()
              .then(() => { return book; })
              .catch((error) => { throw new Error(error); });
          })
          .catch((error) => { throw new Error(error); });
        }
      }
    }
  }
});

Finally, add this mutation to the GraphQL Schema exports like below.

module.exports = new GraphQLSchema({query: queryType, mutation: mutation});

7. Test GraphQL using GraphiQL

To test the queries and mutations of CRUD operations, re-run again the Express.js app then open the browser. Go to this address [http://localhost:3000/graphql](http://localhost:3000/graphql "http://localhost:3000/graphql") to open the GraphiQL User Interface.

To get the list of books, replace all of the text on the left pane with this GraphQL query then click the Play button.

To get a single book by ID, use this GraphQL query.

{
  book(id: 1) {
    id
    isbn
    title
    author
    description
    publishedYear
    publisher
    updatedAt
  }
}

To add a book, use this GraphQL mutation.

mutation {
  addBook(
    isbn: "12345678",
    title: "Whatever this Book Title",
    author: "Mr. Bean",
    description: "The short explanation of this Book",
    publisher: "Djamware Press",
    publishedYear: 2019
  ) {
    updatedAt
  }
}

You will the response at the right pane like this.

{
  "data": {
    "addBook": {
      "updatedAt": "2019-02-26T13:55:39.160Z"
    }
  }
}

To update a book, use this GraphQL mutation.

mutation {
  updateBook(
    id: 1,
    isbn: "12345678221",
    title: "The Learning Curve of GraphQL",
    author: "Didin J.",
    description: "The short explanation of this Book",
    publisher: "Djamware Press",
    publishedYear: 2019
  ) {
    id,
    updatedAt
  }
}

You will see the response in the right pane like this.

{
  "data": {
    "updateBook": {
      "id": 1,
      "updated_date": "2019-02-26T13:58:35.811Z"
    }
  }
}

To delete a book by ID, use this GraphQL mutation.

mutation {
  removeBook(id: 1) {
    id
  }
}

You will see the response in the right pane like this.

{
  "data": {
    "removeBook": {
      "id": 1
    }
  }
}

8. Create Vue 2 Application

To install Vue-CLI type this command from the Terminal or Node command line.

sudo npm install -g @vue/cli

or

yarn global add @vue/cli

Next, check the version to make sure that you have the 3.x version of Vue-CLI.

vue --version
3.7.0

Next, create a new Vue.js project by type this command.

vue create client

For now, use the default for every question that shows up in the Terminal. Next, go to the newly created folder.

cd ./client

To make sure that created Vue.js project working, type this command to run the Vue.js application.

npm run serve

or

yarn serve

You will see this page when open [http://localhost:8080/](http://localhost:8080/ "http://localhost:8080/") in the browser.

9. Install/Configure the Required Modules, Dependencies, and Router

Now, we have to install and configure all of the required modules and dependencies. Type this command to install the modules.

npm install apollo-boost vue-apollo graphql-tag graphql vue-router --save

Next, open and edit src/main.js then add these imports.

import ApolloClient from "apollo-boost";
import VueApollo from "vue-apollo";

Add these constant variables then register VueApollo in Vue 2 app.

const apolloClient = new ApolloClient({
  uri: 'http://localhost:3000/graphql'
});

const apolloProvider = new VueApollo({
  defaultClient: apolloClient
});

Vue.use(VueApollo);

new Vue({
  apolloProvider,
  render: h => h(App)
}).$mount('#app')

To register or create routes for the whole application navigation, create a router folder and index.js file.

mkdir src/router
touch src/router/index.js

Open and edit src/router/index.js then add these imports.

import VueRouter from 'vue-router'
import BookList from '@/components/BookList'
import ShowBook from '@/components/ShowBook'
import AddBook from '@/components/AddBook'
import EditBook from '@/components/EditBook'

Add the router to each component or page.

export default new VueRouter({
  routes: [
    {
      path: '/',
      name: 'BookList',
      component: BookList
    },
    {
      path: '/show-book/:id',
      name: 'ShowBook',
      component: ShowBook
    },
    {
      path: '/add-book',
      name: 'AddBook',
      component: AddBook
    },
    {
      path: '/edit-book/:id',
      name: 'EditBook',
      component: EditBook
    }
  ]
})

Add Vue files for above-registered components or pages.

touch src/components/BookList.vue
touch src/components/ShowBook.vue
touch src/components/AddBook.vue
touch src/components/EditBook.vue

Finally, add or register this router file to src/main.js by adding these imports.

import VueRouter from 'vue-router'
import router from './router'

Register the Vue-Router after Vue.config.

Vue.use(VueRouter)

Modify new Vue to be like this.

new Vue({
  apolloProvider,
  router,
  render: h => h(App)
}).$mount('#app')

10. Create a Component to Display List of Books

Before create or show data to the views, we have to add Bootstrap-Vue. Type this command to install the module.

npm i bootstrap-vue

Next, open and edit src/main.js then add these imports.

import BootstrapVue from 'bootstrap-vue'
import 'bootstrap/dist/css/bootstrap.css'
import 'bootstrap-vue/dist/bootstrap-vue.css'

Add this line after Vue.config.

Vue.use(BootstrapVue);

Now, open and edit src/components/BookList.vue then add this template tags that contain a bootstrap-vue table.

<template>
  <b-row>
    <b-col cols="12">
      <h2>
        Book List
        <b-link href="#/add-Book">(Add Book)</b-link>
      </h2>
      <b-table striped hover :items="books" :fields="fields">
        <template slot="actions" scope="row">
          <b-btn size="sm" @click.stop="details(row.item)">Details</b-btn>
        </template>
      </b-table>
    </b-col>
  </b-row>
</template>

Next, add the script tag for hold all Vue 2 codes.

<script></script>

Inside the script tag, add these imports.

import gql from "graphql-tag";
import router from "../router";

Declare the constant variables for GraphQL query.

const GET_BOOKS = gql`
  {
    books {
      id
      title
      author
    }
  }
`;

Add the main Vue 2 export that contains Vue-Apollo calls that filled Vue 2 data.

export default {
  name: "BookList",
  apollo: {
    books: {
      query: GET_BOOKS,
      pollInterval: 300
    }
  },
  data() {
    return {
      fields: {
        title: { label: "Title", sortable: true, class: "text-left" },
        author: { label: "Author", sortable: true, class: "text-left" },
        actions: { label: "Action", class: "text-center" }
      },
      books: []
    };
  },
  methods: {
    details(book) {
      router.push({ name: "ShowBook", params: { id: book.id } });
    }
  }
};

Finally, add the style tag for styling the template.

<style>
.table {
  width: 96%;
  margin: 0 auto;
}
</style>

11. Create a Component to Show and Delete Books

To show the book details that contains all book detail, edit and delete buttons, open and edit src/components/ShowBook.vue then add these template tags that contain a bootstrap-vue component for display the details.

<template>
  <b-row>
    <b-col cols="12">
      <h2>
        Book List
        <b-link href="#/">(Book List)</b-link>
      </h2>
      <b-jumbotron>
        <template slot="header">{{book.title}}</template>
        <template slot="lead">
          ISBN: {{book.isbn}}
          <br>
          Author: {{book.author}}
          <br>
          Description: {{book.description}}
          <br>
          Published Year: {{book.publishedYear}}
          <br>
          Publisher: {{book.publisher}}
          <br>
          Update At: {{book.updatedAt}}
          <br>
        </template>
        <hr class="my-4">
        <b-btn class="edit-btn" variant="success" @click.stop="editBook(book.id)">Edit</b-btn>
        <b-btn variant="danger" @click.stop="deleteBook(book.id)">Delete</b-btn>
      </b-jumbotron>
    </b-col>
  </b-row>
</template>

Next, add the script tag.

<script></script>

Inside the script tag, add these imports.

import gql from "graphql-tag";
import router from "../router";

Declare the constant variables that handle get a single book and delete book queries.

const GET_BOOK = gql`
  query book($bookId: Int) {
    book(id: $bookId) {
      id
      isbn
      title
      author
      description
      publishedYear
      publisher
      updatedAt
    }
  }
`;

const DELETE_BOOK = gql`
  mutation removeBook($id: Int!) {
    removeBook(id: $id) {
      id
    }
  }
`;

Inside main Vue export, add all required functions, variables, and Vue-Apollo function.

export default {
  name: "ShowBook",
  data() {
    return {
      book: '',
      bookId: parseInt(this.$route.params.id)
    };
  },
  apollo: {
    book: {
      query: GET_BOOK,
      pollInterval: 300,
      variables() {
        return {
          bookId: this.bookId
        };
      }
    }
  },
  methods: {
    editBook(id) {
      router.push({
        name: "EditBook",
        params: { id: id }
      });
    },
    deleteBook(id) {
      this.$apollo
        .mutate({
          mutation: DELETE_BOOK,
          variables: {
            id: id
          }
        })
        .then(data => {
          console.log(data);
        })
        .catch(error => {
          console.error(error);
        });
    }
  }
};

Finally, add the style tags to give the view some styles.

<style>
.jumbotron {
  padding: 2rem;
}
.edit-btn {
  margin-right: 20px;
  width: 70px;
}
</style>

12. Create a Component to Add a New Book

To add a new book, open and edit src/components/AddBook.vue then add this Vue 2 template tag that contains a bootstrap-vue form.

<template>
  <b-row>
    <b-col cols="12">
      <h2>
        Add Book
        <b-link href="#/">(Book List)</b-link>
      </h2>
      <b-jumbotron>
        <b-form @submit="onSubmit">
          <b-form-group
            id="fieldsetHorizontal"
            horizontal
            :label-cols="4"
            breakpoint="md"
            label="Enter ISBN"
          >
            <b-form-input id="isbn" v-model.trim="book.isbn"></b-form-input>
          </b-form-group>
          <b-form-group
            id="fieldsetHorizontal"
            horizontal
            :label-cols="4"
            breakpoint="md"
            label="Enter Title"
          >
            <b-form-input id="title" v-model.trim="book.title"></b-form-input>
          </b-form-group>
          <b-form-group
            id="fieldsetHorizontal"
            horizontal
            :label-cols="4"
            breakpoint="md"
            label="Enter Author"
          >
            <b-form-input id="author" v-model.trim="book.author"></b-form-input>
          </b-form-group>
          <b-form-group
            id="fieldsetHorizontal"
            horizontal
            :label-cols="4"
            breakpoint="md"
            label="Enter Description"
          >
            <b-form-textarea
              id="description"
              v-model="book.description"
              placeholder="Enter something"
              :rows="2"
              :max-rows="6"
            >{{book.description}}</b-form-textarea>
          </b-form-group>
          <b-form-group
            id="fieldsetHorizontal"
            horizontal
            :label-cols="4"
            breakpoint="md"
            label="Enter Publisher"
          >
            <b-form-input id="publisher" v-model.trim="book.publisher"></b-form-input>
          </b-form-group>
          <b-form-group
            id="fieldsetHorizontal"
            horizontal
            :label-cols="4"
            breakpoint="md"
            label="Enter Published Year"
          >
            <b-form-input type="number" id="publishedYear" v-model.trim="book.publishedYear"></b-form-input>
          </b-form-group>
          <b-button type="submit" variant="primary">Save</b-button>
        </b-form>
      </b-jumbotron>
    </b-col>
  </b-row>
</template>

Next, add the script tag.

<script></script>

Inside the script, tag adds Vue 2 codes that contain Vue-Apollo GraphQL mutation to save a new book.

import gql from "graphql-tag";
import router from "../router";

const ADD_BOOK = gql`
  mutation AddBook(
    $isbn: String!
    $title: String!
    $author: String!
    $description: String!
    $publisher: String!
    $publishedYear: Int!
  ) {
    addBook(
      isbn: $isbn
      title: $title
      author: $author
      description: $description
      publisher: $publisher
      publishedYear: $publishedYear
    ) {
      id
    }
  }
`;

export default {
  name: "AddBook",
  data() {
    return {
      book: {}
    };
  },
  methods: {
    onSubmit(evt) {
      evt.preventDefault();

      this.$apollo
        .mutate({
          mutation: ADD_BOOK,
          variables: {
            isbn: this.book.isbn,
            title: this.book.title,
            author: this.book.author,
            description: this.book.description,
            publisher: this.book.publisher,
            publishedYear: parseInt(this.book.publishedYear)
          }
        })
        .then(data => {
          console.log(data);
          router.push({ name: "BookList" });
        })
        .catch(error => {
          console.error(error);
        });
    }
  }
};

Finally, give the view a style by adding the style tag.

<style>
.jumbotron {
  padding: 2rem;
}
</style>

13. Create a Component to Edit a Book

To edit a book after getting single book data, open and edit src/components/EditBook.vue then add this Vue 2 template that contains a bootstrap-vue form.

<template>
  <b-row>
    <b-col cols="12">
      <h2>
        Edit Book
        <router-link :to="{ name: 'ShowBook', params: { id: bookId } }">(Show Book)</router-link>
      </h2>
      <b-jumbotron>
        <b-form @submit="onSubmit">
          <b-form-group
            id="fieldsetHorizontal"
            horizontal
            :label-cols="4"
            breakpoint="md"
            label="Enter ISBN"
          >
            <b-form-input id="isbn" v-model.trim="book.isbn"></b-form-input>
          </b-form-group>
          <b-form-group
            id="fieldsetHorizontal"
            horizontal
            :label-cols="4"
            breakpoint="md"
            label="Enter Title"
          >
            <b-form-input id="title" v-model.trim="book.title"></b-form-input>
          </b-form-group>
          <b-form-group
            id="fieldsetHorizontal"
            horizontal
            :label-cols="4"
            breakpoint="md"
            label="Enter Author"
          >
            <b-form-input id="author" v-model.trim="book.author"></b-form-input>
          </b-form-group>
          <b-form-group
            id="fieldsetHorizontal"
            horizontal
            :label-cols="4"
            breakpoint="md"
            label="Enter Description"
          >
            <b-form-textarea
              id="description"
              v-model="book.description"
              placeholder="Enter something"
              :rows="2"
              :max-rows="6"
            >{{book.description}}</b-form-textarea>
          </b-form-group>
          <b-form-group
            id="fieldsetHorizontal"
            horizontal
            :label-cols="4"
            breakpoint="md"
            label="Enter Publisher"
          >
            <b-form-input id="publisher" v-model.trim="book.publisher"></b-form-input>
          </b-form-group>
          <b-form-group
            id="fieldsetHorizontal"
            horizontal
            :label-cols="4"
            breakpoint="md"
            label="Enter Published Year"
          >
            <b-form-input type="number" id="publishedYear" v-model.trim="book.publishedYear"></b-form-input>
          </b-form-group>
          <b-button type="submit" variant="primary">Update</b-button>
        </b-form>
      </b-jumbotron>
    </b-col>
  </b-row>
</template>

Next, add the script tag that contains all required Vue 2 codes with get data and update function.

<script>
import gql from "graphql-tag";
import router from "../router";

const GET_BOOK = gql`
  query book($bookId: Int) {
    book(id: $bookId) {
      id
      isbn
      title
      author
      description
      publishedYear
      publisher
    }
  }
`;

const UPDATE_BOOK = gql`
  mutation updateBook(
    $id: Int!
    $isbn: String!
    $title: String!
    $author: String!
    $description: String!
    $publisher: String!
    $publishedYear: Int!
  ) {
    updateBook(
      id: $id
      isbn: $isbn
      title: $title
      author: $author
      description: $description
      publisher: $publisher
      publishedYear: $publishedYear
    ) {
      updatedAt
    }
  }
`;

export default {
  name: "EditBook",
  data() {
    return {
      bookId: this.$route.params.id,
      book: {}
    };
  },
  apollo: {
    book: {
      query: GET_BOOK,
      variables() {
        return {
          bookId: this.bookId
        };
      }
    }
  },
  methods: {
    onSubmit(evt) {
      evt.preventDefault();

      this.$apollo
        .mutate({
          mutation: UPDATE_BOOK,
          variables: {
            id: parseInt(this.book.id),
            isbn: this.book.isbn,
            title: this.book.title,
            author: this.book.author,
            description: this.book.description,
            publisher: this.book.publisher,
            publishedYear: parseInt(this.book.publishedYear)
          }
        })
        .then(data => {
          console.log(data);
          router.push({
            name: "ShowBook",
            params: { id: this.$route.params.id }
          });
        })
        .catch(error => {
          console.error(error);
        });
    }
  }
};
</script>

Finally, give the view some style by adding the style tag.

<style>
.jumbotron {
  padding: 2rem;
}
</style>

14. Run and Test GraphQL CRUD from the Vue 2 Application

We assume the PostgreSQL server already running, so you just can run Node/Express.js application and Vue 2 app in the separate terminal tabs.

nodemon
cd client
npm run serve

Next, open the browser then go to this address localhost:8080 and you should see these pages.

That it's, the Node, Express, PostgreSQL, Vue 2 and Graphql CRUD Web App. You can find the full source code on our GitHub.