1

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?

Mason
  • 69
  • 7
  • Plnatescale is mysql compatible (more or less), but is not mysql. Btw, mysql does not support the returning close, so not such a surprise that an ORM using it cannot either. – Shadow Jul 21 '23 at 10:12
  • This is not problem with PlanetScale. If you check the link you will see that Drizzle ORM currently does not provide insert return functionality for MySQL. – Mason Jul 21 '23 at 10:14
  • 1
    mysql doesn't support that in bthe first palce, so an ORM that mimics MySQL functions can't do it either. Mariadb supports it, so maybe you will find there more function like you need – nbk Jul 21 '23 at 10:22
  • I have been using Prisma until now, so I didn't know that MySQL does not support insert return. Thank you for your kind and helpful information! – Mason Jul 21 '23 at 10:28

2 Answers2

1

If the Drizzle ORM does not provide a built-in insert returning function for MySQL, you can still obtain the auto-incremented ID of the newly added user using an alternative approach. Here are a couple of common solutions:

Use LAST_INSERT_ID() function: After inserting the new user record into the database, you can use the LAST_INSERT_ID() function in MySQL to get the auto-incremented ID of the last inserted record within the current session.

Example:

// Assuming you have a MySQL database connection or query execution method named "executeQuery" const result = await executeQuery(INSERT INTO users (username, email, password) VALUES ('new_user', 'new_user@example.com', 'hashed_password'); SELECT LAST_INSERT_ID() as id;);

const newUserId = result[0][0].id; // Use the "newUserId" in your JWT payload or wherever needed.

Wasif Ali
  • 26
  • 2
0

Drizzle ORM doesn't support the returning function for MySQL, but it does have a way of giving you the auto-incremented ID by using the insertId property

Example:

const userTable = await db.insert(user).values({ name: "Jorge"})


const walletTable = await db.insert(wallet).values({ userId: userTable.insertId)
jorgemanc
  • 1