Drizzle Team•2y ago

insertID not returned correctly when using custom UUID

export const users = mysqlTable("users", {
  id: varchar("id", { length: 36 })
    .primaryKey()
    .default(sql`(uuid())`)
    .notNull(),
  email: varchar("email", { length: 255 }).notNull(),
  profileImageUrl: varchar("profile_image_url", { length: 255 }),
  name: varchar("name", { length: 255 }).notNull(),
});

export const users = mysqlTable("users", {
  id: varchar("id", { length: 36 })
    .primaryKey()
    .default(sql`(uuid())`)
    .notNull(),
  email: varchar("email", { length: 255 }).notNull(),
  profileImageUrl: varchar("profile_image_url", { length: 255 }),
  name: varchar("name", { length: 255 }).notNull(),
});

I have a schema where I have set the ID to be a uuid, this works fine and inserts give me the correct ids. On insert I want to get the ID of the inserted record so I can redirect to the correct page, which I thought would be available on insertId but what I get returned from there is just a number. Is there another way to get the actual id of the inserted record?

3 Replies

Dan•2y ago

I think insertId only works for integer IDs. Not sure if there's a way to do that in MySQL. Maybe possible with transactions, but not sure. You can research how to do it with MySQL in general.

Andrii Sherman•2y ago

It's not possible to do it with MySQL insertId is returned only for auto_increment integer fields

focused_morning•2y ago

so, we solved this by using a transaction and creating the uuid before the data calls that needed to be related by it. example: await db.transaction(async (tx) => { const userId = uuidv4() await tx .insert(userSchema) .values({ id: userId, username: walletAddress, walletAddress: lowerCaseWalletAddress, }) .execute() await tx.insert(walletSchema).values({ id: uuidv4(), address: lowerCaseWalletAddress, userId, }) })

Gaming

Programming

insertID not returned correctly when using custom UUID