How to connect a MySQL database with Node.js

Pradeep Maurya

5 years ago

MySQL is one of the most common open source databases in the world, and is also effective. Approximately every popular application language, like Java or PHP, provides a driver to access and run operation with MySQL. Node.js and MySQL are one of the binding requirements for any web application. Before examining stored processes and escaping user input, I will explain how to use the module to link to a MySQL database, execute the normal CRUD operations.

In this tutorial, I will explain the Node.js and MySQL related points. If you’re new to Node and Express then you’re not going to regret taking our Node course.

Learn Node js , Express js, and MongoDB Complete Course

Here’s how to use MySQL with Nodejs in easy steps:

Step -1: Install the mysql module to your node application


npm install mysql –save

npm install mysql –save

Step-2: Require mysql module in your route file


var mysql= require('mysql');

var mysql= require('mysql');

Step-3: Create New Database and Database Table in MySql


CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50),
  email varchar(50),
  etype varchar(50),
  hourlyrate varchar(500),
  totalhour varchar(500),
  total varchar(500),
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE users (

id int(11) NOT NULL AUTO_INCREMENT,

name varchar(50),

email varchar(50),

etype varchar(50),

hourlyrate varchar(500),

totalhour varchar(500),

total varchar(500),

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Step-4: Connecting to the Database

Now, let’s create a file called app.js. Here is the sample code that links to the database and performs a SQL query:


var mysql= require('mysql');

var con=mysql.createConnection({
    host: "localhost", // database host
  user: "root",  // MySQL username
  password: "",   // MySQL password
  database: "employee"  // your database name

});

con.connect(function(err){
if(err) throw err;
console.log('database connected successfully');
});

var mysql= require('mysql');

var con=mysql.createConnection({

host: "localhost", // database host

user: "root", // MySQL username

password: "", // MySQL password

database: "employee" // your database name

});

con.connect(function(err){

if(err) throw err;

console.log('database connected successfully');

});

Note:- Make sure you have started MySQL on your default port and modified the parameter in the code above.

Now open up your terminal and enter node app.js command. Once the connection is successfully established you will see the ‘database connected successfully’ message in the console.

Step-5: Now we will perform CURD Operation using Mysql and Express


var express = require('express');
var path = require('path');
var mysql= require('mysql');

var con=mysql.createConnection({
    host: "localhost",
  user: "root",
  password: "",
  database: "employee"

});

con.connect(function(err){
if(err) throw err;
console.log('database connected successfully');
});

var router = express.Router();

router.use(express.static(__dirname+"./public/"));

/// get all records from  database

router.get('/',function(req, res, next) {
 var getQuery="select * from `users`";
 con.query(getQuery,function(err,result){

    if(err) throw err;

    res.render('index', { title: 'Employee Records', records:result,success:'' });
 
 });

});


//// insert or save records into mysql database table
router.post('/', function(req, res, next) {
 
    var name= req.body.uname;
    var email= req.body.email;
    var etype= req.body.emptype;
    var hourlyrate= req.body.hrlyrate;
   var totalHour= req.body.ttlhr;
   var total= parseInt(req.body.hrlyrate) * parseInt(req.body.ttlhr);
 
   var insertQuery='insert into `users` (`name`,`email`,`etype`,`hourlyrate`,`totalhour`,`total`) VALUES (?,?,?,?,?,?)';
    var hourlyrate= req.body.hrlyrate;
   var total= parseInt(req.body.hrlyrate) * parseInt(req.body.ttlhr);
  var query=mysql.format(insertQuery,[name,email,etype,hourlyrate,totalHour,total]);
  con.query(query,function(err,response){
      if(err) throw err;
     // console.log(response.insertId);
     var getQuery="select * from `users`";
 con.query(getQuery,function(err,result){

    if(err) throw err;
    res.render('index', { title: 'Employee Records', records:result,success:'Record Inserted Successfully' });
 
  });
});
});

//// Edit records into mysql database table

router.get('/edit/:id', function(req, res, next) {
var id=req.params.id;

var getQuery="select * from `users` where `id`=?";
var query=mysql.format(getQuery,id);
 con.query(query,function(err,result){
     if(err) throw err;
     var string=JSON.stringify(result);
        var json =  JSON.parse(string);
       
res.render('edit', { title: 'Employee Records', records:json,success:'' });
 
});
});

//// Update records into mysql database table

router.post('/update/', function(req, res, next) {
    var id= req.body.id;
    var name= req.body.uname;
    var email= req.body.email;
    var etype= req.body.emptype;
    var hourlyrate= req.body.hrlyrate;
   var totalHour= req.body.ttlhr;
   var total= parseInt(req.body.hrlyrate) * parseInt(req.body.ttlhr);
 
   var updateQuery='UPDATE `users` SET `name`=? ,`email`=?,`etype`=?,`hourlyrate`=?,`totalhour`=?,`total`=? where `id`=?';
    var query=mysql.format(updateQuery,[name,email,etype,hourlyrate,totalHour,total,id]);
  con.query(query,function(err,response){
      if(err) throw err;
     // console.log(response.insertId);
  res.redirect('/');
});
});

//// Delete record from mysql database table

router.get('/delete/:id', function(req, res, next) {
    var id=req.params.id;

    var deleteQuery="delete from `users` where `id`=?";
    var query=mysql.format(deleteQuery,id);
     con.query(query,function(err){

         if(err) throw err;
 res.redirect('/');
    });
    
});

module.exports = router;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

var express = require('express');

var path = require('path');

var mysql= require('mysql');

var con=mysql.createConnection({

host: "localhost",

user: "root",

password: "",

database: "employee"

});

con.connect(function(err){

if(err) throw err;

console.log('database connected successfully');

});

var router = express.Router();

router.use(express.static(__dirname+"./public/"));

/// get all records from database

router.get('/',function(req, res, next) {

var getQuery="select * from `users`";

con.query(getQuery,function(err,result){

if(err) throw err;

res.render('index', { title: 'Employee Records', records:result,success:'' });

});

//// insert or save records into mysql database table

router.post('/', function(req, res, next) {

var name= req.body.uname;

var email= req.body.email;

var etype= req.body.emptype;

var hourlyrate= req.body.hrlyrate;

var totalHour= req.body.ttlhr;

var total= parseInt(req.body.hrlyrate) * parseInt(req.body.ttlhr);

var insertQuery='insert into `users` (`name`,`email`,`etype`,`hourlyrate`,`totalhour`,`total`) VALUES (?,?,?,?,?,?)';

var hourlyrate= req.body.hrlyrate;

var total= parseInt(req.body.hrlyrate) * parseInt(req.body.ttlhr);

var query=mysql.format(insertQuery,[name,email,etype,hourlyrate,totalHour,total]);

con.query(query,function(err,response){

if(err) throw err;

// console.log(response.insertId);

var getQuery="select * from `users`";

con.query(getQuery,function(err,result){

if(err) throw err;

res.render('index', { title: 'Employee Records', records:result,success:'Record Inserted Successfully' });

});

//// Edit records into mysql database table

router.get('/edit/:id', function(req, res, next) {

var id=req.params.id;

var getQuery="select * from `users` where `id`=?";

var query=mysql.format(getQuery,id);

con.query(query,function(err,result){

if(err) throw err;

var string=JSON.stringify(result);

var json = JSON.parse(string);

res.render('edit', { title: 'Employee Records', records:json,success:'' });

});

//// Update records into mysql database table

router.post('/update/', function(req, res, next) {

var id= req.body.id;

var name= req.body.uname;

var email= req.body.email;

var etype= req.body.emptype;

var hourlyrate= req.body.hrlyrate;

var totalHour= req.body.ttlhr;

var total= parseInt(req.body.hrlyrate) * parseInt(req.body.ttlhr);

var updateQuery='UPDATE `users` SET `name`=? ,`email`=?,`etype`=?,`hourlyrate`=?,`totalhour`=?,`total`=? where `id`=?';

var query=mysql.format(updateQuery,[name,email,etype,hourlyrate,totalHour,total,id]);

con.query(query,function(err,response){

if(err) throw err;

// console.log(response.insertId);

res.redirect('/');

});

//// Delete record from mysql database table

router.get('/delete/:id', function(req, res, next) {

var id=req.params.id;

var deleteQuery="delete from `users` where `id`=?";

var query=mysql.format(deleteQuery,id);

con.query(query,function(err){

if(err) throw err;

res.redirect('/');

});

module.exports = router;

In the above example code, you will get the curd (Save + Update + Read + Delete ) operation process using express and MySQL. You can also watch below video in Hindi with live explanation.

Conclusion :

MySQL is one of the world’s widely used database engine and it really works very well with Nodejs or express.

How to Create Pagination with Node.js, MongoDB, Express and EJS Step by Step

How to send Emails With Node.js

Express and Node JS Interview Questions & Answers

Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request

Pradeep Maurya

Pradeep Maurya is the Professional Web Developer & Designer and the Founder of “Tutorials website”. He lives in Delhi and loves to be a self-dependent person. As an owner, he is trying his best to improve this platform day by day. His passion, dedication and quick decision making ability to stand apart from others. He’s an avid blogger and writes on the publications like Dzone, e27.co