Sifting through Node.js, Express and mysql module

Question

I'm trying to work through integrating the express framework for node.js and the mysql module https://npmjs.org/package/mysql. I have a simple application setup (by using the express command line) and I also have a module declared for working with some of the database properties.

My DB module is setup like this:

app.js
node_modules
|___db
     |
     node_modules
           |___mysql

With the mysql module setup to be a dependency of the db module.

In my index.js for the db module I have some module exports setup to be accessed by the application:

/*
 * Connection params for database
 */

var mysql = require('mysql');
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'database',
});

var connect = connection.connect(function(err){
  if(!err){
        console.log("You are connected to the database.");
  }
  else{
        throw err;
  }
});

var end = connection.end(function(err){
  if(!err){
        console.log("Mysql connection is terminated.")
  }
  else{
        throw err;
  }
});

module.exports = {
  connect: connect,
  connection: connection,
  end: end,
}

In my app.js file I am requiring my db module and specifying some routes. I'm also trying to use a route middleware function (estDb) inside the app.get method for the clients route:

/**
 * Module dependencies.
 */

var express = require('express')
  , routes = require('./routes')
  , clients = require('./routes/clients')
  , user = require('./routes/user')
  , http = require('http')
  , path = require('path')
  , db = require('db');

var app = express();

app.configure(function(){
  app.set('port', process.env.PORT || 3000);
  app.set('views', __dirname + '/views');
  app.set('view engine', 'jade');
  app.use(express.favicon());
  app.use(express.logger('dev'));
  app.use(express.bodyParser());
  app.use(express.methodOverride());
  app.use(app.router);
  app.use(express.static(path.join(__dirname, 'public')));
});

app.configure('development', function(){
  app.use(express.errorHandler());
});

var estDb = function(req, res, next){
  db.connect;
  db.connection.query("SELECT * FROM Table", function(err, results){
        if(!err){
          req.results = results;
        }
        else{
          throw err;
        }
  });
  db.end;
  next();
}

app.get('/', routes.index);
app.get('/clients', estDb, clients.view);

http.createServer(app).listen(app.get('port'), function(){
  console.log("Express server listening on port " + app.get('port'));
});

The Problem I'm having is that it seems that my db functions (my module exports) are getting called when I start the application as I'm getting the log:

Express server listening on port 3000
You are connected to mysql.
Mysql connection is terminated.

NOT when the url http://localhost/clients is requested (which is what I have the route defined as). As you can see, it is firing the db.connect() and the db.end() right after the console logs the "Express server listening on port 3000" message -- which leads me to believe it's firing from the custom db module I'm using. Subsequently, when I go to the route http://localhost/clients I get an error:

500 Error: Cannot enqueue Query after invoking quit.

If I remove the connection.end() function from the db module, I can connect to the database and retrieve results; however, if I reload the page and try to load the results again, I get an error:

Cannot enqueue Handshake after already enqueuing a Handshake

I don't understand why my module exports are firing when I start the application? I think this is where I'm getting in trouble.

Any suggestions or help on this would be great.

Answer 1

I don't understand why my module exports are firing when I start the application? I think this is where I'm getting in trouble.

I believe this is happening because of this piece of code:

var mysql = require('mysql');
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'database',
});

In here you are actually connecting to the database, rather than defining a function than when called will connect to the database.