The RETURNING clause with explicit column names keeps extra quotes around

[leafac]

Consider the following example:

const Database = require("better-sqlite3");
const db = new Database(":memory:");
db.exec(
  `CREATE TABLE "users" ("id" INTEGER PRIMARY KEY, "name" TEXT NOT NULL);`
);
console.log(
  db
    .prepare(`INSERT INTO "users" ("name") VALUES (?) RETURNING "id"`)
    .get("Leandro Facchinetti")
); // => Actual: { '"id"': 1 } / Expected: { id: 1 }

The current workaround is to not quote the table name, for example:

console.log(
  db
    .prepare(`INSERT INTO "users" ("name") VALUES (?) RETURNING id`)
    .get("Leandro Facchinetti")
); // => { id: 1 }

I believe that in general the output of a RETURNING clause should match the output of a SELECT clause, for example:

console.log(db.prepare(`SELECT "id" FROM "users"`).get()); // => { id: 1 }

Note that the issue also applies to things like RETURNING "users"."id" and so forth.

[Prinzhorn]

First Google DuckDuckGo result https://sqlite.org/forum/forumpost/033daf0b32
It appears there's nothing better-sqlite3 can do unless I'm missing something, I've only peeked into it (I mean it could technically call sqlite3Dequote I guess).

Can you reproduce this in the sqlite cli?

[leafac]

Thanks for looking into this.

I’m not exactly sure what you mean by “Can you reproduce this in the sqlite cli?” But I tested the following on the command line and it behaved like I expected:

$ sqlite3 --version
3.36.0 2021-06-18 18:36:39 5c9a6c06871cb9fe42814af9c039eb6da5427a6ec28f187af7ebfb62eafa66e5

$ sqlite3
sqlite> .mode json
sqlite> CREATE TABLE "users" ("id" INTEGER PRIMARY KEY, "name" TEXT NOT NULL);
sqlite> INSERT INTO "users" ("name") VALUES ('Leandro Facchinetti') RETURNING "id";
[{"id":1}]
sqlite> INSERT INTO "users" ("name") VALUES ('Alexander Prinzhorn') RETURNING id;
[{"id":2}]

Note how the quotes around id in the RETURNING clause don’t affect the output.

[Prinzhorn]

root@1145d93742fb:/# sqlite3 
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .headers on
sqlite> CREATE TABLE "users" ("id" INTEGER PRIMARY KEY, "name" TEXT NOT NULL);
sqlite> INSERT INTO "users" ("name") VALUES ('Leandro Facchinetti') RETURNING "id";
"id"  <=====================================================================================
1
sqlite> INSERT INTO "users" ("name") VALUES ('Alexander Prinzhorn') RETURNING id;
id
2
sqlite> SELECT "id" FROM "users";
id
1
2
sqlite> 

I don't know what .mode json does internally, but it seems the function SQLite offers to get the name of the column does not remove the quotes for RETURNING.

[Prinzhorn]

Yeah, you definitely don't want to rely on .mode json and compare it to the keys that better-sqlite3 returns.

For some reason this has the double quotes.

sqlite3
sqlite> .mode json
sqlite> CREATE TABLE "users" ("'" INTEGER PRIMARY KEY, "name" TEXT NOT NULL);
sqlite> INSERT INTO "users" ("name") VALUES ('Leandro Facchinetti') RETURNING "'";
[{"\"'\"":1}]

sqlite3
sqlite> .headers on
sqlite> CREATE TABLE "users" ("'" INTEGER PRIMARY KEY, "name" TEXT NOT NULL);
sqlite> INSERT INTO "users" ("name") VALUES ('Leandro Facchinetti') RETURNING "'";
"'"
1
sqlite> SELECT "'" FROM "users";
'
1

[JoshuaWise]

The column names returned by better-sqlite3 are pulled directly from SQLite itself, so if you don't like the behavior, you'll have to take it up with them. As @Prinzhorn pointed out, there's already an issue for this on the SQLite forum.

[leafac]

Sounds good. Thanks for all the information 😀