Performing CRUD with Sequelize

In the part one of this article, we covered setting up Sequelize, creating migrations/models and seeding the database. In this part, we'll be performing CRUD building on part one.

If you want to follow along, you can start from part one, if you have not done so, but you are free to jump to this part if you're already comfortable with step one.

You can clone the complete code for this article here

Installing Dependencies

 npm i express

We need to install nodemon which restarts the server each time there's a change hence easing the stress of manually restarting the server.

 npm i -D nodemon

Notice the -D flag which indicates that a package is only needed in a development environment.

Express Server Setup

To set up the server, we need to create two directories - server and routes:

mkdir server routes

Create an index.js file in each of server and routes directory:

touch server/index.js routes/index.js

Add the following code to routes/index.js


const { Router } = require('express');

const router = Router();

router.get('/', (req, res) => res.send('Welcome'))

module.exports = router;

Add the following code to server/index.js

const express = require('express');
const routes = require('../routes');

const server = express();
server.use(express.json());

server.use('/api', routes);

module.exports = server;

Next up, we create the app entry point at the root of the project:

touch index.js

Add the following code to the index.js

require('dotenv').config();

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

const PORT = process.env.PORT || 3300;

server.listen(PORT, () => console.log(`Server is live at localhost:${PORT}`));

Finally, we add the start script to the package.json

Add the following code to package.json

 "scripts": {
    "start-dev": "nodemon index.js"
  },

To start the server run

npm start-dev

Now visiting localhost:3300/api on POSTMAN will return "Welcome" which shows that the server is up and running.

Creating a new post [C IN CRUD]

First of all, let's create a new file controllers/index.js which will house the CRUD logic.

mkdir controllers && touch controllers/index.js

Add the following code to the controllers/index.js

const models = require('../database/models');

const createPost = async (req, res) => {
  try {
    const post = await models.Post.create(req.body);
    return res.status(201).json({
      post,
    });
  } catch (error) {
    return res.status(500).json({error: error.message})
  }
}

module.exports = {
  createPost,
}

Next up, we need to create the route for creating new post. Edit the routes/index.js as follows:

const { Router } = require('express');
const controllers = require('../controllers');

const router = Router();

router.get('/', (req, res) => res.send('Welcome'))

router.post('/posts', controllers.createPost);

module.exports = router;

Now when you visit the Create Post Endpoint [POST] localhost:330/api/posts on Postman and fill in the appropriate values for the request body, a new post will be created as shown in the screenshot below:

Getting a list of posts [R in CRUD]

We are going to create another endpoint for retrieving the list of posts. Here we'll apply the eager loading feature of ORM provided by Sequelize. Eager loading means retrieving the associated models alongside the model being queried. In Sequelize, eager loading is achieved using the include property as shown in the snippet below.

Add the code following to controllers/index.js.

const getAllPosts = async (req, res) => {
  try {
    const posts = await models.Post.findAll({
      include: [
        {
          model: models.Comment,
          as: 'comments'
        },
        {
          model: models.User,
          as: 'author'
        }
      ]
    });
    return res.status(200).json({ posts });
  } catch (error) {
    return res.status(500).send(error.message);
  }
}

Export the getAllPosts by adding it to the module.exports object.

module.exports = {
  createPost,
  getAllPosts
}

Next up, define the endpoint by adding the following code to routes/index.js:

router.get('/posts', controllers.getAllPosts);

Now, when you visit the Get All Post Endpoint [GET] localhost:330/api/posts on Postman, the response is as shown below.

Notice that each post has an array of comments and the author object associated with it, that's eager loading

Getting a single post [R in CRUD]

Sequelize provides a method findOne for retrieving a single record based on a given property of the model.

Add the following code to the controllers/index.js

const getPostById = async (req, res) => {
  try {
    const { postId } = req.params;
    const post = await models.Post.findOne({
      where: { id: postId },
      include: [
        {
          model: models.Comment,
          as: 'comments',
          include: [
           {
            model: models.User,
            as: 'author',
           }
          ]
        },
        {
          model: models.User,
          as: 'author'
        }
      ]
    });
    if (post) {
      return res.status(200).json({ post });
    }
    return res.status(404).send('Post with the specified ID does not exists');
  } catch (error) {
    return res.status(500).send(error.message);
  }
}

Next up, we create the endpoint by adding the following code to routes/index.js

router.get('/posts/:postId', controllers.getPostById);

Now, when you visit [GET] localhost:330/api/posts/1 on Postman, the response is as shown below.

Looking at the response, we used nested eager loading to get the author of a post's comment.

Updating a post [U in CRUD]

The update method in Sequelize updates the given model's fields specified in the object passes to it as a parameter. This reduces the stress of manually checking the object passed to the update method and updating the model's field accordingly.

Add the following code to controllers/index.js

const updatePost = async (req, res) => {
  try {
    const { postId } = req.params;
    const [ updated ] = await models.Post.update(req.body, {
      where: { id: postId }
    });
    if (updated) {
      const updatedPost = await models.Post.findOne({ where: { id: postId } });
      return res.status(200).json({ post: updatedPost });
    }
    throw new Error('Post not found');
  } catch (error) {
    return res.status(500).send(error.message);
  }
};

Then, we create the endpoint by adding the following code to routes/index.js

router.put('/posts/:postId', controllers.updatePost);

Deleting a post [D in CRUD]

Sequelize provides a method destroy for deleting a model's record.

Add the following code to controllers/index.js

const deletePost = async (req, res) => {
  try {
    const { postId } = req.params;
    const deleted = await models.Post.destroy({
      where: { id: postId }
    });
    if (deleted) {
      return res.status(204).send("Post deleted");
    }
    throw new Error("Post not found");
  } catch (error) {
    return res.status(500).send(error.message);
  }
};

Then update the routes/index.js with the DELETE as shown below:

router.delete('/posts/:postId', controllers.deletePost);

Conclusion

We have been able to implement CRUD using Sequelize. However, in a bid to keeping it simple, we have skipped some parts such as form input validation, error handling, proper separation of concerns. Hence, you could decide to take it further and make those improvements.

Feel free to reach out to me via any means you find convenient if you have any question or some contributions to making the article better.

Blog

Performing CRUD with Sequelize

Chinedu Orie