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

[JorgeJimenez15]

@heethjain21, Did you managed to find a workaround?

[heethjain21]

@heethjain21, Did you managed to find a workaround?

No. I was thinking of creating a PR to fix this, but haven't gotten time yet.

@koskimas could you please point me in the direction where I should be looking to fix this problem? Referring to this comment: #1049 (comment)

I'll start once I manage to get some time.

[koskimas]

We have tests. This works. Provide a reproduction.

[heethjain21]

Strange, it worked this time.

Not sure what the issue was last time.

I wanted to adopt Kysely in our project, but didn't do it because of this error.

I tried a little more times, and it worked all of the time.

Don't remember what might have caused the issue last time.

Thanks for your comment anyways, since I was able to get it working now, so I can use it in my project then!

[tsulatsitamim]

Using retuning with query builder return undefined.

my workaround is using raw sql
const { rows: [row] } = await sql`INSERT INTO Transaction (accountId, amount, date, description, type) VALUES (${data.toAccountId}, ${data.amount}, ${data.date}, 'TRANSFER', 'DEBIT') RETURNING id`.execute(trx)