How to Create MySQL Connection with Node JS using Sequelize and Express

Node.js is a Javascript framework that can be deployed on various internet browsers. It helps in streamlining application development into a single language. In the paradigm of NodeJS Javascript is everywhere, which makes it a welcoming platform for the software community and businesses.

A minimalist and flexible framework of node.js with the express can incorporate different features in developing mobile and web-based applications. It includes setting up middleware, defining routing tables, and dynamically rendering the HTML pages based on passing arguments into standard templates.

Larger SQL databases will be challenging to manage but with the help of sequelize, you can recover them. Sequelize is an object-relational mapper which plots an object syntax into database schemas. This can be done easily with the help of node.js and Javascript.

MySQL connection with Node JS:

REST (Representational State Transfer) helps in determining how the API appears, while CRUD (Create, Read, Update, and Delete) is simply an acronym for how to function with stored data. Concerning REST API, CRUD is the standard form of HTTP action verbs. When you understand the API in detail you can understand the ways of utilizing node js with MySQL.

Code source

To build REST API in a node.js environment, here are the steps to follow:

1. Open an Express web server
2. Add the configuration data for an existing MySQL database
3. Open a sequelize
4. In sequelize, create a tutorial model
5. Write the controller
6. Define all the routes to handle each CRUD function
7. Open Postman
8. Test the REST CRUD API

After these steps, MySQL connection with node js is completed.

Tools required to build REST CRUD API:

Many available tools can be deployed to build the REST CRUD API effectively. Some of the most widely used tools are as below:

1. Sequelize: A sequelize is a promise-based ORM for Node.js which incorporates robust reading applications, transaction relationships, support, and loading. Sequelize enables users in managing larger SQL databases. It also makes MySQL with node.js viable.

Code source

2. Express: Express is a web application framework for node.js. It is considered a de-facto for the node.js framework, mainly because it is open-sourced, free, and minimalist.

3. MySQL: It is an RDBMS (relational database management system). MySQL is backed by the Oracle Corporation and it is available for free. Also, the source code in MySQL can be customized as per your requirements.

Pros of sequelize:

• Sequelize reduces complexity by eliminating the subtle disparities between different SQL implementations.
• It also provides a built-in middleware that allows individual customization to eliminate the errors in each field.
• Sequelize incorporates c promise-built library that allows the user to manage exceptions and unforeseen results better.
• It also questions all the complicated join functions, it helps the user learn about the ‘objects’ connect.
• Lastly, the sync database on a changed model will alter or automatically create tables as per the user’s requirements.

Cons of sequelize:

• NoSQL doesn’t support sequelize as it includes some unexplained issues when additional connections to the database are required.
• Sequelize Cloud generates complicated queries.

Creating a node.js application:

While creating a Node JS application, you need to understand the three components that come with it.

1. Importing the required modules - To load the Node JS modules, you have to import the necessary directives.

2. Creating a server - If you want to attend to your user’s request, you need to create a server.

3. Reading the request & return response - The server you have created will read the HTTP request and respond in return.

When you know these components, it becomes easier to understand the concept of node.js using MySQL. For creating a node.js app, the user has to follow these steps:

1. Creating a folder

$ mkdir nodejs-express-Sequelize-mysql

$ cd nodejs-express-Sequelize-mysql

2. Opening a json file

3. Initializing the node.js app

npm init

Name: (nodejs-Sequelize-mysql-express)

Version: (1.0.1)

Description: Node.js, Sequelize, MySQL and REST APIs with Express

Entry point: (index.js) server.js

Test command:

Git Repository:

Keywords: nodejs, Sequelize, mysql, rest, api, express

Author: Turing.com

License: (ISC)

Is it fine? (yes) yes

4. Installing the modules body-parser, express, sequelize, and mysql2

5. Running the command

npm install express Sequelize mysql2 body-parser cors – save

6. The final package

