CODEWITHSUNDEEP
node.jssqlite
AndyRob7
AndyRob7

Reputation: 43

Function to return data from SQLite3 query in node.js

I'm making a bot for Discord, and one of the features in this bot is a level system. I've decided to go from using JSON to store the data, to sqlite. I'm using sqlite3 in node.js and am trying to create a function to create / retrieve a player's data. My goal is to make this function return the data from the query, but I'm running my head into a brick wall trying to figure out what I'm doing wrong. I've read that I need to use callbacks sent to the query function, but that has not worked for me either (nor will it work for the goal of this function).

So any help on how I can create a function to return the data from the query would be extremely helpful! This is the code I have so far before I've given up and decided to come here for help before I lose any more hair. Everything else is working as I intend, I am just unable to retrieve the data from the db.each().

exports.getPlayerData = function(players,msg,mention = false){
if(mention){
    // console.log("mention found");
    var member = msg.mentions.users.first();
} else {
    var member = msg.member.user;
};
var db = new sqlite3.Database('data/levels/' + msg.guild.id + '.db');
var check;
db.serialize(function() {
    var stmt = "CREATE TABLE if not exists uid_" + member.id  + " (id INTEGER PRIMARY KEY, username TEXT, avatarID TEXT, avatarURL TEXT, xp INTEGER, level INTEGER, lastXp INTEGER)";
    db.run(stmt);

    db.run("INSERT OR IGNORE INTO uid_" + member.id  + " (id,username,avatarURL,xp,level,lastXP) VALUES (?,?,?,?,?,?)", member.id,member.username, member.avatarURL , 0, 0, 0);
    db.each("SELECT * FROM uid_" + member.id + " WHERE id = " + member.id , function(err, row) {
        console.log(row);
        return row;
    });

});
// return row;
db.close();

}

Upvotes: 2

Views: 11567

Answers (2)

Mitesh Sharma
Mitesh Sharma

Reputation: 179

You should go with wait.for npm.

More example can be found here: https://www.npmjs.com/package/wait.for

Sharing code which worked for me below you can find it :

var wait = req.app.get('wait');
wait.launchFiber(handleGet, req, res);
function handleGet(req, res)
{   
    var slashes     =   req.app.get('slashes');
    var connection = req.app.get('connection');
    var user_lib = req.app.get('user_lib'); 
    var user_id = req.params.userId;    
    user_id = slashes.add(user_id);
    var user_insert_id = wait.for(user_lib.update_user_status, connection, user_id);
    res.render('view/email_verification', { req:req, res:res});
}

user code lib file :

Object.prototype.update_user_status=function (connection, cc_user_id, callback)
{
     connection.query('update cc_user set cc_user_active="yes" where cc_user_id ="'+cc_user_id+'"',function(err,rows){      
        if(err) throw err;          
        if(rows)
        {
            return callback(err,{rows:rows});
        }
    }); 
}

Upvotes: 1

Mukesh Sharma
Mukesh Sharma

Reputation: 9022

If I understood your problem correctly, then you must be facing issue related to the placement of return statement. You want to return all rows, but aren't able to return, because of asynchronous nature of code.

I checked the documentation of sqlite3's .each, and found out that there exist complete callback that you can call after you have fetched all rows. In that callback, you can return data array that has collection of rows.

Update: You have to use callback

exports.getPlayerData = function(players,msg,mention = false, callback){
    if(mention){
        // console.log("mention found");
        var member = msg.mentions.users.first();
    } else {
        var member = msg.member.user;
    };
    var db = new sqlite3.Database('data/levels/' + msg.guild.id + '.db');
    var data = []; //for storing the rows.
    db.serialize(function() {
        var stmt = "CREATE TABLE if not exists uid_" + member.id  + " (id INTEGER PRIMARY KEY, username TEXT, avatarID TEXT, avatarURL TEXT, xp INTEGER, level INTEGER, lastXp INTEGER)";
        db.run(stmt);

        db.run("INSERT OR IGNORE INTO uid_" + member.id  + " (id,username,avatarURL,xp,level,lastXP) VALUES (?,?,?,?,?,?)", member.id,member.username, member.avatarURL , 0, 0, 0);

        db.each("SELECT * FROM uid_" + member.id + " WHERE id = " + member.id , function(err, row) {
            data.push(row); //pushing rows into array
        }, function(){ // calling function when all rows have been pulled
            db.close(); //closing connection
            callback(data); 
        });
    });
}

To have to pass callback to get player data.

getPlayerData(players, msg, false, function(data){
   console.log(data);
});

Hope it works for you and saved your hair :). Let me know, if there are any issues.

Upvotes: 6

Related Questions