Node.js MySQL Tutorial: Create and Manage an Employee Database

Node.js is an extremely popular open-source, cross-platform web, and network app development resource. Thousands of developers around the world use it daily to develop I/O intensive web applications such as video streaming sites, single-page applications, online chat applications, and other web apps. Built on Google Chrome's JavaScript-based runtime environment, Node.js brings many advantages to the table, making it a better choice than other server-side platforms like Java or PHP. 

This Node.js MySQL Tutorial will help you learn how to connect your webserver to a MySQL database. First, we'll learn the basic concepts of MySQL, and then we will create our web application. The application will enable us to add, edit, and delete employees in a MySQL database, and we can view the changes through the Postman application or phpMyAdmin.

What is MySQL?

• MySQL is a fast and easy-to-use Relational Database Management System (RDMS)
• It is written in C and C++
• Since it is open-source, MySQL is popular with businesses of all sizes

Why Connect Node.js With MySQL?

There are several points that more than justify connecting Node.js with MySQL for data storage:

	MySQL is an open-source database, so you don’t have to pay anything to use it
	MySQL is very powerful. It can handle much of the functionality of the most expensive and powerful database packages
	MySQL supports diverse operating systems with many languages like PHP, JAVA, etc.
	MySQL performs quickly—even with large data sets

For the next part of our tutorial, we are going to learn how to create and manage an employee database. 

How to Create and Manage an Employee Database

Prerequisites

Node.js Installation

1. Download Node.js from https://nodejs.org/en/download/. Select the installer that is compatible with your operating system and environment.

Fig: Node.js download page

2. Run the Node.js installer and accept the license agreement. You can leave the other settings in default mode. The installer will install Node.js and prompt you to click on the finish button.

Fig: Node.js setup

3. Verify that Node.js was properly installed by opening the command prompt and typing this command: node --version

Fig: Node.js verification

4. When we install Node.js, we gain access to NPM (Node Package Manager). NPM includes many libraries that are used in web applications such as React. Verify whether it is installed or not with the following command in CMD: npm --version

Fig: NPM verification

Text Editor

Install the text editor of your choice. We are using Visual Studio Code in this tutorial, but you can also use other editors—like Atom and Sublime Text—if you are more comfortable with them.

Fig: Visual Studio Code download page

MySQL Installation

Similar to Node.js installation, you can download the MongoDB Community Server from its official website. Then, open the installer to install MySQL on your system, keeping the default settings. 

Postman

For this tutorial, we are using the Postman application to work with the API requests in our code.

To download the application, go to its official website.

Project Setup

1. Create an empty folder and name it node.js mysql.
2. Open the newly created directory in VS Code inside the terminal, and type npm init to initialize the project. Press Enter to leave the default settings as they are.

Fig: VS Code project

Let’s Code Now!

We are going to connect the Node.js web server with the MySQL database to store the employee details. The code that we’re writing now will allow us to create the employee database. We will be able to create, update, and delete employees in the database.

Learn to build network applications quickly and efficiently using JavaScript with the Node.js Training. Click to enroll now!

Let’s first take a look at how the project directory should look at the end of this tutorial. We are working only on a single file called index.js, so the project directory is very simple.

index.js

Create a file called index.js in the project directory. Since our main goal is to understand the connection between Node.js and MySQL database, this will be the only file that we create and work on in this project.

So let’s go ahead and explore what each snippet of code does in this file.

• We are importing a couple of modules that we will need in the application: express, and mysql.

const express = require("express");

const mysql = require("mysql");

• We can add these modules using the terminal inside VSCode
• After we add the modules, we use the createConnection() method to create a bridge between the Node.js and MySQL. We define the following parameters, which in turn facilitates proper connection to the database.
• Host: Specifies the host that runs the database
• User: Sets the user’s name

• Password: Sets up a password
• Database: Names the database

// Create connection

const db = mysql.createConnection({

  host: "localhost",

  user: "root",

  password: "simplilearn",

  database: "nodemysql",

});

