Subscribe via RSS Feed

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

May 8, 2013 26 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.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  - (Get Method)

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

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  - (Get Method)

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

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

Our sample api will be  - (Get Method)'/insertuser', function (req, res){
  console.log("POST: ");
  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) { 
         res.writeHead(200, {'Content-Type': 'text/plain'});

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

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

Our sample api will be  - (Post Method)

// Launch server

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.

<form name="input" action="" method="post">
Username: <input type="text" name="user">
<input type="submit" value="Submit">

Now run node app.js from command shell.

Enter your email address:

Delivered by FeedBurner

  • Argha DeySarkar

    Good for beginers to node.js and express.js

  • chirag dhuvad

    good article. Thanks a lot

  • Adel Elechi

    => Cannot GET /insertuser

    • 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)

    • 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 ?

        • 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 : and stackoverflow , but still get the same error

          • 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.


          • 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 .

          • Piyas De


            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

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

            You can look at -


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


          • 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.


    • 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 –

      Also you can view the usage in –

      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!


        • 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.


  • Shailendra Sharma

    great explanation, its all about basic, i am a beginner in node js and trying to create a small node based application with (node+express+mysql in backed and angular at front end ), i am little bit confuse about code structures in node js as i know express is a MVC framework for node js but when i search tutorials about node js development with express i have not seen any model folder in express, its have “routes” folder for routing (api calling),and also in your code where you wrote code to handle GET and POST request (its part of routing) you also wrote database query inside (its part of model or something db interaction).
    so i just want to ask is this only way(define queries in routing ) to create api with express or there is something else where we can separate out routing and our model ,
    and how i can manage routing files with model if i say i have a big project which have thousands api calls.

  • Majid Lotfi

    The same thing here , insert is not working :

    => Cannot GET /insertuser

    • Piyas De

      Hi, have you implemented and started the node.js application server? Also please understand, it is a post request and it will not work from browser url…tell us about the exact work you have done upto now….

      • Majid Lotfi

        thanks for your rapid reply, sorry I did not see sample.html, and because this tutorial has almost two years, express does not have anymore the bodyparser, so we have to install it then do :

        var bodyParser = require(“body-parser”);

        and replace :

        with :
        app.use(bodyParser.urlencoded({ extended: false }));

        but now when I try to submit that form, I got this error :

        TypeError: Cannot read property ‘user’ of undefined
        at C:UsersalotfiUIsMeanStackNodejsPart1-masternodeapp.js:71:22

        Thanks, your help is appreciated.

        • Piyas De

          Thank for replying. Yes,this post is not updated for Express 4. I will do that within a few days. it seems that, we are not getting user from mongodb and the object is null/undefined. Please check the MongoDB Connection. Also please share the file or the line when the issue is coming…


        • Piyas De

          OK. Wait till next week. We will make the new post and inform you here.

  • Julio Sena is the better example how to use nodejs applications