Using NodeJS with MySQL

Post to Twitter

As NodeJS grows supporting libraries continue to be developed and evolve. Today I’m going to use one of the many MySQL libraries available for NodeJS: node-mysql. I’ll be using Ubuntu 10.10 along with MySQL so if you need to install NodeJS then see my article here.


Installing node-mysql:

$ npm install mysql

For this example I used the following SQL to create a table in a database I named NodeSample.

CREATE TABLE `NodeSample`.`MyTable` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`firstname` VARCHAR( 20 ) NOT NULL ,
`lastname` VARCHAR( 20 ) NOT NULL ,
`message` TEXT NOT NULL
) ENGINE = MYISAM ;

I found a good source on how to interact with MySQL + NodeJS: Utah JS and their article here by Dan Baker. I’ve borrowed some of the code there and modified it as needed for this example.

Connecting to MySQL with NodeJS with node.mysql:

var sys = require('sys');

var Client = require('mysql').Client;
var client = new Client();

client.user = 'someuser';
client.password = 'password';

client.connect(function(error, results) {
  if(error) {
    console.log('Connection Error: ' + error.message);
    return;
  }
  console.log('Connected to MySQL');
});

Once the connection is made you set the MySQL table you want to use:

ClientConnectionReady = function(client)
{
	client.query('USE NodeSample', function(error, results) {
		if(error) {
			console.log('ClientConnectionReady Error: ' + error.message);
			client.end();
			return;
		}
	});
};

When that is ready you can actually then query the database or do whatever you need to do. The following is a example of inserting some data to MySQL and then extracting it out again.

var sys = require('sys');

var Client = require('mysql').Client;
var client = new Client();

client.user = 'someuser';
client.password = 'password';

console.log('Connecting to MySQL...');

client.connect(function(error, results) {
  if(error) {
    console.log('Connection Error: ' + error.message);
    return;
  }
  console.log('Connected to MySQL');
  ClientConnectionReady(client);
});

ClientConnectionReady = function(client)
{
	client.query('USE NodeSample', function(error, results) {
		if(error) {
			console.log('ClientConnectionReady Error: ' + error.message);
			client.end();
			return;
		}
		ClientReady(client);
	});
};

ClientReady = function(client)
{
  var values = ['Chad', 'Lung', 'Hello World'];
  client.query('INSERT INTO MyTable SET firstname = ?, lastname = ? , message = ?', values,
    function(error, results) {
      if(error) {
        console.log("ClientReady Error: " + error.message);
        client.end();
        return;
      }
      console.log('Inserted: ' + results.affectedRows + ' row.');
      console.log('Id inserted: ' + results.insertId);
    }
  );
  GetData(client);
}

GetData = function(client)
{
  client.query(
    'SELECT * FROM MyTable',
    function selectCb(error, results, fields) {
      if (error) {
          console.log('GetData Error: ' + error.message);
          client.end();
          return;
      }
      // Uncomment these if you want lots of feedback
      //console.log('Results:');
      //console.log(results);
      //console.log('Field metadata:');
      //console.log(fields);
      //console.log(sys.inspect(results));
      
      if(results.length > 0)
      {          
        var firstResult = results[0];
        console.log('First Name: ' + firstResult['firstname']);
        console.log('Last Name: ' + firstResult['lastname']);
        console.log('Message: ' + firstResult['message']);
      }
  });

  client.end();
  console.log('Connection closed');
};

Results:

Post to Twitter

This entry was posted in JavaScript, Node.js, Open Source, Ubuntu. Bookmark the permalink.

11 Responses to Using NodeJS with MySQL

  1. Gavin says:

    Chad, were you using Ubuntu 10.10 desktop or server? I have no problems connecting and using the node-mysql module on my desktop, but on my server I cannot get it to connect at all. I have had success with node-mysql-native connecting, but I would sure rather be using node-mysql.

    I am assuming I am missing some dependancy or something but after days have not figured out what it may be.

  2. Chad Lung says:

    #Gavin,

    I was using Ubuntu 10.10 Desktop.

    Chad

  3. Gavin says:

    Well – it looks like I found the problem and thought I would post back as it is likely that others may face it. The problem had nothing to do with the OS, it was all the processor.

    I set up a dev server with some old hardware I had just lying around, and the CPU was an AMD Athlon 2600+ which does not have SSE2 support. I was having problems with several modules throwing “Illegal Instruction” errors. The core of Node.js was working just fine for me, but the modules are where the problem came in.

    Hopefully this helps some readers avoid days of hair-pulling and frustration. So much for using old hardware to develop on :( But, hey, I now have an excuse to buy a new machine, lol!

    Thanks, Chad for your reply and putting together great tutorials! The project I am working on is a node/db one and I have coe to find that I will be going with MongoDB as it seems to have many advantages over MySQL for web apps – take a look into it if you have time.

  4. Pingback: Node.js com mysql – link | suissacorp.com.br

  5. Barnaby says:

    Something about mixing Javascript with SQL strings looks very _very_ wrong. Why wouldn’t you use an ORM?

    Wouldn’t something like this be more readable/maintainable:
    myObj.find({ where: {someField: ‘aValue’} }).on(‘success’, function(results) {…..});

  6. Chad Lung says:

    @Barnaby,

    This article isn’t about working with an ORM – the point of the article is just to get NodeJS and MySQL working together.

    Chad

  7. Pingback: How to Install Node.js on Ubuntu « KaixersofT { ScriptBlocK } Weblog

  8. Anthony Webb says:

    This example looks straight forward enough. Trying to use it in an express web app. I thought I would create the connection when the app spun up, and then re-use the client connection.

    It appears as this wont work as the client gets dropped occasionally. Are there any good ways to deal with this? I hate to think I have to build and tear down a mysql client for every single request?

  9. You also have http://nodejsdb.org/db-mysql

    It is faster than the node-mysql pure JS solution because it’s developed in C++ as a node extension.

    db-mysql is part of NodeDB, an effort to code drivers for the most popular relational databases. So far drivers were built for MySQL, Drizzle, and Oracle, and PostgresSQL & MSSQL drivers are being worked on.

  10. Agustin says:

    Hi Chad

    I’m really a newbie in Node.js,
    What is the main benefit of using this node-mysql library?

    Does it offer better performance in queries?

    Agus

  11. Chad Lung says:

    @Agustin,

    I think the benefit right now is how easy it is the install and get up and running. With an alternative like DB-MySQL you need the MySQL client libraries on your system and in your path – and will most likely get better performance (of course everything depends on your code).

    Chad

Comments are closed.