Ken Adeniji's Blog

This article assumes that Node.js and mysql are installed.

Type in, the command below, to install the mysql module.

npm install mysql@2.0.0-alpha3

Listed below, is a sample program, mysql.js, for connecting to mysql.

var mysql      = require('mysql');

var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'user',
  password : 'secret',
  database : 'database name'
});

connection.connect();

connection.query('SELECT * from contact', function(err, rows, fields) {
  if (err) throw err;

  console.log("Number of rows: " + rows.length);
  console.log('Query result: ', rows);

  var columns=new Array(fields.length);
  for (var j = 0; j < fields.length; ++j) {
	columns[j] = fields[j]['name'];
  }

  for (var i in rows) {
	var row = rows[i];
	//console.log(row.FirstName);
	for (var j = 0; j < fields.length; ++j) {
		console.log(columns[j], ': ', row[columns[j]]);
	}	
  }

});

//Escaping query values
var contactId = 1;
var sql    = 'SELECT * FROM Contact WHERE ContactID = ' + connection.escape(contactId);
connection.query(sql, function(err, rows, fields) {
  if (err) throw err;

  console.log("Number of rows: " + rows.length);
  console.log('Query result: ', rows);
});

//Escaping query values  
var contactId = 2;
var sql    = 'SELECT * FROM Contact WHERE Contactid = ?';
connection.query(sql, [contactId], function(err, rows, fields) {
  if (err) throw err;

  console.log("Number of rows: " + rows.length);
  console.log('Query result: ', rows);
});

//Insert
var post  = {Dated: new Date(), FirstName: 'Jane', LastName: 'Smith'};
var query = connection.query('INSERT INTO Contact SET ?', post, function(err, rows, fields) {
  if (err) throw err;
  console.log(query.sql); 
  console.log(rows.insertId);
});

connection.end();

References

felixge / node-mysql