Express.JS + Node-Mysql and 1 connection per http request

I'm new to Express, and am trying to wrap my head around Express + Node-Mysql.

I want to use a new mysql connection for each 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 my route 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 am not sure if it is "proper". Is there a cleaner way to accomplish what I am after?

Thanks (in advance) for your help.

Solution

If you want to do one db connection per request, put this line:

db = mysql.createConnection(require( "./config/db" ));

inside your request handler (e.g. inside routes.js), right before where you do db.connect(). Call db.end() once you have gotten all the results from your queries.

Since connecting to the database is a relatively expensive operation though, you may want to consider using a connection pool. Keeping a few connections open and pulling a free connection out of the pool when you need it will be more efficient than opening and closing a new connection for each request. The generic-pool npm module is a good one.