Subscribe via RSS Feed

Developing Web Application with Node.js, Express.js and MySql

May 8, 2013 18 Comments


This is our first effort towards make a web application with Javascript based Web Server.

To make this possible, we have selected -

1> Node.js for server side development

2> Rest based web service creation with express.js

3> Database – MySql

We have created a Proof of Concept with Javascript based web server, where we have not focused on any Javascript based ORM framework for mysql, with which we will deal with later posts/articles.

Our initial code can be downloaded here.

So here are the steps -

A> Download and install Node.js from here.

B> To Develop the application we need to install mysql extension for Node.js

Command – npm install mysql (should be connected to internet)

C> We need to install express.js for node.js

Command – npm install express  (should be connected to internet)

Now, we will try to describe the code portion -

 
var application_root = __dirname,
    express = require("express"),
	mysql = require('mysql');
    path = require("path");

Here we have initialised the express.js within javascript variables in respect of Node.js concept.

 
var app = express();

Here we have initialised the express web server in app variable.

 
var connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '',
database: "test"
});

Here we have made the connection to the mysql database using the Node.js mysql extension library.

 
// Config

app.configure(function () {
  app.use(express.bodyParser());
  app.use(express.methodOverride());
  app.use(app.router);
  app.use(express.static(path.join(application_root, "public")));
  app.use(express.errorHandler({ dumpExceptions: true, showStack: true }));
});

Here we have made the configuration related to express.js

 
app.get('/api', function (req, res) {
  res.send('Our Sample API is up...');
});

Here we have made our first REST based web service and tested whether the express.js is up.

Our sample api will be  - http://127.0.0.1:1212/api (Get Method)
javascriptnosqlbook

 
app.get('/getallusers', function (req, res) {
   connection.connect();
   connection.query('SELECT * FROM user;', function (error, rows, fields) { 
         res.writeHead(200, {'Content-Type': 'text/plain'});
		 str='';
		 for(i=0;i<rows.length;i++)
			str = str + rows[i].username +'\n';
		 res.end( str);
      });
   connection.end(); 
});

Here we have created another REST api to get all username from database and so have done the mysql query.

Our sample api will be  - http://127.0.0.1:1212/getallusers (Get Method)

 
app.get('/user/:id', function (req, res){
        connection.connect();
        connection.query('SELECT * FROM user where id ='+req.params.id, function (error, rows, fields) { 
         res.writeHead(200, {'Content-Type': 'text/plain'});
		 str='';
		 if(rows.length==0)
		 {
			res.end( 'no such record found...');
			//break;
		 }
		 else
		 {
			str = str + 'User is '+ rows[0].username +'\n';
			res.end( str);
		}
      }); 
      connection.end(); 
});

Here we have selected particular user by his/her id in database via REST calling.

Our sample api will be  - http://127.0.0.1:1212/user/4 (Get Method)

 
app.post('/insertuser', function (req, res){
  console.log("POST: ");
  connection.connect();
 
  username = req.body.user;
  password = req.body.user;
  console.log('insert into user ( username , password ) values (' + "'" + username +"'" +',' + "'"+ password +"'" +');');
  connection.query('insert into user ( username , password ) values (' + "'" + username +"'" +',' + "'"+ password +"'" +');', function (error, rows, fields) { 
		//console.log(error);
         res.writeHead(200, {'Content-Type': 'text/plain'});

			res.end( 'record inerted...');
		      });
connection.end();  
});

Here we have made a POST request to create an user via REST calling.

Our sample api will be  - http://127.0.0.1:1212/insertuser (Post Method)

 
// Launch server
app.listen(1212);

We have made the server to listen at 1212 port. Also below is the sample.html file which is used to call the REST Post API to insert the user.

 
<html>
<body>
<form name="input" action="http://127.0.0.1:1212/insertuser" method="post">
Username: <input type="text" name="user">
<input type="submit" value="Submit">
</form>
</body>
</html>

Now run node app.js from command shell.

Enter your email address:

Delivered by FeedBurner

Sign Up to read the rest of the content

Email will be used only for updates of our site

