Node-sqlite3: odyssee of a noob trying to get the ID of inserted row

[barbalex]

_This may seem like bashing node-sqlite3. But I am very thankful for this tool so please take it as input to make it more accessible to new users (= noobs)._

It took me way too long to create a query that inserts a new row and returns lastID without adding any other data.

Noob-Trap Nr. 1

Coming from other databases, it is not so clear to use this syntax:

INSERT INTO
  table
default VALUES

Seems to me sqlite does not explain it's dialect of sql anywhere. I must have missed it. But that has nothing to do with node-sqlite3.

Noob-Trap Nr. 2

Then it did not seem clear to me what you have to do when you are not passing any params to db.run. Do I have to pass null? Can I simply emit params and the following ,? Seems like the last is true. The trouble here is: When you experiment with adding null, considering the other two problems (sql-syntax mentioned before and retrieving ID mentioned further down), it is hard to isolate the error that occurs.

Noob-Trap Nr. 3

Every node callback uses (error, result) signature. Every but sqlite3 in this case. Or am I understanding this wrong?

When running db.run(sql, (error, result) => {}) result is undefined.

So I searched google and read through the documentation again carefully. Hm. Data is returned in this?

So I try:

db.run(sql, (error, result) => {
  console.log(this)
})

...and get back undefined.

Now I admit that was very nooby. And finally I did realize it would have to be

db.run(sql, function (error, result) {
  console.log(this)
})

because node-sqlite3 is passing something into this. But this trouble only arises because data is returned in this which seems extremely odd to me. Any other comparable tool I have used so far (nano, craddle, pouchdb, pg, mysql) returns the new ID as part of result.

It would have been a great help if there were a simple example explaining how to insert a new row and retrieve the created ID.

Most other comparable tools add some simple examples to every part of the API. It may seem like noise to the expert. But to someone new to the tool, maybe even new to node, and expecially when things are different than what you expect, this can be VERY helpful.

[eqyiel]

Yep, one big problem with using this is that you can't use an arrow function as the callback (since they lexically bind the this value).

[lvivier]

@barbalex SQLite's documentation actually has a section called SQL As Understood By SQLite. It's comprehensive and has nice diagrams.

As an alternative to Database#run(), you can use Database#prepare() to get a Statement:

let stmt = db
  .prepare(sql)
  .run(err => console.log('row:', stmt.lastID))

In practice, if you're inserting many rows it's faster to prepare a statement once and reuse it with different parameters than have SQLite prepare a new statement for each row.

[barbalex]

@lvivier thanks!

[rickhall]

Regarding Noob-Trap Nr. 2...

It is fairly common practice to use square brackets to indicate optionality, which is what the documentation does like so:

Database#run(sql, [param, ...], [callback])

The square brackets tell you that both the potential list of parameters and the callback is optional. Technically, you might argue it should be something like this:

Database#run.(sql [,param, ...] [,callback])

Since the commas only are necessary if the parameter is supplied, but I guess there is an assumption that you should know you wouldn't include the comma without supplying a parameter.