Scaler
Academy
Data Science
Neovarsity
New
Skill Test
Free Masterclass
Search for Articles, Topics
Node JS Tutorial
Sequelize in Node.js

Sequelize in Node.js

Learn via video courses
Topics Covered

Overview

Sequelize is an ORM (Object Relational Mapping) Tool for Node.Js. In nodejs, Sequelize helps us to generate complex queries easily for the databases like MySQL, PostgreSQL, MariaDB, Microsoft`` SQL Server, SQLite, etc. We can create a database, create a table, drop a table, create a row, update a row, delete a row, select a row, etc with the help of object and promise-based Sequelize in nodejs.

Pre-requisites

  • Nodejs and MySQL must be installed on your local development machine. -Basic understandingof javascript objects, promises, and datatypes.
  • Good understanding of basic MySQL queries and relational databases.

Note: Make Sure that you add a path to the environment variable PATH for Nodejs and MySQL.

Introduction

Relation databases (MySQL, PostgreSQL, MariaDB, Microsoft SQL Server, SQLite, etc.) use SQL queries to manage data. These queries may increase complexity and can be time-consuming. Also, We tend to forget these SQL queries. Therefore, We use the ORM (Object-Relational Mapping) tool that maps the javascript object with raw SQL queries of the relational database. Sequelize in nodejs is an object-relational mapper. Sequelize uses javascript objects and promises for database interaction. Hence, Sequelize is less complex, less time-consuming, fast, and easy to learn. Here, We will discuss managing relational databases with Sequelize in nodejs.

introduction to Sequelize in Node.js

Features of Sequelize

  • Sequelize is a promise-based ORM tool built for nodejs.
  • We don't need to remember the syntax for queries as Sequelize uses a javascript object.
  • Sequelize supports various databases i.e. MySQL, PostgreSQL, MariaDB, Microsoft SQL Server, and SQLite, etc.

Installing and Configuring Sequelize

Installing Sequelize

  • Create a new folder named node-with-sequelize and go to the current folder node-with-sequelize with CLI (Command Line Interface) as shown below:
  • Initialize node project that will create a package.json file inside the folder node-with-sequelize.
  • Create a file named server.js inside the folder node-with-sequelize. Now folder structure will look like this as shown below:
  • Install and save the npm package sequelize for the sequelize ORM and mysql2 driver for the MySQL database.

Note: Various databases exist. We use only the MySQL database for this article. However, We can use any other database by installing their respective drivers as shown below:

  • For PostgreSQL, npm install --save pg pg-hstore # Postgres
  • For MySQL,npm install --save mysql2
  • For MariaDB, npm install --save mariadb
  • For SQLite, npm install --save sqlite3
  • For Microsoft SQL Server, npm install --save tedious # Microsoft SQL Server

Creating a Sample Database

We installed and configured the MySQL database. So, We have a username and password to log in to the MySQL database on the local machine.

  • We will run the following command in the command prompt and replace the USERNAME with your database username.
  • After running the above command, MySQL asks for the password. So, We enter the password and press the Enter key.
  • We will create a new database named sequelize_db and check whether the database was created or not.
  • When we run SHOW DATABASES command, the list of databases will appear:

Note: Each SQL command must end with a semicolon and use QUIT to close the MySQL database connection.

Connecting to the MySQL Database

  • We will create a file named config.js and add the following code to the config.js file. You will assign your database username and database password to DATABASE_USERNAME and DATABASE_PASSWORD respectively.
  • Now, We will establish the connection between the node app and MySQL database using Sequelize. We will add the following code to the server.js file.
  • In the above code block, We are creating an instance of Sequelize to connect our node app to the MySQL database. Also, We use the authenticate() method to verify the database credentials and check whether the database is connected or not.

Note: dialect could be different for different databases. It could be MySQL, MariaDB, Postgres, mssql depending on the database we use.

Using Sequelize for Database Queries

  • We will create a new file named studentDB.js inside the folder node-with-sequelize and a database named student_database.
  • We add the above code to the studentDB.js file and connect the student_database to our node app.
  • Now, We will define a model named student using the define() method that makes the model name plural and create a table named students in the student_database. We add the following code to the studentDB.js file:
  • In the above code block, We are using the sequelize.define() method to create a model that represents a table and describes columns.

1. Inserting a New Row

  • We create a new file named addStudent.js inside node-with-sequelize folder and add the following code to the file addStudent.js.
  • In the above code block, We are using the create() method to add a new student to the students table. create() method allows us to pass a new student data in object format.

2. Selecting All Rows

  • We create a file named getStudents.js inside node-with-sequelize folder and add the following code to the file getStudents.js
  • In the above code block, we are using findAll() to retrieve all students from the students table. findAll() method accepts an optional object to provide additional information.
  • In the above example, We use the raw property that accepts boolean value: true or false. When we set raw to true, findall() doesn't return metadata while returning promises.

3. Selecting a Row Using where Clause

  • We create a file named getStudent.js and add the following code to the file getStudent.js.
  • In the above code block, We are using findOne() to retrieve a student by id. However, We can also use firstName and lastName to retrieve a student. findOne() return only the first entry if any duplicate entry exists in the table.

