No returned values on INSERT

[eveningkid]

Hey Tilman,

I was surprised that by default when adding new records, there is no way to get the returned value of an INSERT query (or am I missing something?):

const db = new DB("./test.db");

db.query(
  "CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)",
);

const names = ["Peter Parker", "Clark Kent", "Bruce Wayne"];

for (const name of names) {
  const query = db.query("INSERT INTO people (name) VALUES (?)", [name]);

  // The following would fail
  console.log(query.columns());
}

Would it be possible to add this behaviour? Instead of making another request once it has been added (but not ideal).

I hope this could be added, I am sure it would be better for everyone!

Let me know if that's possible on your end, cheers :)

[dyedgreen]

Hey Arnaud,

Thank you for raising the issue! I'm afraid adding this won't work, for two reasons:

1. The SQLite statement can return column names only for select queries as far as I can tell. (Try running attached example)
2. Queries which have no resulting rows (anything that is not a SELECT, or SELECTs that don't match any rows) will automatically have the associated SQLite statement cleaned up, which allows the library to leak minimal memory-management duties to consumers.

So to get the column names, you will need to make a separate SELECT query on the table, or inter the names based on the SQL string manually 😅

import { DB } from "https://deno.land/x/sqlite@v2.0.0/mod.ts";
import { setStr } from "https://deno.land/x/sqlite@v2.1.0/src/wasm.ts";
import { Rows } from "https://deno.land/x/sqlite@v2.1.0/src/rows.ts";

const db = new DB();
db.query(
  "CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)",
);

// manually create query object to prevent auto-clean up and demonstrate column names
let stmt = 0;
setStr((db as any)._wasm, "INSERT INTO people (name) VALUES ('name')", ptr => {
  stmt = (db as any)._wasm.prepare(ptr);
});
const queryResult = new Rows(db, stmt);

// Returns an empty array, since no columns are returned
console.log(queryResult.columns());

[eveningkid]

Thanks a lot for your answer, very clear.

Will need to add this select query then :)

[dyedgreen]

You're welcome, always happy to help 😃