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 connectionconst pool = createPool({host: "localhost",user: "root",password: "",database: "testDB",connectionLimit: 10});// end of pool connectionapp.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 connectionconst pool = createPool({host: "localhost",user: "root",password: "",database: "testDB",connectionLimit: 10});// end of pool connectionapp.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 connectionconst pool = createPool({host: "localhost",user: "root",password: "",database: "testDB",connectionLimit: 10});// end of pool connectionapp.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.

--

--

--

Computer Science student with MERN stack website development expertise.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

React-Facebook-Messenger-APP 🚀

Level Up Your JavaScript Logging Game

BTC dedicated casino, up to 2BTC can be withdrawn! Arrived in seconds!

Situation with Langtang Valley Nepal #camping read here: https://t.co/GdRs9rI3hg

How to Consume/Publish RabbitMQ message in NodeJS

//platform.twitter.com/widgets.js from Twitter https://twitter.com/thehikeexperts

The “try…catch” :

Useful CLI Aliases for Frontend Developers

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Musab Abbasi

Musab Abbasi

Computer Science student with MERN stack website development expertise.

More from Medium

Express MongoDB

How to build a simple restful BLOG API backend application with Node & Express & MySQL & TypeORM…

Swagger in a Node.js project

Node.JS Express Framework