Node-sqlite3: this.lastID not present after INSERT

[rogusdev]

When I have inserted data into a table, this.lastID does not exist, and thus I cannot get the id from the just run INSERT. I have seen the test for this functionality:
https://github.com/mapbox/node-sqlite3/blob/7322ea714b13451bab75d5232a1dd2a05fcb5052/test/affected.test.js#L19

That is a slightly different case than what I am trying to accomplish, so I'm wondering what I am missing, or if my approach is not supported.

Demo of my experience:

sudo apt-get install -y sqlite3 libsqlite3-dev

mkdir sqlitetest
cd sqlitetest

cat << EOF > package.json
{
  "name": "sqlitetest",
  "version": "0.1",
  "description": "SQLite test",
  "main": "demo.js",
  "private": true,
  "scripts": {
    "start": "node demo.js"
  }
}
EOF

yarn add sqlite3

cat << EOF > demo.js
const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

db.serialize(() => {
  db.run('CREATE TABLE IF NOT EXISTS t1 ( \
    id INTEGER PRIMARY KEY AUTOINCREMENT, \
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, \
    a TEXT NOT NULL \
  )');

  let t1Id = null;
  db.run('INSERT INTO t1 (a) VALUES (?)', "aaa", err => {
    if (err) {
      console.error(err);
      process.exit(1);
    }
    console.log(this);
    console.log(this.lastID);
    console.log('lastID', this.lastID);
  });
});
EOF

node demo.js

the output looks like:

$ node demo.js 
{}
undefined
lastID undefined

Which is to say that after the INSERT has succeeded, this is the empty object {} and this.lastID is subsequently undefined.

I even created a second demo that is basically copy-pasted from the test case:

cat << EOF > demo.js
const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

db.serialize(() => {
  db.run("CREATE TABLE foo (id INT, txt TEXT)");
  var stmt = db.prepare("INSERT INTO foo VALUES(?, ?)");
  stmt.run(1, "demo", err => {
    if (err) throw err;
    console.log(this);
    console.log(this.lastID);
    console.log('lastID', this.lastID);
  });
});
EOF

But this gives the exact same output of the empty this, and thus undefined lastID.

Any help figuring this out? I'd love to get that last inserted id. Thanks.

[rogusdev]

Oh god, I just found out the problem, trying a guess... It is literally the ES6 arrow function syntax I chose. With just a slight change it works:

cat << EOF > demo.js
const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

db.serialize(() => {
  db.run("CREATE TABLE foo (id INT, txt TEXT)");
  var stmt = db.prepare("INSERT INTO foo VALUES(?, ?)");
  stmt.run(1, "demo", function (err) {  # FUNCTION INSTEAD OF ARROW LAMBDA
    if (err) throw err;
    console.log(this);
    console.log(this.lastID);
    console.log('lastID', this.lastID);
  });
});
EOF

Which gives me the expected output:

$ node demo.js 
Statement { sql: 'INSERT INTO foo VALUES(?, ?)', lastID: 1, changes: 1 }
1
lastID 1

I feel like this might merit a special callout somewhere, as ES6 code becomes increasingly popular. Perhaps in the wiki for this behavior?
https://github.com/mapbox/node-sqlite3/wiki/API#databaserunsql-param--callback

Docs do mention the absence of a this in arrow lambdas, but I did not realize it would have this kind of impact:
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Functions/Arrow_functions

[Larryster]

yep same issue here. after 30 mins of head scratching and rewriting came across your post and indeed made the same mistake of using arrow function instead of 'function(error)' >:-/
thanks for your post!

[lancewellspring]

Is there not a better solution? I need to retain the scope of the outer context AND access lastID. I'd prefer to not do the whole var self = this; hack.

I also tried the solution here: https://stackoverflow.com/a/54423628/3042383, but it gives these errors:

TypeError: db.run(...) is not a function
in Database#get('select * from [table] where [condition]', {params... }, [Function])

[aikar]

this is your statement, use

stmt.run(xx, (err) => {
console.log(stmt.lastID);
});

[lancewellspring]

Perfect! That's exactly what I needed, thank you @aikar !

[aikar]

im not sure this issue needs to remain open.

db.run() with fat arrow will be an issue because it uses a temporary statement that you don't have access to, but can be solved by preparing it yourself as shown above.

only other way i can see to improve that scenario is db.run(sql, params, (err, res, stmt) => {

});

if the run callback receives the statement as a third arg.

[oludouglas]

Yeah, I also ended up using the statement from db to
. var stmt = db.prepare("INSERT INTO foo VALUES(?, ?)"); stmt.run(1, "demo", err => { # FUNCTION INSTEAD OF ARROW LAMBDA if (err) throw err; console.log(stmt.lastID); });

This works fine.

[AlanGervin]

Thank you so much. I didn't know about the db.prepare but after using the working part of your example I'm moving onto my next issue.