CODEWITHSUNDEEP
node.jssqliteexpressnode-sqlite3
irondsd
irondsd

Reputation: 1240

SQLite callback after delete

I'm trying to make a delete function with confirmation that something was deleted. With current code rows variable comes back empty if the row is not found or it was deleted. 

app.delete('/api/devices/:id', (req, res) => {
    db.all('delete from devices where id = ' + req.params.id, (err, rows) => 
{
        if (err) {
            return res.status(500).send(err)
        }
        else {
            return res.status(204).send()
        }
    })
})

In case if it's not found, I want to return 404, in case it was actually deleted, I want to return 204. How do I tell them apart?

I know I can make a select query prior to deleting it, but there must be a better way.

Upvotes: 0

Views: 1839

Answers (2)

rags2riches-prog
rags2riches-prog

Reputation: 1761

In case someone else hits this page in search for sqlite3 this.lastID and sqlite this.changes return undefined after a delete query;

The code snippet above should read:

app.delete('/api/devices/:id', (req, res) => {
  db.run('delete from devices where id = ?', req.params.id, function(err) {
    if (err) return res.status(500).send(err)
    return res.status(204).send(this.lastID) // or this.changes
  })
})

These were the things missing from the OP snippet:

  1. we need to execute a run command on the database instance, not all which is by the way memory inefficient...
  2. the sqlite query was missing the question mark for the parametized variable (the id)
  3. the this context is lost if we use arrow functions
  4. the run method callback has only one parameter, the error object

Upvotes: 2

DinoCoderSaurus
DinoCoderSaurus

Reputation: 6520

From this tutorial

In case the DELETE statement executed successfully, the this object of the callback function will contain the changes property that stores the number of rows deleted.

You should consider using the run method, not the all method for the DELETE query, since you are not expecting any results.

Excerpts from the api doc

Runs the SQL query with the specified parameters and calls the callback afterwards. It does not retrieve any result data.

...

If execution was successful, the this object will contain two properties named lastID and changes which contain the value of the last inserted row ID and the number of rows affected by this query respectively. Note that lastID only contains valid information when the query was a successfully completed INSERT statement and changes only contains valid information when the query was a successfully completed UPDATE or DELETE statement. In all other cases, the content of these properties is inaccurate and should not be used. The .run() function is the only query method that sets these two values; all other query methods such as .all() or .get() don't retrieve these values.

Upvotes: 1

Related Questions