Updating a value in SQL (better-sqlite3) with Node.JS

Question

I currently have a database of people with each individual person and they hold a status value. I am trying to change their status value.

  const id = parseInt(req.params.id , 10);
  const { valid, messageObj } = validateId(id);
  if (!valid) {
    res.status(400).send(messageObj);
  }

  let { status, priority } = req.body;
  let people = db.prepare('select * from people').all();
  const person = people.find(person => person.id === id);    

  if(status !== 'none' & status == 'ready' || status == 'done'){
    let updates = db.query(
        'UPDATE people SET ? WHERE ?', 
         [{ status: status }, { id: id }]
    );
  }

I keep getting an error of db.query is not a function but I get that for every function that I try.

Pretty new to SQL but just trying to figure this out or any documentation that will help me as the better-sqlite3 doesn't have any update functions in the official documentation.

Answer 1

I cannot find a function called query() in the better-sqlite3 API for the Database class. I think that you would need to prepare() a Statement object, then run() it.

Also, column names cannot be passed as bound parameters. Your query should look like:

UPDATE people SET status = ? WHERE name = ?

You would need to change this:

let updates = 
    db.query('UPDATE people SET ? WHERE ?', [{ status: status }, { id: id }]);

To:

const stmt = db.prepare('UPDATE people SET status = ? WHERE id = ?'); 
const updates = stmt.run(status, id);