Building REST API using Node/Express and Sequelize

Setup

we will be using Postgres database for this tutorial along with Sequelize. Sequelize is an ORM for Node.js and Relational databases such as MySQL,MSSQL,Postgres and SQLite.

Firstly, postgres and Node.js needs to be installed in your machine. Follow this instruction to install postgres and node.js on your machine.

Once postgres is installed, Start postgres server and you will see a dashboard like following

Sequelize Setup

create a folder called node-sequelize and install express with the following command.

1npm init --yes
2npm install express body-parser

After that, Install Sequelize and postgres with the following command.

1npm install sequelize sequelize-cli pg pg-hstore

sequelize-cli can be installed in two ways. one is globally and another one is locally to the project. Here, we install locally to the project.

Initialize the Sequelize using the following command

1node_modules/.bin/sequelize init

Since, we installed sequelize locally to the project, we need to refer by using node_modules/.bin/sequelize

Above command will create a bunch of folders such as config,migrations and models

• config - config contains the config.json which is a database configuration.
• migrations - it contains the migrations file which create tables in the database.
• models - models are Schema which maps with the DB Table.

Setting up Postgres in Sequelize

config.json will contain three environments such as development,test and production. Change the config.json to postgres database in development environment

1"development": {
2    "username": "postgres",
3    "password": null
4    "database": "testdb",
5    "host": "127.0.0.1",
6    "port" : 5432,
7    "dialect": "postgres"
8  }

In models folder, create files such as post.js and user.js and add the following code.

1//POST Schema
2module.exports = (sequelize, DataTypes) => {
3  let Post = sequelize.define("Post", {
4    title: DataTypes.STRING,
5  })
6
7  Post.associate = function(models) {
8    Post.belongsTo(models.User, {
9      onDelete: "CASCADE",
10      foreignKey: "userId",
11    })
12  }
13
14  return Post
15}

1//USER Schema
2module.exports = (sequelize, DataTypes) => {
3  let User = sequelize.define("User", {
4    email: DataTypes.STRING,
5  })
6
7  User.associate = function(models) {
8    User.hasMany(models.Post, {
9      foreignKey: "userId",
10      as: "posts",
11    })
12  }
13  return User
14}

Sequelize Postgres Association

consider a scenario , where user will have lots of posts and a post will belongs to user. In technical terms of database design, it is called as relationships. there are three types of relationship between two entities.they are,

• One to One Relationship - For Example, User and Address are one to one relationships. User will have one address and an adress belongs to an user.
• One to Many Relationship - For Example, customer will have many orders, an order belongs to a customer
• Many to Many Relationship - Example : Author will publish many books, a book can have multiple authors.

To implement this concepts, we have Assocation in postgres sequelize.

Once you create a models, create files in migrations to create tables in database.

1//auser-migration.js
2module.exports = {
3  up: (queryInterface, Sequelize) =>
4    queryInterface.createTable("Users", {
5      id: {
6        allowNull: false,
7        autoIncrement: true,
8        primaryKey: true,
9        type: Sequelize.INTEGER,
10      },
11      email: {
12        type: Sequelize.STRING,
13        allowNull: false,
14      },
15      createdAt: {
16        allowNull: false,
17        type: Sequelize.DATE,
18      },
19      updatedAt: {
20        allowNull: false,
21        type: Sequelize.DATE,
22      },
23    }),
24  down: (queryInterface /* , Sequelize */) => queryInterface.dropTable("Users"),
25}

1//create-post.js
2module.exports = {
3  up: (queryInterface, Sequelize) =>
4    queryInterface.createTable("Posts", {
5      id: {
6        allowNull: false,
7        autoIncrement: true,
8        primaryKey: true,
9        type: Sequelize.INTEGER,
10      },
11      title: {
12        type: Sequelize.STRING,
13        allowNull: false,
14      },
15      userId: {
16        type: Sequelize.INTEGER,
17        onDelete: "CASCADE",
18        references: {
19          model: "Users",
20          key: "id",
21          as: "userId",
22        },
23      },
24      createdAt: {
25        allowNull: false,
26        type: Sequelize.DATE,
27      },
28      updatedAt: {
29        allowNull: false,
30        type: Sequelize.DATE,
31      },
32    }),
33  down: (queryInterface /* , Sequelize */) => queryInterface.dropTable("Posts"),
34}

