mysql access from node.js (update 04/20/2014)

nodejs_mysql

 

This post explains how to connect and make sql request to mysql database from node.js script. For using, you need to install node.js specific module, next …


 

To do before reading

- mysql server installed;
- know what node.js is (see post on Comprendre node.js
- node.js installed (see post on Installer node.js sous Windows).

 

Create mysql database and on table

It’s not hard to build it. Just execute next sql script on mysql client. You can use phpmyadmin to do that.

Database building mysqltest :

CREATE DATABASE mysqltest;

Create table test :

USE mysqltest;
CREATE TABLE IF NOT EXISTS `test` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `label` VARCHAR(16) NOT NULL,
  `valeur` INT(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

How to install nodejs mysql module

In command line, go to project directory and execute command :

npm install mysql

You must see below on command line :

npm http GET https://registry.npmjs.org/mysql
npm http 304 https://registry.npmjs.org/mysql
npm http GET https://registry.npmjs.org/require-all/0.0.3
npm http GET https://registry.npmjs.org/bignumber.js/1.0.1
npm http GET https://registry.npmjs.org/readable-stream
npm http 304 https://registry.npmjs.org/require-all/0.0.3
npm http 304 https://registry.npmjs.org/bignumber.js/1.0.1
npm http 304 https://registry.npmjs.org/readable-stream
npm http GET https://registry.npmjs.org/string_decoder
npm http GET https://registry.npmjs.org/core-util-is
npm http GET https://registry.npmjs.org/debuglog/0.0.2
npm http 304 https://registry.npmjs.org/core-util-is
npm http 304 https://registry.npmjs.org/string_decoder
npm http 304 https://registry.npmjs.org/debuglog/0.0.2
mysql@2.1.0 node_modules\mysql
├── require-all@0.0.3
├── readable-stream@1.1.11 (debuglog@0.0.2, string_decoder@0.10.25-1, core-util-is@1.0.1)
└── bignumber.js@1.0.1

 

javascript part

In directory project, create a new file named mysql.js to add javascript code for connecting to mysql database.

Retrieve a mysql object from mysql installed module :

var mysql = require('mysql');

Initialize a database connection with mysql object created below, a connection object (named mySqlClient in this example) is returned (change with your connection parameters) :

var mySqlClient = mysql.createConnection({
  host     : "localhost",
  user     : "user",
  password : "password",
  database : "mysqlTest"
});

This example get all datas from test table without distinction.

SELECT * FROM test

In this post, the table test has 3 fields (id, label et valeur), and the sql request return all (select *).

 

Extract data : first way

To run a sql select query and extract a result, mySqlClient object provides query method with parameters :
- sql query in string;
- an anonymous function called after request execution.

This anonymous function takes some parameters :
- an object (error) fixed to true in this case when sql execution error;
- a hashtable with data from sql request (results);
- number of fields or columns retrieved by sql request (fields).

var selectQuery = 'SELECT * FROM test';
 
mySqlClient.query(
  selectQuery,
  function select(error, results, fields) {
    if (error) {
      console.log(error);
      mySqlClient.end();
      return;
    }
 
    if ( results.length > 0 )  { 
      var firstResult = results[ 0 ];
      console.log('id: ' + firstResult['id']);
      console.log('label: ' + firstResult['label']);
      console.log('valeur: ' + firstResult['valeur']);
    } else {
	  console.log("Pas de données");
    }
    mySqlClient.end();
  }
);

The end method called by mySqlClient connection object, close mysql server connection.

 

Extract data : second way

In this work, callback function has not been used but some events linked with mysql library :
- result event is triggered by every rows lines;
- end event is triggered when sql request is terminated;
- error event is triggered by errors.

var selectQuery = 'SELECT * FROM test';
 
var sqlQuery = mySqlClient.query(selectQuery);
 
sqlQuery.on("result", function(row) {
  console.log('myField1: ' + row.myField1);
  console.log('myField2: ' + row.myField2);
});
 
sqlQuery.on("end", function() {
  mySqlClient.end();
});
 
sqlQuery.on("error", function(error) {
  console.log(error);
});
Comments, improvement, idea : let me know. If this post has been helpful, make comments on your favorite social networks.

Next post will illustrate how to build a javascript library for node.js dedicated to mysql database access.

 

2 commentaires pour mysql access from node.js (update 04/20/2014)

Laisser une réponse

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>