UtahJS

• Articles
• Meetings
• Members
• Call For Papers - UtahJS Conference 2012
• Join / Contribute

NodeJS and MySQL Introduction

By Dan Baker, published 2010-09-22

STEP 1: I started at http://github.com/felixge/node-mysql To install:

npm install mysql

Which installed it into the guts of node (\usr\local\lib\node\.npm\mysql\0.5.0\package\lib\mysql) It installed mysql and gently. STEP 2: Create a new test app (app.js) with the following in it:

 var Client = require('mysql').Client; var client = new Client(); client.user = 'myusername'; client.password = 'mypassword'; console.log("connecting..."); client.connect(function(err, results) { if (err) { console.log("ERROR: " + err.message); throw err; } console.log("connected."); clientConnected(client); }); clientConnected = function(client) { } 

Run this (node app.js), and see if you get a "connected" message or an "error" message. If you got an error, it should be because you either don't have MySQL installed on this machine or your username/password is wrong. STEP 3: Create a database to test with. Edit your new app.js file, and add the following:

 clientConnected = function(client) { client.query('CREATE DATABASE test', function(err, results) { if (err && err.number != Client.ERROR_DB_CREATE_EXISTS) { console.log("ERROR: " + err.message); throw err; } console.log("database created OR already exists."); dbCreated(client); }); }; dbCreated = function(client) { }; 

STEP 4: Just select this newly created database to work with. Note: You should notice that each step has a callback function that gets called when the requested database operation completes (or errors). I have called a new function within each of these callback functions to reduce the nesting, and hopefully make it slightly easier to read and understand.

 dbCreated = function(client) { client.query('USE test', function(err, results) { if (err) { console.log("ERROR: " + err.message); throw err; } useOk(client); }); }; useOk = function(client) { }; 

STEP 5: Time to create a table. We'll create a rather standard MySQL table. It will contain an auto-incrementing id field, a couple text fields, and a date field. We'll make the id field a primary key. Hint: If you ever want to see what one of these callback functions get passed to it, try the following super-awesome line: (It displays the contents of a variable -- in this case all arguments passed in)

 console.dir(arguments); 

Now, add the following to your growing app.js file:

 useOk = function(client) { client.query( 'CREATE TABLE table1'+ '(id INT(11) AUTO_INCREMENT, '+ 'title VARCHAR(255), '+ 'text TEXT, '+ 'created DATETIME, '+ 'PRIMARY KEY (id));', function(err, results) { if (err && err.number != Client.ERROR_TABLE_EXISTS_ERROR) { console.log("ERROR: " + err.message); throw err; } console.log("table ready"); tableReady(client); } ); }; tableReady = function(client) { }; 

STEP 6: Let's insert a row of data into our table. We'll use the hint from the previous step to see what the results look like after inserting into the table.

tableReady = function(client) { client.query( 'INSERT INTO table1'+ ' SET title = ?'+ ', text = ?'+ ', created = ?', ['super cool', 'this is a nice text', '2010-08-16 10:00:23'], function(err, results) { if (err) { console.log("ERROR: " + err.message); throw err; } console.dir(results); console.log("Inserted "+results.affectedRows+" row."); console.log("The unique id was " + results.insertId); tableHasData(client); } ); }; tableHasData = function(client) { }; 

STEP 7: Now, we can finally query the database and get back a result set. We will select everything out of our table. If you are following along with this example as you go, you should get a few rows of data (since each time you run this app, it will insert a new row of data). Hint: You can get the meta data associated with the columns (I've commented out those two lines below). If you want to see the meta data, un-comment out the lines and run the app again.

 tableHasData = function(client) { client.query( 'SELECT * FROM table1', function selectCb(err, results, fields) { if (err) { console.log("ERROR: " + err.message); throw err; } console.log("Got "+results.length+" Rows:"); console.log(results); //console.log("The meta data about the columns:"); //console.log(fields); client.end(); }); }; 

That is the end. You should now have a simple app that connects to MySQL, creates a database, creates a table, inserts a row, and queries the table. Good luck.