Run the following command to migrate the tables to database

1node_modules/.bin/sequelize db:migrate

As a result,it will create tables in postgres database.

API Design

Firstly, create a folder called controller. In controller, create files user.js and post.js . basically, we are going to write API to create User and Post.

1//user.js
2const User = require("../models").User
3module.exports = {
4  async getAllUsers(req, res) {
5    try {
6      const userCollection = await User.find({})
7
8      res.status(201).send(userCollection)
9    } catch (e) {
10      console.log(e)
11
12      res.status(500).send(e)
13    }
14  },
15
16  async create(req, res) {
17    try {
18      const userCollection = await User.create({
19        email: req.body.email,
20      })
21
22      res.status(201).send(userCollection)
23    } catch (e) {
24      console.log(e)
25      res.status(400).send(e)
26    }
27  },
28
29  async update(req, res) {
30    try {
31      const userCollection = await User.find({
32        id: req.params.userId,
33      })
34
35      if (userCollection) {
36        const updatedUser = await User.update({
37          id: req.body.email,
38        })
39
40        res.status(201).send(updatedUser)
41      } else {
42        res.status(404).send("User Not Found")
43      }
44    } catch (e) {
45      console.log(e)
46
47      res.status(500).send(e)
48    }
49  },
50}

1//post.js
2const Post = require("../models").Post
3const User = require("../models").User
4
5module.exports = {
6  async getAllPostsOfUser(req, res) {
7    try {
8      const userCollection = await User.find({
9        id: req.params.userId,
10      })
11
12      if (userCollection) {
13        const postCollection = await Post.find({
14          userId: req.params.userId,
15        })
16
17        res.status(201).send(postCollection)
18      } else {
19        re.status(404).send("User Not Found")
20      }
21    } catch (e) {
22      console.log(e)
23      res.status(500).send(e)
24    }
25  },
26
27  async createPost(req, res) {
28    try {
29      const post = await Post.create({
30        title: req.body.title,
31        userId: req.body.userId,
32      })
33      res.status(201).send(post)
34    } catch (e) {
35      console.log(e)
36      res.status(400).send(e)
37    }
38  },
39
40  async update(req, res) {
41    try {
42      const postCollection = await Post.find({
43        id: req.params.postId,
44      })
45
46      if (postCollection) {
47        const updatedPost = await postCollection.update({
48          title: req.body.title,
49        })
50
51        res.status(201).send(updatedPost)
52      } else {
53        res.status(404).send("Post Not Found")
54      }
55    } catch (e) {
56      console.log(e)
57      res.status(400).send(e)
58    }
59  },
60}

In addition,import post.js and user.js in the controller index file.

create a folder called router and add the following code in index.js

1//index.js
2const userController = require("../controller").user
3const postController = require("../controller").post
4module.exports = app => {
5  app.get("/api", (req, res) => {
6    res.status(200).send({
7      data: "Welcome Node Sequlize API v1",
8    })
9  })
10
11  app.get("/api/users", userController.getAllUsers)
12
13  app.post("/api/user/create", userController.create)
14
15  app.put("/api/user/:userId", userController.update)
16
17  app.get("/api/:userId/posts", postController.getAllPostsOfUser)
18
19  app.post("/api/post/create", postController.createPost)
20
21  app.put("/api/:postId", postController.update)
22}

Finally, index.js file will look like

1const express = require("express")
2const bodyParser = require("body-parser")
3const app = express()
4
5app.use(bodyParser.json())
6app.use(bodyParser.urlencoded({ extended: false }))
7
8require("./server/routes")(app)
9
10const PORT = 3456
11app.listen(PORT, () => {
12  console.log(`Server is listening to port ${PORT}`)
13})

Run the Application using the command

1node index.js