Kysely•15mo ago

MySQL Returning Statement Alternative

A while back I read online that MySQL does not support RETURNING statements at all. If that's the case how do I handle a situation in which I insert a new row and then get the inserted row back? Do I depend on the row being inserted to have some unique identifier I can use to fetch it after insertion or is there another way (in the event in which the only unique id is the primary key -- which is autogenerated)

10 Replies

koskimas•15mo ago

Dialects that don't support returning return the inserted id automatically:

const result = await db
  .insertInto('person')
  .values(values)
  .executeTakeFirst()

console.log(result.insertId)

const result = await db
  .insertInto('person')
  .values(values)
  .executeTakeFirst()

console.log(result.insertId)

but those dialects usually have no way to get anything else out of an insert query. You'll just have to run a separate select query. It's a good idea to go through the basic examples we have in kysely.dev: https://kysely.dev/docs/examples/INSERT/single-row https://kysely.dev/docs/category/examples

AdophilusOP•15mo ago

Okay, thanks 👍

AdophilusOP•15mo ago

can I still ask a question after this has been marked as solved? if so I'd like to ask that the insertId comes back as undefined

koskimas•15mo ago

Sure The type is bigint | undefined. It means the value might be undefined in some cases. For example when you use on conflict or similar statements.

AdophilusOP•15mo ago

oh, but I use uuids 🥲 autogenerated one's for that matter

koskimas•15mo ago

Then the databases don't return it It only works on autoincrementing primary keys.

AdophilusOP•15mo ago

oh wow alright I'll work around that

koskimas•15mo ago

You can generate your ids in the typescript code

AdophilusOP•15mo ago

thanks for responding okay

Igal•15mo ago

FYI, mssql doesn't support returning and currently returns undefined as insertId iirc

Gaming

Programming

MySQL Returning Statement Alternative