Nodejs with mysql connectivity example

by Posted on

In this tutorial we are going to learn about how to connect our Mysql database with Nodejs.
Actually it is a great combination every developers need this at some stage of his rich application, Because Mysql is a very popular database for web applications.


So this tutorial will explain you connectivity of Mysql with Nodejs in very simple steps.


First of all create your Mysql database and table

CREATE TABLE IF NOT EXISTS `posts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `description` text NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Create package.json file in your application root directory and paste below code.

package.json

{
  "name": "NodeJs-Mysql",
  "version": "0.0.1",
  "description": "NodeJs with mysql connectivity",
  "dependencies": {
    "express": "^4.13.1",
    "mysql": "^2.8.0"
  }
}

After that go to your app directory and run

 $ npm install

After successful installation of all the dependencies create app.js file and paste below code.

app.js

var express    = require("express");
var mysql      = require('mysql');
var app = express();
 
var connectionInfo = {
  host     : 'localhost',  // mysql server hostname 
  user     : 'root',       // mysql database username
  password : 'root',       // mysql database password
  database : 'node_mysql'  // mysql database name
}
 
var connection = mysql.createConnection(connectionInfo);
 
connection.connect(function(err){
 if(!err) {
    console.log("Database is connected successfully. \n");  
  } else {
   console.error('Error connecting: ' + err.stack);
   return;
  }
});
 
var query = 'SELECT * FROM posts';
connection.query(query, function(err, rows, fields) {
    console.log("Executing query: "+query+' \n');
    connection.end();
    if (err) throw err;
     console.log('Result is: \n\n', rows);
  });
 
console.log("Listening on on 5000");
app.listen(5000);

Your directory structure will be.
nodejs-mysql-1

Now finally run this command to see the results

$ node app.js

You can see the output in below image.
nodejs-mysql-2

You can learn more about nodejs-mysql commands from here https://github.com/felixge/node-mysql/

Hope this simple tutorial will help to to understand nodejs with mysql connectivity. 🙂

If you like this post please don’t forget to subscribe My Public Notebook for more useful stuff.

Tags: Node.js and MySQL tutorial, How to connect nodejs with mysql, Nodejs with mysql connection tutorial step by step

Published by admin