Node.js and MySQL, couple words about MongoDB

After introduction to node.js it’s time to go with more advanced and practical information. Today we play with communication between node.js and MySQL databases, and also we mention of MongoDB.

Node.js and MySQL

If we code using e.g. PHP, we should have LAMP installed in our system, so then we have also MySQL. Additionally we need only node.js and MySQL module:

$ npm install mysql

Configuration

For convenience, let’s create config.js configuration file:

var config = {};
config.db = {};

config.db.type = 'mysql';
config.db.charset = 'utf8';

config.db.username = 'user';
config.db.password = 'pass';
config.db.host = 'localhost';
config.db.dbname = 'node_tests'; // DB name

config.db.users_tbl = 'users'; // table name
// config.db.another_tbl = 'next_table'; // …

// export
module.exports = config;

We created a simple object that stores configuration. It’s important to “export” this object:

module.exports = config;

Now we can write our test script to make MySQL connection.

Example:

var mysql = require('mysql');
var config = require('./config.js');

var db_access = {
    host     : config.db.host,
    user     : config.db.username,
    password : config.db.password,
    database : config.db.dbname
};

var tbl = config.db.users_tbl;

var conn = mysql.createConnection(db_access);
conn.connect();

var queryString = 'SELECT * FROM ' + tbl;

conn.query(queryString, function (err, rows, fields) {
    if (err) { throw err; }

    for (var i in rows) {
        console.log('User names: ', rows[i].name);
    }
});

conn.end();

The code is neat, and his job is to retrieve and display users from our test database (for test purpose we need at least ID and firstname fields in DB table).

We start by adding necessary modules — mysql and our configuration. Then, create an access to the database, required by createConnection() function.

That object must contain following fields:

host, user, password, database

These data were obtained from our configuration file.

Next step it to establish connection and execute the query — conn.query(). At the end, we close connection.

Handling events

It’s a way to handle individual cases, such as received data ready for processing, an error occurred, etc.


var query = connection.query(queryString);

query.on('result', function(row) {
    console.log(row.firstname);

    /*
    connection.pause();
    // do some more processing on the row …
    console.log(row);
    connection.resume();
    */
});

query.on('error', function(err) {
    throw err;
});

connection.on('close', function(err) {
  if (err) {
    // unexpected closing of connection - reconnect back
    connection = mysql.createConnection(connection.config);
  } else {
    console.log('Connection closed');
  }
});

connection.end();

Node.js — creating DB and tables

There is no problem with performing other queries, such as creating databases and tables, as illustrated by the following examples:


connection.query('CREATE DATABASE foobar');

connection.changeUser({database: 'foobar'});

// or just
connection.query('USE foobar')

// table structure
connection.query('CREATE TABLE foobar.mytable(' +
  'id INT NOT NULL AUTO_INCREMENT, ' +
  'author VARCHAR( 128 ) NOT NULL, ' +
  'quote TEXT NOT NULL, PRIMARY KEY ( id )' +
')');

SQL injection, security, stability

Back-end developers should know well such issues.

Although we can use the connection.escape function:


connection.connect();

var key = 'something…';
var queryString = 'SELECT * FROM posts WHERE key = '
  + connection.escape(key);

connection.query(queryString, function(err, rows, fields) {
  …
});

But suppose we implement “Add a comment” feature, or something similar, where we use text gathered from the user, as a part of SQL Insert query.

We have to handle data, as it’s not only about security, but also about stability of our application.

For example apostrophes in text may cause syntactic error of the query. Then our entire node.js script can be interrupted.

So we can easy write our own functions, e.g. sanitize(), or well-known from PHP, addslashes() function (php.js).

Sample implementation:

