async get data from sqlite database in nodeJS

Question

this is the code I have for my warning command, it is reading from a sqlite database.

user = message.mentions.users.first()
embed = new Discord.MessageEmbed()
    .setTitle(`warnings of ${user.username}`)
    .setTimestamp()
    .setFooter('shadowcraft - **Test Mode!!**');

sql = 'SELECT * from moderation WHERE userID = ?'
db.each(sql, [user.id], (err, row) => {
    if (err) throw err;
    mod = client.user.fetch(toString(row.moderatorID))
    console.log(row)
    embed.addField(`${mod.tag}`, `${row.reason} \n${row.date}`);
});
message.channel.send(embed)

When I run it, it prints the row to the console but the embed is empty and has no fields. The problem is that it sends the embed before it gets the results from db.each()

db.each is a callback and not a promise so I cant use await .then() etc

Is there a way to make the callback into a promise so that I can use an async function

this is the full console log, there are no errors etc only the rows

{
  id: 1,
  date: '2020-04-02',
  userID: 'some user id',
  action: 'WARN',
  reason: 'some reason',
  moderatorID: 'some user id'
}
{
  id: 2,
  date: '2020-04-02',
  userID: 'some user id',
  action: 'WARN',
  reason: 'some reason',
  moderatorID: 'some user id'
}

Why aren’t you using the WHERE userID = ${message.author.id}? It makes it easier, also, the ‘from’ has to be in caps, so it would be SELECT * FROM moderation WHERE userID = ? - you don’t have to use the ? Here as you are not vulnerable to sql injection because the user cannot change values in the table remotely — Joe Moore
– Joe Moore, Commented Apr 2, 2020 at 11:45
@Proto it's because i dont want the message author, I want the user that they mentioned — mmoomocow
– mmoomocow, Commented Apr 2, 2020 at 12:26

awrfisher · Accepted Answer · 2020-04-09 17:12:06Z

I think it would be a little cleaner if you did it this way.

Change db.each to db.all, your row in the callback would now be rows

user = message.mentions.users.first()
embed = new Discord.MessageEmbed()
    .setTitle(`warnings of ${user.username}`)
    .setTimestamp()
    .setFooter('shadowcraft - **Test Mode!!**');

sql = 'SELECT * from moderation WHERE userID = ?'

db.all(sql, [user.id], (err, rows) => {
    if (err) throw err;
    Promise.all(rows.map((row) => {
        return processRow(row)
    })).then(result => {
        embed.addFields(result);
        message.channel.send(embed)
    });
});

It would also allow you to do the promise all, on the collection of rows. Then you would create an async function and call into that function that returns a promise. Here is that function.

var processRow = async function(row) {
    return new Promise((resolve, reject) => {
        let mod = client.user.fetch(toString(row.moderatorID))
        console.log(row);
        resolve({name: mod.tag, value: `${row.reason} \n ${row.date}`});
    })
}

After all the promises are resolved, it will use the embed.addFields method, that takes in an array of javascript objects, and adds the fields in bulk. Finally able to send the embed after all processing is completed.

Hope this helps,

Tomáš Kordoš · Accepted Answer · 2020-04-07 12:12:24Z

1

I'm not sure, as I haven't used sqlite, but i assume, you're trying to send the embed before the fields are actually added. (You're running the function synchronously)

https://www.npmjs.com/package/sqlite#each

Make your function async and use await db.each, so it will wait until loop is ended, all fields are added, then send the embed.

Or you can use .then()

answered Apr 7, 2020 at 12:12

Tomáš Kordoš

3332 silver badges12 bronze badges

1 Comment

mmoomocow Over a year ago

I have already tried to do this, it has the same result as before and I get an error from eslint saying that it has no effect

Tarazed · Accepted Answer · 2020-04-08 20:54:03Z

1

Tomi Kordos almost had the right answer. The issue is asynchronous execution. However, in this case sqllite is using callbacks instead of promises so await and .then will not work when used directly on db.each.

There is a way however. Node provides a 'promisify' utility. This works on any function who's last parameter is an error first callback, which is exactly what db.each is.

You first need to bring in the utility.

const {promisify} = require('util');

Then create a promisified reference to the function.

const dbeachpr = promisify(db.each);

Now you can use this reference as if it were a promise returning function. In this case, I suggest storing them in an array and using Promise.all to determine when they are finished.

user = message.mentions.users.first()
embed = new Discord.MessageEmbed()
    .setTitle(`warnings of ${user.username}`)
    .setTimestamp()
    .setFooter('shadowcraft - **Test Mode!!**');

sql = 'SELECT * from moderation WHERE userID = ?'
let promises = [];
promises.push(dbeachpr(sql, [user.id]).then(row => {
        mod = client.user.fetch(toString(row.moderatorID))
        console.log(row)
        embed.addField(`${mod.tag}`, `${row.reason} \n${row.date}`);
    }).catch(err => throw err); // You may want do something more useful with this.
);

Promise.all(promises).then(() => message.channel.send(embed));

answered Apr 8, 2020 at 20:54

Tarazed

2,8121 gold badge10 silver badges28 bronze badges

2 Comments

mmoomocow Over a year ago

i got the error TypeError: promises.push(...).catch is not a function

Tomáš Kordoš Over a year ago

@Nodejs-nerd have you defined promises before?

GBadlon · Accepted Answer · 2020-04-07 17:34:24Z

0

Since you want to get the mentioned user ID you can use the user.id in the SQL command, so in your case, you need to use the following SQL query:

`SELECT * from moderation WHERE userID = ${user.id}`

That way you will be able to query someone's user id and from there find the warning information needed. If you need more explaining let me know :)

answered Apr 7, 2020 at 17:34

GBadlon

381 silver badge6 bronze badges

Comments

razki · Accepted Answer · 2020-04-08 23:38:53Z

Have a look at this, create a new promise and decide when to resolve or reject, you could silently fail the rejects if you choose to.

const updateField = db =>
    new Promise((resolve, reject) => {
        db.each(
            'SELECT * from moderation WHERE userID = ?',
            (err, row) => {
                if (err) {
                    reject(err)
                } else {
                    mod = client.user.fetch(toString(row.moderatorID))
                    embed.addField(`${mod.tag}`, `${row.reason} \n${row.date}`)
                }
            },
            (err, _) => {
                if (err) {
                    reject(err)
                } else {
                    resolve()
                }
            }
        )
    })

module.exports.main = async () => {
    //no idea what your imports/other code looks like...

    user = message.mentions.users.first()
    embed = new Discord.MessageEmbed()
        .setTitle(`warnings of ${user.username}`)
        .setTimestamp()
        .setFooter('shadowcraft - **Test Mode!!**')

    await updateField(db)

    message.channel.send(embed)
}

Collectives™ on Stack Overflow

async get data from sqlite database in nodeJS

5 Answers 5

Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related