MySQL with Node.js

Using MySQL with Node.js & the node-mysql JavaScript Client

Installing node-mysql

node-mysql can be installed via npm. We can get up and running like so:

mkdir sp-node-mysql
cd sp-node-mysql
npm install mysql

Once you are done installing node-mysql module, you are good to go. For this demo I’ll be using a database called sitepoint and a table called employees. Here’s a dump of the database, so that you can get up and running quickly, if you wish to follow along:

CREATE TABLE employees (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50),
location varchar(50),
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO employees (id, name, location) VALUES
(1, 'Jasmine', 'Australia'),
(2, 'Jay', 'India'),
(3, 'Jim', 'Germany'),
(4, 'Lesley', 'Scotland');
Now, let’s create a file called app.js in our sp-node-mysql directory and see how to connect to MySQL from Node.js.

app.js


var mysql = require("mysql");

// First you need to create a connection to the db
var con = mysql.createConnection({
host: "localhost",
user: "jay",
password: "jay"
});


con.connect(function(err){
if(err){
console.log('Error connecting to Db');
return;
}
console.log('Connection established');
});


con.end(function(err) {
// The connection is terminated gracefully
// Ensures all previously enqueued queries are still
// before sending a COM_QUIT packet to the MySQL server.
});

Now open up a terminal and enter node app.js. Once the connection is successfully established you should be able to see the “Connection established” message in the console. If something goes wrong (for example you enter the wrong password), a callback is fired, which is passed an instance of the JavaScript Error object (err). Try logging this to the console to see what additional useful information it contains.

If JavaScript task runners are your thing, you can watch the file app.js for changes and have the task runner execute it every time a change is detected. Here’s how you might do that with Grunt.

package.json

{
"name": "sp-node-mysql",
"version": "0.1.0",
"devDependencies": {
"grunt": "~0.4.5",
"grunt-contrib-watch": "^0.6.1",
"grunt-execute": "^0.2.2",
}
}

Gruntfile.js

module.exports = function (grunt) {
grunt.initConfig({
execute: {
target: {
src: ['app.js']
}
},
watch: {
scripts: {
files: ['app.js'],
tasks: ['execute'],
},
}
});


grunt.loadNpmTasks('grunt-contrib-watch');
grunt.loadNpmTasks('grunt-execute');
};
Then run npm install, followed by grunt watch.

Executing Queries

Now that you know how to establish a connection to MySQL from Node.js, let’s see how to execute SQL queries. We’ll start by specifying the database name (sitepoint) in the createConnection command.

var con = mysql.createConnection({
host: "localhost",
user: "jay",
password: "jay",
database: "sitepoint"
});
Once the connection is established we’ll use the connection variable to execute a query against the database table employees.

con.query('SELECT * FROM employees',function(err,rows){
if(err) throw err;

console.log('Data received from Db:\n');
console.log(rows);
});
When you run app.js (either using grunt-watch or by typing node app.js into your terminal), you should be able to see the data returned from database logged to the terminal.

[ { id: 1, name: 'Jasmine', location: 'Australia' },
{ id: 2, name: 'Jay', location: 'India' },
{ id: 3, name: 'Jim', location: 'Germany' },
{ id: 4, name: 'Lesley', location: 'Scotland' } ]
Data returned from the MySQL database can be parsed by simply lopping over the rows object.

for (var i = 0; i < rows.length; i++) {
console.log(rows[i].name);
};

Creating

You can execute an insert query against a database, like so:

var employee = { name: 'Winnie', location: 'Australia' };
con.query('INSERT INTO employees SET ?', employee, function(err,res){
if(err) throw err;


console.log('Last insert ID:', res.insertId);
});
Note how we can get the ID of the inserted record using the callback parameter.

Updating

Similarly, when executing an update query, the number of rows affected can be retrieved using result.affectedRows:

con.query(
'UPDATE employees SET location = ? Where ID = ?',
["South Africa", 5],
function (err, result) {
if (err) throw err;

console.log('Changed ' + result.changedRows + ' rows');
}
);

Destroying

Same thing goes for a delete query:

con.query(
'DELETE FROM employees WHERE id = ?',
[5],
function (err, result) {
if (err) throw err;

console.log('Deleted ' + result.affectedRows + ' rows');
}
);

No Comment
Add Comment
comment url