Quedemy
Sign In
sequelize-in-node-js-with-mysql

How To Use Sequelize in Node JS with MySQL

arun-pratap-singh
Arun Pratap Singh
 · 12 min read

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.

  Table Of Contents  

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.
Next →
MongoDB Querying Documents with Example
QuedemyLogo

Learn and crack your first job in Software Company with Quedemy

Learn and Grow Your Career for FREE.

Join 100+ subscribers who get actionable tips to upskill and grow career in 4 minutes daily.

Follow us on

Privacy Policy · Terms & Conditions · Cancellation & Refund Policy

© 2023 Quedemy. All rights reserved.