CODEWITHSUNDEEP
mysqltypescriptsql-insertdrizzle
user20231989
user20231989

Reputation:

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?

Upvotes: 6

Views: 13336

Answers (6)

Johaven
Johaven

Reputation: 16

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 }[]

Upvotes: 0

chris
chris

Reputation: 41

According to the Drizzle Docs

await db.insert(users).values({ name: "Dan" }).returning();

// partial return
await db.insert(users).values({ name: "Partial Dan" }).returning({ insertedId: users.id });

This will return an array of inserted rows. To get a single value, you could:

const newUser = await db.insert(users).values({ name: "Partial Dan" }).returning({ insertedId: users.id });

return newUser[0];

EDIT: This is for PostgreSQL and SQLite and is NOT SUPPORTED on MySQL. Apologies as I missed that earlier.

Upvotes: 4

user3271026
user3271026

Reputation: 1

I'm using drizzle 0.30.10 with MySql and supports insertId in the returned object.

const newUser = await db.insert(schema.users).values({ email: email, username: username, password: password })
return newUser[0].insertId;

Upvotes: 0

Vinny
Vinny

Reputation: 819

The accepted answer doesn't seem to be right, this is the way to access the inserted ID as of Drizzle 0.30.7.

const newUser = await db.insert(schema.users).values({ email: email, username: username, password: password })
return newUser[0].insertId;

Upvotes: 2

jorgemanc
jorgemanc

Reputation: 71

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)

Upvotes: 7

Wasif Shahid
Wasif Shahid

Reputation: 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', '[email protected]', 'hashed_password'); SELECT LAST_INSERT_ID() as id;);

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

Upvotes: 0

Related Questions