#sqlite get resulting identifiers from insert statement

1 messages · Page 1 of 1 (latest)

lilac remnant
#

If I run the following sqlite will auto increment a user id for me.
Now how do I get back that id using bun:sqlite?

import { Database } from 'bun:sqlite';

const database = new Database(':memory:');

database.run('create table user (id integer not null unique primary key, username varchar(32) not null unique);');
const user = database.run(`insert into user (username) values ('simylein');`);

// get access to the created user id somehow?
console.log(user?.id); // undefined

const users = database.prepare('select * from user;').all();
console.log(users); // [{id:1, username:'simylein'}]
carmine harness
#

i think run always returns undefined so you could use a transaction.

it's messy but something like this:

const insert = database.prepare("INSERT INTO user (username) VALUES ($username)");

const insertUser = database.transaction(usr => {

    insert.run(usr);

    return database.query(`select id from user where username = $user;`).get({ $user: usr });

});

const user = insertUser('simylein');

console.log( user );

this could work 😄

lilac remnant
#

Yeah I know, I can select it again immediately after the insert, I just hoped there would be a better way, thank you 🙂

ashen arch
#

I think there's a WITH RETURNING

carmine harness
#

yes i tried the RETURNING * ... but the bun database.run() always returns a undefined.

Maybe i missed something.

ashen arch
#

or you want .get()

#

.run() is when you don't care about the return value

carmine harness
#

haha 😄

easy things can be hard ^^

const user = database.query(`insert into user (username) values ($username) RETURNING *`);

console.log(user.get({$username: 'simylein'}));

@lilac remnant this works

Thanks to @ashen arch 💪