drizzle-team / drizzle-team/drizzle-orm

[BUG]: $returningId returns the wrong ids when onDuplicateKeyUpdate is used

Open
#3,912 4 comments 4 reactions 0 assignees View on GitHub
bug db/mysql qb/crud
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.