Mysql: Is it possible to call db.end() after every query?

[ghost]

For example:

db.query('SELECT blah blah here ', function(err, results){

        db.end(); 
    });

Except, can we remove db.end() and put it inside the main db class or something of sort so it always ends the connection after each query automatically? It just seems so tedious and tiresome to call db.end all the fucking time.

Thanks!

[dougwilson]

Unfortunately there is not enough context to really say anything about this besides for the obvious: you can only ever call .end on a connection once. You may want to look into using the Pool and just calling pool.query(...)? Otherwise, please feel free to add more details to the question :)

[ghost]

Well, Im just sick and tired of using db.end() each time inside a db.query function. Is there a way to just edit the main MYSQL files inside \apps\node_modules\mysql\lib and just make it so each time a query is run, it calls db.end() automatically? Hard to explain, thanks.

I'll look into pool.query..

[ghost]

This is because if I do not call db.end I get a sleep query (memory leak) in my mysql pool, which I do not like :( And hundreds can appear with/out db.ending a connection and it's just annoying. I just wish there was a way for the system to automatically close it once that db.query is called.

[EnzoMartin]

Untested, but you could make use of something like this when you create your DB connection:

var query = db.prototype.query;
db.prototype.query = function(){
    var args = Array.prototype.slice.call(arguments);
    var callback = args.pop();

    args.push(function(){
        db.end();
        callback.apply(this,arguments);
    });

    query.apply(this,args);
};

I would just use the query pooling though

[AdriVanHoudt]

If you are using a framework like Hapi you could use a plugin like https://github.com/salesflare/hapi-plugin-mysql that handles the connection endng for you.

[dougwilson]

Gotcha. So the intention is that for every query, you want to open a brand new connection to your MySQL server, make the single query, and then close down the connection? You can certainly do this, but just remember that this is basically a "driver library" for Node.js, and as such, it does require that you manage the lifecycle of connections yourself.

@EnzoMartin gave a pretty simply example :) Another one could be the following:

var mysql = require('mysql');
var db = {};
db.query = function (sql, callback) {
  var _err;
  var _rows;
  var conn = mysql.createConnection();
  conn.query(sql, function (err, rows) {
    _err = err;
    _rows = rows;
  });
  conn.end(function () {
    callback(_err, _rows);
  });
};

and now you can just call db.query('SELECT something', function (err, rows) {}) anywhere and it'll create, connection, query, and end every time.

[ghost]

Not necessarily doug, I do not want to open a new one, but just close the current one if that makes sense.
I'll add you doug on skype or something and see what happens. Not sure how to handle this.

[dougwilson]

Sure, but where are you opening the connection?

[ghost]

I'm just opening it whenever a user connects to my gameserver to let's just say to move an item in their inventory. (changes a slot value).

my javascript sends: 'SU 421 21'

Slot update command get's read by nodejs, goes to my function updateSlot. I run db.query and update their item ID (421) to slot position 21. They do this each time they move an item, mysql is used and I end the connection each time. Is this bad?

I do the same stuff with every other game function, I have hundreds. I run the command to save to db, and db.end it. Hmm..

[dougwilson]

It's not bad, it's just in Node.js this works very differently from, say, PHP. In PHP, you have a single script that would execute: it would start up, run your code, connect, query, and then when the script ends, it'll do things like clean up your db connections.

In Node.js, however, this is completely different. You are not writing scripts but a single _long-running app that never ends_. This means that if you open a new connection, it will never close automatically unless you stop your Node.js server. Because of this, if you want it to end prior to your server closing, you have to end it yourself.

[ghost]

do things like clean up your db connections.

I want that to happen with this module and it will be heaven. I'll try what people have posted above but I do not want extra queries to be ran or boggle down my system just to check if a connection needs to be ended.

[dougwilson]

The answer is that it is literally impossible to do this in Node.js, I'm sorry, I know you didn't want to hear that, but this module has no way to know when you're going to stop calling db.query() because Node.js is async and never ends.