How to use Sequelize Object Relational Mapper with Node.js

Background
I completed CS290 Web Development during my Spring 2022 term at Oregon State University. In this course, I was introduced to Mongoose – a MongoDB object modeling tool designed to work in an asynchronous environment. Mongoose is a great tool that helps developers to speed up the development process of the apps that use the MERN (MongoDB, Express, React, NodeJS) stack. However, during my enrollment in CS340 the following summer, students were prohibited from using Object Relational Mappers in the applications that interact with MySQL databases. Even though writing SQL queries does not take much time, the average mid-size application would have hundreds of lines of code, most of which are simple insert and lookup SQL queries that can’t be reused anywhere else in the app.

Hardcoding SQL queries in your application not only takes up a lot of time and resources but also makes code less readable and hard to understand. After working with Mongoose to communicate with MongoDB, I started wondering if similar OPMs are offered for SQL. I knew that it should be a more efficient way to interact with the database and simplify the development process.

Introducing Sequelize

Before starting my Capstone project, I tested a few different OPMs. Most offer somewhat similar functionality and have decent documentation. After a while, I decided to try Sequelize. Sequelize is a TypeScript and Node.js Object Relational Mapper for Oracle, Postgress, MySQL, MariaDB, SQLite, and SQL Server. Turns out, it is a great OPM that is easy to use, provides detailed documentation, and makes communication of a web application with SQL database extremely simple. I am very excited to tell you more about Sequelize and demonstrate how I use it in my Capstone project.

How to use Sequelize in your app

If you plan on using Sequelize in your application to communicate with MySQL database, you need to install it and its dependencies:

npm i -s sequelize
npm i -s mysql2

Now we can connect your application to the database. Let’s start by initializing Sequelize in your application:

const Sequelize = require("sequelize");

const sequelize = new Sequelize(
    [YOUR_DBNAME],
    [YOUR_DB_USERNAME],
    [YOUR_DB_PASSWORD],
    {
        host: [DB_HOST],
        dialect: 'mysql',
        define: {
            timestamps: false
        }
    } 
)

After initializing Sequelize in your app, we can test the connection:

sequelize.authenticate().then(() => {
        console.log('Connection has been established successfully.');
     }).catch((error) => {
        console.error('Unable to connect to the database: ', error);
     });

Defining Models in Sequelize

Models Models are essential parts of Sequelize and represent a table in your database. The model helps Sequelize to learn about what the model entity represents (name of the table, columns, and data types). Below is the code snippet for the Sequlize Customer model I use in my application:

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

const Customers = sequelize.define("Customers", {
    customerId: {
        type: DataTypes.INTEGER,
        primaryKey: true
    },
    email: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true
    },
    phoneNumber: {
        type: DataTypes.STRING,
        allowNull: false
    },
    firstName: {
        type: DataTypes.STRING,
        allowNull: false
    },
    lastName: {
        type: DataTypes.STRING,
        allowNull: false
    },
    password:{
        type: DataTypes.STRING,
        allowNull: false
    }
},
{
    timestapms: false
});

As you can see, the Customers model represents a single customer in the database that has an id, unique email, phone number, first and last name, and password.

Model Querying

Once a model is created, querying it is extremely easy. For example, if we want to insert a new customer into the database, instead of writing “INSERT INTO Customers (id, …. , password) VALUES (…);”, we can simply refer to our model.

Below is a demonstration on how I used Customers model described above to add a new customer:

async function signup (email, phoneNumber, firstName, lastName, password) 
{
    // Check if user exists
    const exist = await Customers.findOne({where: {email: email} });
    if(exist) {
        throw Error('Email already in use')
    }

    // Hash Password Here
    
    const user = await Customers.create({
        email: email,
        phoneNumber: phoneNumber,
        firstName: firstName,
        lastName: lastName,
        password: password
    })
    return user;
}

The function signup seems to be a little bit bulky, so let’s discuss it. As you can see, it takes as parameters all the values that we need to insert a new customer into the database. First, we would see if this email already exists in the database. Instead of writing the SQL query “SELECT * FROM CUSTOMERS WHERE email=’email’;”, we use a Sequelize model querying findOne. If the email is not in the database, we will await for the model.create(variables) to return the newly created user. 

As you can see, it makes interacting with the database much easier, improves code quality and readability, and speeds up the development process significantly.

Conclusion

Sequelize is definitely a great tool for those who are working on applications and use SQL databases as data storage. It helps developers and teams to keep their project code nice and clean, and helps in understanding and learning good database principles. On top of providing great and detailed documentation, Sequelize is easy to use and learn.

I hope that you enjoyed reading this article and find this small guide on how to use Sequelize with Node.js helpful. Feel free to share your experience with Sequelize in the comment section!

Links:

Sequelize Official Website – https://sequelize.org