How to connect a MySQL database with Node.js

Pradeep Maurya
mysql-with-node

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

Step-2: Require mysql module in your route file 

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 ;

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');
});

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;

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

Related posts