Skip to content

Node.js – Connecting to a database and some very basic authentication

September 30, 2011

A while ago I made a post about my first steps into using Node.js and some basic experimentation with it.  I essentially created my “Hello World”, which consisted of a simple http server and some basic routing being enabled.  This is all for my PRJ66 class which we are creating an inventory system for an actual client that our group is working with.  The next steps for me were to learn how to do the following:

  • Connect to our mysql database
  • Create some form of authentication
  • Create a unique token for a logged in user and give them a login time ( sort of a server side cookie of sorts )
  • Handle logins correctly
The first part I handled was getting my server interacting with our database.  Node.js is pretty awesome in that adding additional functionality onto the core of Node.js is as simple as including a new module.  Getting these modules is also extremely easy due to npm ( node package manager ).  After installing npm, you are able to do something along the lines of:
npm install moduleName
npm does its magic and you are now able to include your module within your project.

Database Work:

So to get my mySql database working in Node.js i found a module to do this for me.  After installing through npm and including it in my project, the code to interact with the database was pretty simple.  Its looks like the following:
var mysql = require( "db-mysql" );
  new mysql.Database({
    hostname: "localhost",
    user: "dave",
    password: "asdfa",
    database: "dbNameHere"
  }).on( "error", function( error ) {
    console.log( "ERROR: " + error );
  }).on( "ready", function( server ) {
   console.log( "Connected to " + server.hostname + " (" + server.version + ")" );
  }).connect( function( error ) {

   if ( error ) {
     console.log( "Error on connect: " + error );

   this.query( "SELECT * FROM " + "USER" ) ).
   execute( function( error, rows, cols ) {

     if ( error ) {
       console.log( "Error on select: " + error );

     response.writeHead( 200, { "Content-Type": "text/plain" } );
     response.write( JSON.stringify( rows ) );

This is done in my requestHandlers.js file from my previous post and this time around I pass along my response in order to keep everything asynchronous.  This means that we dont wait for our database to get the data and then return it while its done ( which means we are waiting to update the response until we get our data back and our query has returned the data to us ).  The approach I used here was passing along my request and telling the database to simply update it once it has the data.  The benefit to doing this is that we are not blocked on waiting for our data.  We simply say “when you have the data, please update the response”.  Using this method allows us to process other requests to our server while this is going on, which really speaks for the power of Node.js.
That is essentially it.  You can use whatever database queries that you want using this method, whether it be selects, updates, inserts, or whatever.  This module is pretty simplistic.  This really speaks for the simplicity of Node.js modules and how quick the learn -> test -> working flow is.  The funny part was, the hardest part about all of this was getting mySql set up on my newly acquired MacBook Pro.


The other stuff that I implemented recently was some basic login and authentication.  For this I ended up switching from the http module to the connect module that I downloaded from npm.  The connect module essentially provides an onion layer model of authentication.  This means that we create numerous different layers of data, and only let requests go through once they are authenticated in one way or form.  This was cool as it allowed me to create an initial layer to do all of my checking and then pass the request of where the user wanted to go once they are validated.
For my validation I didnt want to go with a super clunky authentication module/library like OAuth ( as I think its major overkill ), but rather just want something to:
  • Check that user pass is correct
  • Create a token for this user if validated, and store this in a cookie
  • Auto log the user out after x minutes has passed
  • refresh there login timestamp each time requests are made
  • log the user out if there timestamp is no longer valid
  • On the client side, store the users token in a cookie ( not really valid but still worth noting )
  • Encrypt data before passing it to the server ( dont want plain text going over ), decrypt on the server side
I was able to accomplish all of this, tho it did take quite a bit of though on how to design this since the last time I showed you my server.js file.  Here is what she looks like now:
//  Server.js
//  Used to handle all requests made to the server

//  Include the modules we need, store in variables for use
var connect= require( "connect" );
var url = require( "url" );
var userHashs = {};

//  Start wrapper function used to allow route and handle to pass through
function start( route, handle ) {

  //  Helper function for generating a nice guid ( not true guid but good enough )
  function guidGenerator() {
    var S4 = function() {
      return (((1+Math.random())*0x10000)|0).toString(16).substring(1);
    return (S4()+S4()+"-"+S4()+"-"+S4()+"-"+S4()+"-"+S4()+S4()+S4());

  function validate( request, response, next ) {
    var pathname = url.parse( request.url ).pathname;
    var values = request.query;

    if ( pathname !== "/favicon.ico" ) {

      if ( pathname === "/logout" || ( userHashs[ values.hash ] && userHashs[ values.hash ] < new Date( ) ) ) {
        console.log( "LOGGING OUT" );
        userHashs[ values.hash ] = null;
        route( handle, "/logout", response );

      if ( userHashs[ values.hash ] ) {
        console.log( "ALREADY VALIDATED", userHashs[ values.hash ] );

        //  Update our time as we are still doing stuff
        userHashs[ values.hash ] = new Date( + 1800000 );
        response.guid = values.hash;

      if ( !values.hash || !userHashs[ values.hash ] ) {

        //  Do actual database validation here
        if ( values.user === "asd" && values.pass === "qwe" ) {

          var tempGuid = guidGenerator();

          //  Make sure we get no duplicates, it has to be unique after all
          while ( userHashs[ tempGuid ] ) {
            tempGuid = guidGenerator();

          values.hash = response.guid = tempGuid;

          //  Set our time to 30mins from now, auto log out if not renewed by then
          userHashs[ values.hash ] = new Date( + 1800000 );
        response.writeHead( 200, {
          "Content-Type": "text/plain",
          "Access-Control-Allow-Origin": "*"
        response.write( "Invalid user/pass" );

  //  onRequest function called for each request to the server
  function onRequest( request, response, next ) {

    //  Parse pathname out of url
    var pathname = url.parse( request.url ).pathname;

    //  Get our content from the router
    route( handle, pathname, response );

  //  Actually start the server
  var server = connect.createServer(

  server.listen( 8080 );

//  export our module we just created
exports.start = start;

Some of the challenges I had here were things such as keeping track of hashs and times that they were last validated.  I did this by just created a hash table keyed by the hash and storing the time as a value.  This was also the first time I had the opportunity to use cookies in javascript ( client side obviously ).  It was cool to learn how they operate and how storing and updating data in them works.  It took 2 late nights to get all of this working, but its finally in an early working state.  Some next steps are to add the encryption and decryption stuff in, as well as an actual database query for validation.
Another one of the problems that I’ve run into is figuring out if the code that I’m righting is asnychronous and not blocking in any way or form.  Ive ended up sitting there for an hour or so in deep thought about whether what ive done or not is asnychronous.  I think a lot of the time I just end up confusing myself and not really coming to any sort of conclusion.  I think my next step here is to go into the Node.js irc channel ( #node.js in ) and ask for some tips on how to ensure my code is asnychronous or if there are any good ways to test this.
In all, working with Node.js so far has been a great learning experience.  It has taught me a lot in terms of thinking about coding differently.  Ive been writing client side code for so long that I really dont think about some of these problems anymore ( numerous connecting users, validation, database queries, ect ) and its fun to challenge myself like this.  I feel like once I really understand how to keep everything asynchronous im going to come out a better Javascript developer, and a better programmer in general.  The next major steps for me in Node.js are:
  • Making everything asynchronous
  • Encryption/decryption
  • Filling out all of my functions that requests will be being made to ( both GET and POST requests )
  • Writing some tests to make sure I dont break anything ( I will probably do this once I get the encryption going and I ensure everything is asynchronous )
  • Logging all of the requests to the server in files
  • Ensure that if our machine crashes, our node server will auto start again once the machine is back up.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: