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.
Your log message are not the ones you get from that code (there is no "You are connected to mysql")!? Try the following:
db.connect;
should be
db.connect();
because you want to execute the function. You'd also want to wait for the query to finish before executing next()
var estDb = function(req, res, next){
db.connect();
db.connection.query("SELECT * FROM Table", function(err, results){
if(!err){
req.results = results;
next();
}
else{
throw err;
}
});
db.end();
}
EDIT: What happens here is that the code in your db module executes right away when you require it. What you want do do is this:
var connect = function(){
connection.connect(function(err){
if(!err){
console.log("You are connected to the database.");
}
else{
throw err;
}
})
};
You can then call db.connect() as shown in my post (same for end()). If you put db.connect in one line (without the parenthesis), nothing will happen.
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.