Node.js, Express.js, Sequelize.js and PostgreSQL RESTful API

by Didin J. on jul. 24, 2018 Node.js, Express.js, Sequelize.js and PostgreSQL RESTful API

Step by step tutorial on building REST API using Node.js, Express.js, Sequelize.js and PostgreSQL.

A comprehensive step by step tutorial on building REST API using Node.js, Express.js, Sequelize.js, and PostgreSQL. In this tutorial, we will show how to create a little complex table association or relationship with CRUD (Create, Read, Update, Delete) operations.

Shortcut to the steps:

Using Node.js, Express.js, Sequelize.js, and PostgreSQL is one of the good solutions that combine Enterprise-class RDBMS and non-blocking application server

So, the association or relationship will be like this diagram.

Node.js, Express.js, Sequelize.js and PostgreSQL RESTful API - Relational Database Diagram

Above diagrams describe:

  • One classroom has many students and a student has one classroom
  • Many students take many courses and vice-versa
  • One course has one lecturer and one lecturer teach one course

That the simple explanation for what we will do in the steps of this tutorial.

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

  1. Node.js
  2. PostgreSQL Server
  3. Express.js
  4. Sequelize.js
  5. Terminal or Command Line
  6. Text Editor or IDE

We assume that you have installed the 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 on your machine and can run `node`, `npm`, or `yarn` command in your terminal or command line. Next, check their version by type these commands in your terminal or command line.

node -v
v8.11.1
npm -v
6.1.0
yarn -v
1.7.0

That the versions that we are uses. You can watch the video tutorial on our YouTube channel. Let's continue with the main steps.


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 node-sequelize --view=ejs

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

cd node-sequelize && npm install

You should see the folder structure like this.

