MySQL in Node.js

What is Node.js

Node.js is an open-source, cross-platform, back-end JavaScript runtime environment that runs on the V8 engine and executes JavaScript code outside a web browser.

What is MySQL

MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language.

Prerequisites

1. You should have strong knowledge of Javascript.
2. Basic knowledge of Node.js and Express.js.

Creating Express.js App using Node.js

To get started we will first initialize npm in our backend by executing the below command.

npm init -y

Now once its initialized, lets install the required packages by executing the below command.

npm install express mysql body-parser nodemon

Setting up Node.js App

Next I will create a file called as server.js and the basic structure of our backend in Express.js.

const express = require("express");
const app = express();
const bodyParser = require("body-parser");
app.use(bodyParser.json());
app.get("/", (req, res)=>{
console.log("Root route get request");
res.send("This is root get request");
});
app.listen(5000, (req, res)=>{
console.log("Server is listening on Port 5000")
})

Setting up MYSQL

To start using MySQL I will be using MySQL workbench to execute queries and I will be using XAMPP to run MySQL server.

MySQL Workbench

XAMPP

Creating connection of MySQL and integrating it in your backend.

By default your MySQL credentials will be as follows:

{
    host: "localhost",
    user: "root",
    password: "",
    database: "testDB"
}

Now we will integrate MySQL with our backend.

Before integrating our database with our backend I execute the below commands in MySQL workbench to create database, create a table and insert record.

create database testDB
create table user
insert into user(username, password)
values("User1", "abc")

const express = require("express");
const app = express();
const bodyParser = require("body-parser");
const { createPool } = require("mysql");
app.use(bodyParser.json());
// MySQL Database POOL connection
const pool = createPool({
host: "localhost",
user: "root",
password: "",
database: "testDB",
connectionLimit: 10
});
// end of pool connection
app.listen(5000, (req, res)=>{
console.log("Server is listening on Port 5000")
})

Now we have a database named as testDB with some user data in it containing username and password.

Writing a Query

Now in ‘get’ request I am writing and executing the basic query and selecting data that exist in testDB user table.

const express = require("express");
const app = express();
const bodyParser = require("body-parser");
const { createPool } = require("mysql");
app.use(bodyParser.json());
// MySQL Database POOL connection
const pool = createPool({
host: "localhost",
user: "root",
password: "",
database: "testDB",
connectionLimit: 10
});
// end of pool connection
app.get("/", (req, res)=>{
console.log("Root route get request");
pool.query("select * from user", (err, result, field)=>{
if(err){
console.log(err);
}
else{
res.send(result);
}
});
});
app.listen(5000, (req, res)=>{
console.log("Server is listening on Port 5000")
})

Now the image will show that what will our root route looks like after running the select query and sending it to the web browser.

This is the data that is stored in our database. We are showing the db data on browser.

We are handling the error if an error will occur I will log that error on console. It can be a connection error, invalid query error etc.

Now the above error is occurring since I have deliberately made a mistake in our query to replicate an error.

const express = require("express");
const app = express();
const bodyParser = require("body-parser");
const { createPool } = require("mysql");
app.use(bodyParser.json());
// MySQL Database POOL connection
const pool = createPool({
host: "localhost",
user: "root",
password: "",
database: "testDB",
connectionLimit: 10
});
// end of pool connection
app.get("/", (req, res)=>{
console.log("Root route get request");
pool.query("selct * from user", (err, result, field)=>{
if(err){
console.log(err);
}
else{
res.send(result);
}
});
});
app.listen(5000, (req, res)=>{
console.log("Server is listening on Port 5000")
})

Now you might have better understanding on how to connect MySQL with Node.js. Please comment if you like this article and want to see a full video tutorial on my Youtube channel.