• Now that we have created the bridge between Node.js and MySQL, let’s use the connect() method to actually connect to the database. This method will also throw an error if any issues arise while connecting to the MySQL database.

// Connect to MySQL

db.connect((err) => {

  if (err) {

    throw err;

  }

  console.log("MySql Connected");

});

• Since we are using the Express module to create the web server, we create a variable named app that behaves as an object of the express module.

const app = express();

• We use the GET API request to create the database. Notice that we have first defined the SQL query for creating the database. We then pass this query to the query() method along with the error callback method. The latter throws an error if the query wasn’t successfully executed.

// Create DB

app.get("/createdb", (req, res) => {

  let sql = "CREATE DATABASE nodemysql";

  db.query(sql, (err) => {

    if (err) {

      throw err;

    }

    res.send("Database created");

  });

});

• After the database is created, create a table called “employee” to store the employee details into the database. This table will have the attributes “id”, “name”, and “designation.” For the last step, we pass this query to the query() method and the query executes.

// Create table

app.get("/createemployee", (req, res) => {

  let sql =

    "CREATE TABLE employee(id int AUTO_INCREMENT, name VARCHAR(255), designation VARCHAR(255), PRIMARY KEY(id))";

  db.query(sql, (err) => {

    if (err) {

      throw err;

    }

    res.send("Employee table created");

  });

});

• Now that we have created both the database and the table, let’s add an employee to the database. We start by writing the relevant query for adding the record into the table. The record is added and we get a message stating that employee 1 has been added if the query gets successfully executed. Otherwise, we get an error message.

// Insert employee 1

app.get("/employee1", (req, res) => {

  let post = { name: "Jake Smith", designation: "Chief Executive Officer" };

  let sql = "INSERT INTO employee SET ?";

  let query = db.query(sql, post, (err) => {

    if (err) {

      throw err;

    }

    res.send("Employee 1 added");

  });

});

• We can also update an employee record that resides in the database. For example, to update the name of a particular employee record, we can use a GET request on that employee id, and the name will get updated if the query executes successfully.

// Update employee

app.get("/updateemployee/:id", (req, res) => {

  let newName = "Updated name";

  let sql = `UPDATE employee SET name = '${newName}' WHERE id = ${req.params.id}`;

  let query = db.query(sql, (err) => {

    if (err) {

      throw err;

    }

    res.send("Post updated...");

  });

});

• Finally, let’s delete an employee record from the database. To accomplish this, we first write an appropriate SQL query that deletes the employee whose id was provided in the GET request. We then send a message to the user stating that the employee record was deleted, if the query is successfully executed. Otherwise, the query() method will give an error message to the user.

// Delete employee

app.get("/deleteemployee/:id", (req, res) => {

  let sql = `DELETE FROM employee WHERE id = ${req.params.id}`;

  let query = db.query(sql, (err) => {

    if (err) {

      throw err;

    }

    res.send("Employee deleted");

  });

});

 

app.listen("3000", () => {

  console.log("Server started on port 3000");

});

• In the end, we set the server to listen at Port 3000.

app.listen("3000", () => {

  console.log("Server started on port 3000");

});

That’s all we need to do r to connect our Node.js web server with MySQL database. We can now run the application using the terminal in the VS Code. Use the command node index.js to start the web server.

The terminal should look like this if the server was started without any issue.

If your terminal also displays “server started”, it means that the server is running properly.

To use the API requests that we have defined in the application code, use the Postman application. Fill in the appropriate request for each task that you want to perform and hit the Send button to send the filled-in request.

If you have any problems following any step in this tutorial, please feel free to use the comments section below and get your questions answered.

Get Ahead of the Curve and Master Node.js Today

Now that you know how to connect Node.js to MySQL database, you may wonder how you can gain the skills necessary to take advantage of its rising popularity, either by upskilling or trying for a new career altogether. Fortunately, there are some great options available for learning this exciting and practical skill set at your own pace. 

Simplilearn's Node.js Certification training course will give you a great foundation and solid understanding of this popular platform. It combines live, instructor-led training, self-paced tutorials, and hands-on projects to help you become career-ready. Get started today and seize your future!