How to escape current SQLite3 query if row in table exists, but continue if it doesn't

Question

So I an integrating my Discord.JS bot with SQLite3 and I am adding a function where users can store their ID and username within a table. Currently, what I have will only create a new row in the table, if the ID and name of the user does not already exist within a row of the table, and creates a new row if the data doesn't exist. What I want to know is if there is a way to throw something out of the query if the row does exist, and for me to be able to catch it, and then do something else.

I have tried if..else statements, but I want to know if there is a simpler way to achieve this.

This is what I have currently, that functions as described above.

let userC = message.mentions.members.first()

    db.serialize(() => {
        db.run('CREATE TABLE IF NOT EXISTS user (id TEXT, name TEXT)');
        db.run(`INSERT INTO user (id, name) SELECT '${userC.id}', '${userC.user.username}' WHERE NOT EXISTS(SELECT 1 FROM user WHERE id = '${userC.id}' AND name = '${userC.user.username}')`)
    });
    message.reply('Added the user to the database.');

Ideally, if the row does exist, message.reply('Added the user to the database.'); will not execute, and instead will continue with message.reply('That user already exists within the database'); But if the row doesn't exist, it inserts the row and data, and only continues with message.reply('Added the user to the database.');

@slothiful it contains no data that people wouldnt be able to access through discord developer mode anyways... — Timesis
You can change your username. It's not worth the risk anyway. — slothiful
@slothiful Yeah you can change your username, but what would be the point in hiding that? That data still is 'public' in a sense and having a user ID and username yields nothing. I get what youre saying tho. — Timesis
Just by someone having ` as the first character in their username, you'd receive a SQL syntax error. If they really wanted to, they could change their username to literally wipe the table, or much worse. — slothiful

slothiful slothiful · Accepted Answer · 2019-05-26T19:41:28

According to the API docs here, you can use Database.get() instead of Database.run(). It functions the same way, but the callback will return the rows from the SQL which you can check.

In the code below, you'll notice that I've also implemented placeholders to prevent SQL injection. Consider this normal practice for user-provided variables.

const userC = message.mentions.users.first();

db.get(`SELECT 1 FROM user WHERE id = '${userC.id}' AND name = '?'`, [userC.username], (err, rows) => {
  if (err) return console.error(err);

  if (!rows[0]) {
    db.run(`INSERT INTO user (id, name) VALUES ('${userC.id}', '?'), [userC.username], err => {
      if (err) return console.error(err);

      message.reply('Added the user to the database.');
    });
  } else return message.reply('That user already exists within the database.');
});

Make sure to also catch the promises returned by message.reply().

How to escape current SQLite3 query if row in table exists, but continue if it doesn't

1 Answers