How To Use Sequelize in Node JS with MySQL

Sequelize is an ORM (Object Relation Mapper) tool for Node JS. Sequelize uses the javascript object to store data in relational databases(i.e. MySQL, PostgreSQL, SQLite, and MariaDB, etc).

Sequelize takes data in object format, maps these objects with rows and columns, and also returns promises. We can use Sequelize to manage queries for any relational databases.

Introduction to Sequelize in Node JS

Consider the following example that uses the raw SQL queries to insert a raw in the tutorials table.

INSERT INTO `tutorials_db`.`tutorials`
(`id`,
`title`,
`author`,
`publishedAt`,)
VALUES
(5,
"Sequelize",
"AP Singh",
"2023-02-22",)

In the above example, We have to remember and wrote these queries. But Sequelize makes it easy to work with SQL-based databases.
Consider the following example that uses Sequelize to perform SQL queries.

const Tutorial = sequelizeInstance.define('tutorials', {
	title: {
		type: DataTypes.STRING,
		allowNull: false,
	},
	author: {
		type: DataTypes.STRING,
	},
	publishedAt: {
		type: DataTypes.DATEONLY,
	},
})

Tutorial.create({
	title: 'Sequelize',
	author: 'AP Singh',
	publishedAt: '2023-02-27',
})

In the above example, We don't have to remember those long SQL queries. Simply, We define a schema first and use create() method to insert data.
Hence, Sequelize is fast and easy to use. Let's set up Sequelize with Node JS.

Prerequisites

Node.js Run time Environment
MySQL installed on your local machine
General awareness of JavaScript and MySQL queries

Setting Up Sequelize with Node JS

In this section, We will create a node project and install sequelize. Then, We will create a database in MySQL and connect the Node JS application to MySQL using Sequelize.

Installing Sequelize

Open the CMD(command line tool) and create a folder named sequelize-and-node-js as shown below

mkdir sequelize-and-node-js

Go to the current folder sequelize-and-node-js and initiate a node js project

cd sequelize-and-node-js
npm init -y

Open the folder sequelize-and-node-js in Visual Studio Code using the following command:

code .

Find and open a file named package.json inside the folder sequelize-and-node-js.

{
	"name": "sequelize-and-node",
	"version": "1.0.0",
	"description": "",
	"main": "index.js",
	"scripts": {
		"test": "echo \"Error: no test specified\" && exit 1"
	},
	"keywords": [],
	"author": "",
	"license": "ISC"
}

In the package.json file, Find and update a property named main as shown below

"main": "server.js"

Create a file server.js inside the folder sequelize-and-node-js
Current folder structure will look like this:

sequelize-and-node-js
├─ package.json
├─ server.js

Install the npm package sequelize using the following command

npm install sequelize --save

Creating a database

Open the CMD(command line tool) and login to MySQL database using the following command:

$ mysql -u USER_NAME -p

Replace USER_NAME with your MySQL database username, hit Enter and enter your MySQL database password
Create a new database named tutorials_db in MySQL using the following command:

mysql> CREATE DATABASE tutorials_db;

Verify whether the database tutorials_db is created successfully.

mysql> SHOW DATABASES;

The above command will display the following output:

+--------------------+
| Database           |
+--------------------+
| mysql              |
| performance_schema |
| tutorials_db       |
| world              |
+--------------------+

MySQL Connection using Sequelize and Node JS

Sequelize ORM doesn't have in-built support for database drivers. Hence, We will install a driver for MySQL with the following command:

npm install -save mysql2

Create a file config.js inside the folder sequelize-and-node-js and add the following code

// fileName: config.js
const MYSQL_DATABASE_USERNAME = 'Enter_Your_Database_Username'
const MYSQL_DATABASE_PASSWORD = 'Enter_Your_Database_Password'

module.exports = {
	MYSQL_DATABASE_USERNAME,
	MYSQL_DATABASE_PASSWORD,
}

Add the following code to server.js file

// fileName: server.js
const { Sequelize } = require('sequelize')
const { MYSQL_DATABASE_USERNAME, MYSQL_DATABASE_PASSWORD } = require('./config')

