How to connect Node.Js app with MySQL database?

Why use Node.Js?

Developers use Node.Js, a Javascript run time environment to write both server-side and client-side scripts in JavaScript.

MySQL

MySQL is a relational database management system that enables users to store data sent to servers from front-end applications in a structured manner.

Pre-installed Libraries and Frameworks

Here we have a list of pre-installed software and language dependencies.

• Installed Node.Js
• Installed Npm
• Installed MySQL server
• Installed MySQL Workbench

How to set up MySQL database

Open MySQL Workbench and click on Local instance MySQL80 as shown in the image below:

Upon clicking you will be redirected to another tab with a Query sub-tab open. Enter the following MySQL syntax into the editor and execute it:

create database todoapp;
use todoapp;
-- database table for todos
create table todolist (
taskname varchar(50),
task_date date,
task_detail varchar(50),
is_completed bool);
insert into todolist values ("gym", '2020-12-20', "workout at gym", false);

• Line#1-2: We use the 'create database' statement to create the database 'todoapp' and select it (Line#2) to perform operations on it.
• Line#4: The 'create table' statement is used to create the table 'todolist'.
• Line#6–9: We create columns 'taskname', 'task_date', 'task_detail', and 'is_completed' with the relevant data types and sizes.
• Line#11: End of 'create table' statement.    
• Line#12: We insert a row of data into the table 'todolist'.

Setting Up The Environment

Create a folder and redirect your path using the cd command. In the terminal, enter the following command to create the package.json file: npm init

Follow the instructions in the terminal. Now, install the MySQL package using the command:

npm install mysql2

This will help install all the relevant dependencies needed for this tutorial.

Connecting to the database

Create an 'index.js' file, copy the following code into an IDE like VS code, and enter 'node index.js' in the terminal.

 import mysql from "mysql2"
 var connection = mysql.createConnection({
    host     : 'localhost',
    user     : "<username>",
    password : "<password>",
    database : 'todoapp'
  });
 
   connection.connect(function(err) {
  if (err) {
    return console.error('error: ' + err.message);
  }

  console.log('Connected to the MySQL server.');
 });

 connection.end();

• Line#1: Import the 'mysql2' package as a module object named 'mysql'.
• Line#2: We create a connection to the database using the 'createConnection()' method and pass it to the connection object.
• Line#3–6: Information needed to connect to the database is passed in objects; including 'host', 'user', 'password', and 'database' to be connected.
• Line#7: End of 'createConnection()' scope.
• Line#9–15: The 'connect()' method is called when using the 'connection' object in order to create a connection instance. Lines#10–12 ensure error handling is carried out.
• Line#14 is executed if the database connection is successful and prints “Connected to the MySQL server” in the terminal.
• Line#17: 'connection.end()' method is called to ensure all remaining queries are executed before closing the connection to the database.

Output

Alternate Method

MySQL also provides us with a method to make a pool of connections. Copy the following code into an IDE like VS Code and enter 'node index.js' in the terminal.

 import mysql from "mysql2"
 
  var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'localhost',
  user            : 'momin',
  password        : 'momin140219',
  database        : 'todoapp'
  });
 
 pool.query('SELECT * FROM todolist', function (error, results, fields) 	   {
 if (error) throw error;
 console.log('The solution is: ', results[0]);
 });

• Line#1: We import the 'mysql2' package as a module object named 'mysql'.
• Line#3: We create a connection to the database using the ‘createPool()’ method and pass it to the 'pool' object.
• Line#4–9: Information needed to connect to the database is passed in objects; including 'connectionLimit', 'host', 'user', 'password', and 'database' to be connected.
• Line#10–14: The 'query()' method is used here to retrieve data from the table ‘todolist’. We pass an SQL statement as a string along with a function for error handling. The row of data is returned in a JSON object printed on the terminal see Line#13.

Improving security

In order to keep our database connection secure, we can move our 'username' and 'password' to a '.env' file. First, install the 'dotenv' module by entering the following code in the terminal: npm install dotenv

Make a '.env' file in your project folder and enter the following code:

• DB_USERNAME=<username>
• DB_PASSWORD=<password>

In your 'index.js' make the following highlighted changes.

import mysql from "mysql2"
 import dotenv from "dotenv";
 
 dotenv.config();
 
 var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'localhost',
  user            : `${process.env.DB_USERNAME}`,
  password        : `${process.env.DB_PASSWORD}`,
  database        : 'todoapp'
 });
 
 pool.query('SELECT * FROM todolist', function (error, results, fields) 
{
  if (error) throw error;
 console.log('The solution is: ', results[0]);
});

Line#2: We import the 'dotenv' module.

Line#4: The 'config()' method will parse the '.env' file and assign the contents to 'process.env'.

Line#9–10: 'DB_USERNAME' and 'DB_PASSWORD' are accessed from 'process.env' and passed to the 'user' and 'password' objects.

Ensure you don’t share the '.env' file while sharing this project.