How To Use Sequelize with Node.js and MySQL

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 v24.11.1 and npm version 11.6.2.

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:

mkdir hello-world
cd hello-world

Then, create a sample Node.js application using the following command:

npm init

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:

{
  "name": "hello-world",
  "version": "1.0.0",
  "description": "",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC"
}

Next, create an empty server.js file inside the project folder:

touch server.js

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:

npm i sequelize

After these updates, the package.json file now looks like this:

{
  "name": "hello-world",
  "version": "1.0.0",
  "description": "",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "node server.js"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "sequelize": "^6.37.7"
  }
}

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:

mysql -u your_username -p

-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:

CREATE DATABASE hello_world_db;

To verify whether you have created the database successfully, you can use this command:

SHOW DATABASES;

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:

mysql> QUIT

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:

npm install --save mysql2

In this case, you are using the driver for MySQL.

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:

nano server.js

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:

const Sequelize = require("sequelize");
const sequelize = new Sequelize(
 'hello_world_db',
 'DATABASE_USERNAME',
 'DATABASE_PASSWORD',
  {
    host: 'DATABASE_HOST',
    dialect: 'mysql'
  }
);

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.

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:

...

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

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:

const Sequelize = require("sequelize");

const sequelize = new Sequelize(
   'hello_world_db',
   'DATABASE_USERNAME',
   'DATABASE_PASSWORD',
    {
      host: 'DATABASE_HOST',
      dialect: 'mysql'
    }
  );

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

Save and close your file.

In the project directory, run the server.js application by running the following command:

node server.js

Your output will look like this:

Output
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.

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:

nano book.model.js

Similar to the previous step, add the Sequelize code for database initiation with a new import for DataTypes at the top of the file:

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

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:

...

const sequelize = new Sequelize(
   'hello_world_db',
   'DATABASE_USERNAME',
   'DATABASE_PASSWORD',
    {
      host: 'DATABASE_HOST',
      dialect: 'mysql'
    }
  );

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

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:

...

const Book = sequelize.define("books", {
   title: {
     type: DataTypes.STRING,
     allowNull: false
   },
   author: {
     type: DataTypes.STRING,
     allowNull: false
   },
   release_date: {
     type: DataTypes.DATEONLY,
   },
   subject: {
     type: DataTypes.INTEGER,
   }
});

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:

...

sequelize.sync().then(() => {
   console.log('Book table created successfully!');
}).catch((error) => {
   console.error('Unable to create table : ', error);
});

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.

The final code will look like this:

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

const sequelize = new Sequelize(
   'hello_world_db',
   'DATABASE_USERNAME',
   'DATABASE_PASSWORD',
    {
      host: 'DATABASE_HOST',
      dialect: 'mysql'
    }
  );

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

const Book = sequelize.define("books", {
   title: {
     type: DataTypes.STRING,
     allowNull: false
   },
   author: {
     type: DataTypes.STRING,
     allowNull: false
   },
   release_date: {
     type: DataTypes.DATEONLY,
   },
   subject: {
     type: DataTypes.INTEGER,
   }
});

sequelize.sync().then(() => {
   console.log('Book table created successfully!');
}).catch((error) => {
   console.error('Unable to create table : ', error);
});

Save and close your file.

Run your application by using the following command:

node book.model.js

You will get the following output in your command line:

Output
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:

mysql -u YOUR_USERNAME -p

After inputting your password, change into the sample database:

USE hello_world_db;

And then run the command to show tables:

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:

mysql> QUIT

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.

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:

...

sequelize.sync().then(() => {
   console.log('Book table created successfully!');

   Book.create({
       title: "Clean Code",
       author: "Robert Cecil Martin",
       release_date: "2021-12-14",
       subject: 3
   }).then(res => {
       console.log(res)
   }).catch((error) => {
       console.error('Failed to create a new record : ', error);
   });

}).catch((error) => {
   console.error('Unable to create table : ', error);
});

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:

node book.controller.js

Your output will look similar to the following:

Output
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:

...

sequelize.sync().then(() => {

    Book.findAll().then(res => {
        console.log(res)
    }).catch((error) => {
        console.error('Failed to retrieve data : ', error);
    });

}).catch((error) => {
    console.error('Unable to create table : ', error);
});

...

Save and close the file.

Next, run the application again using the following command:

node book.controller.js

Your output will look similar to the following:

Output
[
  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:

...

sequelize.sync().then(() => {

    Book.findOne({
        where: {
            id: 1
        }
    }).then(res => {
        console.log(res)
    }).catch((error) => {
        console.error('Failed to retrieve data : ', error);
    });

}).catch((error) => {
    console.error('Unable to create table : ', error);
});

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:

node book.controller.js

Your output will look similar to the following:

Output
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:

...
sequelize.sync().then(() => {

  Book.destroy({
      where: {
        id: 2
      }
  }).then(() => {
      console.log("Successfully deleted record.")
  }).catch((error) => {
      console.error('Failed to delete record : ', error);
  });

}).catch((error) => {
    console.error('Unable to create table : ', error);
});

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:

node book.controller.js

Your output will look like the following:

Output
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.

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() and hasOne() 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 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.

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:

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

const sequelize = new Sequelize(
   'student_db',
   'DATABASE_USERNAME',
   'DATABASE_PASSWORD',
    {
      host: 'DATABASE_HOST',
      dialect: 'mysql'
    }
  );

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

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:

...
const Student = sequelize.define("students", {
   student_id: {
       type: DataTypes.UUID,
       defaultValue: DataTypes.UUIDV4,
       primaryKey: true,
   },
   name: {
       type: DataTypes.STRING,
       allowNull: false
   }
});

This student model contains two columns: student_id and name.

Next, add a code block for the Grade model:

...
const Grade = sequelize.define("grades", {
   grade: {
       type: DataTypes.INTEGER,
       allowNull: false
   }
});

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:

...
const grade_data = [{grade : 9}, {grade : 10}, {grade : 11}]

const student_data = [
   {name : "John Baker", gradeId: 2},
   {name : "Max Butler", gradeId: 1},
   {name : "Ryan Fisher", gradeId: 3},
   {name : "Robert Gray", gradeId: 2},
   {name : "Sam Lewis", gradeId: 1}
]

sequelize.sync({ force: true }).then(() => {
   Grade.bulkCreate(grade_data, { validate: true }).then(() => {
       Student.bulkCreate(student_data, { validate: true }).then(() => {
           …
       }).catch((err) => { console.log(err); });
   }).catch((err) => { console.log(err); });
}).catch((error) => {
   console.error('Unable to create the table : ', error);
});

Here, you provide sample data and insert 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:

...
Student.belongsTo(Grade);

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:

...
sequelize.sync({ force: true }).then(() => {
   Grade.bulkCreate(grade_data, { validate: true }).then(() => {
       Student.bulkCreate(student_data, { validate: true }).then(() => {
           Student.findAll({
               include: [{
                   model: Grade
               }]
           }).then(result => {
               console.log(result)
           }).catch((error) => {
               console.error('Failed to retrieve data : ', error);
           });
       }).catch((err) => { console.log(err); });
   }).catch((err) => { console.log(err); });
}).catch((error) => {
   console.error('Unable to create the table : ', error);
});

The complete code looks like the following:

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

const sequelize = new Sequelize(
   'student_db',
   'DATABASE_USERNAME',
   'DATABASE_PASSWORD',
    {
      host: 'DATABASE_HOST',
      dialect: 'mysql'
    }
  );
sequelize.authenticate().then(() => {
   console.log('Connection has been established successfully.');
}).catch((error) => {
   console.error('Unable to connect to the database: ', error);
});

const Student = sequelize.define("students", {
   student_id: {
       type: DataTypes.UUID,
       defaultValue: DataTypes.UUIDV4,
       primaryKey: true,
   },
   name: {
       type: DataTypes.STRING,
       allowNull: false
   }
});

const Grade = sequelize.define("grades", {
   grade: {
       type: DataTypes.INTEGER,
       allowNull: false
   }
});

const grade_data = [{grade : 9}, {grade : 10}, {grade : 11}]

const student_data = [
   {name : "John Baker", gradeId: 2},
   {name : "Max Butler", gradeId: 1},
   {name : "Ryan Fisher", gradeId: 3},
   {name : "Robert Gray", gradeId: 2},
   {name : "Sam Lewis", gradeId: 1}
]

// One-To-One association
Student.belongsTo(Grade);

sequelize.sync({ force: true }).then(() => {
   Grade.bulkCreate(grade_data, { validate: true }).then(() => {
       Student.bulkCreate(student_data, { validate: true }).then(() => {
           Student.findAll({
               include: [{
                   model: Grade
               }]
           }).then(result => {
               console.log(result)
           }).catch((error) => {
               console.error('Failed to retrieve data : ', error);
           });
       }).catch((err) => { console.log(err); });
   }).catch((err) => { console.log(err); });
}).catch((error) => {
   console.error('Unable to create the table : ', error);
});

Save and close your file.

Run the file by using the following command:

node one_to_one.js

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:

Output
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:

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

const sequelize = new Sequelize(
   'student_db',
   'DATABASE_USERNAME',
   'DATABASE_PASSWORD',
    {
      host: 'DATABASE_HOST',
      dialect: 'mysql'
    }
  );

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

const Student = sequelize.define("students", {
   student_id: {
       type: DataTypes.UUID,
       defaultValue: DataTypes.UUIDV4,
       primaryKey: true,
   },
   name: {
       type: DataTypes.STRING,
       allowNull: false
   }
});
const Grade = sequelize.define("grades", {
   grade: {
       type: DataTypes.INTEGER,
       allowNull: false
   }
});

const grade_data = [ {grade : 9}, {grade : 10}, {grade : 11}]

const student_data = [
   {name : "John Baker", gradeId: 2},
   {name : "Max Butler", gradeId: 1},
   {name : "Ryan Fisher", gradeId: 3},
   {name : "Robert Gray", gradeId: 2},
   {name : "Sam Lewis", gradeId: 1}
]

After the student_data block, use the hasMany() method to create a new relationship:

...
Grade.hasMany(Student)

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:

...
sequelize.sync({ force: true }).then(() => {
   Grade.bulkCreate(grade_data, { validate: true }).then(() => {
       Student.bulkCreate(student_data, { validate: true }).then(() => {
           Grade.findAll({
               where: {
                   grade: 9
               },
               include: [{
                   model: Student
               }]
           }).then(result => {
               console.dir(result, { depth: 5 });
           }).catch((error) => {
               console.error('Failed to retrieve data : ', error);
           });
       }).catch((err) => { console.log(err); });
   }).catch((err) => { console.log(err); });
}).catch((error) => {
   console.error('Unable to create table : ', error);
});

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:

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

const sequelize = new Sequelize(
   'student_db',
   'DATABASE_USERNAME',
   'DATABASE_PASSWORD',
    {
      host: 'DATABASE_HOST',
      dialect: 'mysql'
    }
  );

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

const Student = sequelize.define("students", {
   student_id: {
       type: DataTypes.UUID,
       defaultValue: DataTypes.UUIDV4,
       primaryKey: true,
   },
   name: {
       type: DataTypes.STRING,
       allowNull: false
   }
});
const Grade = sequelize.define("grades", {
   grade: {
       type: DataTypes.INTEGER,
       allowNull: false
   }
});

const grade_data = [ {grade : 9}, {grade : 10}, {grade : 11}]

const student_data = [
   {name : "John Baker", gradeId: 2},
   {name : "Max Butler", gradeId: 1},
   {name : "Ryan Fisher", gradeId: 3},
   {name : "Robert Gray", gradeId: 2},
   {name : "Sam Lewis", gradeId: 1}
]

// One-To-Many relationship
Grade.hasMany(Student);

sequelize.sync({ force: true }).then(() => {
   Grade.bulkCreate(grade_data, { validate: true }).then(() => {
       Student.bulkCreate(student_data, { validate: true }).then(() => {
           Grade.findAll({
               where: {
                   grade: 9
               },
               include: [{
                   model: Student
               }]
           }).then(result => {
               console.dir(result, { depth: 5 });
           }).catch((error) => {
               console.error('Failed to retrieve data : ', error);
           });
       }).catch((err) => { console.log(err); });
   }).catch((err) => { console.log(err); });
}).catch((error) => {
   console.error('Unable to create table : ', error);
});

Save and close your file.

Run the file with the following command:

node one_to_many.js

The output will look similar to the following. It will be quite long, but all students in grade 9 will be returned as follows:

Output
[ 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.

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

const sequelize = new Sequelize(
   'student_db',
   'DATABASE_USERNAME',
   'DATABASE_PASSWORD',
    {
      host: 'DATABASE_HOST',
      dialect: 'mysql'
    }
);

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

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.

...

const Student = sequelize.define("students", {
    student_id: {
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4,
    },
    name: {
        type: DataTypes.STRING,
        allowNull: false
    }
});

const Course = sequelize.define("courses", {
    course_name: {
        type: DataTypes.STRING,
        allowNull: false
    }
});

const StudentCourse = sequelize.define('StudentCourse', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
      allowNull: false
    }
  });

