Performing CRUD with Sequelize
Tags: node, express, javascript, postgres
Published: 2019-08-09 16:02:12

In the [part one of this article](https://www.oriechinedu.com/posts/getting-started-with-sequelize-and-postgres), 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](https://www.oriechinedu.com/posts/getting-started-with-sequelize-and-postgres), 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](https://github.com/oriechinedu/sequelize-with-postgres-tutorial.git)


### 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`

```javascript

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`

```javascript
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`

```javascript
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`

```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`

```javascript
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:

```javascript
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:

![](https://thepracticaldev.s3.amazonaws.com/i/4mud24wrysmj5dotj5ak.png)

### 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`.

```javascript
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.

```js
module.exports = {
  createPost,
  getAllPosts
}
```

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

```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.


![](https://thepracticaldev.s3.amazonaws.com/i/ng3d64furgs2l3zjodxu.png)
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`

```javascript
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`

```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.

![](https://thepracticaldev.s3.amazonaws.com/i/v9q735jz4mlkw3pyh46j.png)
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`

```javascript
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`

```javascript
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`

```javascript
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:

```js
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.

#### Suggested resources

 - [Express Validator](https://express-validator.github.io/docs/)
 - [Sequelize Docs](https://sequelize.org/)
 - [How to write a production-ready Node and Express app](https://www.freecodecamp.org/news/how-to-write-a-production-ready-node-and-express-app-f214f0b17d8c/)