$returningId returning extra rows

Not sure if this is intended behaviour but with the mysql2 driver in drizzle-orm this:

console.log(await db.insert(table).values([row1, row2]).$returningId())

console.log(await db.insert(table).values([row1, row2]).$returningId())

Outputs

[
  {
    id: 1,
  }, {
    id: undefined,
  }, {
    id: 2,
  }, {
    id: undefined,
  }
]

[
  {
    id: 1,
  }, {
    id: undefined,
  }, {
    id: 2,
  }, {
    id: undefined,
  }
]

When I'd expect it to output

[
  {
    id: 1,
  }, {
    id: 2,
  }
]

[
  {
    id: 1,
  }, {
    id: 2,
  }
]

Any help is appreciated!

1 Reply

JamesOP•2mo ago

It happens when there is a secondary unique id on the table, in my case I have a publicId field:

char({
    length: NANO_ID_LENGTH,
  })
    .notNull()
    .unique()
    .$defaultFn(generateNanoId)

char({
    length: NANO_ID_LENGTH,
  })
    .notNull()
    .unique()
    .$defaultFn(generateNanoId)

Using a workaround until this is fixed:

export function getInsertedIds([
  { affectedRows, insertId },
]: MySqlRawQueryResult) {
  return Array.from(
    { length: affectedRows },
    (_, i) => BigInt(insertId) + BigInt(i)
  );
}

export function getInsertedIds([
  { affectedRows, insertId },
]: MySqlRawQueryResult) {
  return Array.from(
    { length: affectedRows },
    (_, i) => BigInt(insertId) + BigInt(i)
  );
}

Usage:

await db.insert(table).values([row1, row2]).then(getInsertedIds)

await db.insert(table).values([row1, row2]).then(getInsertedIds)

Gaming

Programming

$returningId returning extra rows