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:
- Create Express.js Project and Install Required Modules
- Add and Configure Sequelize.js Module and Dependencies
- Create or Generate Sequelize Models and Migrations
- Create Express Controller and Router for Classroom Model
- Create Express Controller and Router for Student Model
- Create Express Controller and Router for Lecturer Model
- Create Express Controller and Router for Course Model
- Advance Express Route and Function for Association
- Run and Test The Node, Express, Sequelize, and PostgreSQL REST API
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.
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:
- Node.js
- PostgreSQL Server
- Express.js
- Sequelize.js
- Terminal or Command Line
- 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.0That 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 -gNext, create an Express.js app using this command.
express node-sequelize --view=ejsThis 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 installYou 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.
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-cliTo install the Sequelize.js module, type this command.
npm install --save sequelizeThen install the module for PostgreSQL.
npm install --save pg pg-hstoreNext, create a new file at the root of the project folder that initializes the Sequelize configuration.
touch .sequelizercOpen 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 initThat 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 postgresNext, 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 djamwareEnter 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:integerThat 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.jsOpen 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.jsOpen 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.jsOpen 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.jsOpen 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.jsOpen 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.
nodemonOpen 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_studentsTo see data persist to PostgreSQL table, open a new terminal tab then run `psql`.
psql postgres -U djamwareConnect 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_courseNow, 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:
- Node. JS
- Learning Express JS: Creating Future Web Apps & REST APIs
- Angular + NodeJS + PostgreSQL + Sequelize
- Build 7 Real World Applications with Vue. js
- Full-Stack Vue with GraphQL - The Ultimate Guide
Thanks!