.

[undone cove]

You'll need to use the sqlite RETURNING clause

import { Database } from "bun:sqlite";

// generate some data
let db = new Database(":memory:");
db.run(
  "CREATE TABLE foo (id INTEGER PRIMARY KEY AUTOINCREMENT, greeting TEXT)"
);
const query = await db.query(
  "INSERT INTO foo (greeting) VALUES ($greeting) RETURNING id;",
  {
    $greeting: "Welcome to bun!",
  }
);

console.log("running");
console.log(await query.get());
// { id: 1 }
console.log(await query.all());
// [{ id: 1 }]
console.log(await query.values());
// [[1]]
console.log(await query.run());
// undefined

.run() always returns undefined, that's intentional

but .values() returning 1 is strange, I'll open an issue

[warm mango]

https://github.com/oven-sh/bun/issues/5872

[lofty thorn]

I asked this before and still wonder what the answer to this is. It's honestly really inconvenient. @undone cove @night schooner

[undone cove]

Makes sense

We'll look into it

[paper spade]

Sorry to reopen a "closed" thread, but i think the answer to the original question would be to run another query to SELECT last_insert_rowid()

db.query("INSERT INTO counts DEFAULT VALUES;").run();
const newId = db.query("SELECT last_insert_rowid()").get()["last_insert_rowid()"];

I don't have any previous experience with sqlite or better-sqlite3, so this probably is just a workaround for something the api should implement.

[lofty thorn]

RETURNING like Colin says is probably better because if you run two queries like this you need to make sure that no other queries run in between (from another request for example).

[undone cove]

yeah this approach is a dangerous unless run in a transaction
but this is why RETURNING exists

you could do this with CTEs as well, it's a bit more readable

WITH new_product AS (
  INSERT INTO Product (name, description, price)
  VALUES (:name, :description, :price)
  RETURNING id
)
SELECT id FROM new_product;