crud operation in mysql sequelize in node js with routes and controller models full explained

Sure, I can explain the CRUD operations in MySQL using Sequelize in Node.js with routes and controller models.

First, we need to install Sequelize and MySQL2 packages in our Node.js project using the following command:

npm install sequelize mysql2

Once the installation is done, we can create a new Sequelize instance in our project by providing the configuration object containing the database name, username, password, and other relevant details. Here's an example of how we can create a Sequelize instance:

javascript
const Sequelize = require('sequelize');
const sequelize = new Sequelize('database_name', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql'
});

We can then define our database model by creating a new Sequelize model and specifying the table name, columns, and their data types. Here's an example of a User model with the name, email, and password columns:

javascript
const User = sequelize.define('User', {
  name: {
    type: Sequelize.STRING,
    allowNull: false
  },
  email: {
    type: Sequelize.STRING,
    allowNull: false,
    unique: true
  },
  password: {
    type: Sequelize.STRING,
    allowNull: false
  }
});

Once we have defined our model, we can then create our CRUD operations in our controller. Here's an example of how we can create a controller with the methods for creating, reading, updating, and deleting users:

javascript
const User = require('../models/user');

exports.createUser = async (req, res) => {
  try {
    const user = await User.create(req.body);
    res.status(201).json(user);
  } catch (error) {
    res.status(400).json({ message: error.message });
  }
};

exports.getUsers = async (req, res) => {
  try {
    const users = await User.findAll();
    res.json(users);
  } catch (error) {
    res.status(500).json({ message: error.message });
  }
};

exports.getUserById = async (req, res) => {
  const { id } = req.params;
  try {
    const user = await User.findByPk(id);
    if (user) {
      res.json(user);
    } else {
      res.status(404).json({ message: 'User not found' });
    }
  } catch (error) {
    res.status(500).json({ message: error.message });
  }
};

exports.updateUser = async (req, res) => {
  const { id } = req.params;
  try {
    const [rowsUpdated, [updatedUser]] = await User.update(req.body, {
      where: { id },
      returning: true
    });
    if (rowsUpdated === 1) {
      res.json(updatedUser);
    } else {
      res.status(404).json({ message: 'User not found' });
    }
  } catch (error) {
    res.status(500).json({ message: error.message });
  }
};

exports.deleteUser = async (req, res) => {
  const { id } = req.params;
  try {
    const rowsDeleted = await User.destroy({ where: { id } });
    if (rowsDeleted === 1) {
      res.json({ message: 'User deleted successfully' });
    } else {
      res.status(404).json({ message: 'User not found' });
    }
  } catch (error) {
    res.status(500).json({ message: error.message });
  }
};

In the above code, we have created the following methods:

• createUser: This method creates a new user record in the database by calling the create method on the

above code continue

User model.

• getUsers: This method retrieves all the user records from the database by calling the findAll method on the User model.

• getUserById: This method retrieves a specific user record from the database by calling the findByPk method on the User model and passing the id parameter from the request.

• updateUser: This method updates a specific user record in the database by calling the update method on the User model and passing the id parameter from the request and the updated data from the request body.

• deleteUser: This method deletes a specific user record from the database by calling the destroy method on the User model and passing the id parameter from the request.

We can then create our routes by using the above controller methods. Here's an example of how we can create routes in our app.js file:

javascript
const express = require('express');
const userController = require('./controllers/user');

const app = express();

app.use(express.json());

app.post('/users', userController.createUser);
app.get('/users', userController.getUsers);
app.get('/users/:id', userController.getUserById);
app.put('/users/:id', userController.updateUser);
app.delete('/users/:id', userController.deleteUser);

app.listen(3000, () => {
  console.log('Server started on port 3000');
});

In the above code, we have defined the following routes:

• POST