Node.js closing database connections

Question

I inherited some node.js code that I clearly have no idea how it works. The issue I have is it's opening database connections and not closing or reusing them. So at some point I have to kill the node process to release the connections or the database will start to complain. I've tried to close the connections myself but must not understand the event based logic as I either end up closing the connection before a second query can be run or not at all. I'm using node 0.6.12 on Ubuntu 12.04.2 LTS with the node mysql module v0.9.6. Below is an example model method.

this.search = function(options, callback) {
    if (options.post) {
        var query;
        var param;
        var limit;
        var results_per_page = 10;
        var page = (options.post.hasOwnProperty('page') && parseInt(options.post.page)) ? options.post.page : 1;

        limit = ' LIMIT ' + ((page-1)*(results_per_page)) + ',' + results_per_page;
        if (options.post.term) {
                var escaped_term = options.database_client.escape('%' + options.post.term + '%');
                query = "Q" + limit;
        } else {
                query = "Q" + limit;

        }
        options.database_client.query(query, [], query_results);
        // Tried closing DB connection here
        function query_results(err, results, fields) {
            var all_results = {};
            all_results.total_results = 0;
            all_results.total_pages = 0;
            all_results.current_page = page;
            all_results.results = [];

            if (err) {
                console.log('You have an error: ' + err);
                throw err;
            } else {
                if(!results[0]) {
                    callback(undefined, all_results);
                    return;
                } else{
                    options.database_client.query("SELECT found_rows() AS total", [], function(err, results2, fields) {
                        if (err) {
                            console.log('You have an error: ' + err);
                            throw err;
                        } else {
                            if (!results2[0]) {
                                callback(undefined, all_results);
                                return;
                            } else {
                                var all_results = {};
                                all_results.total_results = results2[0].total;
                                all_results.total_pages = Math.ceil(all_results.total_results/results_per_page);
                                all_results.current_page = page;

                                for(var property in results) {
                                    if(results.hasOwnProperty(property)){
                                        if(results[property] == "" || results[property] == " "){
                                            results[property] = null;
                                        }
                                    }
                                }
                                all_results.results = results;

                            }
                            callback(undefined, all_results);
                            return;
                        }

                    }); // end query
                }
            }
            // I think here is only closing one connection
            options.database_client.end();
        }
    // Also tried here but total_pages, total_results end up being 0 in the results callback
    }
};

I'm using options.database_client.end(); to close the database connection per the node.js mysql modules documentation. Any help would be greatly appreciated.

score 0 · Answer 1 · answered Jun 12 '13 at 20:31

actually you are doing this 1. query and call query_results, when the results are ready 2. query again (found rows) with an anoymous function 3. end the db connection

but, theres one problem with this: the end is called, before the anon. function for the found_rows result, i think.

i think you have several points in that code, which should call options.database_client.end() 1. when throwing an error (2 places) 2. between "callback(undefined, all_results);" and "return;" (2 places too)

or better: close the db, after the callback in the first line is called from outside this method or even better: at the end of the request from outside

Node.js closing database connections

1 Answers1