4. Deleting a Row Using where Clause

  • We create a file named removeStudent.js and add the following code to the file removeStudent.js.
  • In the above code block, we use the destroy() method to delete a particular student from the students table. destroy() method accepts an optional object to provide additional information. In the above example, We use the where property to get the id of a student and remove a student from the students table.

5. Deleting All Rows

  • We create a file named removeStudents.js and add the following code to the file removeStudents.js.
  • In the above example, We use the truncate property, set it to true, and remove all students from the students table.

Creating Associations Using Sequelize

Sequelize has four methods to create association between models:belongsTo(), hasOne(), hasMany(), belongsToMany().

1. One-to-One Relationship

  • Consider, We have a sample of students' data and three levels of grades: A, B, and C. Each student can have only one grade. Therefore, It is a one-to-one relationship.
  • In the above example, We are using the hasOne() method that adds the foreign key studentId to the target model Grade:
  • We can also use the belongsTo() method and it will add the foreign key gradeId to the source model Student:

2. One-to-Many Relationship

  • Again, Consider the same sample of students and three levels of grades: A, B, and C. We can give one grade to several students. Therefore, It is a one-to-many relationship.
  • In the above example, We use the hasMany() method that adds the foreign key gradeId to the target model Student:

Many-to-Many Relationship

  • Consider, We have three models: Student, Course, and Enrollment. Any student can enroll in any course. Therefore, We have many-to-many relationships.
  • In the above example, We establish the relationship between the Student model and the Course model through the Enrollment model. We use belongsToMany() method that adds the foreign key courseId and studentId to the Enrollment model.

Working with Raw Queries

1. Populate Table Using bulkCreate()

In the above code block, We use the bulkCreate() method to add multiple rows. bulkCreate() accepts two parameters: An array of Student Data and an optional object to pass additional information.

2. Array Replacement

In the above code block, We are using raw MySQL query with Sequelize. The symbol ? is used to retrieve student data and will be replaced with the array passed to the replacements property.

3. Object Replacement

In the above code block, We use the : name key to retrieve student data. The:name will be replaced with the key of the object passed to the replacements property.

Examples

Sequelize Model Definition

  • The model in Sequelize creates a table in the database that contains rows and columns. In the above code block, sequelize is the instance of Sequelize ORM. .define() contains three parameters:

Syntax

  • Model Name: It creates a new table in the database that contains rows and columns. It must be a string.

  • Attributes: It creates fields in the column with its datatypes and other parameters. It must be an object

  • Others: It contains any other optional parameters and it is a type of object.

  • Consider, We want to create a table of students that contains the columns: id, firstName, and lastName. So, We create a file named student.model.js and add the following code to the file student.model.js

Output When we run the node student.model command, it will make the model name plural and create a table named students in the student_database.

In student_database, It will create a students table as shown below:

Sequelize Dropping Table

  • We use .drop() method to delete the table from the database.
  • We will create a file named student.drop.js and add the following code to the file student.drop.js

Output When we run the node student.drop command, it will delete the students table from student_databse.

Sequelize bulkCreate

  • We use the bulkCreate() method to add multiple rows at once.
  • We will create a file named student.bulkCreate.js and add the following code to the file student.bulkCreate.js.

Output When we run the node student.bulkCreate command, it will add student data the to students table of student_database.

Sequelize findOne

  • We use the findOne() method to get one row by id.
  • We will create a file named student.findOne.js and add the following code to the file student.findOne.js.

Output

Sequelize with Async, Await

Output

Sequelize Count

  • We use the count() method to get no. of rows in the table.
  • We will create a file named countRow.js and add the following code to the file countRow.js.

Output

Sequelize Delete Row

  • We use the destroy() method to delete a row using id.
  • We will create a file named student.delete.js and add the following code to the file student.delete.js.

Output

Sequelize Update Row

  • We use the update() method to update a row using id.
  • We will create a file named student.update.js and add the following code to the file student.update.js.

Output

Sequelize FindAll

  • We use the findAll() to get the data from all rows.
  • We will create a file named student.findAll.js and add the following code to the file student.findAll.js.

Output:

Sequelize Select Columns With Attributes

  • We pass vathe lue to attributes property using findAll() and growsall the row with a particular column field.
  • We will create a file named student.findAllColAttr.js and add the following code to the file student.findAllColAttr.js.

Output:

Sequelize Order By Clause

  • the We will use order property with findAll() to get alpracticeows in a partiwe use We pass value to attributes property using findAll()rowsd get all the row with a particular column field.
  • We will create a file named student.orderByClause.js and add the following code to the file student.orderByClause.js.

Output

Take your coding skills to new heights with our Node.js Free course. Join today and learn to create efficient and high-performance backend systems.

Conclusion

  • Sequelize is a third-party package that uses an object to create a table in a database.
  • We can connect the node app to the database and manage the database without any command.
  • Sequelize model define and describe the table in a database.
  • Datatypes module of Sequelize helps to control the datatypes of the model's property.
  • Methods available in Sequelize help to manage tables, rows, and columns.
  • We can establish the relationship between models using methods: belongsTo(), hasOne(), hasMoney(), and belongsToMany().