Thursday, February 29, 2024

Sequelize in Node.js Application | Sequelize in Express Application | Establishing Database Connections in Node.js

Today, I am going to discuss establishing a database connection in Node.js. I have already installed MySQL Server 8.0 on my local machine. I will use an ORM (Object Relational Mapping) framework named Sequelize to connect with the database in my Node.js and Express application.

Note: I have created a video tutorial about the same topic. Below is the link of the video tutorial.

Video tutorial of Sequelize DB connection in Node.js Application

Below are the steps to establish the database connection and interact with it.

Step1> Install Sequelize and mysql2 driver npm packages in your application.

{
  "name": "helloworld",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.18.2",
    "morgan": "^1.10.0",
    "mysql2": "^3.9.2",
    "sequelize": "^6.37.1"
  }
}

Step2> Create a file named as index.js in a folder named as model in your application. In this index.js file, add DB config and create an object of Sequelize. Afterwards, you need to pass this sequelize object in all your model files definition. Model files are nothing but the replicas of a particular table. Below is the code for creating connection between Sequelize and MySql DB. Here, my dbName=testdb, username=root and password=admin. As I have installed MySql in my local machine, so, hostname=localhost otherwise it would be IP address or path of your remote server having the DB.

const { Sequelize, DataTypes } = require('sequelize');

// Initialize Sequelize connection
const sequelize = new Sequelize('testdb', 'root', 'admin', {
    host: 'localhost',
    dialect: 'mysql', // Specify the dialect (in this case, MySQL)
    operationsAliases: false,
    pool: {
      max: 5,
      min: 0,
      acquire: 30000,
      idle: 10000
    }
});

const db = {};
db.sequelize = sequelize;
db.models = {};
db.models.User = require('./user')(sequelize, Sequelize.DataTypes);
module.exports = db;

// Close Sequelize connection when the Node.js process exits
process.on('exit', () => {
    sequelize.close()
      .then(() => {
        console.log('Sequelize connection closed');
      })
      .catch((error) => {
        console.error('Error closing Sequelize connection:', error);
      });
  });
 
  // Handle Ctrl+C or SIGINT signal to gracefully close the connection
  process.on('SIGINT', () => {
    sequelize.close()
      .then(() => {
        console.log('Sequelize connection closed');
        process.exit(0);
      })
      .catch((error) => {
        console.error('Error closing Sequelize connection:', error);
        process.exit(1);
      });
  });
 
  // Handle uncaught exceptions and promise rejections
  process.on('uncaughtException', (error) => {
    console.log(error);
    sequelize.close()
      .then(() => {
        console.log('Sequelize connection closed');
        process.exit(1);
      })
      .catch((closeError) => {
        console.error('Error closing Sequelize connection:', closeError);
        process.exit(1);
      });
  });
 
  process.on('unhandledRejection', (reason, promise) => {
    sequelize.close()
      .then(() => {
        console.log('Sequelize connection closed');
        process.exit(1);
      })
      .catch((closeError) => {
        console.error('Error closing Sequelize connection:', closeError);
        process.exit(1);
      });
  });
 
 

If you go through the above code, I have written few blocks of code to gracefully close the DB connection if the process is failing due to any reasons like process.exit or SIGINT signal or Unhandled exception or Unhandled rejections.

Step 3> Now, We should create a model file. As discussed, model is nothing but the replica of a table in your DB. I have created here a model named as User corresponding to the table Users. Here, model name and table name may be same. If these are not same then we have to specify the table name by attribute tableName: 'users'. While defining this model, I am using the sequelize object being passed from index.js file where we have created the DB connection with Sequelize.

module.exports = (sequelize, DataTypes) => {
  const UserModel = sequelize.define('User', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false
    }
  }, {
    tableName: 'users',
    timestamps: false
  });
  return UserModel;
}

Step 4> Next, we will utilize this model file in our service class to perform CRUD operations on users table. Here, I have created functions to get list of users, add new user and delete an user.

const {models: {User}} = require('../model');
const getUsers = async function () {
  try {
    const users = await User.findAll();
    return users;
  } catch (error) {
    throw error;
  }
}
const addUsers = async function (userObj) {
  try {
    const insertResult = await User.create(userObj);
    return insertResult;
  } catch (error) {
    throw error;
  }
}
const deleteUser = async function (id) {
  try {
    const deleteResult = await User.destroy({"where": {"id": id}});
    return deleteResult;
  } catch (error) {
    throw error;
  }
}
module.exports = {
  getUsers, addUsers, deleteUser
}

Step 5> Next, we will create a controller class where we will use service functions to interact with DB. The controller functions will be used as middleware in route file.

const userService = require('../service/user');
const getUsers = async (req, res, next) => {
    try {
        const users = await userService.getUsers();
        res["result"] = users;
        next();
    } catch (error) {
        throw error;
    }
};
const addUsers = async (req, res, next) => {
    try {
        const userObj = {
            "name": req.body.name,
            "email": req.body.email
        };
        const insertResult = await userService.addUsers(userObj);
        res["result"] = insertResult;
        next();
    } catch (error) {
        throw error;
    }
};
const deleteUser = async (req, res, next) => {
    try {        
        const deleteResult = await userService.deleteUser(req.query.id);
        res["result"] = deleteResult;
        next();
    } catch (error) {
        throw error;
    }
};
module.exports = {
    getUsers, addUsers, deleteUser
}

Step 6> Create a route file and call the controller middleware functions in each routes.

const express = require('express');
const router = express.Router();
const userCtrl = require('../controller/user');
const { routeMiddleware } = require('../myMiddleware/customMiddleware');

router.get('/helloWorld', (req, res) => {
  res.send('Hello, World!');
});

router.delete('/deleteuser', userCtrl.deleteUser, (req, res) => {
  const { name, email } = req.query;
  res.json({"status": "success", "result": res.result});
});

router.get('/users', userCtrl.getUsers, (req, res) => {
  res.json({"status": "success", "result": res.result});
});

router.post('/adduser', userCtrl.addUsers, (req, res) => {
  const { name, email } = req.body;
  res.json({"status": "success", "result": res.result});
});

module.exports = router;

Step 7> One most important step that we need to do here is to trigger sync() function of Sequelize in your app.js file (where you are initializing your server) to get all your defined models synced with DB.

const express = require('express');
const bodyParser = require('body-parser');
const morgan = require('morgan');
const db = require('./model');
const { errorHandler, applicationMiddleware } = require('./myMiddleware/customMiddleware');
const routes = require('./route/routes');

const app = express();
const port = 3000;

(async () => {
  await db.sequelize.sync();
})();

// Built-in Middleware to parse JSON data in the request body
app.use(bodyParser.json());

//Using Third-party middleware function for logging
app.use(morgan('dev'));

// Application level middleware
app.use(applicationMiddleware);

// Use the routes in your app
app.use('/', routes);

//Using error handler middleware
app.use(errorHandler);

app.listen(port, () => {
  console.log(`Server is running on http://localhost:${port}`);
});

I have created a video tutorial about the same topic. Below is the link of the video tutorial.

Video tutorial of Sequelize DB connection in Node.js Application