[SOLVED] Drizzle ORM not support Insert Returning

Drizzle ORM not support Insert Returning

I have a question while working with Drizzle ORM and MySQL.

Currently, Drizzle ORM does not provide insert returning function for MySQL. Check this link.

My website adds users to the database and issues JWT tokens when they sign up. Since the payload of the JWT must include the id of the newly added user, it is essential to know the id of the user that was just added.

In this case, how do I get the id which is an auto-incrementing integer for the record I added?

Solution

The best solution is to parse the ResultSetHeader:

await this.db.insert(users).values({"login": "xxxx"})

returns insertId :

[
  ResultSetHeader {
    fieldCount: 0,
    affectedRows: 1,
    insertId: 33,
    info: '',
    serverStatus: 2,
    warningStatus: 0,
    changedRows: 0
  },
  undefined
]

Edit: Since version 0.32.0 you can use the $returningId() function and automatically receive all inserted IDs as separate objects :

const result = await db.insert(users).values([{ name: 'John' }, { name: 'John1' }]).$returningId(); 
//    ^? { id: number }[]