How to use Sequelize with Node JS

Table of Contents

Can we use Sequelize in node JS?

Sequelize is a promise-based ORM for Node.js that supports PostgreSQL, MySQL, MariaDB, SQLite, and MSSQL and features solid transaction support, relations, read replication, etc. Sequelize provides support for more than one database system and it also provides you an OOP Databases API with all queries in form of methods and functions so you don’t have to write or care about queries plus its advanced Association system for putting relations between Models.

Also Learn: How to Setup Node.js in Linux

Prerequisites

A basic understanding of Node js
Basic knowledge of MySQL

In this article, we will use Sequelize with Node JS to perform some basic CRUD operations with MySQL Database.

Perform CRUD Operation on Sequelize with Node js

Project Setup

Create a new directory named sequelize-mysql, and change it into that directory. we will set up sequelize from npm.

mkdir sequelize-mysql
cd sequelize-mysql

Inside the sequelize-mysql directory, initialize a node js project using

npm init

You will see the following package.json file if you press enter with the default settings

Next, let’s install the required modules. The module going to be installed are express, sequelize, and mysql2. mysql2 is needed to install because it’s a MySQL client for Node.js with a focus on performance. It supports prepared statements, non-utf8 encodings, binary log protocol, compression, SSL, and much more.

npm install express mysql2 sequelize --save

Now let’s create a web server named index.js in the root directory of our project.

const express = require('express')
const app = express();

app.use(express.json());
app.use(express.urlencoded({extended: true}));

app.listen(3000, function () {
    console.log('App listening on port 3000!');
});

The above code will initialize the express framework and set up the server at port 3000.

Then let us create a configuration to communicate with the database using Sequelize. For the database configuration create a file named database.js in the root directory with the following code. Before creating the database.js file create a sample MySQL database named sampleDB using any convenient way.

How do I connect MySQL Sequelize to node js?

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

const database = new Sequelize({
  host: "DB_HOST",
  database: "DB_NAME",
  username: "DB_USER",
  password: "DB_PASSWORD",
  dialect: "mysql",
});

module.exports = { database };

The above sample will initialize the sequelize with the provided options.

Here, the parameters

host: location of your MySQL server and database

database: Name of the database you want to communicate with

username: Name used to connect to the database

password: Password used to connect to the database

dialect: Type of the database to communicate using sequelize

Now, let’s create a file named server.js where all the database-related queries will be added.

How do I use Sequelize in node js with MySQL?

Inserting a Data

const {
  database,
} = require("./database");

const { QueryTypes } = require('sequelize');

async function insertSampleData(data) {
  const {
    name,
    address,
    email,
  } = data;

  const insertQuery = `INSERT INTO sampleDB.user (id, name, address, email) 
                      VALUES (:id, :name, :address, :email)`;

  const response = await database.query(insertQuery, {
    replacements: {
      id: 1,
      name,
      address,
      email,
    },
    type: QueryTypes.INSERT,
  });
  return response;
}

In the above sample code, we will insert the user name, address and email in the user table of sample DB database. The insertSampleData will receive the parameters passed from the API request in the data variable.

Here,

database variable consists of sequelize model from above.

insertQuery contain the raw SQL to insert the user detail into the database.

replavements object consists of variables whose value will be replaced in the insertQuery.

type determines the type nature of the operation to be performed

Also Read: How To Upload Files in Node js Using Multer

Fetching data from the database

async function getAllSampleData() {
  const selectQuery = `SELECT * FROM sampleDB.user`;
  const result = await database.query(selectQuery, {
    type: QueryTypes.SELECT,
  });
  return result;
}

The above function returns all the data available in the user table in an array of objects.

async function getParticularSampleData() {
  const selectQuery = `SELECT * FROM sampleDB.user where email = :email`;
  const result = await database.query(selectQuery, {
    replacements: {
      email: "dummy@gmail.com"
    },
    type: QueryTypes.SELECT,
  });
  return result;
}

The getParticularSampleData the function returns all the samples matching the condition i.e. email = “dummy@gmail.com.

Deleting data from the database

async function deleteSampleData(id) {
  const deleteQuery = `DELETE FROM sampleDB.user WHERE id = :id`;

  const result = await database.query(deleteQuery, {
    replacements: {
      id,
    },
    type: QueryTypes.DELETE,
  });
  return result;
}

In the above sample code, we have a SQL query to delete data with the matching id provided in the function parameter.

Updating a data

async function updateSampleData(id, data) {
  const {
    name,
    address,
    email,
  } = data;

    const updateQuery = `UPDATE sampleDB.user SET name = :name, address = :address, email = :email WHERE id = :id`;
    const result = await database.query(updateQuery, {
      replacements: {
        id,
        name,
        address,
        email,
      },
      type: QueryTypes.UPDATE,
    });
    return result;
}

In the above sample code, the record matching the id will be updated with the value provided in the data variable.

Conclusion

In this article, we learn how we can use Sequelize with Node JS and MySQL Database. Sequelize is used to perform some operations on databases with ease, similarly, we can use Liquibase to manga our database objects.