MySQL `insertId` is undefined after executeTakeFirstOrThrow()

[Dan1ve]

I'm trying to transform the following SQLite statement into the corresponding MySQL expression:

SQLite:

const insertedId = await this.db
    .insertInto('Vehicle')
    .values(vehicle)
    .onConflict((builder) => builder.doNothing())
    .returning('Vehicle.vehicleId')
    .executeTakeFirst();

I've replaced onConflict() with ignore() and removed returning() , as both of them aren't supported by the MySQL dialect. So now I have

MySQL:

const insert = await this.db.insertInto('Vehicle')
            .values(vehicle)
             .ignore()
             .executeTakeFirstOrThrow();
const insertedId  = insert.insertId;

My understanding is that insert.insertIdshould be the id of the inserted entry, but it is undefined .

Is there another way to retrieve the ID of the inserted entry? Re-querying it directly after the insertion is quite cumbersome.

Further details:

I'm using Prisma and prisma-kysely , if this is relevant here.

The vehiceId field is defined as follows in the schema.prisma :

vehiceId    String       @id @default(dbgenerated("(uuid())"))

PS: Thank you for creating such a nice library 🙌🏼

[Dan1ve]

Closed, likely a duplicate to #316 :

InsertId is irrelevant and may not be available when the table's primary key is not an auto increment column.

Also, the docs state:

This property can be undefined when the query contains an on conflict

Still a little bummer that the original query has to be split up into two, but oh well...

[koskimas]

Those are not our limitations but MySQL's. Feel free to take this up to MySQL.

[heethjain21]

Those are not our limitations but MySQL's. Feel free to take this up to MySQL.

Not sure if I understand the workings under the hood, but I do get a insertId when using mysql2, so maybe not a MySQL limitation

This is the response from mysql2:

    { rows: [], insertId: '0', rowsAffected: 1 }

And in Kysley, I get this:

InsertResult { insertId: undefined, numInsertedOrUpdatedRows: 1n } 

The docs mention this:

This property can be undefined when the query contains an on conflict clause that makes the query succeed even when nothing gets inserted.

In my case, there is

• no conflict
• the primary key has an auto increment param

So maybe not an issue with MySQL, something else