Node.JS with MySQL

This article shows how to implement a small Node.js application that pulls data from a local MySQL server.

We’re doing this on a CentOS server but that’s not important – any Linux will do. But if you’re using Windows, the installation process will differ.

yum install nodejs
npm install mysql 

Create a database using the following table format:

CREATE TABLE customers (
    LastName varchar(255),
    FirstName varchar(255)
);

Populate it with some dummy data:

INSERT INTO customers (LastName, FirstName) VALUE ("Smith", "John");
INSERT INTO customers (LastName, FirstName) VALUE ("Jones", "Kym");
INSERT INTO customers (LastName, FirstName) VALUE ("Carny", "Sam");
INSERT INTO customers (LastName, FirstName) VALUE ("Smithy", "James");
INSERT INTO customers (LastName, FirstName) VALUE ("Bonny", "Ben");

And grant permissions:

GRANT ALL ON mydatabase.* TO myuser@"localhost" IDENTIFIED BY "mypassword";
flush privileges;

Create a directory of your own choice and place a file in it called “node.js”. Populate it with the following:

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "myuser",
  password: "mypassword",
  database: "mydatabase"
});

con.connect(function(err) {
  if (err) throw err;
  con.query("SELECT * FROM customers", function (err, result, fields) {
    if (err) throw err;
    console.log(result);
  });
});

Now you can run the following command:

node node.js

You should get something like the following:

[ RowDataPacket { LastName: 'Smith', FirstName: 'John' },
  RowDataPacket { LastName: 'Jones', FirstName: 'Kym' },
  RowDataPacket { LastName: 'Carny', FirstName: 'Sam' },
  RowDataPacket { LastName: 'Smithy', FirstName: 'James' },
  RowDataPacket { LastName: 'Bonny', FirstName: 'Ben' } ]