drizzle-team / drizzle-team/drizzle-orm
[BUG]: $returningId returns the wrong ids when onDuplicateKeyUpdate is used
- Dominant language
- TypeScript
- Stars
- 35.8k
- Forks
- 1.6k
- Avg merge
- 2d 7h
- Merged PRs (30d)
- 4
Description
### Report hasn't been filed before.
- [X] I have verified that the bug I'm about to report hasn't been filed before.
### What version of `drizzle-orm` are you using?
0.38.3
### What version of `drizzle-kit` are you using?
0.30.1
### Other packages
_No response_
### Describe the Bug
If i do the following:
```
const values: (typeof phones.$inferInsert)[] = Array.from(
new Set(
Array.from(entities.values()).map((entity) => ({
idClient,
countryCode: entity.countryCode ?? undefined,
phone: entity.phone ?? undefined,
extension: entity.extension ?? undefined,
}))
).values()
);
this.orm
.insert(phones)
.values(values)
// ? This prevents MySQL throwing errors on duplicated keys.
.onDuplicateKeyUpdate({set: {phone: sql`phone`}})
.$returningId()
```
The $returningId will return the correct ids of the actual inserts, but will return a wrong id (I believe it is the row index in the table of the updated row, rather than its primary key) of the ones it handles with
```
.onDuplicateKeyUpdate({set: {phone: sql`phone`}})
```
This is the phones schema:
```
import {sql} from "drizzle-orm";
import {
int,
mysqlTable,
timestamp,
unique,
varchar,
} from "drizzle-orm/mysql-core";
export const phones = mysqlTable(
"phones",
{
id: int("id").autoincrement().primaryKey(),
countryCode: varchar("countryCode", {length: 10}).default(""),
phone: varchar("phone", {length: 20}).default(""),
extension: varchar("extension", {length: 50}).default(""),
type: varchar("type", {length: 50}),
createdAt: timestamp("created_at", {mode: "string"})
.defaultNow()
.notNull(),
updatedAt: timestamp("updated_at", {mode: "string"}) // Tracks updates
.onUpdateNow()
.notNull(),
},
(table) => [
// primaryKey({columns: [table.id]}),
unique("UNQ_PHONES::CC_PHONE_EXTENSION").on(
table.countryCode,
table.phone,
table.extension
),
]
);
```
This can be easily tested following like this:
1. Create the table and create new entries (run the query once)
2. Delete the table entries
3. Execute the insert query twice to insert the same entities 2 times, the second one leading to the use of the onDuplicateKeyUpdate
> (Steps 1 and 2 are required to make sure that the ids of the entries don't match the row number because of the autoincrement property)
By comparing the same query executed twice, i get the following results:
```
[Nest] 14313 - 07/01/2025, 11:52:27 VERBOSE [console] Returning IDs
[ { id: 22659 }, { id: 22660 }, { id: 22661 }, { id: 22662 }, { id: 22663 } ]
[Nest] 14313 - 07/01/2025, 11:53:11 VERBOSE [console] Returning IDs
[ { id: 0 }, { id: 1 }, { id: 2 }, { id: 3 }, { id: 4 } ]
```
Contributor guide
Assessment
This issue has not been assessed yet.