Express.JS + Node -Mysql and 1 connection for HTTP request
I'm new to Express and I'm trying to wrap my head around Express + Node -Mysql.
I want to use a new mysql connection for every HTTP request.
Here is my app.js file:
var express = require( "express" ),
mysql = require( "mysql" ),
routes = require( "./routes" );
var app = module.exports = express(),
db = mysql.createConnection( require( "./config/db" ) );
app.configure(function(){
app.set( "views", __dirname + "/views" );
app.set( "view engine", "jade" );
app.set( "db", db );
app.use( express.logger( "dev" ));
app.use( express.bodyParser() );
app.use( app.router );
app.use( express.static( "front" ));
});
app.configure( "development", function(){
app.use( express.errorHandler() );
});
routes( app );
app.listen(80);
Then, in the routes files, I do something like the following:
module.exports = function( app ){
var db = app.get( "db" );
app.get( "/stuff", function( req, res ){
db.connect( function(err){
var sql = "SELECT * FROM stuff";
db.query( sql, function( err, result ){
res.send( result );
db.end(function( err ){ });
});
});
});
};
This works, but I'm not sure if it is "correct". Is there a cleaner way to accomplish what I want?
Thanks (in advance) for your help.
source to share
If you want to make one db connection per request, put this line:
db = mysql.createConnection(require( "./config/db" ));
inside your request handler (like inside routes.js), right before you do db.connect()
. Call db.end()
after getting all the results from your queries.
Since connecting to a database is a relatively expensive operation, you might want to consider using a connection pool. Keeping multiple connections open and pulling a free connection from the pool as needed will be more efficient than opening and closing a new connection for each request. common pool npm module is good.
source to share