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

NoSQL databases are all the rage these days and probably the preferred back-end for Node.js applications. But you shouldn’t architect your next project based on what’s hip and trendy, rather the type of database to be used should depend on the project’s requirements. If your project involves dynamic table creation, real time inserts etc. then NoSQL is the way to go, but on the other hand, if your project deals with complex queries and transactions, then a SQL make much more sense.

In this tutorial, we’ll have a look at getting started with the node-mysql module — a Node.js driver for MySQL, written in JavaScript. I’ll explain how to use the module to connect to a MySQL database, perform the usual CRUD operations, before examining stored procedures and escaping user input.

Installing node-mysql

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

[code language="bash"]
mkdir sp-node-mysql
cd sp-node-mysql
npm install mysql
[/code]

If you need help using npm, then be sure to check out this article, or ask in our forums.

Getting Started

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:

[code language="sql"]
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');
[/code]

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

[code language="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.
});
[/code]

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.

Pro Tip

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

[code language="js"]
{
"name": "sp-node-mysql",
"version": "0.1.0",
"devDependencies": {
"grunt": "~0.4.5",
"grunt-contrib-watch": "^0.6.1",
"grunt-execute": "^0.2.2",
}
}
[/code]

Gruntfile.js

[code language="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');
};
[/code]

Then run npm install, followed by grunt watch.

Executing Queries

Reading

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.

[code language="js"]
var con = mysql.createConnection({
host: "localhost",
user: "jay",
password: "jay",
database: "sitepoint"
});
[/code]

Once the connection is established we’ll use the connection variable to execute a query against the database table employees.

[code language="js"]
con.query('SELECT * FROM employees',function(err,rows){
if(err) throw err;

console.log('Data received from Db:\n');
console.log(rows);
});
[/code]

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.

[code language="js"]
[ { id: 1, name: 'Jasmine', location: 'Australia' },
{ id: 2, name: 'Jay', location: 'India' },
{ id: 3, name: 'Jim', location: 'Germany' },
{ id: 4, name: 'Lesley', location: 'Scotland' } ]
[/code]

Data returned from the MySQL database can be parsed by simply lopping over the rows object.

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

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

by Jay Raj via SitePoint