// mytools.js
var mytools = {

  sanitize: function (html) {
    return String(html)
      .replace(/&(?!\w+;)/g, '&amp;')
      .replace(/</g, '&lt;')
      .replace(/>/g, '&gt;')
      .replace(/"/g, '&quot;');
  },

  addslashes: function (str) {
    return (str + '')
      .replace(/[\"']/g, '\$&')
      .replace(/\u0000/g, '





                                
After introduction to node.js it’s time to go with more advanced and practical information. Today we play with communication between node.js and MySQL databases, and also we mention of MongoDB.
Node.js and MySQL
If we code using e.g. PHP, we should have LAMP installed in our system, so then we have also MySQL. Additionally we need only node.js and MySQL module:
$ npm install mysql
Configuration
For convenience, let’s create config.js configuration file:

var config = {};
config.db = {};

config.db.type = 'mysql';
config.db.charset = 'utf8';

config.db.username = 'user';
config.db.password = 'pass';
config.db.host = 'localhost';
config.db.dbname = 'node_tests'; // DB name

config.db.users_tbl = 'users'; // table name
// config.db.another_tbl = 'next_table'; // …

// export
module.exports = config;


We created a simple object that stores configuration. It’s important to “export” this object:
module.exports = config;
Now we can write our test script to make MySQL connection.

Example:

var mysql = require('mysql');
var config = require('./config.js');

var db_access = {
    host     : config.db.host,
    user     : config.db.username,
    password : config.db.password,
    database : config.db.dbname
};

var tbl = config.db.users_tbl;

var conn = mysql.createConnection(db_access);
conn.connect();

var queryString = 'SELECT * FROM ' + tbl;

conn.query(queryString, function (err, rows, fields) {
    if (err) { throw err; }

    for (var i in rows) {
        console.log('User names: ', rows[i].name);
    }
});

conn.end();



The code is neat, and his job is to retrieve and display users from our test database (for test purpose we need at least ID and firstname fields in DB table).
We start by adding necessary modules — mysql and our configuration. Then, create an access to the database, required by createConnection() function.
That object must contain following fields:
host, user, password, database
These data were obtained from our configuration file.
Next step it to establish connection and execute the query — conn.query(). At the end, we close connection.
Handling events
It’s a way to handle individual cases, such as received data ready for processing, an error occurred, etc.


var query = connection.query(queryString);

query.on('result', function(row) {
    console.log(row.firstname);

    /*
    connection.pause();
    // do some more processing on the row …
    console.log(row);
    connection.resume();
    */
});

query.on('error', function(err) {
    throw err;
});

connection.on('close', function(err) {
  if (err) {
    // unexpected closing of connection - reconnect back
    connection = mysql.createConnection(connection.config);
  } else {
    console.log('Connection closed');
  }
});

connection.end();



Node.js — creating DB and tables 


There is no problem with performing other queries, such as creating databases and tables, as illustrated by the following examples:


connection.query('CREATE DATABASE foobar');

connection.changeUser({database: 'foobar'});

// or just
connection.query('USE foobar')

// table structure
connection.query('CREATE TABLE foobar.mytable(' +
  'id INT NOT NULL AUTO_INCREMENT, ' +
  'author VARCHAR( 128 ) NOT NULL, ' +
  'quote TEXT NOT NULL, PRIMARY KEY ( id )' +
')');




SQL injection, security, stability
Back-end developers should know well such issues.
Although we can use the connection.escape function:


connection.connect();

var key = 'something…';
var queryString = 'SELECT * FROM posts WHERE key = '
  + connection.escape(key);

connection.query(queryString, function(err, rows, fields) {
  …
});



But suppose we implement “Add a comment” feature, or something similar, where we use text gathered from the user, as a part of SQL Insert query.
We have to handle data, as it’s not only about security, but also about stability of our application.
For example apostrophes in text may cause syntactic error of the query. Then our entire node.js script can be interrupted.
So we can easy write our own functions, e.g. sanitize(), or well-known from PHP, addslashes() function (php.js).
Sample implementation:

// mytools.js
var mytools = {

  sanitize: function (html) {
    return String(html)
      .replace(/&(?!\w+;)/g, '&amp;')
      .replace(/</g, '&lt;')
      .replace(/>/g, '&gt;')
      .replace(/"/g, '&quot;');
  },

  addslashes: function (str) {
    return (str + '')
      .replace(/[\"']/g, '\$&')
      .replace(/\u0000/g, '\0');
  }
};

module.exports = mytools;


Usage

var tools = require('./mytools.js');

var comment = tools.sanitize(res_data.comment);
// comment = tools.addslashes(comment);


In this way, we can safely use the variable in our query. We will avoid both using dangerous elements (e.g. JS scripts in the content), as well as issues with executing queries (apostrophes in text).
Query example — node.js and MySQL — Insert:


function insertCommentData(socket, our_data) {

  var connection = mysql.createConnection(db_access);
  connection.connect();

  var clean = tools.addslashes(socket.content);

  var q = "INSERT INTO " + comments_tbl;
  q += " (user_id, content, created_at)";
  q += " VALUES (" + socket.user_id + ", " + clean + ", NOW() )";
  // console.log(q);

  connection.query(q, function (qe, qr) {
    if (qe) { console.log(qe); }

    // do something with result …

    connection.end();
  });
}



When querying, we can perform some operations on the result, and e.g. close the connection, if won’t be necessary anymore.
The qe and qr variables are respectively: query errors (if any) and the query results.
Very useful information from the result can be the ID of last inserted record.
Example — last insert ID:


…
connection.query(q, function (qe, qr) {
  if (qe) { console.log(qe); }

  // add new information to the object
  our_data.comment_id = qr.insertId;

  // send this object to all sockets
  io.sockets.emit("e_new_comment", {
    message: our_data
  });

  connection.end();
});



Examples assume data transfer through the socket. About socket.io we will write more in the next article.
SELECT COUNT
Now we show how to execute and get the result of SELECT COUNT query.
For example we may want to execute INSERT query (add new data), or UPDATE, depending on whether a record is already in the database or not:


function setStatus(user_id, status) {

  var connection = mysql.createConnection(db_access);
  connection.connect();

  var q1 = "SELECT COUNT(1) AS cnt FROM " + user_status_tbl + " WHERE user_id = '" + user_id + "'";

  connection.query(q1, function (err, rows, fields) {
    if (err) { console.log(err); }

    if ((rows[0].cnt) > 0) {
      // update
      var q2 = "UPDATE " + user_status_tbl + " SET status = " + status + ", updated_at = NOW() ";
      q2 += "WHERE user_id = '" + user_id + "' ";

      connection.query(q2, function (qe, qr) {
        if (qe) { console.log(qe); }

        console.log('Updated #' + user_id);

        connection.end();
      });
    } else {
      // add
      var q2 = "INSERT INTO " + user_status_tbl + " (user_id, updated_at, status) ";
      q2 += "VALUES (" + user_id + ", NOW(), " + status + ")";

      connection.query(q2, function (qe, qr) {
        if (qe) { console.log(qe); }

        console.log('Inserted #' + user_id);

        connection.end();
      });
    }
  });
}



We added alias “cnt” for out result, so we refer as following: rows[0].cnt
With these fundamentals, we are actually ready to work effectively with node.js and MySQL.


Now let’s take a look at MongoDB — another database, very popular in case of node.js.
Node.js and MongoDB
Mongo is open, non-relational database management system, characterized by high scalability.
The data are stored as documents in the JSON format, which allows applications to process the data in more natural way, with maintaining the ability to create a hierarchy and indexing. It is a very efficient system.
Preparation
Install MongoDB for our system.
E.g. in Windows:
$ cd C:\Program Files\MongoDB 2.6 Standard\bin
$ md \data\md
$ mongod
New DB:
$ mongod –dbpath C:\Projects\node\catalog1\data
Open the next console and connect to the DB:
$ cd C:\Program Files\MongoDB 2.6 Standard\bin
$ mongo
In mongo console write:
$ use catalog1
We choose the database. We can now insert test data in console:

db.usercollection.insert({ "username" : "testuser1", "email" : "testuser1@testdomain.com" })
// …


Getting data (the pretty() function adds linebreaks):
$ db.usercollection.find().pretty()
To save data permanently, use save():

db.usercollection.save({ "username" : "testuser1", "email" : "testuser1@testdomain.com" })
// …


Remove all data:

db.dropDatabase();


To work with MongoDB in node.js, we will need modules mongodb, and for example monk:



var mongo = require('mongodb');
var monk = require('monk');
var db = monk('localhost:27017/catalog1');
…


In one of the next tutorials we will write about express.js, and then we also will work more with MongoDB in practice.
Node.js and MySQL — summary
We have tried to present the key information you need to work with MySQL in node.js. Also mentioned about MongoDB. We’re working on the next articles and tutorials about great node.js technology (and related solutions).
Thank you!

Posted in Advanced, Node.js, Practice, Tutorials

Tags: DB, examples, node.js, tutorial

2 Responses to “Node.js and MySQL, couple words about MongoDB”

Node.js and socket.io - a basic tutorial - javascript-html5-tutorial.com says:

[…] presenting basics of node.js and how to work with databases, it’s time to reach for even more interesting […]
Node.js and socket.io tutorial - simple real-time comments - javascript-html5-tutorial.com says:

[…] Then we call the insertComment() function, which saved the data in DB. […]

Node.js and socket.io - a basic tutorial - javascript-html5-tutorial.com says:

[…] presenting basics of node.js and how to work with databases, it’s time to reach for even more interesting […]

Node.js and socket.io tutorial - simple real-time comments - javascript-html5-tutorial.com says:

[…] Then we call the insertComment() function, which saved the data in DB. […]

JavaScript & HTML5 Tutorial