NodeJs MySQL Tutorial
MySQL is one of the most preferred databases by developers as it is open-sourced as well as efficient. This is why most prominent programming languages like Java, Python, Node.js, etc, provide drivers to access and perform transactions with MySQL. In this Node.js MySQL tutorial, I will demonstrate how to establish a connection with MySQL and perform various CRUD operations in a few simple and easy steps.
Below is the detailed list of the topics that will be discussed in this Node.js MySQL Tutorial:
- What is MySQL?
- Why Connect Node.js With MySQL?
- How to Create and Manage an Employee Database
What is MySQL?
MySQL is a relational database management system.
It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL Server.
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.
How to Create and Manage an Employee Database
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.
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 the 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
- Create an empty folder and name it node.js mysql.
- 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.
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 Node.js and MySQL. We define the following parameters, which in turn facilitate proper connection to the database.
- Host: Specifies the host that runs the database
- User: Sets the user’s name
- After we add the modules, we use the createConnection() method to create a bridge between Node.js and MySQL. We define the following parameters, which in turn facilitate proper connection to the database.
- Password: Sets up a password
- Database: Names the database
// Create connection
const db = mysql.createConnection({
host: "localhost",
user: "root",
password: "Datacademy",
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 in 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 to 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: "Sunil Shetty", 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 the 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 issues.
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.
This brings us to the end of this article on the Node.js MySQL Tutorial. Hope, it helped in adding value to your knowledge. If you want to gain more insights into Node.js you can refer to Our other articles on Node.js as well.
FAQ’s
Q1 What is Node.js MySQL?
Node.js MySQL is a Node.js library that provides an interface for communicating with a MySQL database. It enables you to perform operations on a database, such as reading and writing data, through Node.js applications.
Q2 How to install Node.js MySQL?
To install Node.js MySQL, you need to have Node.js installed on your system. Then, you can use npm (Node Package Manager) to install the Node.js MySQL library by running the following command in your terminal: npm install MySQL
Q3 How to perform CRUD operations in Node.js MySQL?
To perform CRUD (Create, Read, Update, Delete) operations in Node.js MySQL, you need to connect to the database and use various methods provided by the library, such as query() to execute SQL statements, insert() to insert data, select() to retrieve data, update() to update data and delete() to delete data.