const course_data = [
    {course_name : "Science"},
    {course_name : "Maths"},
    {course_name : "History"}
]

const student_data = [
    {name : "John Baker", courseId: 2},
    {name : "Max Butler", courseId: 1},
    {name : "Ryan Fisher", courseId: 3},
    {name : "Robert Gray", courseId: 2},
    {name : "Sam Lewis", courseId: 1}
]

const student_course_data = [
    {studentId : 1, courseId: 1},
    {studentId : 2, courseId: 1},
    {studentId : 2, courseId: 3},
    {studentId : 3, courseId: 2},
    {studentId : 1, courseId: 2},
]

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:

...
Course.belongsToMany(Student, { through: 'StudentCourse'})
Student.belongsToMany(Course, { through: 'StudentCourse'})

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:

...
sequelize.sync({ force: true }).then(() => {
    Course.bulkCreate(course_data, { validate: true }).then(() => {
        Student.bulkCreate(student_data, { validate: true }).then(() => {
            StudentCourse.bulkCreate(student_course_data, { validate: true }).then(() => {
                Course.findAll({
                    include: {
                        model: Student,
                    },
                }).then(result => {
                    console.log(result);
                }).catch((error) => {
                    console.error('Failed to retrieve data : ', error);
                });
            }).catch((error) => {
                console.log(error);
            });
        }).catch((error) => {
            console.log(error);
        });
    }).catch((error) => {
        console.log(error);
    });
}).catch((error) => {
    console.error('Unable to create table : ', error);
});

The complete code looks like the following:

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

const sequelize = new Sequelize(
   'student_db',
   'DATABASE_USERNAME',
   'DATABASE_PASSWORD',
    {
      host: 'DATABASE_HOST',
      dialect: 'mysql'
    }
);

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

const Student = sequelize.define("students", {
    student_id: {
       type: DataTypes.UUID,
       defaultValue: DataTypes.UUIDV4,
    },
    name: {
       type: DataTypes.STRING,
       allowNull: false
    }
});

const Course = sequelize.define("courses", {
    course_name: {
        type: DataTypes.STRING,
        allowNull: false
    }
});

const StudentCourse = sequelize.define('StudentCourse', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
      allowNull: false
    }
  });

const course_data = [
    {course_name : "Science"},
    {course_name : "Maths"},
    {course_name : "History"}
]

const student_data = [
    {name : "John Baker", courseId: 2},
    {name : "Max Butler", courseId: 1},
    {name : "Ryan Fisher", courseId: 3},
    {name : "Robert Gray", courseId: 2},
    {name : "Sam Lewis", courseId: 1}
]

const student_course_data = [
    {studentId : 1, courseId: 1},
    {studentId : 2, courseId: 1},
    {studentId : 2, courseId: 3},
    {studentId : 3, courseId: 2},
    {studentId : 1, courseId: 2},
]

Course.belongsToMany(Student, { through: 'StudentCourse'})
Student.belongsToMany(Course, { through: 'StudentCourse'})

sequelize.sync({ force: true }).then(() => {
    Course.bulkCreate(course_data, { validate: true }).then(() => {
        Student.bulkCreate(student_data, { validate: true }).then(() => {
            StudentCourse.bulkCreate(student_course_data, { validate: true }).then(() => {
                Course.findAll({
                    include: {
                        model: Student,
                    },
                }).then(result => {
                    console.log(result);
                }).catch((error) => {
                    console.error('Failed to retrieve data : ', error);
                });
            }).catch((error) => {
                console.log(error);
            });
        }).catch((error) => {
            console.log(error);
        });
    }).catch((error) => {
        console.log(error);
    });
}).catch((error) => {
    console.error('Unable to create table : ', error);
});

Save and close the file.

Run the file using the following command:

node many_to_many.js

The output will be long, but will look something similar to the following:

Output
[ courses {
    dataValues:
     { id: 1,
       course_name: 'Science',
       createdAt: 2022-05-11T04:27:37.000Z,
       updatedAt: 2022-05-11T04:27:37.000Z,
       students: [Array] },
    _previousDataValues:
     { id: 1,
       course_name: 'Science',
       createdAt: 2022-05-11T04:27:37.000Z,
       updatedAt: 2022-05-11T04:27:37.000Z,
       students: [Array] },
    _changed: Set {},
    _options:
     { isNewRecord: false,
       _schema: null,
       _schemaDelimiter: '',
       include: [Array],
       includeNames: [Array],
       includeMap: [Object],
       includeValidated: true,
       attributes: [Array],
       raw: true },
    isNewRecord: false,
    students: [ [students], [students] ] },
  courses {
    dataValues:
     { id: 2,
       course_name: 'Maths',
       createdAt: 2022-05-11T04:27:37.000Z,
       updatedAt: 2022-05-11T04:27:37.000Z,
       students: [Array] },
    _previousDataValues:
...

As you can see in this output, the courses with associated students were retrieved. Within the courses block, you will see separate id values that indicate each course. For example, id: 1 is connected to the course_name: Science for the Science class, whereas id: 2 is the Maths class, and so on.

In the database, you can see the three generated tables with the sample data you inserted.

In this step, you used Sequelize to create one-to-one, one-to-many, and many-to-many associations. Next, you will work with raw queries.

In this step, you will work with raw queries in Sequelize. In previous steps, you used Sequelize built-in methods, such as insert() and findAll(), to handle data insertion and selection from the database. You may have noticed that those methods follow a specific pattern for writing a query. However, with the use of raw queries, you don’t need to worry about Sequelize built-in methods and patterns. Using your knowledge of SQL queries, you can conduct a range of queries in Sequelize from simple to more advanced.

Here is an example of raw queries that perform the action of selecting all values from a particular table, deleting the selected values according to the condition, and updating the table with the given values.

SELECT * FROM table_name;
DELETE FROM table_name WHERE condition;
UPDATE table_name SET y = 42 WHERE x = 12;

In Sequelize, raw queries can be used with primarily two methodologies: array replacement and object replacement. When you are passing values to the SQL query, you can use either an array or an object to do that replacement.

Before writing a raw query, you will first need to supply student data in a sample database. Following the previous section, Creating a Sample Database, log in to MySQL, create a database called sample_student_db, and log out of MySQL.

Next, you’ll add some raw data to start working with raw queries. Create a new file called add_student_records.js and add the following code blocks, which contain the previously discussed Sequelize methods of authenticate(), sync(), and bulkCreate().

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

const sequelize = new Sequelize(
   'sample_student_db',
   'DATABASE_USERNAME',
   'DATABASE_PASSWORD',
    {
      host: 'DATABASE_HOST',
      dialect: 'mysql'
    }
   );

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

const Student = sequelize.define("students", {
    student_id: {
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4,
        primaryKey: true,
    },
    name: {
        type: DataTypes.STRING,
        allowNull: false
    }
});

const student_data = [
    {name : "John Baker"},
    {name : "Max Butler"},
    {name : "Ryan Fisher"},
    {name : "Robert Gray"},
    {name : "Sam Lewis"}
]

sequelize.sync({ force: true }).then(() => {

    Student.bulkCreate(student_data, { validate: true }).then((result) => {
        console.log(result);
    }).catch((error) => {
        console.log(error);
    });

}).catch((error) => {
    console.error('Unable to create table : ', error);
});

Here, you initiate the database connection, create the model, and insert a few student records inside the new database.

Save and close the file.

Next, run this script using the following command:

node add_student_records.js

The output will be something similar to the following. It will be quite long, but all the student records which you inserted will be returned as follows. Note that since the student_id is an auto-generated UUID (Universally Unique Identifiers) value, it will be different depending on the user.

Output
Executing (default): SELECT 1+1 AS result
Executing (default): DROP TABLE IF EXISTS `students`;
Connection has been established successfully.
Executing (default): DROP TABLE IF EXISTS `students`;
Executing (default): CREATE TABLE IF NOT EXISTS `students` (`student_id` CHAR(36) BINARY , `name` VARCHAR(255) NOT NULL, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`student_id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `students`
Executing (default): INSERT INTO `students` (`student_id`,`name`,`createdAt`,`updatedAt`) VALUES ('45d1f26c-ba76-431f-ac5f-f41282351710','John Baker','2022-06-03 07:27:49','2022-06-03 07:27:49'),('1cb4e34d-bfcf-4a97-9624-e400b9a1a5f2','Max Butler','2022-06-03 07:27:49','2022-06-03 07:27:49'),('954c576b-ba1c-4dbc-a5c6-8eaf22bbbb04','Ryan Fisher','2022-06-03 07:27:49','2022-06-03 07:27:49'),('e0f15cd3-0025-4032-bfe8-774e38e14c5f','Robert Gray','2022-06-03 07:27:49','2022-06-03 07:27:49'),('826a0ec9-edd0-443f-bb12-068235806659','Sam Lewis','2022-06-03 07:27:49','2022-06-03 07:27:49');
[
  students {
    dataValues: {
      student_id: '45d1f26c-ba76-431f-ac5f-f41282351710',
      name: 'John Baker',
      createdAt: 2022-06-03T07:27:49.453Z,
      updatedAt: 2022-06-03T07:27:49.453Z
    },
    _previousDataValues: {
      name: 'John Baker',
      student_id: '45d1f26c-ba76-431f-ac5f-f41282351710',
      createdAt: 2022-06-03T07:27:49.453Z,
      updatedAt: 2022-06-03T07:27:49.453Z
    },

…

In the next section, you will apply raw queries using one of the student_id outputs in the code block above. Copy it down so that you have it for the next sections, where you will use the query() method for array and object replacements.

Array Replacement

In this section, you’ll use the query() method for an array replacement. With this method, Sequelize can execute raw or already prepared SQL queries.

To get started, copy the contents of the server.js file from Step 1, as that includes the initiate Sequelize() method and database initiation. Paste the contents into a new file called array_raw_query.js. Update the database name to sample_student_db:

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

const sequelize = new Sequelize(
   'sample_student_db',
   'DATABASE_USERNAME',
   'DATABASE_PASSWORD',
    {
      host: 'DATABASE_HOST',
      dialect: 'mysql'
    }
   );

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

At the end of the file, add the following code block for an array replacement, making sure to replace REPLACE_STUDENT_ID with the student_id value that you copied in the previous section.

...
sequelize.query(
    'SELECT * FROM students WHERE student_id = ?',
    {
      replacements: ['REPLACE_STUDENT_ID'],
      type: sequelize.QueryTypes.SELECT
    }
).then(result => {
    console.log(result);
}).catch((error) => {
    console.error('Failed to insert data : ', error);
});

For array replacement, you pass the query() method with the SQL query and the configuration object. It contains the replacements value and type. To replacements, you pass data as an array and catch those values using the question mark (?) symbol.

Next, since you need to get data about a specific student, the student_id is passed as the second parameter. After that, you pass the type: sequelize.QueryTypes.SELECT key-value pair, which you can use to select data from the database.

There are some other types as well, such as QueryTypes.UPDATE and QueryTypes.DELETE. Depending on the requirement, you can select the type that suits your purpose.

The following shows the full code block. Here you connect to the database and retrieve the selected student data using a raw query.

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

const sequelize = new Sequelize(
   'sample_student_db',
   'DATABASE_USERNAME',
   'DATABASE_PASSWORD',
    {
      host: 'DATABASE_HOST',
      dialect: 'mysql'
    }
   );

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

sequelize.query(
    'SELECT * FROM students WHERE student_id = ?',
    {
      replacements: ['REPLACE_STUDENT_ID'],
      type: sequelize.QueryTypes.SELECT
    }
).then(result => {
    console.log(result);
}).catch((error) => {
    console.error('Failed to insert data : ', error);
});

Save and close your file.

Next, you can run this script using the following command:

node array_raw_query.js

You will see output similar to the following:

Output
Connection has been established successfully.
[ { student_id: 'STUDENT_ID_YOU_RETRIEVED',
    name: 'Robert Gray',
    createdAt: 2022-05-06T13:14:50.000Z,
    updatedAt: 2022-05-06T13:14:50.000Z } ]

Due to the selected student_id, your output values may differ.

Object Replacement

On the surface, object replacement is similar to array replacement, but the pattern of passing data to the raw query is different. In the replacement option, you pass data as an object, and in the query option, you use values like :key.

To get started, create a new file called object_raw_query.js and paste the complete code blocks from the server.js file, updating the database to sample_student_db.

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

const sequelize = new Sequelize(
   'sample_student_db',
   'DATABASE_USERNAME',
   'DATABASE_PASSWORD',
    {
      host: 'DATABASE_HOST',
      dialect: 'mysql'
    }
   );

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

Then, add the following code block to the end of the new object_raw_query.js file:

...
sequelize.query(
  'SELECT * FROM students WHERE student_id = :id',
  {
    replacements: { id: 'REPLACE_STUDENT_ID' },
    type: sequelize.QueryTypes.SELECT
  }
 ).then(result => {
    console.log(result);
}).catch((error) => {
    console.error('Failed to insert data : ', error);
});

Here, you get selected student data using the object replacement method. You create a replacement object, setting the id as the student information you wish to retrieve: { id: 'REPLACE_STUDENT_ID' }.

In the query(), you indicate: 'SELECT * FROM students WHERE student_id = :id'. Using the query() method, you pass the replacement value as an object, which is why this method is known as object replacement.

Here is the complete code:

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

const sequelize = new Sequelize(
   'sample_student_db',
   'DATABASE_USERNAME',
   'DATABASE_PASSWORD',
    {
      host: 'DATABASE_HOST',
      dialect: 'mysql'
    }
   );

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

sequelize.query(
  'SELECT * FROM students WHERE student_id = :id',
  {
    replacements: { id: 'REPLACE_STUDENT_ID' },
    type: sequelize.QueryTypes.SELECT
  }
 ).then(result => {
    console.log(result);
}).catch((error) => {
    console.error('Failed to insert data : ', error);
});

Save and close the file.

Next, run this script using the following command:

node object_raw_query.js

The output will look similar to the following:

Output
Connection has been established successfully.
[ { student_id: 'STUDENT_ID_YOU_RETRIEVED',
    name: 'Robert Gray',
    createdAt: 2022-05-06T13:14:50.000Z,
    updatedAt: 2022-05-06T13:14:50.000Z } ]

Due to the selected student_id, your output values may differ.

In this step, you worked with Sequelize raw queries using two different methodologies: array replacement and object replacement.

Up to this point, you’ve stored database credentials directly in your code. While this approach works for local experimentation, it’s not secure. Hardcoding credentials exposes sensitive information such as database passwords and hostnames, and increases the risk of breaches when the code is shared or deployed. In this section, you’ll learn how to store these credentials securely using environment variables.

Why Use Environment Variables?

Environment variables act as external configuration parameters that your application can read at runtime. Instead of embedding credentials directly in your code, they are stored in your system or in a separate configuration file that is never committed to version control. This approach provides multiple benefits:

• Security: Your credentials remain hidden from public repositories and version control systems.
• Flexibility: Each environment (development, staging, production) can use a different database configuration without altering the source code.
• Maintainability: Centralized configuration allows easy updates, if the database password changes, you only need to modify one file or variable.
• Compliance: This practice aligns with the Twelve-Factor App methodology, which promotes environment-specific configuration management for scalable applications.

Setting Up Environment Variables

You’ll use the dotenv package to manage your environment variables in Node.js. This library reads variables from a .env file and injects them into the Node.js process.env object, making them accessible throughout your application.

Install the dotenv package by running the following command:

npm install dotenv

After installing, create a new file named .env in the root directory of your project. This file will hold all sensitive data required for database connectivity.

Here’s an example .env file configuration for your Sequelize setup:

DB_NAME=hello_world_db
DB_USER=your_username
DB_PASSWORD=your_password
DB_HOST=localhost
DB_DIALECT=mysql

Each variable corresponds to a specific parameter that Sequelize uses to establish the connection.

Securing the .env File

It’s crucial that your .env file never appears in version control. To prevent this, add it to your .gitignore file so Git skips it when committing changes:

.env

In production environments, environment variables are typically configured directly in your hosting platform’s settings or secret manager rather than a local .env file.

Updating Your Sequelize Configuration

Now that you have defined environment variables, update your Sequelize initialization to use them. Begin by importing and configuring dotenv at the top of your main server file (for e.g., server.js):

require('dotenv').config();
const Sequelize = require('sequelize');

const sequelize = new Sequelize(
  process.env.DB_NAME,
  process.env.DB_USER,
  process.env.DB_PASSWORD,
  {
    host: process.env.DB_HOST,
    dialect: process.env.DB_DIALECT
  }
);

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

1. The line require('dotenv').config() loads all variables defined in the .env file into process.env.
2. Sequelize reads the environment variables when initializing a new instance.
3. This ensures no sensitive information like usernames or passwords, appears in your code.

This approach enhances security and allows different environments to use unique configurations simply by changing environment variable values.

Verifying the Configuration

Once you’ve updated your configuration, you can verify the connection by running your Node.js application:

node server.js

If your .env file is set up correctly and the MySQL server is running, you’ll see the following output:

Output
Connection has been established successfully.

If an error appears, double-check that:

• The credentials in your .env file match those in your MySQL instance.
• The MySQL server is running and accessible from your host.
• The .env file is located in your project root and properly formatted.

Best Practices for Secure Configuration

Using environment variables is a strong first step toward secure configuration management. To ensure continued security and reliability, consider the following additional best practices:

• Use separate configurations per environment: Maintain different .env files for development, staging, and production. You can name them .env.development, .env.staging, and .env.production.
• Leverage secret managers: For production environments, use secret managers instead of storing secrets in files.
• Validate environment variables: Use a configuration utility or a startup script that checks whether required variables are defined before the app runs. This helps prevent runtime errors.
• Rotate credentials regularly: Update database credentials periodically to reduce the impact of potential leaks.
• Restrict database access: Configure database user roles to have the least privileges necessary for the application.

By implementing environment variables and following these practices, you ensure that your Sequelize application maintains both flexibility and security across development and production environments.

When evolving a database schema, you have two primary approaches with Sequelize: writing and running explicit migrations, or using sequelize.sync() to let Sequelize generate schema changes automatically. Both approaches are valid, but they serve different purposes and come with trade-offs. Let’s understand when to use each, how to write safe migrations, and how to run them reliably in development and production.

Migrations vs sequelize.sync() — when to use which

• Migrations (recommended for production): Migrations are explicit, versioned changes to your schema. They are stored in source control, code-reviewed, and executed as part of your deployment process. Use migrations in production for any schema change that affects data integrity or availability.

• sequelize.sync() (use for development and prototypes only): sync() is convenient for local development, tests, and quick prototypes because it can create or adjust tables automatically. However, do not use sync({ force: true }) or sync({ alter: true }) in production: these options can drop data or make unsafe changes without a controlled rollback path.

Tooling: sequelize-cli and migration runners

The official CLI, sequelize-cli, is the simplest way to manage migrations with Sequelize. For teams that need more control or custom workflows, umzug is a popular alternative.

To install and initialize the CLI in your project, run:

npm install --save-dev sequelize-cli
npx sequelize-cli init

This creates the following folders:

config/        # Stores database configuration files
models/        # Stores your Sequelize model definitions
migrations/    # Stores migration files
seeders/       # Stores seeder files for inserting initial data

You should add these folders to version control (except any files containing credentials). This ensures every environment runs the same database changes consistently.

Creating and Running Migrations

To create a new migration file, use the following command:

npx sequelize-cli migration:generate --name add-isbn-to-books

This creates a timestamped file in the migrations/ folder. Open the file and define what should happen when you apply the migration (up) and how to undo it (down):

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.addColumn('books', 'isbn', {
      type: Sequelize.STRING
    });
  },
  async down(queryInterface) {
    await queryInterface.removeColumn('books', 'isbn');
  }
};

• The up function applies the migration (adds the column).
• The down function reverses the change (removes the column).

Run the migration using:

npx sequelize-cli db:migrate

To revert the last migration, use:

npx sequelize-cli db:migrate:undo

You can also undo all migrations in development by running:

npx sequelize-cli db:migrate:undo:all

Writing safe, idempotent migrations

Follow these rules when authoring migrations:

• Wrap operations in transactions when supported by the database. This ensures the migration either fully succeeds or fully rolls back on error.

  module.exports = {
    async up(queryInterface, Sequelize) {
      return queryInterface.sequelize.transaction(async (t) => {
        await queryInterface.addColumn('books', 'isbn', {
          type: Sequelize.STRING
        }, { transaction: t });
      });
    },
    async down(queryInterface) {
      return queryInterface.sequelize.transaction(async (t) => {
        await queryInterface.removeColumn('books', 'isbn', { transaction: t });
      });
    }
  };
  

• Keep migrations small and focused. Each migration should perform a single logical change (add a column, create an index, rename a column). Smaller migrations are easier to review, test, and roll back.

• Be defensive and idempotent. When appropriate, check for existence to avoid failures in environments with drift. Use queryInterface.describeTable() or raw queries to guard operations.

• Avoid destructive operations in one step. For example, to remove a column safely, add the new column, backfill it, switch reads to the new column in application code, and only then drop the old column in a later migration.

Long-running and zero-downtime migrations

Large tables and index operations can cause locks. Use these strategies to minimize impact:

1. Add nullable columns first. Adding a nullable column is typically fast. Avoid adding columns with nontrivial defaults that trigger full-table rewrites.
2. Backfill in small batches. Implement a background job to populate new columns in chunks rather than running a single massive UPDATE.
3. Create indexes without exclusive locks where possible. Use database-specific features (for example, Postgres CONCURRENTLY and recent MySQL online DDL) via raw SQL in migrations.
4. Feature-flag rollouts. Deploy application code that reads the new column behind a feature flag. After backfill completes and tests pass, flip the flag and later remove the old field.

Rollbacks and emergency fixes

• Provide a down migration. Every up should have a reverse down that documents how to revert the change. Even if you rarely run down in production, it serves as a clear rollback plan.
• Plan for emergency rollbacks. If a migration causes issues, be prepared to roll back application code first and restore the database from backups if needed. Regularly test restore procedures.

Seeding data and schema changes

• Keep seeders minimal and stable. Use seeders for essential lookup or configuration rows. Avoid seeding large volumes of business data through migrations.
• Separate seeds from structural migrations. If you must populate data that depends on a schema change, prefer a follow-up seeder so responsibilities remain clear.

CI, code review, and deployment automation

• Run migrations in CI and staging first. Add a CI step that runs migrations against an ephemeral database to catch errors early.

• Treat migrations like code. Lint and review migration files in pull requests. Include automated checks where possible.

• Automate migration execution during deploys. Ensure migrations run before application processes accept traffic. Example package.json scripts:

  "scripts": {
    "migrate": "npx sequelize-cli db:migrate",
    "start": "node server.js",
    "deploy": "npm run migrate && npm run start"
  }
  

In containerized deployments, run migrations as an init container or in a deploy hook so the web processes start only after migrations complete.

Testing migrations

• Test both directions. Run db:migrate then db:migrate:undo:all locally and in CI to validate that up and down both work.
• Use disposable databases for tests. Prefer ephemeral databases or containers for deterministic test runs.

Following these best practices ensures your Sequelize migrations remain reliable, traceable, and safe for long-term projects.

Even with a solid setup, Sequelize applications can sometimes run into unexpected problems such as database connection errors, migration failures, slow-running queries, deadlocks, or concurrency conflicts. This section provides step-by-step guidance on identifying and fixing these issues, along with performance best practices that help you keep your application stable and efficient.

Troubleshooting Common Issues

1. Connection Errors

Problem: Sequelize cannot connect to the MySQL database and throws errors such as SequelizeConnectionError: connect ECONNREFUSED, Access denied for user, or SequelizeHostNotFoundError.

How to Fix:

Start by confirming that your connection details are correct. Double-check the values in your .env file, including the database name, username, password, host, and port. Even a small typo or trailing space can cause Sequelize to fail to connect.

Next, make sure your MySQL server is running and listening on the default port (3306). You can test connectivity by logging in manually:

mysql -u your_username -p -h your_host -P 3306

If this command fails, the problem likely lies with your MySQL service or credentials.

If you are using the dotenv package, ensure that require('dotenv').config() appears before Sequelize is initialized. This step ensures that all environment variables are available at runtime.

Finally, for remote connections, verify your firewall settings or security group rules. The MySQL server must accept inbound connections from your application’s host.

2. Migration Failures

Problem: You run npx sequelize-cli db:migrate or sequelize.sync() and encounter errors such as missing tables, duplicate columns, or schema mismatches.

How to Fix:

Migration issues usually occur when files are executed in the wrong order or contain errors in SQL operations. Sequelize processes migrations based on timestamps in the filenames, so confirm that the files are named correctly.

If a migration fails, inspect the error message in detail. Then, safely revert the last migration using:

npx sequelize-cli db:migrate:undo

After fixing the migration file, re-run it.

Always wrap your migration logic inside a transaction to ensure atomicity: either all changes succeed or none do. Before running migrations on production, test them thoroughly on a staging or local database to confirm correctness.

3. Model Synchronization Issues

Problem: Your Sequelize models and database schema do not match, leading to missing columns, incorrect data types, or outdated tables.

How to Fix:

Avoid using sequelize.sync({ force: true }) in production, as it will drop and recreate tables, causing irreversible data loss. Similarly, use sequelize.sync({ alter: true }) cautiously; it may modify schemas unpredictably.

Instead, prefer explicit migrations that clearly define every schema change. To compare your models with the actual database structure, use:

await queryInterface.describeTable('tableName');

This helps identify mismatches before they affect production.

4. Query and Timeout Errors

Problem: You see timeout-related errors such as Connection timeout or Too many connections.

How to Fix:

Timeout issues are often caused by exhausted connection pools or unclosed connections. Ensure all Sequelize queries are awaited properly so that connections return to the pool after use.

You can also fine-tune Sequelize’s pool configuration to better suit your application’s workload:

const sequelize = new Sequelize(DB_NAME, DB_USER, DB_PASSWORD, {
  host: DB_HOST,
  dialect: 'mysql',
  pool: {
    max: 10,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
});

If your application handles many concurrent users, consider increasing the MySQL max_connections parameter in your database configuration. Monitor active connections using SHOW PROCESSLIST; in MySQL to detect leaks or unused sessions.

5. Slow Query Performance

Problem: Certain queries take significantly longer than expected, causing slow API responses or high latency.

How to Diagnose:

Start by identifying which queries are slow. You can log all queries Sequelize executes by enabling logging temporarily:

const sequelize = new Sequelize(..., { logging: console.log });

Then, use MySQL’s EXPLAIN statement to understand how the database executes the query:

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

If the output shows a “full table scan,” the query is missing an index.

You can also enable the MySQL slow query log to automatically capture slow operations:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

How to Fix:

Add indexes to frequently queried columns (especially those used in WHERE, JOIN, and ORDER BY clauses). Avoid SELECT *, instead, explicitly fetch only the fields you need using the attributes option.

If Sequelize generates inefficient queries with unnecessary joins, rewrite the query using raw SQL. For large reports or analytics queries, break them into smaller steps or use caching tools like Redis to store repetitive results.

6. Deadlocks and Concurrency Errors

Problem: You receive errors such as Deadlock found when trying to get lock; try restarting transaction, especially under high load.

How to Diagnose:

Check InnoDB’s status in MySQL to identify which queries or tables were involved:

SHOW ENGINE INNODB STATUS\G

This output includes detailed information about recent deadlocks and lock waits. You can also enable general or transaction logs to review the sequence of queries leading up to the issue.

How to Fix:

Deadlocks occur when two or more transactions are waiting on each other’s locks. To minimize them:

1. Keep transactions as short as possible. Do not call external services or perform heavy computation while holding a lock.

2. Always access tables and rows in the same order across transactions to avoid circular waits.

3. For recurring deadlocks, implement a simple retry mechanism with exponential backoff:

   async function runWithRetry(fn, retries = 3) {
     for (let i = 0; i < retries; i++) {
       try {
         return await fn();
       } catch (err) {
         if (isDeadlockError(err) && i < retries - 1) {
           await sleep(100 * (i + 1));
           continue;
         }
         throw err;
       }
     }
   }
   

4. Use row-level locks (SELECT ... FOR UPDATE) only when necessary, and release them promptly.

5. Split large transactions into smaller batches to reduce contention.

6. Adjust transaction isolation levels if appropriate, but only after confirming it won’t affect data consistency.

Performance Optimization Tips

Performance issues often stem from inefficient queries, missing indexes, or poor connection handling. Here are a few practical tips to improve Sequelize performance without sacrificing clarity or maintainability.

1. Optimize Queries

Write queries that request only what’s needed. Fetching unnecessary data increases memory usage and slows down responses. Use attributes to limit fields:

const users = await User.findAll({ attributes: ['id', 'name'] });

Always filter results using indexed columns and avoid broad searches like LIKE '%term%' when possible.

2. Use Indexes Effectively

Indexes make lookups faster but can slow down writes. Add indexes only where they provide measurable benefits. Analyze query performance with MySQL’s EXPLAIN to verify improvements before and after adding an index:

await queryInterface.addIndex('users', ['email']);

3. Tune the Connection Pool

The connection pool determines how many database connections Sequelize maintains.

• For small apps, 5–10 connections are usually sufficient.
• For high-load systems, increase the pool size and adjust timeouts to prevent connection bottlenecks. Monitor usage regularly using MySQL’s SHOW PROCESSLIST;.

4. Enable Logging and Monitoring

Turn on Sequelize logging temporarily to review SQL queries. Use the MySQL slow query log to identify queries that exceed your performance threshold. For real-time tracking, integrate monitoring tools such as PM2, New Relic, or Datadog to measure latency and resource usage.

5. Cache Frequent Queries

Cache results for data that doesn’t change often. Tools like Redis or Memcached reduce database hits and improve response times, especially for read-heavy workloads.

6. Paginate Results

Large result sets consume significant memory. Always paginate queries in APIs:

const users = await User.findAll({ limit: 50, offset: 100 });

Pagination keeps responses lightweight and consistent under load.

7. Use Raw Queries for Complex Operations

If Sequelize’s abstraction results in inefficient SQL for a specific use case, execute a raw SQL query directly:

const [results] = await sequelize.query('SELECT name FROM users WHERE active = 1');

Use raw queries sparingly and document them clearly for future maintenance.

1. What is Sequelize in Node.js?

Sequelize is an object-relational mapper (ORM) for Node.js that provides a high-level, JavaScript-based API for working with SQL databases such as MySQL, PostgreSQL, SQLite, and MariaDB. Instead of writing raw SQL for every operation, you define models that map to database tables and use model methods to insert, query, update, and delete records. Sequelize also supports associations (relations between models), transactions, migrations, and raw queries when you need direct SQL.

2. How do I connect Sequelize to MySQL?

To connect Sequelize to MySQL you create a Sequelize instance with your database credentials and options. In a Node.js file, require Sequelize, optionally load environment variables, then instantiate it with the database name, username, password and host/dialect options. For example:

require('dotenv').config();
const { Sequelize } = require('sequelize');

const sequelize = new Sequelize(
  process.env.DB_NAME,
  process.env.DB_USER,
  process.env.DB_PASSWORD,
  {
    host: process.env.DB_HOST || 'localhost',
    dialect: 'mysql'
  }
);

await sequelize.authenticate();
console.log('Connected to the database successfully!');

Always keep credentials outside source control (for example, in a .env file or a secrets manager).

3. How do I create and sync models in Sequelize?

In Sequelize, models represent tables. You define them using sequelize.define() or by extending the Model class. After that, you can sync them with your database so Sequelize creates the tables automatically (great for development). Here’s a small example:

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

const Book = sequelize.define('Book', {
  title: { type: DataTypes.STRING, allowNull: false },
  author: { type: DataTypes.STRING, allowNull: false },
  releaseDate: DataTypes.DATEONLY
});

// Create the table if it does not exist
await sequelize.sync();

For production environments, it is better to use migrations so you have version control over schema changes.

4. How do I use Sequelize with Express?

In an Express app, initialize Sequelize in a separate file (like db.js), export the connected instance and models, and import them wherever you need them in your route handlers or controllers. Use async/await in route handlers to call Sequelize model methods, and handle errors with try/catch or an error middleware. Here’s how that looks in practice:

// db.js
const sequelize = new Sequelize(...);
const User = sequelize.define(...);
module.exports = { sequelize, User };

// app.js
const { User } = require('./db');
app.get('/users', async (req, res) => {
  const users = await User.findAll();
  res.json(users);
});

Make sure you run migrations or call sequelize.sync() before starting your Express server so your database tables exist when requests come in.

5. What is the difference between Sequelize and raw SQL queries?

Sequelize lets you write database logic in JavaScript, while raw SQL means writing SQL statements directly. Sequelize makes your code cleaner and easier to maintain because you can define models and relationships once and use them across your app. Raw SQL gives you more control and can sometimes be faster for very complex queries.

In short, use Sequelize for most everyday tasks, and use raw SQL when you need to fine tune performance or run something that Sequelize cannot express easily.

6. How can I define associations in Sequelize?

Sequelize makes it easy to describe relationships between tables. You can set up one-to-one, one-to-many, or many-to-many relationships using simple methods like hasOne, hasMany, and belongsToMany. For example:

// One to one
User.hasOne(Profile);
Profile.belongsTo(User);

// One to many
Author.hasMany(Book);
Book.belongsTo(Author);

// Many to many
Student.belongsToMany(Course, { through: 'StudentCourse' });
Course.belongsToMany(Student, { through: 'StudentCourse' });

Once defined, you can use include in your queries to automatically fetch related data.

7. How do I handle migrations and schema changes?

In development, you can use sequelize.sync() to quickly create tables. But in production, you should always use migrations. Migrations are versioned files that describe how your database schema changes over time, similar to how Git commits track code changes.

You can create a migration with the Sequelize CLI:

npx sequelize-cli migration:generate --name add-users-table

Each migration has up and down methods, one to apply the change and one to undo it. Run your migrations with:

npx sequelize-cli db:migrate

This way, everyone on your team (and every deployment) keeps the same database structure.

Use sequelize.sync() for quick experiments, but stick to migrations once your app is live because they are safer, reversible, and easier to track.

In this tutorial, you set up Sequelize with Node.js and MySQL to manage data through models, associations, and migrations. You also secured your database credentials with environment variables and explored best practices for troubleshooting and performance tuning.

With these steps, you now have a solid foundation for building and maintaining secure, efficient Node.js applications that use Sequelize and MySQL for data management.

To learn more about Sequelize, check out the product documentation. For more such Node.js tutorials, check out the following articles:

• How To Set Up a GraphQL Server in Node.js with Apollo Server and Sequelize
• How To Implement Caching in Node.js Using Redis
• How To Create a Web Server in Node.js with the HTTP Module