const sequelizeInstance = new Sequelize(
	'tutorials_db', // databse name
	MYSQL_DATABASE_USERNAME,
	MYSQL_DATABASE_PASSWORD,
	{
		host: 'localhost',
		dialect: 'mysql',
	},
)

In the above code, We created an instance of sequelize ORM with new operator. Sequelize can accept various parameters and essential data credentials ( i.e. Database Name, Database Username, Database Password, etc)
Add the following code block to the server.js file.

...

// Establish database connection
sequelizeInstance.authenticate()
    .then(() => {
        console.log("Connection to MySQL has been done successfully.")
    })
    .catch((error) => {
        console.log("Not Connected! Try again", error)
    });

In the above code, We used authenticate() method to establish a connection to the database and verify Whether the credentials are accurate.
Run the server.js file with the following command:
```
node server.js
```
The above command will produce the following output:

Connection to MySQL has been done successfully.

Creating a Table in MySQL Database Using Sequelize

Add the following code to server.js file.

...

// Create model
const Tutorial = sequelizeInstance.define("tutorials", {
  title: {
    type: DataTypes.STRING,
    allowNull: false
  },
  author: {
    type: DataTypes.STRING,
  },
  publishedAt: {
    type: DataTypes.DATEONLY,
  },
});

We used define() method to create a model that will create a table tutorials in the database tutorials_db.
Now, We will add Tutorial model to the database using sync() method and add the following code to the server.js file.

...

sequelizeInstance.sync()
  .then(() => {
    console.log("Successfully created the table: tutorials ")
  })
  .catch((error) => {
    console.log("Error in creating table: tutorials", error)
  })

Run the command node server.js and it will display the following output:

Executing (default): SELECT 1+1 AS result
Executing (default): SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'tutorials' AND TABLE_SCHEMA = 'tutorials_db'
Connection to MySQL has been done successfully
Executing (default): CREATE TABLE IF NOT EXISTS `tutorials` (`id` INTEGER NOT NULL auto_increment , `title` VARCHAR(255) NOT NULL, `author` VARCHAR(255), `publishedAt` DATE, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `tutorials`
Successfully created the table: tutorials

Now, We will verify whether the table tutorials is created.

mysql> USE tutorials_db;
mysql> SHOW TABLES;

The above command will display the following output

+------------------------+
| Tables_in_tutorials_db |
+------------------------+
| tutorials              |
+------------------------+
1 row in set (0.00 sec)

Hence, it is verified that the table tutorials is created successfully.

Perform Database Queries Using Sequelize

Adding a New Row

Use create() method to add a new entry to the database and add the following code to the file addingNewRow.js

const { Sequelize, DataTypes } = require('sequelize')
const { MYSQL_DATABASE_USERNAME, MYSQL_DATABASE_PASSWORD } = require('./config')

const sequelizeInstance = new Sequelize(
	'tutorials_db',
	MYSQL_DATABASE_USERNAME,
	MYSQL_DATABASE_PASSWORD,
	{
		host: 'localhost',
		dialect: 'mysql',
	},
)

const Tutorial = sequelizeInstance.define('tutorials', {
	title: {
		type: DataTypes.STRING,
		allowNull: false,
	},
	author: {
		type: DataTypes.STRING,
	},
	publishedAt: {
		type: DataTypes.DATEONLY,
	},
})

sequelizeInstance
	.sync()
	.then(() => {
		Tutorial.create({
			title: 'Sequelize: A Complete Guide',
			author: 'Arun',
			publishedAt: '2023-02-27',
		})
			.then((res) => {
				console.log(res.dataValues)
			})
			.catch((error) => {
				console.log('Error! Try again', error)
			})

		console.log('Data added to the table successfully')
	})
	.catch((error) => {
		console.log('Error in creating table:', error)
	})

Run the command node addingNewRow.js and it will display the following output:

{
  id: 1,
  title: 'Sequelize: A Complete Guide',
  author: 'Arun',
  publishedAt: '2023-02-27',
  updatedAt: 2023-02-27T13:28:57.182Z,
  createdAt: 2023-02-27T13:28:57.182Z
}

Adding Rows in Bulk

Use bulkCreate() method to add new entries to the database and add the following code to the file addingRowsInBulk.js

const { Sequelize, DataTypes } = require('sequelize')
const { MYSQL_DATABASE_USERNAME, MYSQL_DATABASE_PASSWORD } = require('./config')

const sequelizeInstance = new Sequelize(
	'tutorials_db',
	MYSQL_DATABASE_USERNAME,
	MYSQL_DATABASE_PASSWORD,
	{
		host: 'localhost',
		dialect: 'mysql',
	},
)

const Tutorial = sequelizeInstance.define('tutorials', {
	title: {
		type: DataTypes.STRING,
		allowNull: false,
	},
	author: {
		type: DataTypes.STRING,
	},
	publishedAt: {
		type: DataTypes.DATEONLY,
	},
})

sequelizeInstance
	.sync()
	.then(() => {
		Tutorial.bulkCreate([
			{
				title: 'Node JS: A Complete Guides',
				author: 'Pratap',
				publishedAt: '2023-02-24',
			},
			{
				title: 'MySQL: A Complete Guides',
				author: 'Singh',
				publishedAt: '2023-02-20',
			},
		])
			.then((res) => {
				console.log(res)
			})
			.catch((error) => {
				console.log('Error! Try again', error)
			})

		console.log('Data added to the table successfully')
	})
	.catch((error) => {
		console.log('Error in creating table:', error)
	})

Run the command node addingRowsInBulk.js and it will display the following output:

[
  tutorials {
    dataValues: {
      id: 2,
      title: 'Node JS: A Complete Guides',
      author: 'Pratap',
      publishedAt: '2023-02-24',
      createdAt: 2023-02-27T13:36:04.581Z,
      updatedAt: 2023-02-27T13:36:04.581Z
    },
    _previousDataValues: {
      title: 'Node JS: A Complete Guides',
      author: 'Pratap',
      publishedAt: '2023-02-24',
      id: 2,
      createdAt: 2023-02-27T13:36:04.581Z,
      updatedAt: 2023-02-27T13:36:04.581Z
    },
    uniqno: 1,
    _changed: Set(0) {},
    _options: {
      isNewRecord: true,
      _schema: null,
      _schemaDelimiter: '',
      include: undefined
    },
    isNewRecord: false
  },
  tutorials {
    dataValues: {
      id: 3,
      title: 'MySQL: A Complete Guides',
      author: 'Singh',
      publishedAt: '2023-02-20',
      createdAt: 2023-02-27T13:36:04.581Z,
      updatedAt: 2023-02-27T13:36:04.581Z
    },
    _previousDataValues: {
      title: 'MySQL: A Complete Guides',
      author: 'Singh',
      publishedAt: '2023-02-20',
      id: 3,
      createdAt: 2023-02-27T13:36:04.581Z,
      updatedAt: 2023-02-27T13:36:04.581Z
    },
    uniqno: 1,
    _changed: Set(0) {},
    _options: {
      isNewRecord: true,
      _schema: null,
      _schemaDelimiter: '',
      include: undefined
    },
    isNewRecord: false
  }
]

Selecting All Rows

Use findAll() method to select entries in the database and add the following code to the file selectingAllRows.js

const { Sequelize, DataTypes } = require('sequelize')
const { MYSQL_DATABASE_USERNAME, MYSQL_DATABASE_PASSWORD } = require('./config')

const sequelizeInstance = new Sequelize(
	'tutorials_db',
	MYSQL_DATABASE_USERNAME,
	MYSQL_DATABASE_PASSWORD,
	{
		host: 'localhost',
		dialect: 'mysql',
	},
)

const Tutorial = sequelizeInstance.define('tutorials', {
	title: {
		type: DataTypes.STRING,
		allowNull: false,
	},
	author: {
		type: DataTypes.STRING,
	},
	publishedAt: {
		type: DataTypes.DATEONLY,
	},
})

sequelizeInstance
	.sync()
	.then(() => {
		Tutorial.findAll({ raw: true })
			.then((res) => {
				console.log(res)
			})
			.catch((error) => {
				console.log('Failed to get the data', error)
			})
	})
	.catch((error) => {
		console.log('Error in syncing the table:', error)
	})

Run the command `node selectingAllRows.js and it will display the following output:

[
  {
    id: 1,
    title: 'Sequelize: A Complete Guide',
    author: 'Arun',
    publishedAt: '2023-02-27',
    createdAt: 2023-02-27T13:28:57.000Z,
    updatedAt: 2023-02-27T13:28:57.000Z
  },
  {
    id: 2,
    title: 'Node JS: A Complete Guide',
    author: 'Pratap',
    publishedAt: '2023-02-24',
    createdAt: 2023-02-27T13:36:04.000Z,
    updatedAt: 2023-02-27T13:36:04.000Z
  },
  {
    id: 3,
    title: 'MySQL: A Complete Guide',
    author: 'Singh',
    publishedAt: '2023-02-20',
    createdAt: 2023-02-27T13:36:04.000Z,
    updatedAt: 2023-02-27T13:36:04.000Z
  }
]

Selecting Rows with the `where` clause

Use the findOne() method to select entries in the database and add the following code to the file usingWhereClause.js

const { Sequelize, DataTypes } = require('sequelize')
const { MYSQL_DATABASE_USERNAME, MYSQL_DATABASE_PASSWORD } = require('./config')

const sequelizeInstance = new Sequelize(
	'tutorials_db',
	MYSQL_DATABASE_USERNAME,
	MYSQL_DATABASE_PASSWORD,
	{
		host: 'localhost',
		dialect: 'mysql',
	},
)

const Tutorial = sequelizeInstance.define('tutorials', {
	title: {
		type: DataTypes.STRING,
		allowNull: false,
	},
	author: {
		type: DataTypes.STRING,
	},
	publishedAt: {
		type: DataTypes.DATEONLY,
	},
})

sequelizeInstance
	.sync()
	.then(() => {
		Tutorial.findOne({
			where: { id: '3' },
			raw: true,
		})
			.then((res) => {
				console.log(res)
			})
			.catch((error) => {
				console.log('Failed to get the data', error)
			})
	})
	.catch((error) => {
		console.log('Error in syncing the table:', error)
	})

Run the command `node usingWhereClause.js and it will display the following output:

{
  id: 3,
  title: 'MySQL: A Complete Guide',
  author: 'Singh',
  publishedAt: '2023-02-20',
  createdAt: 2023-02-27T13:36:04.000Z,
  updatedAt: 2023-02-27T13:36:04.000Z
}

Deleting a Row

Use destroy() method to delete an entry from the database and add the following code to the file deletingRow.js

const { Sequelize, DataTypes } = require('sequelize')
const { MYSQL_DATABASE_USERNAME, MYSQL_DATABASE_PASSWORD } = require('./config')

const sequelizeInstance = new Sequelize(
	'tutorials_db',
	MYSQL_DATABASE_USERNAME,
	MYSQL_DATABASE_PASSWORD,
	{
		host: 'localhost',
		dialect: 'mysql',
	},
)

const Tutorial = sequelizeInstance.define('tutorials', {
	title: {
		type: DataTypes.STRING,
		allowNull: false,
	},
	author: {
		type: DataTypes.STRING,
	},
	publishedAt: {
		type: DataTypes.DATEONLY,
	},
})

sequelizeInstance
	.sync()
	.then(() => {
		Tutorial.destroy({
			where: { id: '3' },
		})
			.then(() => {
				console.log('Successfully deleted data')
			})
			.catch((error) => {
				console.log('Failed to delete data', error)
			})
	})
	.catch((error) => {
		console.log('Error in syncing the table:', error)
	})

Run the command `node deletingRow.js and it will display the following output:

Executing (default): SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE
TABLE' AND TABLE_NAME = 'tutorials' AND TABLE_SCHEMA = 'tutorials_db'
Executing (default): SHOW INDEX FROM `tutorials`
Executing (default): DELETE FROM `tutorials` WHERE `id` = '3'
Successfully deleted data

Conclusion

We can install Sequelize using the command npm install sequelize.
Sequelize use mysql2 driver to perform MySQL database queries. We can install mysql2 driver using the command npm install mysql2.
Sequelize use the authenticate() method to verify the database credentials and connect to the database.
We can insert raw in the database table using the create() method.
We can insert more than one row in the database table using the bulkCreate() method.
We can fetch one row using findOne() method and where clause.
We can fetch all rows from the database using the findAll() method.
We can delete a row from the database using the destroy() method.