{
	“name” : “nodejs-Sequelize-mysql-express”,
	“version” : “1.0.1”,
	“description” : “Node.js, Sequelize, MySQL and REST APIs with Express”,
	“main”: “server.js”,
	“scripts”: {
	“test”: “echo “Error: No test is specified” && exit 1”
	},
	“keywords”: [
	“nodejs”,
	“Sequelize”,
	“mysql”,
	“rest”,
	“api”,
	“express”
	],
	“author”: “Turing.com”,
	“license” : “ISC”,
	“dependencies”: {
	“body-parser”: “^1.20.0”,
	“cors”: “^2.8.6”,
	“express”: “^4.17.2”,
	“mysql2”: “^2.0.1”,
	“sequelize”: “^5.18.23”
	}
}

Setting up the express.js web server:

Following are the steps to set up the node js with express web server:

1. Open the Windows Explorer.

2. Navigate to the root folder.

3. In the root folder, you can create a new server.js file.

const express = require(“express”);
const bodyParser = require(“body-parser”);
const cors = require(“cors”);

const app = express();

Var corsOptons = {
	origin: “http://localhost:8080/”
		};
app.use (cors(corsOptions));

// parse requests of content-type - application/json

app.use (bodyParser.json());
// parse requests of content-type - application/x-www-form-urlencoded

app.use (bodyParser.urlencoded({extended:true}));

//simple route
app.get (“/”, (req, res) => {
res.json({message: “Welcome to Turing.com”});
});

// set port, listen for requests
const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
console.log (‘Server is running on port $(PORT).’ );
});

4. Import the cors, body-parser, and the express modules

Body-parser will parse the request for the creation of the req.body object. Express will help in building the REST APIs, while cors will provide express middleware. This will offer various options to cors.

5. Create an express app

Code source

6. Use() for adding cors, and body-parser middlewares.

Origin is set as ‘http://localhost:8081’

7. Define a GET route - ensure that the route is simple and easy to access.

8. Tune into port 8080 for the incoming request.

9. Run the app with the command: node server.js

10. Open a web browser

11. Go to the “http:// localhost: 8080/”. The specified message will be displayed.

Configuring MySQL database with sequelize:

Configuring the MySQL database with sequelize is a very crucial aspect. It helps in the development and configuration. The combination of node.js and sequelize works based on the configuration. Here are the following steps to perform:

1. Open Windows Explorer.
2. Navigate to the application folder.
3. Within the application folder, you can create a configuration folder as a db.config.js file under:

module.exports = {
HOST: “localhost”,
USER: “root”,
PASSWORD: “12345678”,
DB: “testdb”,
dialect: “mysql”, 
pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000
}
};

The first five parameters are mainly intended for MySQL. The pool parameter is an optional one and will be deployed in the sequelize connection pool configuration. The parameters used are explained below:

• Max - maximum number of connections that are permissible in a pool
• Min - minimum number of connections that are permissible in a pool
• Idle - maximum time in milliseconds that can be held idle before the release
• Acquire - maximum time in milliseconds which the pool seeks for making the connection, just before an error message pops up

Initializing a sequelize:

You can perform the following steps when you want to initialize a sequelize:

1. Open the Windows Explorer.

2. Navigate to the app folder.

3. Create a model folder in the app folder

4. In the model folder, create an index.js by entering the below code:

const dbConfig = require(“../config/db.config.js:);

const Sequelize = require(“Sequelize”);
const Sequelize = new Sequelize(dbCofig.DB, dbConfig.USER,
dbConfig.PASSWORD, {
	host: dbConfig.HOST,
	dialect: dbConfig.dialect,
	operationsAliases: false,
	pool: {
	max: dbConfig.pool.max,
	min: dbConfig.pool.min,
	acquire: dbConfig.pool.acquire,
	idle: dbConfig.pool.idle
	}
};
const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.tutorials = require(“./tutorial.model.js”) (sequelize, Sequelize);

module.exports = db;
The user should not forget to summon the sync() method in the server.js.
const app = express();
app.use(....);

const db = require(“./app/models”);
db.sequelize.sync();

5. When you need to drop the existing tables and the database is required to be resynchronized, enter the force: true code like the below:

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

console.log(“Drop and resync db.”);

});

