Beginner's guide for creating ORM in Node.JS? | What is Sequelize in Node.JS?

How to connect Node.JS to database using ORM ?

We will use the Sequelize library of Node.JS to connect Node.JS to the database.
Sequelize is like the ORM tool to connect Node.JS to the database. ORM allows developers to interact with a database using the programming language's syntax and constructs rather than writing raw queries. It increases the readability of the code to a developer without knowing databases. It also increases the flexibility in switching the databases.

Installation of the package

Run the following command in your project directory to install the dependency.

npm i sequelize

Importing the package

const Sequelize = require('sequelize');

Connecting Node.JS to database

Following is the code to connect to the database

// creating instance of database
const database = new Sequelize('<database_name>','<username>','<password>',{
  host: 'localhost', // localhost for localserver use database url if using cloud server
  dialect: 'mysql', // use database type like sqlite, postgres, mysql etc
});

// connecting to the database
database.sync({ force: false })
.then(() => {
  console.log('connected to database successfully');
})
.catch((error) => {
  console.error('Failed to connect to database:', error);
});

Disabling the timestamps

By default, Sequelize creates two columns i.e. createdAt and updatedAt, for every model you define. It uses the following Date data type to store the date and time when something is updated or created in the model. If you don't want it, it can be disabled on the instance of the Sequelize.

const database = new Sequelize('<database_name>','<username>','<password>',{
  host: 'localhost', // localhost for localserver use database url if using cloud server
  dialect: 'mysql', // use database type like sqlite, postgres, mysql etc
  define : {
    timestamps: false, // disable timestamps
  }
});

How to define the model ?

Following code shows how the model is defined.

// creating the table
const User = database.define('<table_name>',{

   // id is the autoincrement primary key
    id:{type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true},

    // name with string type with condition set to not null
    name:{
        type: Sequelize.STRING,
        allowNull:false
    },

    // username with string type with default value "default_username"
    username:{
        type: Sequelize.STRING,
        default:'default_username'
    },
});

In the above code, we use the database object to define a model which corresponding to the table. Database object has the function name "define" to define a table. It creates the table automatically if the table does not exist.
It takes two arguments i.e. name of the table and an object defining the name and properties of the columns like its type, default value, etc. You can find the column data types for the model here.

Saving the data in Model.

To create the row in the table, we use the instance of the model which has "create" method. It takes the JSON object defines the values in the row. The JSON object's keys should be the exact names of the columns, and their values should be the corresponding values.

User.create({
  name:"name",
  username:"username"
}).then(user => {
  console.log("Data added successfully");
})
.catch(error => {
  console.error('Error In Adding Data : ', error);
});

Fetching the data from Model.

The instance of the model has two methods, "findAll" and "findOne".
The"findAll" method returns all the rows in the table or the rows that satisfy the specified query if provided.
The "findOne" method returns a single row that satisfies the specified query.
For querying, a JSON object is created just like shown in the code below and explained in detail in the following post.

Fetch data from the Model (Table)
User.findAll() // Getting all users
.then(users => {
  console.log('All users:', users);
})
.catch(error => {
  console.error('Error fetching users:', error);
});

// Getting a specific row from quering 
User.findOne(
  { where: { name: "name" } } // Query object to find the row where name is "name"
)
.then(user => {
  console.log('Row found:', user);
})
.catch(error => {
  console.error('Error fetching row:', error);
});

Deleting the data from Model.

The instance of the model has the"destroy" method. It takes the JSON object defining the query and deletes the rows satisfying it.

// Delete the table row
User.destroy({
  where: { name: "tanay"} // the table row where the name is "name"
}).then((data) => {
  console.log(data); // console log the number of rows affected
});

Updating the data from Model.

The instance of the model has the "update" method in which you have to pass to parameters first the JSON object defining the new data and another JSON object interpreting the query.

// Update the table row where the current name is "old_name"
User.update(
    {
        name: "new_name", // Updated data: set the 'name' field to "new_name"
    },
    {
        where: { name: "old_name" } // Query: find the row where 'name' is "old_name"
    }
)
.then((data) => {
    console.log(data); // Log the result of the update operation
})
.catch((error) => {
    console.error('Error updating user:', error); // Log any errors that occur during the update
});

How to create the query object ?

Following shows some sample queries with the basic operators to get to know about more operators you can follow this link.

To use the operators we need to import "Op" class from sequelize.

const {Op} = require("sequelizez");

Comparison operators :

We use comparison operators to compare values. Following are the comparison operators

Greater than: Op.gt
Greater than or equal to: Op.gte
Less than: Op.lt
Less than or equal to: Op.lte

// How to use 
{
  where:{
    age: {
      [Op.gt]:21 // change Op.gt to change the operator 
    }
  }
}

SQL QUERY OF ABOVE WILL LOOK LIKE BELOW.

SELECT * FROM TABLE_NAME WHERE age > 21

Like operators :

Like and Not Like operator is use to check weather value matches the patter or not.

{
  where:{
    name: {
      [Op.like]:"sample"
    }
  }
}

SQL QUERY OF ABOVE WILL LOOK LIKE BELOW.

SELECT * FROM TABLE_NAME WHERE "name" LIKE "SAMPLE"

Not Like operators :

{
  where:{
    name: {
      [Op.notLike]:"sample" 
    }
  }
}

SQL QUERY OF ABOVE WILL LOOK LIKE BELOW.

SELECT * FROM TABLE_NAME WHERE "name" NOT LIKE "SAMPLE"

Between operators :

The Between and Not Between operators are used to check if a value is between two values.


{
  where:{
    age: {
      [Op.between]:[20,30]
    }
  }
}
// SQL VERSION OF ABOVE QUERY IS "SELECT * FROM TABLE WHERE "age" BETWEEN 20 AND 30

Not Between operators :

{
  where:{
    age: {
      [Op.notBetween]:[20,30]
    }
  }
}
// SQL VERSION OF ABOVE QUERY IS "SELECT * FROM TABLE WHERE "age" NOT BETWEEN 20 AND 30

Related Blogs

What are Microservices and Molecular in NodeJS? -- Beginner's guide

Read

The beginner's guide to creating react component

Read