Node.js Quickies – Working with MySQL

In this tutorial I’ll give you a quick overview on how to work with MySQL and Node.js.

Firstly, you need to include the mysql package in your package.json file, under “dependencies”. Make sure you run “npm install” to download all your project’s dependencies.

"dependencies": {
   "node-mysql": "*"
},

Like with many things in life, you start by establishing a connection:

var mysql = require('mysql');

//mysql setup
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'username',
  password : 'secret_password',
  database: 'database_name'
});

connection.connect();

This is how you can select data:

connection.query('SELECT * FROM user', function(err, rows, fields){
  if (err) throw err;
  
  rows.each(element, index) {
    console.log(element.firstName + " " + element.lastName);
  }
});

How about updating data:

connection.query('UPDATE user SET first_name = "'+ fName +'" WHERE username = "'+username+'"', function(err, result){
  console.log('updated user! ' + element.username);
});

Why you so asynchronous?

The main difference between working with MySQL in Node.js and other web application technologies such as PHP, is that in Node these calls are asynchronous. In PHP, once you do a query, the script execution will stop until the database has responded to the query. In Node, on the other hand, the query is an asynchronous call, so the script keeps on going.

If you are doing many queries and the asynchronous nature of it becomes too complex it’s worth looking into using Promises. I’ll cover promises using the Q package in another tutorial.

You can learn more about node-mysql by checking out it’s Github repo.

BUILD GAMES