CRUD operations with MYSQL using sequelize

ORM and reasons to use it

An Object-Relational Mapping (ORM) tool gives you the power to manage databases. It can do this by using an object-oriented paradigm, which helps increase the code’s readability by a lot.
Putting raw SQL queries in your code might/should not be the best choice in most cases. For example, to perform insertion.

1
2
INSERT INTO tableName(c1, c2, c3, c4)
VALUES(v1, v2, v3);

Now, if there are many columns, then the query size will keep on increasing. Also if the data is coming from the request body in the form of a JSON object then you will have to parse it. While using an ORM you can just do the following.

modelName.create(data);

It makes your code easier to read and write, as well as object-oriented. In a similar manner, most of the operations become a lot easier.
Let’s set up our project. In an empty folder, run npm init to initialize npm. Now install a few packages using the following command.
npm i express mysql2 sequelize sequelize-cli dotenv

Now, let’s set up our server.
Create index.js in your root folder with the following code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
const express = require('express');
const app = express();
app.use(express.json());
app.use(express.urlencoded({
  extended: false
}));

app.get('/', (req, res) => {
  console.log('API is up');
})

app.listen(3000, (req, res) => {
  console.log('server started');
})

Now run Sequelize init in terminal to create the required files for Sequelize. Delete the seeders and migrations folder; you do not need that. It should be looking like this.

Now let’s connect to our database, open the models/index.js file and replace the following line:

const config = require(__dirname + '/../config/config.json')[env];
with:
const config = require(__dirname + '/../config/config.js')[env];

We have changed JSON to js.
Now in your .env file, we add the database connection credentials in the following format:

1
2
3
4
5
DB_USERNAME =
  DB_PASSWORD =
  DB_DATABASE =
  DB_HOST =
  DB_DIALECT =

Here the host will be localhost and the dialect will be MYSQL. The rest will depend upon your MySQL setup. After you have done this replace the code in config/config.js with the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
require('dotenv')
  .config()
module.exports = {
  "development": {
    "username": process.env.DB_USERNAME,
    "password": process.env.DB_PASSWORD,
    "database": process.env.DB_DATABASE,
    "host": process.env.DB_HOST,
    "dialect": process.env.DB_DIALECT
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

We are using the values from .env file.
Now we are ready. We will be dealing with book data here.
Create models/books.js. We will be storing the name of the book and its author. Along with that we will also be providing an auto-incrementing id to each entity. So paste the following code in books.js.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
module.exports = (sequelize, DataTypes) => {
  const Book = sequelize.define(
    'Book', {
      id: {
        type: DataTypes.INTEGER,
        autoIncrement: true,
        primaryKey: true
      },
      name: {
        type: DataTypes.STRING,
      },
      author: {
        type: DataTypes.STRING,
      },
    },
  );
  return Book;
};

If you see the following in your terminal then your query has been processed.

You can open your MYSQL workbench to see a table present there.

Now, let’s start to manipulate the data in the table.

Create data:

We will be creating an API that will take input data sent via postman and add it to the database. It will look as follows.

1
2
3
4
5
6
7
8
9
app.post('/books', async (req, res) => {
  const data = req.body;
  try {
    const book = await db.Book.create(data);
    res.send(book);
  } catch (err) {
    res.send(err);
  }
})

Paste code index.js in the root folder right above the db.sequelize line. Here we are extracting the data from the request body and adding it to the db.Book model.

To make the request, hit it on the http://localhost:3000/books as a post request with the following body.

1
2
3
4
{
  "name": "3 body problem",
  "author": "Cixin Liu"
}

You should get the following response in postman.

1
2
3
4
5
6
7
{
  "id": 1,
  "name": "3 body problem",
  "author": "Cixin Liu",
  "updatedAt": "2021-10-09T11:29:34.415Z",
  "createdAt": "2021-10-09T11:29:34.415Z"
}

The MYSQL workbench should be looking like this.

Go ahead and add some more books. A faster way to do that is to replace the db.Book.create(data) with db.Book.bulkCreate(data) in API, and now you can send an array in req.body like this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[
  {
    "name": "Hero of Ages",
    "author": "Brandon Samderson"
    },
  {
    "name": "Our mathematical Universe",
    "author": "Max Tegmark"
    },
  {
    "name": "Brief history of time",
    "author": "Stephen Hawking"
    }
]

And now we have several books in the database.

Read operation:

The API for this is very simple as well.

1
2
3
4
5
6
7
8
9
10
11
12
13
app.get('/books/:id', async (req, res) => {
  const id = req.params.id;
  try {
    const book = await db.Book.findOne({
      where: {
        id
      }
    });
    res.send(book);
  } catch (err) {
    res.send(err);
  }
});

We are asking for the id for the book that needs to be fetched and finding it in the DB. To execute this, make a get request to http://localhost:3000/books/:id and in params write the id you want to fetch. The following should be the response for id 1.

1
2
3
4
5
6
7
{
  "id": 1,
  "name": "3 body problem",
  "author": "Cixin Liu",
  "createdAt": "2021-10-09T11:29:34.000Z",
  "updatedAt": "2021-10-09T11:29:34.000Z"
}

Let’s handle a few errors:
What if the book you requested does not exist?
Add this line right before you are sending the response in your API,

console.log(book.dataValues);

and make a request. You will see that the book gets printed in the console.

In order to make sure that we do not send a blank object back to client we can put up a check like below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
app.get("/books/:id", async (req, res) => {
  const id = req.params.id;
  try {
    const book = await db.Book.findOne({
      where: {
        id
      }
    });
    console.log(book);
    if (!book) res.send({
      Book: "Book not found"
    });
    res.send({
      Book: book
    });
  } catch (err) {
    res.send(err);
  }
});

Update:

When updating you also need to provide the data you want to be updated. Let’s say we want to convert the name of the book with id 2 to The Hero of Ages. We will do that in the following way.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
app.put('/books/:id', async (req, res) => {
  const id = req.params.id;
  const data = req.body;
  try {
    const book = await db.Book.update(data, {
      where: {
        id
      }
    });
    res.send('book updated');
  } catch (err) {
    res.send(err);
  }
});

In the postman, we will send a post request with the URL the same as the get/delete request, but the body will look like this.

1
2
3
{
  "name": "The hero of ages"
}

As you can see the entity has been updated.

We can handle the error here in the same way we did in the delete request because it also returns the number of records updated.

Delete:

The delete is really similar to read, you only need to replace findOne with destroy and change the request type with delete like this.

1
2
3
4
5
6
7
8
9
10
11
12
13
app.delete('/books/:id', async (req, res) => {
  const id = req.params.id;
  try {
    const book = await db.Book.destroy({
      where: {
        id
      }
    });
    res.send('book deleted');
  } catch (err) {
    res.send(err);
  }
});

And now in postman just change the read request type to delete and see the API in action.

As you can see, the book with id one has been deleted.

Let’s handle some errors:
What if you deleted a record that does not exist? Sequelize provides a nice way to handle this, it returns the number of records that were deleted. So if you get zero as a response then no rows were deleted. Modify your code like this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
app.delete("/books/:id", async (req, res) => {
  const id = req.params.id;
  try {
    const book = await db.Book.destroy({
      where: {
        id
      }
    });
    if (book === 0) {
      res.send('No records were deleted');
    } else {
      res.send(`{book} number of records were deleted`);
    }
  } catch (err) {
    res.send(err);
  }
});

Now, let’s delete some record that does not exist.

See that it has handled the error.

You can find the entire code here at https://github.com/thrive-articles/sequelize-crud