Working with Node JS ang MySql (Part 1)

Hello friends, now a days javascript is very useful for web development and very popular scripting language. So javascript basically a Client-Side scripting language, that runs on client browser. We can not connect the databases using javascript. But in this article we will learn about the connectivity between MySql database and javascript but via one application named as Node JS written in C, C++, JavaScript. So first I will speak about Node JS, then we will discuss how can we connect to MySql database using some JavaScript functions.

In this post we will cover following things,

• Why we need to connect with database using Node
• How we connect to MySql database from Node
• Step to connect with MySql

Why we need to connect with database using Node ?

Its a very common and basic thing that every Server-Side programming language or technology has some in-build classes, or methods or procedures to connect with database server for storing data.

Now a days every application is connecting with the database for storing data, modifying data, retrieving data and deleting the unused data. If a programming language or technology wont provide any such functionalities then the use of these programming language or technology is meaningless.

So as Node is a server side technology, it should have the functionalities to connect with databases.

How we connect to MySql database from Node ?

Like other programming languages or technologies, Node did not come with built-in functionalities to connect with databases like MySql. But there is an external module available (i.e mysql module) in the internet, by using that module we can able to connect with MySql database. 

So by using mysql module, we can able to connect and to do some operation using mysql module.

Steps to connect with MySql :
Now we are ready to learn how can we connect to MySql database by using Node.JS code. Following are the step by step procedure to connect with MySql database from Node application,

Step 1 : (Installing mysql module by using NPM)

If you already have installed mysql module for Node, then no worries otherwise you can use following command to install mysql module for Node,

> npm install mysql -----------> For Windows OS
or
> $npm install mysql ----------> For Linux OS

Now the following steps are the coding part.

Step 2 : (Creating the mysql object)

In this step we need to create a mysql object, which will take care about the connectivity with MySql database from our Node application. Following is the code to create the mysql object using mysql module,

var mysql = require('mysql');

Now the question is "what does this mysql object contains ?",  to know the answer just console it by using console.log(mysql). By consoling this object we we will see the following JSON or JavaScript object containing some pre-defined methods and fields,

{ 
  createConnection: [Function: createConnection],
  createPool: [Function: createPool],
  createPoolCluster: [Function: createPoolCluster],
  createQuery: [Function: createQuery],
  escape: [Function: escape],
  escapeId: [Function: escapeId],
  format: [Function: format] 
}

Step 3 : (Creating connection object)
The above mysql object contains some methods, by using the createConnection() method we can create connection object, this methods takes the object of the fields, such as host, user, password and database name as a parameter. So following is the code to create connection object,

var connection = mysql.createConnection({
 host: 'localhost', // Your Host Name
 user: 'root', // Your Database User Name
 password: '', // Your Database Password
 database: 'test' // Your Database Name
});

Now we created the connection object, to know what does this connection object contains, just consoling it by using console.log(connection), you will see following JSON or JavaScript object,

{ 
  domain: null,
  _events: {},
  _maxListeners: undefined,
  config:
   { host: 'localhost',
     port: 3306,
     localAddress: undefined,
     socketPath: undefined,
     user: 'root',
     password: undefined,
     database: 'test',
     connectTimeout: 10000,
     insecureAuth: false,
     supportBigNumbers: false,
     bigNumberStrings: false,
     dateStrings: false,
     debug: undefined,
     trace: true,
     stringifyObjects: false,
     timezone: 'local',
     flags: '',
     queryFormat: undefined,
     pool: undefined,
     ssl: false,
     multipleStatements: false,
     typeCast: true,
     maxPacketSize: 0,
     charsetNumber: 33,
     clientFlags: 455631 },
  _socket: undefined,
  _protocol:
   { domain: null,
     _events: {},
     _maxListeners: undefined,
     readable: true,
     writable: true,
     _config:
      { host: 'localhost',
        port: 3306,
        localAddress: undefined,
        socketPath: undefined,
        user: 'root',
        password: undefined,
        database: 'test',
        connectTimeout: 10000,
        insecureAuth: false,
        supportBigNumbers: false,
        bigNumberStrings: false,
        dateStrings: false,
        debug: undefined,
        trace: true,
        stringifyObjects: false,
        timezone: 'local',
        flags: '',
        queryFormat: undefined,
        pool: undefined,
        ssl: false,
        multipleStatements: false,
        typeCast: true,
        maxPacketSize: 0,
        charsetNumber: 33,
        clientFlags: 455631 },
     _connection: [Circular],
     _callback: null,
     _fatalError: null,
     _quitSequence: null,
     _handshakeSequence: null,
     _handshaked: false,
     _ended: false,
     _destroyed: false,
     _queue: [],
     _handshakeInitializationPacket: null,
     _parser:
      { _supportBigNumbers: false,
        _buffer: ,
        _longPacketBuffers: [],
        _offset: 0,
        _packetEnd: null,
        _packetHeader: null,
        _packetOffset: null,
        _onError: [Function],
        _onPacket: [Function],
        _nextPacketNumber: 0,
        _encoding: 'utf-8',
        _paused: false } },
  _connectCalled: false,
  state: 'disconnected',
  threadId: null 
}

Step 4 : (Open the connection)
Now will open the connection between Node application and MySql database, by using connect() method present in the above connection object, 

connection.connect();

Note :

If there is some wrong in connection parameters (i.e host, user, password, database) or database is down, then you will get the following error by saying,

ReferenceError: con is not defined
    at Object. (Line No)
    at Module._compile (module.js:460:26)
    at Object.Module._extensions..js (module.js:478:10)
    at Module.load (module.js:355:32)
    at Function.Module._load (module.js:310:12)
    at Function.Module.runMain (module.js:501:10)
    at startup (node.js:129:16)
    at node.js:814:3

Step 5 : (Executing the query)
After opening the connection successfully, now we can able to execute our SQL Query by using query() method present in connection object. Following the code to execute a SELECT query,

connection.query('SELECT * from < table name >', function(err, rows, fields) {
  if (!err)
    console.log('The rows are : ', rows);
  else
    console.log('Error while performing Query.');
});

The query() takes two parameters such as the SQL Statement as first parameter and one anonymous function as second parameter.

Note :

In this step I used only SELECT query, you can use your query according to your requirement and you can write your logics to operate the output coming from query() method.

Step 6 : (Close the connection)
This is the final step of this procedure. By using the end() method present inside connection object, we can close the connection. 

connection.end();

After closing the connection, we can not perform any database query execution operation.

So we need understand the above steps to connect with database and to perform certain database related operation from our Node application.

Following is the complete code to connect MySql database and to perform SELECT Query operation,

var mysql = require('mysql');
var connection = mysql.createConnection({
 host: 'localhost',
 user: 'root',
 password: '',
 database: 'test'
});
connection.connect();
connection.query('SELECT * from user', function(err, rows, fields) {
  if (!err)
    console.log('The rows are : ', rows);
  else
    console.log('Error while performing Query.');
});
connection.end();

If your mentioned table contains data, then you can those data as a JSON object by running the above code.

Note :

This is just a simple but basic concept about the connectivity between MySql database and Node application. In next post we will learn about more programming example related to connectivity between MySql and Node application.