Well, as promised in the posts before (http://chilion.nl/setting-up-a-nodejs-api-with-express/ & http://chilion.nl/setting-up-a-nodejs-api-with-express-extending-the-router/) now I will show you how to use MySql.

As you might already have guessed, start with installing a package for that:

npm install mysql

Thats quite easy right? To keep overview, I decided to create a new folder called “functions” and within it a file called users.js I will include this file in the serve.js so that I can keep the functions and other stuff from the complete serve.js file.

So, create the folder and the file and fill the file with the following code:

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'test.example.com',
  user     : 'root',
  password : 'bobbyH@$N0P@$$w0rd!',
  database : 'well-one-for-sure'
});

connection.connect(function(err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }

  console.log('connected to the database as id ' + connection.threadId);
});

What this does? Well, first of all we get mysql, then we create a connection, check if it works and return the connection. Of course use your own credentials.

 

Of course we want to do a query, As you might have guessed from my obvious filenames, I want to do something with users. I don’t care if you have a big or a small usertable, it just has to have an ID for this tutorial ;-)

Add a function to the users.js file:

exports.returnUser = function (req, res) {
        var result;

        connection.query("SELECT * FROM users WHERE id = 2",
                (function (err, rows, fields) {
                        if (err) throw err;
                        this.result = rows;

                        console.log(rows);
        }).bind(this));

}

It seems obvious, we do a query and console.log the outcome. We also need to bind the scope this to it, otherwise we can use the database.

We might want to return something right? Well, add this before or after the console log.

res.json(this.result);

Lets move on, see if we can query based on input given!

For this to work we have a few steps to complete, first of all, we need to adjust our routes, change the user/2 route to the following:

  /**
        * User functions
        */
        var users = require("../functions/users.js");
         router.get("/users", [users.returnUser]);

Next, open up the users.js file and change the where clause. Because we won’t work hardcoded, we will need the “req” object of express to give us what we need. We need the query parameters and their values so in this case we will use the query.id value.

Your new query:

 connection.query("SELECT * FROM users WHERE id = " + req.query.id,

I would say, test it out, it should work already. Don’t forget we changed the url! http://localhost/api/users?id=2

Last pointer, I want to know for sure if there is a user ID the request and not an SQL Injection. There is a beautiful function for this, every language has this as far as I see.

Change your query to the following: (Want to read more about it?)

 connection.query("SELECT * FROM users WHERE id = ?", [req.query.id],

The complete code is on Github, in this Branch.

Want to read more? Stay tuned!