Node.js, Express.js, Sequelize.js and PostgreSQL RESTful API - Project Structure

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


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 the 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 that initializes the Sequelize configuration.

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": "dj@mw@r3",
    "database": "node_sequelize",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "test": {
    "username": "root",
    "password": "dj@mw@r3",
    "database": "node_sequelize",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "production": {
    "username": "root",
    "password": "dj@mw@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 a password then give access for creating the database.

postgres-# CREATE ROLE djamware WITH LOGIN PASSWORD 'dj@mw@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 node_sequelize;

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

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


Create or Generate Sequelize Models and Migrations

We will use Sequelize-CLI to generate a new Sequelize model. Type this command to create a model for `Classroom`, `Student`, `Lecturer`, `Course`, and `StudentCourse`.

sequelize model:create --name Classroom --attributes class_name:string
sequelize model:create --name Student --attributes classroom_id:integer,student_name:string
sequelize model:create --name Lecturer --attributes lecturer_name:string
sequelize model:create --name Course --attributes lecturer_id:integer,course_name:string
sequelize model:create --name StudentCourse --attributes student_id:integer,course_id:integer

That command creates a model file to the model's folder and a migration file to folder migrations. Next, modify `models/classroom.js` then add association with `Student` model inside `associate` function.

  class Classroom extends Model {
    static associate(models) {
      Classroom.hasMany(models.Student, {
        foreignKey: 'classroom_id',
        as: 'students',
      });
    }
  };

Next, modify `models/student.js` then add association with `Classroom` and `Coursemodel` models inside `associate` function.

  class Student extends Model {
    static associate(models) {
      Student.belongsTo(models.Classroom, {
        foreignKey: 'classroom_id',
        as: 'classroom'
      });
      Student.belongsToMany(models.Course, {
        through: 'StudentCourse',
        as: 'courses',
        foreignKey: 'student_id'
      });
    }
  };

Next, modify `models/lecturer.js` then add the association with the `Course` model inside the `associate` function.

  class Lecturer extends Model {
    static associate(models) {
      Lecturer.hasOne(models.Course, {
        foreignKey: 'lecturer_id',
        as: 'course',
      });
    }
  };

Next, modify `models/course.js` then add association with `Student` and `Lecturer` models inside `associate` function.

  class Course extends Model {
    static associate(models) {
      Course.belongsToMany(models.Student, {
        through: 'StudentCourse',
        as: 'students',
        foreignKey: 'course_id'
      });
      Course.belongsTo(models.Lecturer, {
        foreignKey: 'lecturer_id',
        as: 'lecturer'
      });
    }
  };

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 using Sequelize.

sequelize db:migrate


Create Express Controller and Router for Classroom Model

To create the controller, first, create a folder for controllers and a new Javascript file by type these commands.

mkdir controllers
touch controllers/classroom.js

Open and edit `controllers/classroom.js` then add these lines of codes.

const Classroom = require('../models').Classroom;
const Student = require('../models').Student;

module.exports = {
  list(req, res) {
    return Classroom
      .findAll({
        include: [{
          model: Student,
          as: 'students'
        }],
        order: [
          ['createdAt', 'DESC'],
          [{ model: Student, as: 'students' }, 'createdAt', 'DESC'],
        ],
      })
      .then((classrooms) => res.status(200).send(classrooms))
      .catch((error) => { res.status(400).send(error); });
  },

  getById(req, res) {
    return Classroom
      .findByPk(req.params.id, {
        include: [{
          model: Student,
          as: 'students'
        }],
      })
      .then((classroom) => {
        if (!classroom) {
          return res.status(404).send({
            message: 'Classroom Not Found',
          });
        }
        return res.status(200).send(classroom);
      })
      .catch((error) => {
        console.log(error);
        res.status(400).send(error);
      });
  },

  add(req, res) {
    return Classroom
      .create({
        class_name: req.body.class_name,
      })
      .then((classroom) => res.status(201).send(classroom))
      .catch((error) => res.status(400).send(error));
  },

  update(req, res) {
    return Classroom
      .findByPk(req.params.id, {
        include: [{
          model: Student,
          as: 'students'
        }],
      })
      .then(classroom => {
        if (!classroom) {
          return res.status(404).send({
            message: 'Classroom Not Found',
          });
        }
        return classroom
          .update({
            class_name: req.body.class_name || classroom.class_name,
          })
          .then(() => res.status(200).send(classroom))
          .catch((error) => res.status(400).send(error));
      })
      .catch((error) => res.status(400).send(error));
  },

  delete(req, res) {
    return Classroom
      .findByPk(req.params.id)
      .then(classroom => {
        if (!classroom) {
          return res.status(400).send({
            message: 'Classroom Not Found',
          });
        }
        return classroom
          .destroy()
          .then(() => res.status(204).send())
          .catch((error) => res.status(400).send(error));
      })
      .catch((error) => res.status(400).send(error));
  },
};

In that controller, we have all CRUD (Create, Read, Update, and Delete) functions. To make this controller available via the controller's folder, add these files for declaring this controller file and other controllers files.

touch controllers/index.js

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

const classroom = require('./classroom');

module.exports = {
  classroom,
};

For the router, we will use the existing router that generated by Express Generator. Open and edit `routes/index.js` then declare the Classroom controller after other variables.

const classroomController = require('../controllers').classroom;

Add these routes after the existing route for the Classroom controller.

router.get('/api/classroom', classroomController.list);
router.get('/api/classroom/:id', classroomController.getById);
router.post('/api/classroom', classroomController.add);
router.put('/api/classroom/:id', classroomController.update);
router.delete('/api/classroom/:id', classroomController.delete);


Create Express Controller and Router for Student Model

Type this command to create a controller and router file for a Student model.

touch controllers/student.js

Open and edit `controllers/student.js` then add these lines of codes that contain full CRUD function for the Student model.

const Student = require('../models').Student;
const Classroom = require('../models').Classroom;
const Course = require('../models').Course;

module.exports = {
  list(req, res) {
    return Student
      .findAll({
        include: [{
          model: Classroom,
          as: 'classroom'
        },{
          model: Course,
          as: 'courses'
        }],
        order: [
          ['createdAt', 'DESC'],
          [{ model: Course, as: 'courses' }, 'createdAt', 'DESC'],
        ],
      })
      .then((students) => res.status(200).send(students))
      .catch((error) => { res.status(400).send(error); });
  },

  getById(req, res) {
    return Student
      .findByPk(req.params.id, {
        include: [{
          model: Classroom,
          as: 'classroom'
        },{
          model: Course,
          as: 'courses'
        }],
      })
      .then((student) => {
        if (!student) {
          return res.status(404).send({
            message: 'Student Not Found',
          });
        }
        return res.status(200).send(student);
      })
      .catch((error) => res.status(400).send(error));
  },

  add(req, res) {
    return Student
      .create({
        classroom_id: req.body.classroom_id,
        student_name: req.body.student_name,
      })
      .then((student) => res.status(201).send(student))
      .catch((error) => res.status(400).send(error));
  },

  update(req, res) {
    return Student
      .findByPk(req.params.id, {
        include: [{
          model: Classroom,
          as: 'classroom'
        },{
          model: Course,
          as: 'courses'
        }],
      })
      .then(student => {
        if (!student) {
          return res.status(404).send({
            message: 'Student Not Found',
          });
        }
        return student
          .update({
            student_name: req.body.student_name || student.student_name,
          })
          .then(() => res.status(200).send(student))
          .catch((error) => res.status(400).send(error));
      })
      .catch((error) => res.status(400).send(error));
  },

  delete(req, res) {
    return Student
      .findByPk(req.params.id)
      .then(student => {
        if (!student) {
          return res.status(400).send({
            message: 'Student Not Found',
          });
        }
        return student
          .destroy()
          .then(() => res.status(204).send())
          .catch((error) => res.status(400).send(error));
      })
      .catch((error) => res.status(400).send(error));
  },
};

Next, open and edit `controllers/index.js` then register the Student controller in that file.

const classroom = require('./classroom');
const student = require('./student');

module.exports = {
  classroom,
  student,
};

Next, open and edit `routes/index.js` then add a required variable for the student controller.

const studentController = require('../controllers').student;

Add the routes for all CRUD functions of the student controller.

router.get('/api/student', studentController.list);
router.get('/api/student/:id', studentController.getById);
router.post('/api/student', studentController.add);
router.put('/api/student/:id', studentController.update);
router.delete('/api/student/:id', studentController.delete);


Create Express Controller and Router for Lecturer Model

Type this command to create a controller and router file for the Lecturer model.

touch controllers/lecturer.js

Open and edit `controllers/lecturer.js` then add these lines of codes that contain full CRUD function for the Lecturer model.

const Lecturer = require('../models').Lecturer;
const Course = require('../models').Course;

module.exports = {
  list(req, res) {
    return Lecturer
      .findAll({
        include: [{
          model: Course,
          as: 'course'
        }],
        order: [
          ['createdAt', 'DESC'],
          [{ model: Course, as: 'course' }, 'createdAt', 'DESC'],
        ],
      })
      .then((lecturers) => res.status(200).send(lecturers))
      .catch((error) => { res.status(400).send(error); });
  },

  getById(req, res) {
    return Lecturer
      .findByPk(req.params.id, {
        include: [{
          model: Course,
          as: 'course'
        }],
      })
      .then((lecturer) => {
        if (!lecturer) {
          return res.status(404).send({
            message: 'Lecturer Not Found',
          });
        }
        return res.status(200).send(lecturer);
      })
      .catch((error) => res.status(400).send(error));
  },

  add(req, res) {
    return Lecturer
      .create({
        lecturer_name: req.body.lecturer_name,
      })
      .then((lecturer) => res.status(201).send(lecturer))
      .catch((error) => res.status(400).send(error));
  },

  update(req, res) {
    return Lecturer
      .findByPk(req.params.id, {
        include: [{
          model: Course,
          as: 'course'
        }],
      })
      .then(lecturer => {
        if (!lecturer) {
          return res.status(404).send({
            message: 'Lecturer Not Found',
          });
        }
        return lecturer
          .update({
            lecturer_name: req.body.lecturer_name || classroom.lecturer_name,
          })
          .then(() => res.status(200).send(lecturer))
          .catch((error) => res.status(400).send(error));
      })
      .catch((error) => res.status(400).send(error));
  },

  delete(req, res) {
    return Lecturer
      .findByPk(req.params.id)
      .then(lecturer => {
        if (!lecturer) {
          return res.status(400).send({
            message: 'Lecturer Not Found',
          });
        }
        return lecturer
          .destroy()
          .then(() => res.status(204).send())
          .catch((error) => res.status(400).send(error));
      })
      .catch((error) => res.status(400).send(error));
  },
};

Next, open and edit `controllers/index.js` then register the Lecturer controller in that file.

const classroom = require('./classroom');
const student = require('./student');
const lecturer = require('./lecturer');

module.exports = {
  classroom,
  student,
  lecturer,
};

Next, open and edit `routes/index.js` then add a required variable for the lecturer controller.

const lecturerController = require('../controllers').lecturer;

Add the routes for all CRUD functions of the lecturer controller.

router.get('/api/lecturer', lecturerController.list);
router.get('/api/lecturer/:id', lecturerController.getById);
router.post('/api/lecturer', lecturerController.add);
router.put('/api/lecturer/:id', lecturerController.update);
router.delete('/api/lecturer/:id', lecturerController.delete);


Create Express Controller and Router for Course Model

Type this command to create a controller and router file for the Course model.

touch controllers/course.js

Open and edit `controllers/course.js` then add these lines of codes that contain full CRUD function for the Course model.

const Course = require('../models').Course;
const Student = require('../models').Student;
const Lecturer = require('../models').Lecturer;

module.exports = {
  list(req, res) {
    return Course
      .findAll({
        include: [{
          model: Student,
          as: 'students'
        },{
          model: Lecturer,
          as: 'lecturer'
        }],
        order: [
          ['createdAt', 'DESC'],
          [{ model: Student, as: 'students' }, 'createdAt', 'DESC'],
        ],
      })
      .then((courses) => res.status(200).send(courses))
      .catch((error) => { res.status(400).send(error); });
  },

  getById(req, res) {
    return Course
      .findByPk(req.params.id, {
        include: [{
          model: Course,
          as: 'course'
        }],
      })
      .then((course) => {
        if (!course) {
          return res.status(404).send({
            message: 'Course Not Found',
          });
        }
        return res.status(200).send(course);
      })
      .catch((error) => res.status(400).send(error));
  },

  add(req, res) {
    return Course
      .create({
        course_name: req.body.course_name,
      })
      .then((course) => res.status(201).send(course))
      .catch((error) => res.status(400).send(error));
  },

  update(req, res) {
    return Course
      .findByPk(req.params.id, {
        include: [{
          model: Course,
          as: 'course'
        }],
      })
      .then(course => {
        if (!course) {
          return res.status(404).send({
            message: 'Course Not Found',
          });
        }
        return course
          .update({
            course_name: req.body.course_name || classroom.course_name,
          })
          .then(() => res.status(200).send(course))
          .catch((error) => res.status(400).send(error));
      })
      .catch((error) => res.status(400).send(error));
  },

  delete(req, res) {
    return Course
      .findByPk(req.params.id)
      .then(course => {
        if (!course) {
          return res.status(400).send({
            message: 'Course Not Found',
          });
        }
        return course
          .destroy()
          .then(() => res.status(204).send())
          .catch((error) => res.status(400).send(error));
      })
      .catch((error) => res.status(400).send(error));
  },
};

Next, open and edit `controllers/index.js` then register the Course controller in that file.

const classroom = require('./classroom');
const student = require('./student');
const lecturer = require('./lecturer');
const course = require('./course');

module.exports = {
  classroom,
  student,
  lecturer,
  course,
};

Next, open and edit `routes/index.js` then add a required variable for the course controller.

const courseController = require('../controllers').course;

Add the routes for all CRUD functions of the course controller.

router.get('/api/course', courseController.list);
router.get('/api/course/:id', courseController.getById);
router.post('/api/course', courseController.add);
router.put('/api/course/:id', courseController.update);
router.delete('/api/course/:id', courseController.delete);


Advance Express Route and Function for Association

Now, we have to make the association more useful. To make a Classroom include the students, add this function to `controllers/classroom.js`.

  addWithStudents(req, res) {
    return Classroom
      .create({
        class_name: req.body.class_name,
        students: req.body.students,
      }, {
      	include: [{
          model: Student,
          as: 'students'
        }]
      })
      .then((classroom) => res.status(201).send(classroom))
      .catch((error) => res.status(400).send(error));
  },

Next, add this new function to the route file `routes/index.js`.

router.post('/api/classroom/add_with_students', classroomController.addWithStudents);

To add a lecturer include a course, add this function to `controllers/lecturer.js`.

  addWithCourse(req, res) {
    return Lecturer
      .create({
        lecturer_name: req.body.lecturer_name,
        course: req.body.course
      }, {
        include: [{
          model: Course,
          as: 'course'
        }]
      })
      .then((lecturer) => res.status(201).send(lecturer))
      .catch((error) => res.status(400).send(error));
  },

Next, add this new function to the route file `routes/index.js`.

router.post('/api/lecturer/add_with_course', lecturerController.addWithCourse);

To add a course for a student, add this function to `controllers/student.js`.

  addCourse(req, res) {
    return Student
      .findByPk(req.body.student_id, {
        include: [{
          model: Classroom,
          as: 'classroom'
        },{
          model: Course,
          as: 'courses'
        }],
      })
      .then((student) => {
        if (!student) {
          return res.status(404).send({
            message: 'Student Not Found',
          });
        }
        Course.findByPk(req.body.course_id).then((course) => {
          if (!course) {
            return res.status(404).send({
              message: 'Course Not Found',
            });
          }
          student.addCourse(course);
          return res.status(200).send(student);
        })
      })
      .catch((error) => res.status(400).send(error));
  },

Next, add this new function to the route file `routes/index.js`.

router.post('/api/student/add_course', studentController.addCourse);

That's a few of the Association features that might be useful for your project. We will add another useful function to this article later.


Run and Test The Node, Express, Sequelize, and PostgreSQL REST API

Type this command to run the application.

nodemon

Open the new terminal tab or command line tab then type this command for save or persist classroom data include with students.

curl -i -X POST -H "Content-Type: application/json" -d '{ "class_name":"Class A","students": [{ "student_name":"John Doe" },{ "student_name":"Jane Doe" },{ "student_name":"Doe Doel" }] }' localhost:3000/api/classroom/add_with_students

To see data persist to PostgreSQL table, open a new terminal tab then run `psql`.

psql postgres -U djamware

Connect to the database then running the queries.

postgres=> \c node_sequelize
node_sequelize=> SELECT * FROM public."Classrooms";

 id | class_name |         createdAt          |         updatedAt
----+------------+----------------------------+----------------------------
  2 | Class A    | 2018-07-24 09:18:30.062+07 | 2018-07-24 09:18:30.062+07
(1 row)

node_sequelize=> SELECT * FROM public."Students" WHERE classroom_id=2;

id | classroom_id | student_name |         createdAt          |         updatedAt
----+--------------+--------------+----------------------------+----------------------------
 1 |            2 | John Doe     | 2018-07-24 09:18:30.125+07 | 2018-07-24 09:18:30.125+07
 2 |            2 | Jane Doe     | 2018-07-24 09:18:30.125+07 | 2018-07-24 09:18:30.125+07
 3 |            2 | Doe Doel     | 2018-07-24 09:18:30.125+07 | 2018-07-24 09:18:30.125+07
(3 rows)

Using `curl` you just get a classroom then the students will be included with the response.

curl -i -H "Accept: application/json" localhost:3000/api/classroom/2

HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 512
ETag: W/"200-9RPafOJtDdkqqMBVkSNCFoQ3p9s"
Date: Tue, 24 Jul 2018 03:18:45 GMT
Connection: keep-alive

{"id":2,"class_name":"Class A","createdAt":"2018-07-24T02:18:30.062Z","updatedAt":"2018-07-24T02:18:30.062Z","students":[{"id":1,"classroom_id":2,"student_name":"John Doe","createdAt":"2018-07-24T02:18:30.125Z","updatedAt":"2018-07-24T02:18:30.125Z"},{"id":2,"classroom_id":2,"student_name":"Jane Doe","createdAt":"2018-07-24T02:18:30.125Z","updatedAt":"2018-07-24T02:18:30.125Z"},{"id":3,"classroom_id":2,"student_name":"Doe Doel","createdAt":"2018-07-24T02:18:30.125Z","updatedAt":"2018-07-24T02:18:30.125Z"}]}

Run this `curl` for save or persist Lecturer, Course, and Student/Course data.

curl -i -X POST -H "Content-Type: application/json" -d '{ "lecturer_name":"Kylian Mbappe","course": { "course_name":"English Grammar" }}' localhost:3000/api/lecturer/add_with_course
curl -i -X POST -H "Content-Type: application/json" -d '{ "student_id":1,"course_id": 1}' localhost:3000/api/student/add_course

Now, you can see the data exists using `psql` query for each table.

That's it, the Node.js, Express.js, Sequelize.js, and PostgreSQL REST API. You can get the full working source code on our GitHub.

That just the basic. If you need more deep learning about Node.js, Express.js, PostgreSQL, Vue.js and GraphQL or related you can take the following cheap course:

Thanks!

Loading…