Introduction
Sequelize is a Node.js-based Object Relational Mapper that makes it easy to work with MySQL, MariaDB, SQLite, PostgreSQL databases, and more. An Object Relational Mapper performs functions like handling database records by representing the data as objects. Sequelize has a powerful migration mechanism that can transform existing database schemas into new versions. Overall, Sequelize provides excellent support for database synchronization, eager loading, associations, transactions, and database migrations while reducing development time and preventing SQL injections.
In this tutorial, you will install and configure Sequelize with MySQL on your local development environment. Next, you will use Sequelize to create databases and models, as well as perform the insert, select, and delete operations. Then, you will create Sequelize associations for one-to-one, one-to-many, and many-to-many relationships. Finally, you will create Sequelize raw queries for array and object replacements.
Prerequisites
To complete this tutorial, you will need:
- A development environment running Node.js. To set this up, follow the tutorial How to Install Node.js and Create a Local Development Environment for your operating system.
- MySQL installed in your working environment. Follow the How To Install MySQL tutorial for your environment, including the steps to create a user.
- Familiarity with JavaScript and MySQL queries, which you can develop with the How To Code in JavaScript series and the tutorial An Introduction to Queries in MySQL.
- A basic understanding of relational database concepts, which you can find in the tutorial, Understanding Relational Databases.
This tutorial was tested on Node.js version 14.17.6 and npm version 6.14.15 on macOS Catalina.
Step 1 — Installing and Configuring Sequelize
In this step, you will install Sequelize and create the connection to your MySQL database. To do that, first you will create a Node.js application. Then, you will install Sequelize, configure the MySQL database, and develop a simple application.
Installing Sequelize
Begin by creating a project folder. In this example, you can use hello-world. Once the folder is created, navigate to the folder using the terminal:
Then, create a sample Node.js application using the following command:
Next, you will be prompted to answer some set-up questions. Use the following output for your configuration. Press ENTER to use the displayed default value and be sure to set the main entry point as server.js. This creates a project structure that is easy to maintain.
The output will look as follows, which will populate the package.json file:
Next, create an empty server.js file inside the project folder:
After following the previous steps, your final folder structure will look like this:
hello-world/
├─ package.json
├─ server.js
Now you can install Sequelize with the following command:
Note: This command installs version 6.11.0. If you need to install the latest version, run npm i sequelize.
After these updates, the package.json file now looks like this:
In the dependencies section, you will now see a Sequelize dependency.
You have set up the project and installed Sequelize. Next, you’ll create a sample database to connect to.
Creating a Sample Database
As part of the prerequisites, you installed and configured MySQL, which included creating a user. Now you will create an empty database.
To do that, first, you need to log in to your MySQL instance. If you are running remotely, you can use your preferred tool. If you are using a locally running MySQL instance, you can use the following command, replacing your_username with your MySQL username:
-u is username and the -p option is passed if the account is secured with a password.
The MySQL server will ask for your database password. Type your password and press ENTER.
Once you’re logged in, create a database called hello_world_db using the following command:
To verify whether you have created the database successfully, you can use this command:
Your output will be similar to this:
+--------------------+
| Database |
+--------------------+
| hello_world_db |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
After creating the sample database, disconnect from the MySQL server:
Now, you need to install a manual driver for your database of choice. As Sequelize provides ORM features only, it doesn’t include built-in database drivers. Therefore, you’ll need to install drivers according to your preference. To do that, navigate to the project directory using the terminal and install the MySQL driver to the project using the following command:
In this case, you are using the driver for MySQL.
Note: Since this tutorial uses MySQL as the database, you are using a driver for that. Depending on your database, you can manually install the driver like so:
npm install --save pg pg-hstore # Postgresnpm install --save mysql2npm install --save mariadbnpm install --save sqlite3npm install --save tedious # Microsoft SQL Server
Now that you have a sample database, you can create your first Sequelize application with database connectivity.
Connecting to the MySQL Database
In this section, you will connect the Node.js application to the MySQL database using Sequelize.
To connect to the database, open server.js for editing using nano or your preferred code editor:
Here, you will create a database connection in your application using a Sequelize instance. In the new Sequelize() method, pass the MySQL server parameters and database credentials as follows, replacing DATABASE_USERNAME and DATABASE_PASSWORD with the credentials of your MySQL user:
host is where the MySQL server is hosted, so you’ll need to provide a server URL or an IP address. If you are using a locally installed MySQL server, you can replace DATABASE_HOST with localhost or 127.0.0.1 as the value.
Similarly, if you are using a remote server, make sure to replace database connection values accordingly with the appropriate remote server details.
Note: If you are using any other database server software, you can replace the dialect parameter accordingly. `dialect: ‘mysql’, ‘mariadb’, ‘postgres’, ‘mssql’.
Next, call a promise-based authenticate() method to instantiate a database connection to the application. To do that, add the following code block to the your server.js file:
The authenticate() method is used to connect with the database and tests whether the given credentials are correct. Here, the database connection is open by default and the same connection can be used for all queries. Whenever you need to close the connection, call the sequelize.close() method after this authenticate() call. To learn more about Sequelize, please see their getting started guide.
Most of the methods provided by Sequelize are asynchronous. That means you can run processes in your application while an asynchronous code block is in its execution time. Also, after the successful asynchronous code block execution, it returns a promise, which is the value returned at the end of a process. Therefore, in asynchronous code blocks, you can use then(), catch(), and finally() to return the processed data.
At this point, the server.js file will look like the following:
Save and close your file.
In the project directory, run the server.js application by running the following command:
Your output will look like this:
Connection has been established successfully!
You have created the database connection successfully.
In this step, you installed Sequelize, created a sample database, and used Sequelize to connect with the database. Next, you will work with models in Sequelize.
Step 2 — Creating a Database Table Using Sequelize
Now that you have created a sample MySQL database, you can use Sequelize to create a table and populate it with data. In Sequelize, database tables are referred to as models. A model is an abstraction that represents a table of the database. Models define several things to Sequelize, such as the name of the table, column details, and data types. In this step, you will create a Sequelize model for book data.
To begin, create a new file called book.model.js in the project directory:
Similar to the the previous step, add a Sequelize code for database initiation with a new import for DataTypes at the top of the file:
Sequelize contains many built-in data types. To access those data types, you add an import for DataTypes. This tutorial refers to some frequently used data types, such as STRING, INTEGER, and DATEONLY. To learn more about other supported data types, you can refer to the official Sequelize documentation.
Then, include the lines you used previously to create a connection to your MySQL database, updating your MySQL credentials accordingly:
Next, you will create a model called books, which includes title, author, release_date, and subject ID. To do that, use the sequelize.define() method as shown:
The sequelize.define() method defines a new model, which represents a table in the database. This code block creates a table called books and stores the book records according to the title, author, release_date, and subject.
In this code, allowNull shows that the model column value cannot be null. Likewise, if you need to set such a value, you can use defaultValue: "value".
Next, you’ll add the book model to your database. To do that, you’ll use the sync() method as follows:
In the sync() method, you’re asking Sequelize to do a few things to the database. With this call, Sequelize will automatically perform an SQL query to the database and create a table, printing the message Book table created successfully!.
As mentioned, the sync() method is a promise-based method, which means it can also perform error handling. In this code block, you’ll check whether the table is created successfully. If not, it will return an error via the catch method and print it on the output.
Note: You can manage model synchronization by passing force parameters to force the creation of a new table if it does not exist, or else use an existing one. Here are some examples, which may be helpful to you while working with Sequelize:
model.sync(): This creates the table if it doesn’t exist already.model.sync({ force: true }): This creates the table by dropping it if the same table exists already.
The final code will look like this:
Save and close your file.
Run your application by using the following command:
You will get the following output in your command line:
Executing (default): SELECT 1+1 AS result
Executing (default): CREATE TABLE IF NOT EXISTS `books` (`id` INTEGER NOT NULL auto_increment , `title` VARCHAR(255) NOT NULL, `author` VARCHAR(255) NOT NULL, `release_date` DATE, `subject` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Connection has been established successfully.
Executing (default): SHOW INDEX FROM `books`
Book table created successfully!
In the output, you will see the return log contains the message, Book table created successfully!. You can verify this by checking your database to see the new books table created in the hello_world_db database.
To verify the creation of the new table, log into your MySQL instance:
After inputting your password, change into the sample database:
And then run the command to show tables:
Your output will be similar to this:
+---------------------------+
| Tables_in_hello_world_db |
+---------------------------+
| books |
+---------------------------+
1 row in set (0.00 sec)
Finally, disconnect from the MySQL server:
You have verified that the book model creation was successful. Using this process, you can create any number of models by following the same procedure.
In this step, you created a model in a database and initiated working with a model using built-in methods. You also used Sequelize-supported data types to define your model. Next, you will work with basic model queries.
Step 3 — Using Sequelize for Database Queries
In this step, you will use the Sequelize built-in queries for insertion, selection, selection with conditional clauses, and deletion.
Inserting a New Record
In the previous step, you created a book model inside the database. In this section, you’ll insert data into this model.
To get started, copy the contents of book.model.js from the previous step. Create a new file called book.controller.js to handle the query logic. Add the code from book.model.js to book.controller.js.
In book.controller.js, locate the sync() method. In the sync() method, add the following highlighted lines:
Here, you insert a new book record into the books model you’ve already created using the sync() method, which supports adding new records to previously created models. Once the sync() method executes successfully, it runs the then() method. Inside the then() method, you call create() method to insert the new records to the model.
You use the create() method to pass the data you need to add to the database as an object. The highlighted section of code will insert a new entry to your existing books table. In this example, you add Clean Code by Robert Cecil Martin, which has been categorized with the subject ID of 3. You can use the same code, updated with information for other books, to add new records to your database.
Save and close the file.
Run the application using the following command:
Your output will look similar to the following:
books {
dataValues:
{ id: 1,
title: 'Clean Code',
author: 'Robert Cecil Martin',
release_date: '2021-12-14',
subject: 3,
updatedAt: 2021-12-14T10:12:16.644Z,
...
}
You inserted a new record to the model you created in the database. You can continue adding multiple records using the same process.
Selecting All Records
In this section, you will select and get all the book records from the database using the findAll() method. To do that, first open book.controller.js and remove the previous Book.create() method. In the sync() method, add the Book.findAll() method as shown:
Save and close the file.
Next, run the application again using the following command:
Your output will look similar to the following:
[
books {
dataValues: {
id: 1,
title: 'Clean Code',
author: 'Robert Cecil Martin',
release_date: '2020-01-01',
subject: 3,
createdAt: 2021-02-22T09:13:55.000Z,
updatedAt: 2021-02-22T09:13:55.000Z
},
_previousDataValues: {
id: 1,
title: 'Clean Code',
author: 'Robert Cecil Martin',
release_date: '2020-01-01',
subject: 3,
createdAt: 2021-02-22T09:13:55.000Z,
updatedAt: 2021-02-22T09:13:55.000Z
},
...
]
The output contains all book data as an array object. You successfully used the Sequelize findAll() method to return all book data from the database.
Selecting with the where Clause
In this section, you will select values with conditions using the where clause. The where clause is used to specify a condition while fetching data. For this tutorial, you will get a book by a specific record ID from the database using the findOne() method.
To do that, open book.controller.js for editing, delete the findAll() method, and add the following lines:
Here, you select a specific book record from the database using the findOne() method with the where option. In this example, you are retrieving the book data whose id is equal to 1.
Save and close the file.
Next, run the application:
Your output will look similar to the following:
books {
dataValues: {
id: 1,
title: 'Clean Code',
author: 'Robert Cecil Martin',
release_date: '2020-01-01',
subject: 'Science',
createdAt: 2021-02-22T09:13:55.000Z,
updatedAt: 2021-02-22T09:13:55.000Z
},
...
}
You have successfully used where clauses to get data from Sequelize models. You can use the where clause in the database application to capture conditional data.
Deleting a Record
To delete a specific record from the database model, you use the destroy() method with the where option. To do that, open book.controller.js, remove the findOne() method, and add the following highlighted lines:
Here, you remove a book record from the database by using the destroy() method with the where option and passing in the id of the book to remove. You are going to remove the book record whose id equals 2.
Save and close the file.
Next, run the application:
Your output will look like the following:
Successfully deleted record.
The record has been deleted.
In this step, you experimented with your database model and model querying. You initiated the database, created models, inserted records, retrieved records, retrieved records with conditions using the where clause, and deleted selected records. With this knowledge of Sequelize, you will now create associations in Sequelize. After that, you will be able to define and work with a variety of relationships using Sequelize models.
Step 4 — Creating Associations Using Sequelize
In this step, you will use the standard association types that Sequelize supports: one-to-one, one-to-many, and many-to-many associations. You’ll use sample data about students, courses, and grade levels.
Sequelize uses association types based on the following database relationships:
-
one-to-one relationship: A one-to-one relationship means a record in one table is associated with exactly one record in another table. In terms of Sequelize, you can use
belongsTo()andhasOne()associations to create this type of relationship. -
one-to-many relationship: A one-to-many relationship means a record in one table is associated with multiple records in another table. With Sequelize, you can use
hasMany()associations methods to create this type of relationship. -
many-to-many relationship: A many-to-many relationship means multiple records in one table are associated with multiple records in another table. With Sequelize, you can use
belongsToMany()associations to create this type of relationship.
Before creating these associations, you will first create a new database called student_db and add new models and some sample data for students, courses, and grade level.
To create the database, follow the same process in Step 1 — Installing and Configuring Sequelize to log into MySQL and create a database called student_db. Once the new database has been created, log out of MySQL. Next, you’ll start creating database associations.
Creating a One-to-One Relationship with belongsTo()
In this section, you will create a one-to-one relationship using Sequelize models. Imagine you want to get one student’s details along with their grade level. Since one student can have only one grade level, this type of association is a one-to-one relationship and you can use the belongsTo() method.
Note: There is a difference between belongsTo() and hasOne(). belongsTo() will add the foreignKey on the source table, whereas hasOne() will add it to the target table. In any case, if both relationships are used at the same time, it will work as Sequelize bidirectional one-to-one relationships.
The belongsTo() method allows you to create a one-to-one relationship between two Sequelize models. In this example, you are using the Student and Grade models.
Create a new file called one_to_one.js. As you did in the previous section, Connecting to the MySQL Database, include the lines to create a connection to the database and authenticate your MySQL user to the top of the file. Be sure to update the MySQL credentials as needed:
In this section, you will create three models in the new student_db database: Student, Grade, and Course. You’ll begin by creating the Student and Grade models. Later in this step, you’ll create the Courses model.
For the Student model, add the following code block to one_to_one.js:
This student model contains two columns: student_id and name.
Next, add a code block for the Grade model:
The Grade model contains the column grade.
To demonstrate the associations, you’ll need to add sample data to the database. For that, you’ll use the bulk() method. Rather than inserting data into the rows one by one, the bulkCreate() method allows you to insert multiple rows into your database models at once.
So now, import the Grade and Student data to their respective models in the database as shown:
Here, you provide sample data and import the data into the Student and Grade models. With your database, models, and sample data in place, you’re ready to create associations.
In one-to-one.js, add the following line below the student_data block:
Next, you will need to check whether the association is working properly. To do that, you can retrieve all students’ data with associated grade levels by passing the include parameter inside the findAll() method.
Since you need to get the student grade level, you’ll pass Grade as the model. In the sequelize.sync() method, add the highlighted lines as shown:
The complete code looks like the following:
Save and close your file.
Run the file by using the following command:
The output will be long, and you will see all students’ data with grade levels. Here is a snippet of the output showing student data:
students {
dataValues:
{ student_id: '3e786a8f-7f27-4c59-8e9c-a8c606892288',
name: 'Sam Lewis',
createdAt: 2021-12-16T08:49:38.000Z,
updatedAt: 2021-12-16T08:49:38.000Z,
gradeId: 1,
grade: [grades] },
_previousDataValues:
...
Depending on the command line tools you are using, the output may print as an expanded view or not. If it is an expanded view, it prints the expanded grade object as the output.
In this section, you created a one-to-one relationship using the Student.belongsTo(Grade); method call and got the details according to the association you created.
Creating a One-to-Many Relationship with hasMany()
In this section, you will create a one-to-many relationship using Sequelize models. Imagine you’d like to get all the students associated with a selected grade level. Since one specific grade level can have multiple students, this is a one-to-many relationship.
To get started, copy the contents of one_to_one.js into a new file called one_to_many.js. In one_to_many.js, remove the lines after the student_data block. Your one_to_many.js file will look like this:
After the student_data block, use the hasMany() method to create a new relationship:
The hasMany() method allows you to create a one-to-many relationship between two Sequelize models. Here, you are using the Grade and Student models.
Next, add the sequelize.sync() method with the findAll() method below the hasMany() line:
Here you are trying to access all the students in a particular grade level—in this case, all the students in grade 9. You also added the Student model in the include option.
Here is the complete code:
Save and close your file.
Run the file with the following command:
The output will look similar to the following. It will be quite long, but all students in grade 9 will be returned as follows:
[ grades {
dataValues:
{ id: 1,
grade: 9,
createdAt: 2021-12-20T05:12:31.000Z,
updatedAt: 2021-12-20T05:12:31.000Z,
students:
[ students {
dataValues:
{ student_id: '8a648756-4e22-4bc0-8227-f590335f9965',
name: 'Sam Lewis',
createdAt: 2021-12-20T05:12:31.000Z,
updatedAt: 2021-12-20T05:12:31.000Z,
gradeId: 1 },
...
students {
dataValues:
{ student_id: 'f0304585-91e5-4efc-bdca-501b3dc77ee5',
name: 'Max Butler',
createdAt: 2021-12-20T05:12:31.000Z,
updatedAt: 2021-12-20T05:12:31.000Z,
gradeId: 1 },
...
In this section, you created a one-to-many relationship using the Grade.hasMany(Student); method call. In the output, you retrieved the details according to the association you created.
Creating Many-to-Many Relationships with belongsToMany()
In this section, you will create many-to-many relationships using Sequelize models. As an example, imagine a situation where students are enrolled in courses. One student can enroll in many courses and one course can have many students. This is a many-to-many relationship. To implement this using Sequelize, you will use the models Student, Course, and StudentCourse with the belongsToMany() method.
To get started, create a file called many_to_many.js and add the database initiation and authentication code blocks as follows. (You can reuse the code blocks from the previous one_to_many.js example.) Make sure to update the highlighted database connection values as needed.
Next, you’ll create the database models for many-to-many relationships: Student and Course. Then you’ll add some sample data to those models.
Here, you create the Student and Course models and provide some sample data. You also set a courseID, which you will use to retrieve students according to this relationship type.
Finally, you defined a new model called StudentCourse, which manages the relationship data between Student and Course. In this example, studentId 1 is enrolled in courseId 1 and courseId 2.
You have completed the database initiation and added sample data to the database. Next, create many-to-many relationships using the belongsToMany() method as shown:
Within the belongsToMany() method, you pass the through configuration with the name of the model as the configuration option. In this case, it is StudentCourse. This is the table that manages the many-to-many relationships.
Finally, you can check whether the association is working properly by retrieving all course data with associated students. You’ll do that by passing the include parameter inside the findAll() method. Add the following lines to many_to_many.js:
The complete code looks like the following:
