1

I got a Node script with an Express server, im trying to insert a new entry into a Table. If got folloing setup:

db.run('CREATE TABLE lists(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)');
...
db.get(`INSERT INTO lists (name) VALUES (?) RETURNING *;`, [name], (err, row) => {
        ...
    });

And the Insert statement throws:

Error: SQLITE_ERROR: near "RETURNING": syntax error
--> in Database#get('INSERT INTO lists (name) VALUES (?) RETURNING *;', [ 'Testing' ], [Function (anonymous)])

but this should be working according to the sqlite wiki

any advise?

Sifi_crafter
  • 99
  • 1
  • 7
  • What version of Sqlite are you using? (The Sqlite engine version, not the npm package version) – Dai Nov 02 '21 at 15:51
  • My package.json states: ``` "sqlite3": "^5.0.2" ``` – Sifi_crafter Nov 02 '21 at 15:54
  • sqlite> select sqlite_version(); 3.36.0 @Dai – Sifi_crafter Nov 02 '21 at 15:56
  • The version `3.36.0` you see is from the Sqlite command-line interface, that's not the same version of Sqlite that's being loaded into NodeJS. – Dai Nov 02 '21 at 15:58
  • if i run the command with nodeJs the verison ist 3.34.0 – Sifi_crafter Nov 02 '21 at 16:01
  • There's your problem. You need Sqlite version **3.35** to use `RETURNING`. – Dai Nov 02 '21 at 16:04
  • Ok thanks how do i specify the version of the SQLite Version is there an option to specify it? @Dai – Sifi_crafter Nov 02 '21 at 16:09
  • See my answer. Assuming you have `sqlite3` version `5.0.2` configured (check your `package.json` file) then you likely have a corrupt npm package cache or some older files sticking around, in which case that's a different problem you should post a new question for. – Dai Nov 02 '21 at 16:18

1 Answers1

2

  • Support for the RETURNING clause was added in Sqlite 3.35.

  • While you have Sqlite 3.36 installed in your operating system's environment, it seems your Node environment has an older version of the sqlite3 npm package which has Sqlite 3.34.

  • On GitHub, as of early November 2021, the sqlite3 package version 5.0.2 for npm was released in February 2021 with an older version of Sqlite.

Dai
  • 141,631
  • 28
  • 261
  • 374
  • Thanks I see the problem but i can't find where my npm is not working even after a npm cach verify and npm update the sqlite engine is still 3.34 any advise? – Sifi_crafter Nov 02 '21 at 16:22
  • @Sifi_crafter I have no further advice, sorry. – Dai Nov 02 '21 at 16:26
  • The engine version for sqlite3 is not updated in the release v5.0.2 because the commit you showed is from 25 days ago but the release is from february – Sifi_crafter Nov 02 '21 at 16:28
  • @Sifi_crafter Whoops, sorry, I clicked the wrong link in the GitHub release page - I thought I was looking at "commits **in** this release" - I was actually looking at commits **after** that release. – Dai Nov 02 '21 at 16:29