No Thanks

  • Argha DeySarkar

    Good for beginers to node.js and express.js

  • chirag dhuvad

    good article. Thanks a lot

  • Adel Elechi

    http://127.0.0.1:1212/insertuser

    => Cannot GET /insertuser

    • http://www.phloxblog.in/ Piyas De

      May you please explain the problem, so that we can review it at a deeper level?

  • Pingback: Express | Pearltrees

  • Rasika Vijayakrishnan

    is there any configuration to be done prior to running the testmysql.js file ? I am not able to connect to databse at all . i am a newbie with node.js . Error: connect ECONNREFUSED
    at errnoException (net.js:901:11)
    at Object.afterConnect [as oncomplete] (net.js:892:19)
    ——————–
    at Handshake.Sequence (/home/user/testproj/node_modules/mysql/lib/protocol/sequences/Sequence.js:15:20)
    at new Handshake (/home/user/testproj/node_modules/mysql/lib/protocol/sequences/Handshake.js:9:12)
    at Protocol.handshake (/home/user/testproj/node_modules/mysql/lib/protocol/Protocol.js:42:50)

    • http://www.phloxblog.in/ Piyas De

      After node.js installation, we need to run npm install mysql

      for mysql module for node.js installation and nothing else. What we guess from your code, the configuration for mysql is to be checked. i.e. to check -

      var connection = mysql.createConnection({
      host : ‘localhost’,
      user : ‘root’,
      password : ”,
      database: “test”
      });

      in the code. Your database host, user, password and dbname are to be specified here correctly.

      Hope, this can help you.

      • Rasika Vijayakrishnan

        Thanks for the quick response ! yea i checked the make.bat and the username password are correct . do i need to run “sudo yum install mysql” in the terminal as well ?

        • http://www.phloxblog.in/ Piyas De

          Please check if there is any port blocking issue from firewall or antivirus in your computer. If you have already installed mysql in your machine, then you do not need to run -”sudo yum install mysql” in your machine. Also check whether the mysql can be connected from terminal in your machine with the given credential as in the testmysql.js file.

          • Rasika Vijayakrishnan

            Thanks. . I tried checking via the terminal .and am getting the error : ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (111). Followed steps from :http://wordpressapi.com/solved-issue-connect-local-mysql-server-socket-varlibmysqlmysql-sock-2/ and stackoverflow , but still get the same error

          • http://www.phloxblog.in/ Piyas De

            Please try to reinstall the mysql and see whether the mysql can be connected from terminal. Then work with the node.js application.

            For any remaining issue get back to us.

            Thanks

          • Rasika Vijayakrishnan

            Thanks a lot .got it now ! :) and One more doubt .How to host the sample.html onto the server ? If i use my_http.createServer(function(request,response){ …
            I can request for the page and display to user. but in your code i am not able to understand where this functionality is taken care of .

          • http://www.phloxblog.in/ Piyas De

            Hi,

            You can look at app.js in the same repostitory. Please study express.js for this. When you do – node app.js, it will run on 127.0.0.1:1212.

            You can run the sample.html from any server, with proper cross-origin issue handling.

            You can look at -

            http://www.phloxblog.in/single-page-application-angular-js-node-js-mongodb-mongojs-module/

            for better understanding. There we have used mongodb as data repository instead of mysql.

            Thanks

          • Rasika Vijayakrishnan

            Thanks again.

  • pamasway

    Great post!! I have two questions:

    1- Do we need to write the res.writeHead part?
    2- I see that you didn’t open nor closed the connections. Does this way you’ve all that it needs? It worked here, but I’ve been reading that connections have to close around.

    Thanks!

    • http://www.phloxblog.in/ Piyas De

      Thanks for the Questions.

      Answer to your questions -

      1> We have used writeHead function as we were just showing the message to Browser in one line. Otherwise we should use response.write function.

      2> Thanks for rightly pointed out. While we were testing we have just used those code without connection closing and those were working fine.

      We will use connection.connect() and connection.end() in Production environment.

      In node.js, the code will work without using the connection.connect() command also.

      Anyway, we have committed the revised code in github – https://github.com/piyasde/NodejsPart1

      Also you can view the usage in – https://github.com/felixge/node-mysql

      Thanks again for correcting us…

      • pamasway

        I just read the updated code and I have just one more question, hehehe;

        I see that the connections is being closed after the res.end() method, which means that the response is already on the client. But only after that the connections is being closed, which means that node keeps reading and executing the rest of the code, right?

        sorry if I didn’t get how node works completely!

        Cheers

        • http://www.phloxblog.in/ Piyas De

          Yes…This is a good question…In node.js all the function calls are async in nature. so call to connection.query will execute in async process where the res.end is there and connection.end in the same function call which will be called in sync manner in the same function. So due to non-blocking execution model of node.js the connection.end will be called though the res.end will be called in separate child process.

          Thanks