Defining the sequelize model:

Follow these steps to define the sequelize model:

1. Open the Windows Explorer
2. Navigate to the models' folder
3. Create a tutorial.model.js file in the models folder using the below code:

module.express = (sequelize, Sequelize) => {
const Tutorial = sequelize.define(“Tutorial”, {
title: {
type: Sequelize.STRING
},
description: {
type: Sequelize.STRING
},
published: {
type: Sequelize.BOOLEAN
}
});
return Tutorial;
};

In the MySQL database, the model mentioned above will represent a tutorial table. The columns will get generated and published automatically. After the system initializes sequelize, the user will not have to write CRUD functions.

Creating the controller:

Here is a code to create a controller:

const db = require(“../models”);
// models path depends on your structure
const Tutorial = db.tutorials;

exports.create = (req, res) => {
// Validating the request
if (!req.body.title) {
res.status(400).send ({
message: “Content can be placed here!”
});
return;
}

// Creating a Tutorial
const Tutorial = {
title: req.body.title,
description: req.body.description,
published: req.body.published ? req.body.published : false
};

// Saving the Tutorial in the database
Tutorial.create(tutorial). then(data => {
res.send(data);
}) .catch(err => {
res.status(500).send ({
Message:
err.message || “Some errors will occur when creating a tutorial”
});
});
};

Retrieving the objects

To retrieve the objects, you should use the below code:

exports.findAll = (req, res) => {
const title = req.query.title;
var condition = title ? { title: { [Op.like]: ‘%${title}%’}}: null;

Tutorial.findAll({where: condition}). then(data => {
res.send(data);
}) .catch (err => {
res.status(500).send({
message:
err.message || “Some error have occurred when retrieving the tutorials.”
});
});
};

You must use the req.query.title to receive the query string from the Request and you can consider using the findAll() method for the same.

Retrieving a single object:

To find a single tutorial object with an id, the below code is applicable:

exports.findOne = (req, res) => {
const id = req.params.id;

Tutorial.findByPk(id).then(data => {
res.send(data);
}) .catch(err => {
res.status(500).send({
message: “Error while retrieving tutorial with id=”+ id
});
});
};

Defining the routes:

When the clients request an endpoint via an HTTP request like DELETE, GET, PUT, or POST, you can determine how the server will respond to every request. Such a response from the server is possible when you are setting up the below routes:

/api/tutorials: GET, POST, DELETE

/api/tutorials/:id: GET, PUT, DELETE

/api/tutorials/published: GET

To create a tutorial.routes.js inside the app/routes folder, use the below code:

module.exports => {
const tutorials = require(“../controllers/tutorials.controller.js”);

var router = require(“express”).Router();

// Creating a new Tutorial
router.post(“/”, tutorials.create);

// Retrieving all the Tutorials
router.get(“/”, tutorials.findAll);

// Retrieving all the published Tutorials
router.get(“/published”, tutorials.findAllPublished);

// Retrieving a single Tutorial with id
router.get(“/:id”, tutorials.findOne);

// Updating a Tutorial with ID
router.put(“/:id”, tutorials.update);

// Deleting a Tutorial with ID
router.delete(“/:id”, tutorials.delete);

// Creating a new Tutorial
router.delete(“/”, tutorials.deleteAll);

app.use(‘/api/tutorials’, router);
};

A controller from the /controllers/tutorial.controller.js is used in this procedure. You can also include routes in server.js by using app.listen(). To include the routes, you can use the below script:

require(“./app/routes/tutorial.routes”)(app);

// setting the port, listening for requesting
const PORT = ….;
app.listen(...);

Testing the API:

To test the APIs, here are the steps:

1. Open the Windows Explorer
2. Run the Node.js application
3. Type in the command on ‘node server.js’. The server is running on port 8080.

The pros and cons when you connect MySQL with node.js will allow the reader to have a better understanding of the library. This will help in creating a controller, defining the routes, and testing the APIs, which are vital for a smooth software functioning perspective.