My entire game is server side and I use nodejs with mysql.

When players connect or join a game, their player data (Items, Stash, Quests, etc) all get stored into the memory buffer.

For example, if a player joins a game and there are 40 items on the floor and he decides to pick them all up, it's easier just to add that to their Inventory object instead of using a insert statement each time. Then, when that user disconnects I can save all that data respectively. (Now that I think of it, that query would run 40 insertion rows..)

That's the problem I have though, do I save the data during the looting process or wait until the player disconnects and run a db update to save their memory buffer (only the changed buffer) to the database?

The Issue I have with that is, if a player picks up over 40 items in the game, that's going to be a lot of sql insertions when they disconnect..

Imagine them finding a ton of items from mobs and having a huge amount of new updated buffer data that needs to be stored.. (after they disconnect), that would be a dreadful update. Especially if they stored hundreds of items in their stash or whatnot. Hmmm.

enter image description here


1 Answer 1


[1] Database Reader / Writer Forked Process

You could use a db update child process that is called when a user leaves the game.
That would be non-blocking for your node.js socket server's main thread and the other clients wouldn't experience the lag during the db update

// server.js
var process = require('child_process'); 
var fs = require('fs'); 
var db_rwp = process.fork('db_rwp.js');
    db_rwp.on('message', function(data) {
        /* INIT : ERROR  */ case 'error':
            console.log(' # database reader / writer ERROR  ');     
        /* INIT :  OK!   */ case 'success':
            console.log(' # database reader / writer : 0N   ');     

// db_rwp.js
var mysql = require('mysql');
var fs = require('fs');
//When Spawned Return Connection Status
var db = mysql.createConnection({
    host:'host', user:'user', password:'pass', database: 'database'
    if (err) { process.send({acid:'error',arg1:err}); }
    else { process.send({acid:'success'});}

Comunication example :
(maintain the socket.id of the request to return post-action results to specific client)

// server.js -to- db_rwp.js :
db_rwp.send({acid:'action_id', csid:socket.id});
// db_rwp.js -to- server.js :
process.send({acid:'action_id', csid:data.csid});
// server.js -to- client :


[2] Global save from main thread

You could call a db update using a desired world save frequency.
Freezing all the clients to prevent dupes/etc...
While the server saves you could display a message in the clients:
'The world is saving please wait...'

#1 would need a way to detect when a client is performing a log-in whilst another client is already inside that account. (Avoid loading obsolete data from db)
#2 is useful for having a recent backup to load in case of crash, RunUO (Ultima Online Server Emulator) does this to save the world state.

  • \$\begingroup\$ Oh wow, I forgot about this topic :P. I went with the db.update [1] for non blocking and I'm cringing hard looking back at this question. Storing the items into the buffer data and then saving them later is a really bad idea I have no idea what I was thinking back then, part of learning process I guess . \$\endgroup\$ Feb 12, 2016 at 23:18
  • 1
    \$\begingroup\$ I added a code example to the answer, it might help someone else. \$\endgroup\$ Feb 12, 2016 at 23:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .