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;
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `label` varchar(16) NOT NULL,
  `valeur` int(11) NOT NULL,
  PRIMARY KEY (`id`)


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
npm http 304
npm http GET
npm http GET
npm http GET
npm http 304
npm http 304
npm http 304
npm http GET
npm http GET
npm http GET
npm http 304
npm http 304
npm http 304
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.


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';

  function select(error, results, fields) {
    if (error) {
    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");

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() {

sqlQuery.on("error", function(error) {

[important]Comments, improvement, idea : let me know. If this post has been helpful, make comments on your favorite social networks.[/important]

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